Bot to add rows to tables on a schedule

[Robbo]

I have been using bots to send emails on regular schedule without trouble. I now need to work out how to add a few rows to a few tables on a schedule,  before I send an email. I know how to start building the Bot and schedule an event. But I am lost when I try to add a step and select Run a Data Action. Can anyone tell me what to do when I get to the following step? It doesn't seem to allow me to configure a New step. 

[Bethel_Vasto]

You should be able to configure the action in the right side sidebar . If you don't see it, look for a tiny arrow on the right hand side of your screen and click on it, it should make the sidebar visible.

[Robbo]

That's what I thought but the screen below shows the sidebar is blank. Whereas when I select Run a Task it shows the options

[krupan-google · Accepted solution]

The Task View for Data action is disabled as there is no data change table associated with a scheduled bot. We plan to address this in the upcoming release. You could instead use the "Data Change Trigger" to configure "Data Action" task and then switch the bot to a scheduled event.

[Robbo]

Sorry, but I don't understand what you're suggesting.

I tried creating a Bot with an Event that should respond to any changes to the Invoice table and a step that runs a data action to add a record to my table. When I saved it, I had no errors. Then I changed the Event to a schedule and got the following error message. 

"The event and process of the 'New bot' Bot are not compatible. The output of event 'New event' and the input of the process 'Process for New bot - 1' must be the same table or 'None' in the case of a scheduled event that is not 'For Each Row'. No "Call Process for each row" step is allowed in the process when using scheduled event"

[Robbo]

Got it. Figured it out. Thanks

[TedM]

@ Robbo
Could you explain how you did this as I am in the same situation.

🙄

[Giane_Mosquera]

Hello @krupan-google . Is the upcoming release due soon? 

[Aadam]

Hi @krupan-google will this still be implemented? 

[Robbo]

Hi TedM,

I struggled with that one a while and ended up finding a solution that works, but I wouldn’t call it elegant. However since I only need it to run once a month, and need it to do very little work, I didn’t spend a lot of time making it elegant.

My solution was to use a table called Monthly Invoices that has details of the charges I need to raise monthly.

I then created a Bot with an event that is triggered monthly, in my case on the 21st of the month at 1:00 AM For Each Row in my Monthly Invoices table

The Bot runs a Data Action that adds 1 row rows to my Invoices table for each row in the Monthly Invoices table. This becomes the invoice header. These invoice records have a column called [Monthly Invoice Id] which I use in the next bot to identify monthly invoices that need Line Items added.

I then run a second Bot an hour later, 2:00AM, against the Invoices table using a filter: AND(ISNOTBLANK([Monthly Invoice Id]) ,[Invoice Date]=TODAY()). It adds the Line Items, then triggers a recalculation of totals, and emails the invoices

It works for me. Hope I've explained adequately

[Aadam]

I had a similar challenge I wanted to send a report every 3 days which only sends new data, so once sent will not send previous data. 

I already had a table called reports which added a row with datetime everytime I manually sent the report so to automate on schedule I just added rows into the Google sheet table and added 3 days to each row

and then the FILTER for EACH ROW IN TABLE formula was like this

DATE([DATE])=TODAY()

And used the =LOWER(DEC2HEX(RANDBETWEEN(0, 4294967295), 8)) formula in the spreadsheet to assign a unique id to each row, then sync app to ensure all the rows are there, its also not pretty but it works. I just set the schedule to check every day at 08:00am if it is finds a row with DATE=TODAY() then it fires action and sends the report, so the schedule is essentially predetermined by your spreadsheet. 

[Robbo]

Just curious. You said you "used the =LOWER(DEC2HEX(RANDBETWEEN(0, 4294967295), 8)) formula in the spreadsheet to assign a unique id to each row". Couldn't you use UniqueId()?

[Aadam]

I don’t think uniqueid() works directly in Google Sheets, I stand corrected though…Have not tested. I use the other formula to generate the same kind of unique id you get in appsheet when working directly in the google sheet, so in this instance I was adding/pre-populating rows to appsheet in the sheet itself not via the app, this was for me the quickest way to add the dates I wanted my reports to be sent on as it was a copy and drag exercise, but from within appsheet the user can still fire a report or change the date of any of the planned dates from within the app. 

Hoping appsheet or google will add the feature for action with schedule at some stage to avoid all that. 

[Robbo]

Sorry, I missed the point that you use it directly in Google Sheet. Clever approach