How do I delete data on a Google Sheet from ReTool?

Queries and Resources
1

I have a Google Sheet that makes calculations but it needs reports added to it for it to work.

My question is: How can I delete data on certain sheets using ReTool?

Goal: To have a button delete data from a sheet and add new data on the existing sheet.

2

Hi @djbeto2209, I noticed you liked my answer on a different thread. Were you able to figure this one out?

Let me know if you want some screenshots or direction of this implementation

3

No, I was not able to figure this out but I would love some direction if possible.

Is my question enough information to guide me?

4

Hey @djbeto2209,

Let's see,

  • You only want to delete some data on some sheets based on some logic
  • You want to add new data to a certain sheet in a spreadsheet.

The Google Sheets query has both options available. You only need to deal with the logic of how you determine what needs to be deleted.

If I take your other question that @jocen linked to into account, a structure like this should work out for you:

  • One JS query which determines which rows/cells need to be deleted
    • The JS query then triggers the Google sheet query with the nec. information to delete the rows/update the cells
  • One JS query to prepare your data to be imported to GSheets
    • Which then triggers the Google sheet query to append a new row or update your data.

If that seems confusing for any reason, share your setup so we can have a closer look at your specific use-case.

Cheerios,
mj

5

Hey @djbeto2209,

Do you have access on creating your own Google Sheet API resource? If not, my bad on the assumption.
You might want to follow @minijohn's take on the process. Personally, I prefer to avoid using the delete a row function of the native google sheet resource in retool.

However, if you can create your own GSheet API resource, my approach would be:

  • Add input components for Spreadsheet ID and Sheet Range to be deleted (refer to screenshot below)
  • Custom GSheet Resource clearing the user input sheet range -- this can be added as a button event but making sure the above input components are filled with the right values (refer to screenshot below)
  • Update the spreadsheet with the new data using the native google sheet resource

Screen Shot 2022-02-02 at 10.05.21 am
Screen Shot 2022-02-02 at 10.05.21 am1320×506 40.5 KB

But yeah, both MJ and I are only taking much assumption from your given information so would be good to share your specific use-case.

1 Like
6

Thank you to everyone for the great responses.

I followed the instructions from @jocen's post and I would like to add How to set up OAuth 2.0 for ReTool. Not having credentials setup was my first hump to get over. I believe I now have a solid connection.

So I have a worksheet that was give to me to automate. The workbook already has formulas on it. User will go to a site, pull a report and export it as a CSV. From there the user would copy that data and paste it into this workbook daily. They remove the data from the previous day and enter in the data from the new report. The workbook takes that data and does calculations based on the data provided and generates a report for another software to understand.

All this copy and paste is currently done manually. My goal is to just get the data formulated for someone to export from ReTool as a CSV that the final software will understand when it is imported.

Sorry for so much info, I didn't want to leave anything out! Again thank you both for your help!

1 Like
7

Is there any documentation to show how to setup a resource with a Google API?

I have set up a Google API and I now have credietals, but I don't think I am entering in the write information to create a proper resource in ReTool.

8

Happy to help with that! :slightly_smiling_face:

I recently set up a resource connection to Google Calendar using the OAuth2.0 auth type in a Rest API resource:

https://docs.retool.com/docs/api-authentication#oauth-20

My resource set up page:

1654×1636 231 KB

And make sure to include the OAuth callback URL (from your Retool resource) as an Authorized redirect URI (in your Google Credentials page)!

2146×1728 273 KB

9

Let me know if you have any specific questions or error messages you're running into

10

@victoria Thank you for this, it helps tremendously!

If I click on "see token status" Should I see tokens after I test "OAuth Integration"?

I clicked on OAuth Integration and it seems like it worked just fine but I don't see any tokens, it just says "No" when I look under "Exists".

11

Hmm! I believe you should see tokens. But you mention you're able to go through the "Test integration" successfully?

12

Yes, I get no error.

13

And you're able to load the data associated with your API?

14

That is correct. If I click on "Test OAuth intergration with your own account" it will ask me for permission and then go back to the "Edit Resource" screen.

15

1428×78 13.9 KB

Does your resource URL now have finishedOAuth=true? If so, you should be able to test it out in an app to see if you can fetch data! Depending on the API's auth setup, it just might not be returning certain tokens (like the ID token) and that's completely okay.

16

Yes, my URL does have finishedOAuth=true?

It is working fine now. Thank you for your help.

1 Like
17

Oh, perfect! Very glad to hear it + let us know if we can help with anything else :blush:

18

Victoria to the rescue :mechanical_arm:

1 Like