Google spreadsheet as api rest using apps script

• Are you in a startup and need a basic backend as rest api?
• Is your precious data in google sheet an you need to consume it from web, backend, mobile or mocks?
• No one of these awesome online services works for you : https://www.mocky.io/ https://www.mockapi.io/ https://jsonplaceholder.typicode.com/

If your answers are: yes, yes and yes. Follow these steps in order to publish your google sheet data as rest api ready to consume from anywhere!

Steps

• Create a google sheet with this data:

• If everything works, you could add your own columns
• Go to tools > script editor and create a new script and save it
• Copy paste this script https://gist.githubusercontent.com/jrichardsz/870caadc511e9b55448c07845fdbd4ad/raw/305231a454349d81a89a00510e9e2ddf0b9a05f0/sheet-to-api-rest-v7-comple-query.js
• Replace these values sheetName, sheetId and token with your own values. token is to secure your api rest. sheetName is the name of your sheet, sheetId is the rare string in the sheet url. After /d/ and before /edit

• Save the script with some name like: sheet-as-api
• Scrip needs authorizations. To prompt them, click on “run” option and select testFindAll() function. Accept the requested permissions.

• You should see the consent popup. Click on review permissions

• Select your account. In the next script ensure that is your mail and press Go to… (unsafe) and then allow. Basically google is asking you if you trust in the developer. But the developer is you :b

• The previous steps just was to allow the script. Then press run again. You should see a log like this:

• This test retrieve all the available data in the sheet as json. If you don’t see this, there is an error. Feel free to ask me ;)
• Click Deploy >> New Deployment in the upper right corner

• Select type: web app

• Enter a description, select your mail and choose anyone. Then press deploy

• Previous version looked like this

• After that, you will see the url of your web app. Copy that and save it

• Previous version looked like this

• The url should look like this
• https://script.google.com/macros/s/AKfycbzj6-zXCTbxib/exec
• Save it, this is the url of you new rest api!!.
• This url change on each new deploy

FindAll Test

Just perform a simple get invocation to the url of your deployed api

• http method: get
• token: security token configured at the beginning of this story
• operation: findAll
• url (your api url) : https://script.google.com/macros/s/AK**6j0SpZF4/exec

Response will be something like:

Simple Query Test

Just perform a simple post to the url of your deployed script

• http method: get
• api url : https://script.google.com/macros/s/AK**6j0SpZF4/exec
• token: your security token
• operation: simpleQuery
• field1: some column of your sheet. In the example is name
• comparator: Any simple math javascript operator. In this example ==
• field2: Is the expected value related to the entered field1. Don’t forget to quote if it is a string: ‘Richard’

Response will be something like:

That’s all. You have a simple, secure and ready to use rest api to be consumed for backends like android, ios, etc. Don’t forget, just for tests!!

Roadmap

• test it on javascript client side web apps
• add crud operations