'Set row values' not working as part of a bot action

[Distantspaces]

I have created an action that should update a value for a column in a newly created row. I've included this action as part of a bot which should trigger when the new row is created. The bot is triggering fine and it completes successfully (thanks to help offered in another post), yet the value is not updated in my underlying spreadsheet. If I run the 'hello' action manually (through a button in the UI), the spreadsheet does update, so the problem seems to be in the bot.

According to the event monitor, the bot receives the following input:

....."Wheel Size": "24\"", "Unique ID": "A62", "Status": "", "Bike Location": "",.....

As an output for the 'update status' step of the bot, it shows this:

 ....."Wheel Size": "24\"", "Unique ID": "A62", "Status": "", "Bike Location": "",.....

 So even though it should update the status value to "Testing", the value remains unchanged, but no error is reported either. According to the Audit log too, the operation completes successfully:

Properties: 
{ 
"TableName": "Donors", 
"AppTemplateVersion": "1.000131", 
"DataActionName": "hello",
"Updated rows' keys": [], 
"AppTemplateName": "916e3c19-a84f-4a05-8796-4eb0fff66b07", 
"Operation": "Edit row", 
"RecordType": "Stop", 
"Result": "Success" 
}

I've spent hours trying to make this work, but still can't figure out the problem. Could the 'updated rows keys' being empty in the log above have something to do with it? I'm wondering if the bot doesn't know which row to update (because the action runs fine when I run it from a UI button in the record details view).

All help is appreciated.

[1minManager]

---

@Distantspaces wrote:

If I run the 'hello' action manually (through a button in the UI), the spreadsheet does update

---

Its almost certainly that you've got either that column or the "Are updates allowed?" setting on the table referencing UserEmail() somewhere.  Since the bot operates as UserEmail()=NULL. Whereas the action with operate as UserEmail()="YourLoginEmail".

To get around this you need to put CONTEXT("Host")="Server" in your formula somewhere as this will be TRUE if a bot is doing the editing and FALSE at all other times.

https://support.google.com/appsheet/answer/10107885?hl=en&sjid=2768842476187938171-EU

Simon, 1MinManager.com

[Distantspaces]

I think I've found the problem - the key column in my table is not being recognised correctly.

Appsheet had assigned a 'timestamp' column as the key column. It is of type DateTime. The bot doesn't manage to find the relevant row using this timestamp key column. I think this has something to do with locales (US - EU), but I'm not sure. The locale in my sheet and table are set correctly, but the data payload seems to use the US locale, and that might be causing something to break?

However, when I changed the key column to '_rownumber', the bot started working. The audit log shows that it passed on a key value, unlike before:

 { "TableName": "Donors", "AppTemplateVersion": "1.000190", "DataActionName": "Status test", "Updated rows' keys": [ "13" ],

That's great, but I saw the help article warning that I shouldn't use Rownumber as a key. So I tried creating a new key column using uniqueid(), but that doesn't help me, as the underlying Google Sheet gets its data from a Google form, whereas the keys are generated in Appsheet. If I try creating unique keys in Google sheet directly (using an arrayformula for the key column), then I'm not able to assign it as a key in Appsheet because it is based on a worksheet formula.

 The timestamp would have been a good unique key, but then the bot fails. Any suggestions on how else to proceed?

[1minManager]

So you have 2 possible solutions regarding allocating a key column to a Google Forms related Sheet.  First the simple one - if no one is ever going to delete any rows, or reorder the rows, then its fine to use [_RowNumber].  So if only you has edit access, do this.

If not then what I suggest you do it to create GoogleForm[KeyColumn] as the last column in the Google Form Sheet.  Otherwise it buggers up the form if you put it anyware else.  Then have a bot run that looks for rows where ISBLANK([KeyColumn]) and if it finds any it writes UniqueID() in that column.

If you want to prepopulate the spreadsheet with unique ids, then use something like RANDBETWEEN(10000000,99999999) as a spreadsheet formula in a new column then Copy all those cells and do Paste Special -> Values Only in to GoogleForm[KeyColumn].  Then delete the column with the sheets formula.

Hope this helps 🙂