Alright!
So I have been using sheet.best as a rest API to do numerous things with google sheets automatically. However, my API calls are getting to be a lot and it gets expensive using a 3rd party.
I figured out how to use google API (free!!!!) and to launch CURL in execute shell script action for all google sheet needs.
The most difficult part is the authentication.. its almost another language figuring it out. I found an awesome guide that walks through this.
and the google sheets API info is here (to see what post/get request(s) you want to use):
Use the API CONSOLE on the right and once you do a practice run it gives you the CURL code which works great in KM.
Theres a few things that the guide does not explicitly say so these are the steps I took:
step 1) go to https://console.cloud.google.com/projectcreate
step 2) name it whatever you want - leave location alone and click create
step 3) go to libraries on the left
step 4) type in google sheets API and select and then enable
step 5) click oauth consent screen and publish the project
step 6) go to credentials and then create credentials select API (save this somewhere)
step 7) select none and then set api restriction to dont restrict key
step 8) go to credentials - create credentials - select google sheets API
step 9) ignore scope and then select application type as web application
step 10) add an Authorized redirect URIs as http://localhost (IMPORTANT)
step 11) continue and copy and save the client ID and client Secret
step 12) under oauth 2.0 client IDs click the downwards arrow to download a JSON file with the AUTH info and open it.
step 13) Perform the following steps to generate the authorization code:
- Enter the following URL in the Google chrome browser:
https://accounts.google.com/o/oauth2/auth?
access_type=offline&approval_prompt=auto&client_id=<client_id>&response_type=code&scope=h
ttps://www.googleapis.com/auth/spreadsheets&redirect_uri=<redirect_uri>
The client ID is in the JSON FILE and the redirect_uri is http://localhost
step 14) paste the new URL into chrome - follow the prompts - bottom left click advanced and then continue
step 15) (this is in the guide and follows well) - basically it will show you a webpage that says cant reach but the info you need was populated in the URL - copy that info and then use that token in the postman steps to get the bearer tokens needed to do your API calls.
They have the info you need to get the new token from the refresh token as the tokens expire every hour.
I tried my best to highlight what I did - understanding how to use KM vars in shell script is the other challenge but theres guides all around for that.
I just wanted to share some info that is really useful for me and my automations! google-sheets has been so useful and powerful for me!
PS - I am sure there is a better way to do this, but I have no idea what it is and there isn't any info in the forum that explains this, so here is a start!
Goodluck!
