Solved: Updating a large number of related records - appro... - Page 2

bradlegassick · 10-18-2023 12:46 PM

Hi Community, I have retro fitted 2 columns to an app that I need to be populated. The records are invoice details, the invoice details are already reference connected to products. I have added the product category field to the invoice details table. This value can be dereferenced from the products ref connection. However I now need to update all 50 000 records in the table to have the categories populated.
I have used a chunking script that combines the AppSheet API, Automations & Apps Script to update the table. However it is really slow.
I am passing 10 Invoice IDs at a time to the AppSheet API, invoking an action which writes a value into the automation file dof the Invoice record. These records are held in a slice which then triggers the ref update action on the Related Invoice Details records.
It works, but it is painfully slow & I have limited the test run to 5 loops, retrieving 10 Invoice IDs at a time & porcessing them. On a successful response form the API it grabs the next 10 Invoice IDs.
Is there a quicker way to do this?
Thanks for any tips.

Suvrutt_Gurjar

You may want to share more details. It sounds that your requirement is as follows

If so, you may want to give it a try by pure AppSheet API webhook to update the [Product Category] column in the "Invoice details" table.

I believe you could try an AppSheet API webhook with following details

The webhook body code can be something like

{
"Action": "Edit",
"Properties": {
"Locale": "en-US",
"Location": "47.623098, -122.330184",
"Timezone": "Pacific Standard Time"
},
"Rows": [ <<Start: SELECT(Invoice details[ Invoice Details Key], [_ROWNUMBER] <10000)>>
{ "Invoice Details Key" : "<<[Invoice Details key]>>",
"Product Category column in Invoice Details Column" : "<<[Ref column referencing product table].[Product Category column in Products table]>>",

}
<< END >>

]
}

Please change / omit highlighted code suitably and change column names as per actual column names.

The Start: SELECT(Invoice details[ Invoice Details Key], [_ROWNUMBER] <10000)>> expression could be modified suitably to run in batches of say anywhere between 500 - 10000 records or so with some experimentation. Since the webhook has to update only one column, I believe , it will run on substantial number of rows at a time.

I believe this webhook approach will be significantly less time consuming.

View solution in original post

Updating a large number of related records - approximately 50 000