New rows in google sheets are not adding up

Moazam Saleem Strategic Partner

• June 17, 2022 15:33

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

• Official comment

  

  Scott Henderson CTO

  • October 19, 2023 22:13

  

  

  

  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.
• 

  SteinChu Strategic Support Lead

  • June 17, 2022 16:22

  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

  0
• 

  SteinChu Strategic Support Lead

  • June 17, 2022 16:36

  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.

  0
• 

  SteinChu Strategic Support Lead

  • June 17, 2022 20:01

  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:

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

  0
• 

  Gabe Montoya

  • June 23, 2022 17:43

  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!

  0
• 

  SteinChu Strategic Support Lead

  • June 23, 2022 21:56

  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

  0
• 

  Gabe Montoya

  • June 28, 2022 23:45

  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!

  0

Please sign in to leave a comment.