How to clear records from a Google Sheet and then Post new records on the same sheet?

Answered

Comments

8 comments

  • Official comment
    Scott Henderson CTO
    Celigo University Level 1: Skilled
    Answer Pro
    Top Contributor

    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.

  • Swapnil Shah
    Celigo University Level 4: Legendary
    Engaged
    Awesome Follow-up

    Hi Community Members,

    Can anyone assist me with this issue?

    0
  • Anitha Abraham Community moderator Principal Technical Writer
    Celigo University Level 3: Master

    Hi Swapnil,

    I'm checking with some folks internally. In the meantime, if you haven't seen these articles, take a look: 

    Thanks,
    Anitha

    0
  • Swapnil Shah
    Celigo University Level 4: Legendary
    Engaged
    Awesome Follow-up

    Hi Anitha,

    The issue got resolved. I had to set the page size to 1 in the export side from NetSuite as Google Sheets API doesn't allow concurrent requests I guess. 

    Also I used this article -> HOW TO: Populate data into Google Sheets which you have suggested above for reference to build my flow. Only difference is I used POST method while updating the records in the google sheet (after clearing the records) instead of PUT/PATCH. Now its working. Thanks for your help!

    Regards,

    Swapnil Shah

    0
  • Anitha Abraham Community moderator Principal Technical Writer
    Celigo University Level 3: Master

    Thanks for your update on the resolution, Swapnil! Happy to be of help!

    0
  • Swapnil Shah
    Celigo University Level 4: Legendary
    Engaged
    Awesome Follow-up

    Hi Anitha/Community Members,

    When the data gets posted on the google sheets, how do I ensure the datatype (format) doesn't change for the attribute.

    Like in my use case, I am passing Customer Category and Amount. How do I ensure "Amount" datatype is always a number? Current issue which I am facing is when the values get posted through integration flow on the sheet, an apostrophe gets appended the number in the beginning. for eg: '12345

    Due to the that I cannot do summation of the Amount column on the sheet. Then I have to manually change the format of that column on the sheet to currency or number and then it works. But when the flow runs again, the sheet gets updated with latest values and I run into the same issue of an apostrophe getting appended to the amount values.

    Can I pass a datatype as well in the request body? or Is there any function I can apply on the sheet using appscript so that datatype doesnt change for a particular column?

    0
  • Bhavik Shah Senior Product Manager
    Answer Pro
    Great Answer

    Swapnil Shah You may pass the necessary inputs in the request body if the underlying endpoint from Google Sheets is expecting the same. Looks like there are some nuances associated with the datatypes and formatting. https://developers.google.com/sheets/api/guides/values#writing

     Also, you could try preview and mock data options while making the changes on import form to test out the changes in an easy way. 

    0
  • Swapnil Shah
    Celigo University Level 4: Legendary
    Engaged
    Awesome Follow-up

    Hi Bhavik, Thank you for the article. I was able to fix the issue when I changed it from RAW to USER_ENTERED. 

    0

Please sign in to leave a comment.