Skip to main content

New rows in google sheets are not adding up

Comments

6 comments

  • SteinChu Strategic Support Lead

    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

    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

    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

    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

    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

    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.