Using Google sheets as an API For your next app

Featured on Hashnode

Subscribe to my newsletter and never miss my upcoming articles

Listen to this article

Yup, you can use google sheets as an API for your next app .. yes it is possible. Google already provides an API that you can use to read and write data in google sheets also there is an npm package to do the same. so you can basically do all basic function in google sheets.

But this post is not about that..

via GIPHY

This blog is about a trick which I recently discovered, It makes work a lot easy if you just need to display data from google sheet in your App. Imagine a scenario in which you need to display data from a google sheet list of students, list of all important Contract numbers in case of emergency. or something like that If you only need to display data and you are in a hurry.

Firstly go to a google sheet and publish the web.

ezgif.com-gif-maker (1).gif

once it is done now take a look at the URL of your google sheet.

Screenshot from 2021-01-24 17-59-03.png now just copy this unique URL and

https://spreadsheets.google.com/feeds/worksheets/{Unique URL }/public/basic?alt=json

now just hit this URL in your browser or in postman. now look a the response of the API.. just search for the entry array in the link object.. here you will find the various link to your google sheet .. but they will arrange data in a different format.. one will give you data in a list format and another will just throw data in a cell wise format. just go with format you are comfortable with.

Screenshot from 2021-01-24 18-19-53.png

but if you hit that URL directly it will not give you data in the desired format. just remember to add a query argument (alt=json)

you can take this URL as an example

https://spreadsheets.google.com/feeds/list/1cXItNrEtnHRBIAo612LImhjnsFMSbgli-R94T2SPhHE/od6/public/basic?alt=json

Screenshot from 2021-01-24 18-29-36.png

just remember to add ?alt=json at the last of the URL. Hope you like my articles if I can improve my writing more please let me know in the comment section. Also do react and share if it helped you.

Bhargav Ponnapalli's photo

Super helpful. I always wondered if we can manage data easily using sheets with Google or notion.

Thanks Gaurav Tewari! Keep going!!

Shubham Waje's photo

Wow! That looks really cool. But, I don't think we can make http requests like POST, PUT, DELETE etc. on it. If I'm wrong please correct me.

But, really a great way to convert a large amount of data in an api.

Gaurav Tewari's photo

yes can't make other HTTP requests like PUT, POST, and Delete, but you can use google sheets API to do that developers.google.com/sheets/api

Shubham Waje's photo

Oh i see, then it would be a great option for small projects. Thanks for sharing this✌🏻Gaurav Tewari

Salman Qureshi's photo

Thats very informative πŸ‘

Zurik Phillips's photo

Interesting, I've always used json formatted data, placed in a .db file and then used npm install jason-server. However this method is nice because I dont need to run any additional server to serve my data. Google already does it for me!

Nice, I will use this for my next project. Keep up the good work!

Gaurav Tewari's photo

yes, that's the best part no need for a server. which saves a lot of time and money.

Emam Miya's photo

Thanks this is very helpfull

theroyakash's photo

Man super helpful.

Alimam Miya's photo

Can you send me the full source code?

Show +1 replies
Gaurav Tewari's photo

Alimam Miya oh this is the response we get from the API. spreadsheets.google.com/feeds/list/1cXItNrE..

hit this URL you will get the same data.. I am using a chrome extension to beautify the response we are getting from this request.

Nishant Tiwari's photo

impressive πŸ˜ƒ