How to connect Google Sheets to the Web API component

I am looking for a way to connect a google spreadsheet to the web API in Thunkable. I would add a datasource but you cant switch between different tables and you can’t create new tables which I need to do as I need a way to store user data. I would also use Airtable but there are limits on the amount of records that can be in each base for a free plan and you can’t create new bases in Airtable through its API. In Thunkable you also can’t add data sources using blocks so my only real option is to use the Web API but i don’t know how to get the API URL from the google sheets API and all I know how to find is my API key. Any help is appreaciated as I need to create a functioning way to store user data for free while being able to edit and retrieve data. :grinning:

I’ve been looking for days about how to do this but so far I have found nothing that makes sense on how to connect web api to google sheets.

Update: I have figured out how to get the data from the google sheet but I still do not know how to edit the data in my sheet from thunkable through the web api component.

It’s not clear to me what you’re trying to do. Why the Web API? If you’re storing data in a Google Sheet, you can get data and update data using Thunkable blocks.

Why would you need to add a data source? As an example, I was able to manipulate a Google Sheet with 20,000 rows and a dozen columns using only blocks in Thunkable. That should be plenty for most purposes.

1 Like

@tatiang I am creating a database of every DC Comics comic book which you will be able to add to your collection that is saved across multiple platforms through your login data in google firebase. Due to the limitations on how much data can be on the Realtime database on firebase, I am linking the google sheets api so that in one spreadsheet I can create a new sheet for each user to store which comic books they have added to their collection which is retrievable through the collection section of my app. I am using the Web API because you aren’t allowed to switch between different sheets/tables when using the datasource Google Sheets which would prevent me from having multiple sheets/tables with each individual user’s data.

It makes more sense now, thanks. It’s a little over my head… I’m assuming you can’t just have every user store data in a single row of a Google Sheet because there are too many columns of data?

It might not fit your timeline for doing all this but @Darren is offering a course soon that covers data storage/retrieval (Firebase, Google Sheets, APIs, etc.).

1 Like

@tatiang Ok thank you I did successfully connect to the Google API although I don’t know how to use the Get, Post, Put, or Delete blocks for the web api. Do you happen to know anything about those?

Use prestoAPI

I would use that @jared but I am trying to run my app with a minimum cost and sadly that costs money and I am trying to do everything that I possibly can for free. But thank you I may use that in the future once I am able to invest more money into my app.

I’m just figuring out how to use the Post command in Thunkable. So I’ll let you know if I get it working. There are lots of examples (I Googled thunkable post api and found a bunch of screenshots and explanations).

@tatiang Ok thank you.

Also, I’ll be sharing How to use an easier api for data storage/retrieval as compared to thunkable very soon. I’ll include user authentication too!

Bye bye Firebase. This will require you to learn the api and working with that stuff!

1 Like

I saw that my problem is I’m going to be calling the API alot.

My upcoming solution will solve this! Promise!

I’ll have something up in a week or less. I’m currently working out the geolocation services!

2 Likes

Thank you I can’t wait :grinning:

@supermanbritt had you thought of just querying from one sheet using the user’s login e.g suisng a unique ID like the email of the user and in that way whenever a user stores their data it would be queried to their specific sheet I would do it like this;

Send all data to one sheet (HomeSheet) then from there sort it out by building a query that picks email and puts that data on a specific sheet but every time there is a new email Id have an Automator like zapier create a new sheet with the email name so that now that data is constantly being queried there.

For the call back, I’d build a block calling a sheet with the email as the address.

Just trying to think out of the box here.

1 Like

I’m trying to avoid using any programs like zapier that cost money. I have figured out how to use the get in the web api but i have no idea how to use post or put.

Check this out, maybe you can draw inspiration. I used this to post on a payment API but the documentation was quite good which made it easier. I edited some parts to remove details but this gives you the post idea. Just know you will need three parts. The URL web API, the Headers which has the authorisation keys (where applicable) and the Content-Type (this may not be required sometime). Then the body (this is what you are writing/posting from your app to the API.

1 Like

What limitation are you running into in Firebase? I have JSON trees with over 40,000 nodes. When I start to get into the tens of thousand of nodes, the VIEWER in google stops refreshing in real time, but the database still works and the real time updates still work.

The firebase documentation states the maximum size of a string is 10MB (around 10 million characters). There is also a 256MB limit on the size of as single read. That shouldn’t be an issue unless you put your entire massive database under a single root node.

What limits have you encountered? I don’t mean to be critical, it is just that I’ve been using Firebase extensively and I have found the performance VASTLY superior to AirTable, Google Docs, and any sort of third party API. Maybe I can help you troubleshoot your issue. AND IT IS FREE! :slight_smile:

because teddy loves JSON…

Happy Thunking!

2 Likes

The hard part is querying Firebase. I want to dig into the documentation to figure this part out!

1 Like