Speed, Performance or Limitation of BOTS and AUTOMATION

[NOA-Alex]

Dear All,

I am trying to understand the speed, performance or limitations of BOTS and AUTOMATION.

GoogleSheet formulas update easily 10k rows within seconds. But those formulas show up in AppSheet as “Sync to compute” till synced. Even the value will remain the same as before the edit.
Using Virtual Columns would move those (simple) calculations to AppSheet. But might cause performance issues once there are thousands or ten thousands of rows (with many virtual columns).

So I would prefer to use AppSheet formulas. But they re-calculate only if the row gets updated. So for some updates (like summary or income and expense per project or client) I would like to run a bot once per day or once per week to update the time stamp on all active projects or clients so that they pull the latest date out of all recorded payments or invoices.
I tried to do an ACTION on all rows. Even on a simple test App with a few thousand rows just with a column as key and another column as timestamp (NOW()). And it takes at least 1 second to save/sync one row back to the table (with just an updated NOW() timestamp). Where I wonder why each row syncs one by one rather than syncing hundreds or thousands of rows at the same time (as the formula (in this case just time stamp) is not depending on other rows at all).

I also tried it with BOTS in the AUTOMATION section. Also here I did not got any speed improvement. Even I switched to the paid CORE version. Even I deployed the app.
Using the multi-row ACTION on 2k rows in my real App the action never finishes. Even I let it run the whole night. Running a manual started or time scheduled BOT on my real App updates me always 50-150 rows. But stops then for some reason. Not sure if there is a limitation how long a bot could run. Or how many API calls can happen.

Also I don’t see any errors for the latest execution inside the BOT monitor. And the few error messages I got yesterday are not really helping me how to resolve them, like:

Error: ‘Execute a Sequence of Actions’ Grouped action ‘Reset Row’ child action ‘Auto Update Row’ failed due to error: Error: ‘Set Column Values’ Data action ‘Auto Update Row’ failed with exception Error: Data table ‘Contacts’ is not accessible due to: Google.Apis.Requests.RequestError
Request had invalid authentication credentials. Expected OAuth 2 access token, login cookie or other valid authentication credential. See https://developers.google.com/identity/sign-in/web/devconsole-project. [401]
Errors [
Message[Invalid Credentials] Location[Authorization - header] Reason[authError] Domain[global]
]

Please let me know if there is any documentation or blog or forum I did not found yet which explains the speed, performance or limitations of BOTS and AUTOMATION. Or what would be the best way to “get on speed” here. Maybe also execute all rows or groups of those more parallel or asynchronous.

[Steve]

First, some general statements:

All Automation behaviors are performed on the server, so nothing done by Automation will be available to the user’s device until the device syncs. If you want something to happen without a sync, you have to do it with actions triggered by a user interaction with the app.

For large-scale changes, Automation performed by the server will always be faster than actions performed by the device. Except you have the delay of the sync to contend with for Automation.

Virtual column values are only updated by the device when a non-virtual column value of the same row is updated as a result of user interaction with the app. All virtual columns are updated when a sync occurs, in which case the virtual column values are computed by the server.

NOA-Alex:

And it takes at least 1 second to save/sync one row back to the table (with just an updated NOW() timestamp).

This is a an inherent limitation of Google Sheets an/or Google Drive as a data source. If you use a database as your data source, I believe that limitation is not present. (I say I believe because I have no direct experience with a database-based data source.)

NOA-Alex:

Where I wonder why each row syncs one by one rather than syncing hundreds or thousands of rows at the same time (as the formula (in this case just time stamp) is not depending on other rows at all).

I imagine the why is due to some deep internals of which I’m not aware, so I can’t say. What I will note is that the server does not do its recalculations of virtual columns nor does it send other server-updated values until all of the app updates have been received. So if the app has five pending updates, the server is not recomputing and sending its data between each; instead it waits until all five updates have been received from the app.

NOA-Alex:

Using the multi-row ACTION on 2k rows in my real App the action never finishes. Even I let it run the whole night. Running a manual started or time scheduled BOT on my real App updates me always 50-150 rows. But stops then for some reason. Not sure if there is a limitation how long a bot could run. Or how many API calls can happen.

This sounds like a bug. Please contact support@appsheet.com for help with this.

NOA-Alex:

the few error messages I got yesterday are not really helping me how to resolve them

I agree that the error you cite is not very helpful. I would speculate it means your Google login has expired and you need to re-login to appsheet.com. But that’s pure speculation. Best to engage support@appsheet.com for help with this one.

[Former Community Member]

@Adam FYI for sync issues.
@Dan_Bahir FYI for grouped actions and error messages.

@Carlos_Salazar as Bots run in the server, certain combination of grouped actions don’t make sense - eg: update something & then navigate to a site. As Steve mentioned, please open a support ticket and we will be happy to look into why it only processes a few rows for you vs all the rows.