Convert Google Sheets into a REST API

How to use Google Sheets as a database by turning it into a REST API with Google Apps Script. This makes Google Sheets a powerful tool for managing data online, allowing for easy data entry and retrieval without manual sharing or updating.
This allows you to add and get data from the sheet over the internet without sharing the sheet. It shows how to:
Write code in Google Sheets that listens for web requests.
Use doGet and doPost functions to handle these requests.
Insert data into the sheet and fetch a summary of this data through the web.
Deploy the script as a web app to get a URL for accessing the API.
Test the API with tools like Postman or embed it in a web form.

[@Gainmackain1]

You can update your current deployment to the latest version. No need to redeploy every time.

[@BetterSheets]

Thanks for the reminder!

[@rahul_bali]

that's really cool... can we do this with json data ?

[@BetterSheets]

Yes

[@iamGobinda]

Is it safe to use this? Is there any way to secure the APIs?

[@BetterSheets]

Yes. You can secure it as much as you want.

[@baldyardigan]

When I create an API with this and send a request from my next.js app, I get a CORS issue. Have you encountered or solved this?

[@BetterSheets]

Havent seen this yet.

[@felixobianozie3745]

Are you sending the requests using fetch, axios, or what? Maybe a few tweaks in your configuration might help if you can share. But take the following into consideration: content type should be plain text and increase your default timeout if you are waiting for async responses else you will suffer response timeout problems too.

[@alfredoramirez8336]

WOOOW your genius! excellent tutorial... may i invite you a cofee on patreon or anything similar? excellent tutorial!

[@BetterSheets]

Become a member of better sheets. More awaits inside!

[@1lllllllll1]

Fantastic! After over an hour watching useless redundant other videos, finally I found yours. Useful, technical, concise, clear. Thank you!

[@BetterSheets]

Glad you enjoyed!

[@1lllllllll1]

Watching it again and waiting two minutes until you realize the get function is supposed to be a post, phew! I was worried I’m not understanding this. Would have been good to edit that. But I appreciate the real-time and the debugging, makes this a lot more useful. Now why does google have a method for insertRows and another for insertRow? Why can’t the plural method handle a single row insert. Seems weird.

[@riccardoluise8630]

0:00 Introduction 0:40 API Overview and Benefits 1:01 Project Overview 2:00 Setting Up App Script 5:01 Testing the API 6:39 Debugging 7:34 Redeploying the Web App 8:43 Fetching Data with doGet 9:48 Using ImportData Function 10:30 Creating and Deploying an Online Form 12:55 Summary and Conclusion

[@hanzo_process]

amazing! i was looking for this solution for a week!! thanks!

[@BetterSheets]

Glad to be helpful! Anything else you need help with?

[@polgomezroyo5233]

This is amazing!! Thanks a lot for the very clear explanation!

[@polgomezroyo5233]

Is there anything I should change to make it work with the keys being defined in the API Call body? I'm such a noob with this lmao

[@AlaskaFuture]

Halo, why di Rest API cannot fetch in PHP something wrong or this cannot use in PHP?

[@BetterSheets]

Cannot use PHP

[@AlaskaFuture]

@@BetterSheets it's work and have tried, now i can use in PHP, Thankyou

[@cgc2300]

hello I'm creating a new message because I just noticed that the replies to my first message are all deleted I think KZbin deletes them automatically otherwise I wanted to ask you if you use the APIs ? If so then I don't know at all how to do it and I wanted to ask you if the ideas I had are feasible and how I could make them come true

[@BetterSheets]

Which api?

[@cgc2300]

@BetterSheets to start I would have liked to read examples to understand what I can do by connecting 2 APIs that of Google sheet and Google drag for example, what result could I obtain then I would like to understand what I could do if I connect Google sheet with the chatgpt API when I create a custom GPT and finally I would like to know the other way to use the GPT chat API, in The Playground, as a gpt agent, what result I could obtain if I connect it with different applications including that of Google sheets

[@cgc2300]

@@BetterSheets To summarize, do you know if there is a site on which I could read examples, use cases, so that I can understand how to use the database function and the API function, so that I can understand why to use these two functions? The tutorials give me the theory but don't explain it to me concretely

[@arienugraha7554]

Cool stuff! Is it possible to make an online form using this method. (if I'dont want to use GForm) how's the performance if the concurrent hit reach 10k per minute.

[@BetterSheets]

Yes you can. I dont think its great at so many calls at once.

[@lavilimp]

I love this! Thank you so much for giving me a cool idea to utilize for my projects going forward!

[@BetterSheets]

glad it was helpful!

[@macreations0528]

When i calling from Postman works fine but same api calling from my current local web project throwing error as CORS issue.

[@BetterSheets]

Not sure what you mean by local web project. Perhaps an issue with headers or GET/ POST.

[@macreations0528]

@@BetterSheets i mean calling with using axios.

[@corneduring9730]

My POST function works when testing it with postman, but I get CORS errors when implementing it with my react application. How do I fix this?!

[@BetterSheets]

React app? So not google sheets?

[@dhimahi8949]

I guess the OP is making the call to the post endpoint from the react app while trying to read/write to the google sheets.

[@dhimahi8949]

What workef fot me is setting the content type to be text/plain; charset=utf-8

[@tig4rx]

Does each post/get request counts as one Google Sheets API request? And is possible to use Bearer token to prevent anyone with the link to just be able to manipulate the data?

[@BetterSheets]

You can set up a token system yes.

[@tig4rx]

@@BetterSheets Awesome, do you know if each post/get request counts as one Google Sheets API request? Or they aren't sharing the quota?

[@BetterSheets]

@@tig4rx don't think they are sharing the quota.

[@cgc2300]

hello

[@BetterSheets]

Hi

[@cgc2300]

could I write to you, the problem is that I am French and the translators are very bad and I thought that gpt could translate better but it is also bad

[@BetterSheets]

@@cgc2300 members can email me anytime.

[@cgc2300]

hello I made a product catalog on Google sheet then I brought this catalog into WordPress I was wondering if it would be possible thanks to the combination of Google Sheet APIs, WordPress and the use of n8n software during the sequence that we are going to see and how many times would it be possible for all the keywords present in the description column of each of the products to be automatically replaced by good keywords? and the same with the images

[@BetterSheets]

Anything is possible