Is there a way to delete a range in Google Sheets before updating cells?

Queries and Resources
1

Hey All,

I've created a retool app the populates a table using a snowflake query and then updates a specific range in a google sheet. The data updates every 3 hours. Sometimes the number of rows changes when we roll into a new period. In these cases, the "old" data is still visible in the google sheet since the retool app is only "updating" the range.

Is there a way to delete/empty a selected range in Google Sheets before updating the values with the data in the table?

2

Can you share a screenshot with a little more detail about what remains visible?

3

Hi @ysp,

I have a similar issue when I was generating a 6mos worth of data for a report. I usually generate this every week and sometimes week1 will have 14k rows and week2 will have 13k rows. The 1k row difference will still remain as GSheet resource here only updates what was the length of the data you are passing. I didn't want to do a for-loop for the delete a row method by the native gsheet resource so I did a custom api.

My workaround: Get the G Sheet API as a custom API resource.
My 2-step process here is:

  1. Clear the specified range using this POST request; and
  2. Update the values using gsheet resource of Retool.

At this point for me, if it's not within native gsheet methods in retool, I use the custom API. If you have already setup a gmail api resource, you can re-use your OAuth2.0 creds for this. Otherwise, you can follow the instructions here.

2 Likes
4

@jocen I would appreciate if you provided a more detailed walkthrough of how to setup the google api in retool as a custom API resource .

5

Hi @p.siontoros!

@victoria already wrote a detailed walkthrough in a separate post and she also linked through a documentation in retool for connecting to google apis with OAuth2.0.

2 Likes
6