Bot: Send an e-mail with a table at 0900hrs Monday, every week

AppSheet AppSheet Q&A
, ,
1

Hi folks,

I’ve got a table that has been constructed via Google Sheets to act as a summary data table. It has two columns: Size and Number. Looks something like this:

Size Number
18 2
17 45
21 4

The data is dynamic, peeling out from Google Sheets.

This sheet is pulled directly into my app and i’d like to send something very much like the layout above in an e-mail saying:

"Hi Warehouse team,

Here is a report on the sizes and numbers required:

Size Number
18 2
17 45
21 4

Bw,

Me"

This is how i’ve started to set it up:

dhdevans_1-1675098880859.png
dhdevans_1-1675098880859.png1454×677 52.4 KB

dhdevans_0-1675098853976.png
dhdevans_0-1675098853976.png1436×766 57.3 KB

In ‘E-mail Body’ i’m entirely unsure how to construct the HTML/variables required to report all the data. in the table.

Can someone help me with this? Seems a simple enough thing to do but i’m getting very frustrated!

2

Hi @dhdevans

Did you try generating a template documents with AppSheet? That may help you a lot without having to any HTML template.

Aurelien_0-1675099400556.png
Aurelien_0-1675099400556.png376×428 17.2 KB

Aurelien_1-1675099417212.png
Aurelien_1-1675099417212.png400×246 10.3 KB

Aurelien_2-1675099432782.png
Aurelien_2-1675099432782.png391×302 12.4 KB

For reference:

Sample email templates - AppSheet Help

3

Well, there’s a feature i didn’t know existed!

I’ve tried setting up a template like below to just test getting a table into the e-mail:

dhdevans_1-1675159288761.png
dhdevans_1-1675159288761.png923×274 3.24 KB

I save this and the app but the preview e-mail is blank. Is my syntax bad here? For reference, the [Size/Gender] column is text type and [No.] column is, surprisingly, a number.

4

I’m glad you learnt something new then :slightly_smiling_face:

Preview email is not fully accurate, I don’t use it a lot.

I prefer clicking on “run” or “test” button attached to the top bar of the bot :slightly_smiling_face:

Aurelien_0-1675161423866.png

Please note it may have run empty is there is no data to write for the specific record you are evaluating.

5

OK, so yet another new feature for me! I appreciate that, thank you.

Using the template above does not generate an e-mail at all. I tested with it, then without it (stuck a ‘HELLO WORLD’ in as the body) - e-mail sends me HELLO WORLD which is reassuring, but leads me to think something is broken in my syntax for the variables.

Took a different tack by clearing out the template and plugging the following in the ‘Email Body’ field:

<<Start: [Size/Gender]>>
<>

That… was a mistake. You know that programmers song 99 bugs in the code? Yeah. I get it.

dhdevans_0-1675172708388.png
dhdevans_0-1675172708388.png433×317 19.3 KB

OK, so what in the fresh hell is this? As mentioned before the column type for ‘Size/Gender’ is text, and it’s just… a column of different text categories.

Any ideas?

6

:rofl:

Start is expecting a list of reference values of another table. For example, let’s say you have an order (main table) and order items (child table).

Most of time, it’s like this:

<<Start:[Related ChildTableNames]>> your expression <<End>>

For reference:

Use Start expressions in templates - AppSheet Help

7

So, this is just one table with no relationships to any other table at all - seems like i might be overcomplicating it.

Is there a way that you know to simply reproduce the table in the e-mail?

8

it all depends on your table structure.

It may be a very simple table, with no start nor end syntax if [Size/Gender] and [No.] are one of the columns of your table.

I think this is the case?

9

