Schedule daily consolidated email/file based on condition

[Nishit]

Hello anyone reading 

I have made an app that allows users to make modifications to a text field. If user makes a change then a column which monitors changes in the sheet gets updated by a 'ChangeTimeStamp'

Now I need a daily file/email that consolidates all the changes that happened in the last 24 hrs in the entire app.

I have been semi-successful in this pursuit as I have been able to send emails for individual updates using scheduled event and 'ForEachRowInTable' but I want to do have a single email/file. Currently, I am bombarded with multiple emails (one for every change)

TIA

[Aurelien · Accepted solution]

Perfect. Remove any libe break. 

Like this:

Intent	Utterance	Response
<<Start: FILTER("Master Sheet",AND((TIMENOW() - [_THISROW].[Change Time]) < "024:00:00",(TIMENOW() - [_THISROW].[Change Time]) > "000:00:01",ISNOTBLANK([_THISROW].[Change Time])))>><<[Intent]>>	<<[Utterance]>>	<<[Response]>><<End>>

[Aurelien]

Hi @Nishit 

So you would need to send a report, running on a table.

I suggest you have a look to this documentation:

Use Start expressions in templates

More specifically, this section may interest you:

[Nishit]

Hi @Aurelien 

Thank you for the documentation. I looked through it and instead of Select() I decided to use Filter() as I want to send data to a xlsx and want to send all columns from the table but my automation is not running.

I have put the event as change in the 'Master Sheet' and asked the bot to run a task to create a file.

My expresssion in the first cell of the template is as follows:

<<Start: FILTER(
"Master Sheet",AND(
(TIMENOW() - [_THISROW].[Change Time] < "24:00:00"),
(TIMENOW() - [_THISROW].[Change Time] > "00:00:01"),
(ISNOTBLANK([_THISROW].[Change Time])
)
)>><<[Intent]>>

All the other cells contain of the template contain just the <<column name>>. Does my expression seem right to you?

[dbaum]

It sounds like you want to use a Scheduled automation instead of triggering the automation by a data change. 

https://support.google.com/appsheet/answer/11432969

[Nishit]

Hi @dbaum 

That was my first try as well but I want to schedule not just basis time but an additional criteria as well that I want the table to contain only those rows in which change time is within the last 24 hours.

How can we do it using scheduled reporting?

[dbaum]

You're getting far more experienced guidance from @Aurelien. Nonetheless, in case it's helpful: Maybe you can accomplish what you need by entering in the automation event's Condition property an expression that returns "only those rows in which change time is within the last 24 hours":

[Aurelien]

<<Start: FILTER(
"Master Sheet",
  AND(
(TIMENOW() - [_THISROW].[Change Time]) < "024:00:00",
(TIMENOW() - [_THISROW].[Change Time]) > "000:00:01",
ISNOTBLANK([_THISROW].[Change Time])
)
)>><<[Intent]>>

I added/moved/removed a few parenthesis and additional "0" for duration.

Not sure that will work, but it should do the job 👍

Do not forget the final <<End>> after your last value.

[Nishit]

Still not working. And I did put the <<end>> tag too

Intent	Utterance	Response
<<Start: FILTER( "Master Sheet", AND( (TIMENOW() - [_THISROW].[Change Time]) < "024:00:00", (TIMENOW() - [_THISROW].[Change Time]) > "000:00:01", ISNOTBLANK([_THISROW].[Change Time]) ) )>><<[Intent]>>	<<[Utterance]>>	<<[Response]>><<End>>

[Aurelien · Accepted solution]

Perfect. Remove any libe break. 

Like this:

Intent	Utterance	Response
<<Start: FILTER("Master Sheet",AND((TIMENOW() - [_THISROW].[Change Time]) < "024:00:00",(TIMENOW() - [_THISROW].[Change Time]) > "000:00:01",ISNOTBLANK([_THISROW].[Change Time])))>><<[Intent]>>	<<[Utterance]>>	<<[Response]>><<End>>

[Nishit · Accepted solution]

@Aurelien 

It worked yet it didn't.

With this change I am able to create a report but the generated report is having an error.

Intent	Utterance	Response
<<templateReference:id=1>>

Searching for "templateReference:id=1", I find hits in this forum saying the reason for this is that there's no data which matches the filter condition.

After a lot of hit and trial the following did work

<<Start: FILTER("Master Sheet",AND((TIMENOW() - [Change Time]) < "24:00:00",(TIMENOW() - [Change Time]) >= "00:00:00",ISNOTBLANK([Change Time])))>><<[Intent]>>

So now I can schedule a daily file that contains those rows which changed in the last 24 hours.

Thanks for the help