Hi, Im having issues while running a loop with spreadsheet append from a table.
Theres a fileButton defined to load a csv into a table. A button triggers a query that iterates through the table data and augment the data coming from the current row and triggers spreadsheet append query as additionalScope param.
On the spreadsheet query onSuccess handler, the current row should get updated with the augmented data.
This query1 is the loop that augment, trigger spreadsheet query and finally update table
This query2 is spreadsheet resource for appending data
I ran the query with 12 values on the table and having two issues
- Inserted data is well formatted but Im getting duplicated rows and a lot of missing ones
- When the onSuccess handler is executed, instead of updating the row with the new data, its removing it. When iteration is done, table is left with just one row, correctly updated.
Spreadsheet updated
Initial table
Table end result
Hey @nico.io!
If you want to update and insert rows at the same time you'll need to use a combination of the "Update spreadsheet" and "Append data to a spreadsheet" actions (more on that here). Otherwise, if the rows just need to be updated you can stick with "Update spreadsheet"
For the second problem, can you try accumulating the data before passing it to the table? A pattern like the one described here may help. Instead of setting the table data on success, you can try adding the value to some aggregator and then setting your table:
const successes = [];
const promises = table1.data.map((row, i) => {
const values = {...};
return query1.trigger({
additionalScope: { values },
onSuccess: () => successes.push(values),
});
});
await Promise.all(promises);
table1.setData(successes);
Does that help? Let me know if it raises any further questions or issues!
Hi @Kabirdas, thanks for your reply.
The fileButton component will load a csv to the table. Each row will be processed and each result being success or fail will be will be appended on an audit spreadsheet.
Following your template on awaiting all promises to update table did work, now all rows on table1 are updated with no data loss.
However still experiencing data loss on spreadsheet row appending. As I show with screenshots, Im loading a 12 rows table but Im getting a 5 row spreadsheet.
Hey @nico.io!
It looks like this might be because you're making several simultaneous requests to the Google Sheets API at once. Can you try uploading them all together instead? The append data action supports arrays of objects as well, so you can try something like:
const valuesToAppend = table.data.map((row, i) => ({
...row,
printId: i + 1,
// etc.
}));
query1.trigger({
additionalScope: { values: valuesToAppend },
onSuccess: () => table1.setData(valuesToAppend)
});
Does that work?
That did work, all solved. Thanks @Kabirdas