That’s what i was thinking, i should be able to slap down a table with <<[Size/Gender]>> in one cell and <<[No.>> in another but it only produces one of the records in the e-mail. And then it sends 65 individual record e-mails…

10

Just to see more clearly, would you mind sharing a screenshot of your tables’ structure? (from your app editor)

11

dhdevans_0-1675420365923.png
dhdevans_0-1675420365923.png1403×314 17.4 KB

No worries!

12

To add more fuel to the frustrating fire I’ve managed to wangle an expression that produces the first record in the table:

<<[_THISROW].[Size/Gender]&" - "&SUM(SELECT(Picklist Summary Table[No.],([Size/Gender]=[_THISROW].[Size/Gender]),FALSE))>>

This throw back the first row only. So, getting there i guess?

13

Hi @dhdevans

From your first post, I deducted the email is supposed to be sent to a warehouse.

Would you mind adding a “warehouse” table, and a “warehouse” column in your current Sheets that will be of type Ref, source table “Warehouse”?

The idea is to establish a correct parent/child relationship and to make your email to fire properly.

This is my suggestion, for instance:

References between tables - AppSheet Help

14

I think I see what you’re getting at, but want to point out that this table is standalone with zero relationships to any other table in the app. All the calculation is being performed in Google Sheets behind the scenes.

I may be wrong, but to me it seems that creating an extra table to establish a parent/child relationship seems like creating more work and relationships that don’t exist?

15

I juste realized something:could you try this expression?

size num
<Start:YourTableName[Size/Gender]><<[Size/Gender]>> <<[No.]>><>

(with no line break)

16

Honestly, i figured the above would be a solution but also considered my syntax was crap. Tried several iterations of the above previously but it shows a blank e-mail in preview and when i fire the bot as a test it doesn’t send anything.

I’m absolutely perplexed!

17

Found a workaround, but not happy with how convoluted it is.

Created a helper column in the data set combining [Size/Gender] and [No.] ([Size/Gender]&" - "&[No.]) which presents the data as a single string (e.g. 10in - Female - 2). I hate it taking away the dynamics of calculating a value but… whatever. It stinks and I can’t see another solution.

Using this new string (named [Combo]) I’ve put together a template that allows me to call each row of the report individually:

<<INDEX(SPLIT((SELECT(Picklist Summary Table[Combo], TRUE,FALSE)), " , "),1)>>

This is absolutely MacGyvered bollocks, but it works. The SELECT statement grabs all the values from [Combo] as a single string separated by commas, the SPLIT statement snips them into individual values, the INDEX command grabs each row according to the number (in bold and underlined above) at the end of the command.

The issue with this is that the above command has to be copy pasted as many times as there are rows. Now, as my report is dynamic (e.g. each week there might not be 20 rows in the report) I have copy pasted it 40 times to be safe. So the template looks like this:

Report<<INDEX(SPLIT((SELECT(Picklist Summary Table[Combo], TRUE,FALSE)), " , "),1)>><<INDEX(SPLIT((SELECT(Picklist Summary Table[Combo], TRUE,FALSE)), " , "),2)>>



<<INDEX(SPLIT((SELECT(Picklist Summary Table[Combo], TRUE,FALSE)), " , "),3)>>


<<INDEX(SPLIT((SELECT(Picklist Summary Table[Combo], TRUE,FALSE)), " , "),4)>>


<<INDEX(SPLIT((SELECT(Picklist Summary Table[Combo], TRUE,FALSE)), " , "),5)>>


<<INDEX(SPLIT((SELECT(Picklist Summary Table[Combo], TRUE,FALSE)), " , "),6)>>


<<INDEX(SPLIT((SELECT(Picklist Summary Table[Combo], TRUE,FALSE)), " , "),7)>>


<<INDEX(SPLIT((SELECT(Picklist Summary Table[Combo], TRUE,FALSE)), " , "),8)>>


<<INDEX(SPLIT((SELECT(Picklist Summary Table[Combo], TRUE,FALSE)), " , "),9)>>

etc. which results in blank rows in the e-mail.

It does what it needs to do but it’s absolutely barbaric in its conduct and in no way neat or tidy.

Answers on a postcard about how to crib the above to only grab the rows in the report…

18

You had to pull your hair out to do it :rofl:

Small improvement suggestion: you can try replacing this:

<<INDEX(SPLIT((SELECT(Picklist Summary Table[Combo], TRUE,FALSE)), " , "),1)>>

with this:

<<INDEX(Picklist Summary Table[Combo],1)>>