Skip to main content

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

Answered

Comments

7 comments

  • 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

    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

    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.