Answered

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

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

On the Export Side it is: NetSuite from where I am exporting records.

Google Sheets needs to be updated every 15 mins, so I am trying to clear the existing records and then post the refreshed data from the NetSuite in the same sheet. 

I am getting HTTP 400 Bad Request Error.

0

Comments

8 comments
Date Votes
  • Official comment

    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.

  • Hi Community Members,

    Can anyone assist me with this issue?

    0
  • 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
  • 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
  • Thanks for your update on the resolution, Swapnil! Happy to be of help!

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

 

Didn't find what you were looking for?

New post