Google Sheets + AWS Lambda = JSON backend 😎

TL;DR: Simple JSON API for simple people with simple purposes without any complicated servers.

5 min readApr 11, 2018

Why? 🙄

We needed a simple back end for our upcoming React project. Our target was that hosting the backend should be easy and low-cost, and it shouldn’t take too much time to create or modify data on it. Basically back end would need to be so easy to use that you could show it to anyone and they could start modifying the data.

Before we even considered Firebase or similar for the back end, we bumped across the fact that you can get JSON out of Google Sheets. That is just perfect! Running a Google Sheet costs 0 euros, and it is super easy to add and remove new data columns on it. We already use G Suite for everything so permission control is easy. In addition Sheets includes version control, and we don’t have to worry about server maintenance or scalability. How cool and easy!

How? 🤔

Google Sheets

To get started, you need to create a new Sheet and fill in some data. Use first row for your titles. These titles will be keys in outputted JSON.

Choose File -> Publish to the web.

Default settings are fine: Entire Document & Web Page. Make sure you have Automatically republish when changes are made selected.

Click Publish.

Now take a look at your url in your browser’s address bar. It should be something like https://docs.google.com/spreadsheets/d/LONG-KEY-HEERE/edit#gid=0

Copy the key from the middle to a safe place as our lambda will use it. Google Sheets part is now done!

AWS Lambda

Even though it is possible, Google Sheets JSON is not so good backend by itself. Its URL is ugly, its output is ugly, and you would have to parse the output in your frontend. That is not very cool, especially if you plan to use the same API for a web page and a mobile app. That is why we need a lambda to make it beautiful.

To get started, download lambda’s code from our GitHub. Edit index.js with your favourite editor and add in the Google Sheets key from previous step.

Now just run npm install in command line and zip the contents of the folder. Make sure that you have node_modules folder also within the .zip file.

Fill in name for your lambda and choose desired role. I chose a completely new role.

When the lambda is created, upload your zipped folder to the lambda. Click Save from the top right corner after selecting the zip file.

To make sure that everything works fine, click Test, configure new test event, and Create the test. Hello World is good template for us, as our lambda doesn’t take any input.

Click Test and verify that that you see contents of your Sheet.

Yay, we have a working lambda! 💪

Amazon API Gateway

API Gateway is needed to make the lambda visible to the world. Navigate your way to Amazon API Gateway and click New API. Select New API, name it, and click Create API.

Create Resource to your brand new API.

Give name and path to your resource. Remember to check Enable API Gateway CORS, as otherwise front end can’t use the API.

Create Method to your resource.

Select GET as method type.

Choose Lambda Function, fill in its name, and Save it. Make sure to select correct Lambda Region. From popup, agree to Give permissions for API Gateway to invoke your lambda.

Choose Test, and you should see contents of your Sheet.

Now you are ready to Deploy API.

Choose New Stage, give it a name, and you are ready to Deploy!

Now you see your Invoke URL from which you can find your API. Just Save Changes and you are done!

Now head to <invoke_url>/<resource>. My stage was named production and resource was api, so my url ends ...amazonaws.com/production/api. Go to your URL and your API should be ready! 🎉 Now make changes to your Sheets to verify that API is updated immediately.

If you want, you can also set up a custom domain for your API. We didn’t do that as we are quite lazy 😰

Before you use your great API, please note that Sheets outputs all the keys without underscores. So start_date becomes startdate and so on. There is not much we can do about this, but its good to know.

Next? 🤗

Now you are ready to conquer the world! If you use Sheets+Lambda back end on some of your projects I’d love to hear about it, so don’t hesitate to send me a message! 📩

I’m a technology enthusiast and software developer with passion to pixel perfect design. I really love building new things, especially when it involves technology.
If you liked this post, follow us to receive the latest trends in tech & design 🤓