New rows in google sheets are not adding up
Hi SteinChu, I am trying to Add/update data in google sheets through put method like this way
I have also fixed page size to 1. But still when I send request to google sheets it's just updating a single row.
Can you suggest any work around to create/update multiple rows in google sheets.
0
Comments
We published a brand new template in our marketplace that makes it super easy to work with Google Sheets. We published a new article too explaining all the details (here). Hope this helps.
hi Moazam Saleem, What you have shown in your screenshot, and setting page size to 1 has worked in my tests. I think I'll need some additional info to understand your setup better. Can you join my zoom here? https://zoom.us/j/234354427
thanks for joining the call Moazam. The usecase you described is to update rows if they have the same id and add if there's a new id. I will be doing some tests on my end and will get back to you.
Update from our discussion, I could not figure out a way to find existing id and add new ids, but the alternate approach I shared was what was done here:
https://docs.celigo.com/hc/en-us/community/posts/360071544551-HOW-TO-Populate-data-into-Google-Sheets
For NetSuite export, we added a presave script to combine the records into one array:
function preSavePage (options) {var itemArray = options.data;
return {
data: [{itemArray}],
errors: options.errors,
abort: false,
newErrorsAndRetryData: []
}
}
We also increased the page size to fit all the records in one page since there is currently a limitation for pre save page to execute per page.
Next step we cleared the google sheet:
Then last step, we save all the records in google sheet
{"values": [
["id", "ItemName", "Quantity", "Price", "Location"],
{{#each data.itemArray}}
["{{id}}", "{{Name}}", "{{Qty}}", "{{Price}}", "{{Location}}"]
{{#unless @last}},{{/unless}}{{/each}}
]
}
Hi SteinChu, is this still your recommended way of importing NetSuite records into Google Sheets? I'm getting the same sort of errors.
When I use PUT without 'append' in the URI (if it's there it causes an error), only one line gets updated and overwritten.
When I use POST and add 'append' to the URI, I get different lines, but some of those lines get overwritten (I can watch it happen in real time).
Thank you!
hi Gabe Montoya , I've tried a different approach that I think works better. Try this demo flow I created:
https://drive.google.com/file/d/18GjxPv05CBq5QUtX2q-9vJ1766s2eqi7/view?usp=sharing
Thanks for sharing SteinChu! I wasn't fully able to replicate, but was able to do some testing.
For some reason {{#each data.[0].itemArray}} gave me an error, while {{#each data}} did not.
However, I was able to resolve my issue of rows overwriting each other by setting page size to 1.
Thank you!
Please sign in to leave a comment.