New rows in google sheets are not adding up

Integrator.io
,
1

Hi @steinchu-SteinChu">@steinchu, I am trying to Add/update data in google sheets through put method like this way

5301043caa20740200000001-03291a92c4e94db0b7423d7ccb185edd.png
5301043caa20740200000001-03291a92c4e94db0b7423d7ccb185edd.png1677×598 56.3 KB


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.

2

hi @moazamsaleem522, 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

3

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.

4

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:

undefined

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:

v4/spreadsheets/xxxx/values/Sheet1:clear

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}}
]
}
5

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!

6

hi @gabemontoya , 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

7

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!

8

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.