Making Google Sheet Into a Public REST API

#googlesheets #api #onescales
This is simple and fun way to convert your Google Sheet into a public Rest API for both reading and writing.
The example we use is a simple one column sheet which can be read in json output and written to append to the last line.
Steps:
- Create Google Sheet
- Create Apps Script
- Test and Start Using
See full detail in our Github Repo at github.com/onescales/google-s...

[@JobinAugustine]

Thank you for this video, it gave me a good starting for my Apps Script

[@onescales]

Your welcome. Good luck in your next steps

[@user-gy4so2vw9n]

You are genius.

[@onescales]

Thanks. Happy you like the topic

[@haha7836hahah]

I want to add some authorization to the api. Is there a way to store environment variables or do i need to put the token in the code for performing comparisons

[@onescales]

should be possible by setting a set key in the code and asking for this as another parameter

[@kwwong8633]

really thanks to this simple yet efficient way to insert data. is that a way I could manage more than one column? especially for writing. thanks in advance.

[@onescales]

Your welcome. Yes it is possible to modify the script fairly easily to support more than one column. It’s in our todo task list to improve this tutorial but currently not in priority. Sorry about that. Once I get to it I can update you. Otherwise if you are brave you can try chatgpt to ask it to improve the code to have 2 columns and you probably can get it to work

[@kwwong8633]

really appreciate much for your prompt feedback. looking forward to the new updates. thank you.

[@onescales]

@@kwwong8633hope to have an update for you

[@patricklafontaine4313]

Hi! thanks for this, its working fine but I have an issue with a 3rd party app that only sent post request. It looks like the script only get params from the url so does not catch params from json post request... Do you have any ideas on how to do it? Maybe a line to add to catch those json params? Thanks!

[@onescales]

Hi. I'm pretty sure you can make it work by changing the doGET function to doPOST. Do you have the same exact requirements as in the video?

[@patricklafontaine4313]

Yes, I was able to make it works just after my previous comment, it was exactly that, I used DoPost() and adjust my request with my params and its working. The only little issue is that it wont trigger change event trigger, so I need to use more code to do what I want inside the DoPost(). Pretty cool feature no matter this! :) thanks again!@@onescales

[@patricklafontaine4313]

actually, I still have an issue or 2... its working but wont do some code. I tried the same code on my change event and its working well, but from DoPost() it wont execute those lines: var today = new Date(); var year = today.getFullYear(); if(sheet.getRange(3,19).getValue()==0 || sheet.getRange(3,19).getValue()==""){
sheet.getRange(3,19).setValue(1);
sheet.getRange(3,20).setValue(year);}
dont know why, and the other thing is that it seems not to trigger zapier event neither.
Pretty weird, I try to make it working but not sure on what to do with my content since it wont trigger anything. I ll try to make another web request from DoPost() to forward my data but I must make the calculation works before.@@onescales

[@onescales]

try to output error. this will be the best way to beug and figure out@@patricklafontaine4313

[@patricklafontaine4313]

Thanks for reply!
It started working suddenly… weird but I ll take it!💁😊
Thanks!

[@RareLykeIN]

Great work my friend. Is it possible to reduce the delay between making the rest api request and receiveing data.

[@onescales]

Thanks! Do you mean it takes time to get the data. For example -5 seconds? If so, depend on the size of your sheet but probably not much to optimize. Can you share more?

[@RareLykeIN]

@@onescales Yes exactly that 5 seconds delay time, can it be reduced, like if we are to use it for some real time inventory management system or maybe controlling an IOT device ON/OFF state.

[@onescales]

@@RareLykeIN not really. This free solution is not intended to be real time or used for time dependent tasks. For those you would want a server, database and code

[@RareLykeIN]

​@@onescalesAlso I was curious if we can add connection like websocket to monitor data instead of refreshing the URL. What is your opinion?

[@onescales]

@@RareLykeIN I don’t think so. It is not intended to be used this way.