Create a RESTful API with Google Sheets
Learn how to serve data from Google Sheets using Google Apps Script
If you are searching for a quick and straightforward way to expose data via HTTP endpoints, you are in the right place.
We will use Google Sheets which are a simple and cost-effective way to store structured data. Then, on top of a Google Sheet, we will create a RESTful API to serve data using Google Apps Script.
This can be very useful for quick prototyping and when non-technical users need to collaborate by modifying data.
No more talk, let’s get to the point!
How it works
Of course, the first step is to create a Google Sheet with your Google account and if you are here you know how to do it.
Now we can store our data in tabular format. The first line contains the column names. Let’s store the personal data of the Big Bang Theory characters, in any particular order (anyway Raj is my favourite).
Our goal (at least for today) is to create an HTTP endpoint able to serve this data in JSON format. We will do it through a Google script.
Open the Google Apps Script editor by clicking on Tools => Extensions => Apps Script.
An empty App Script project will open In a new window. It contains an empty function myFunction.
Replace myFunction with the following code (note that the function name has to be doGet, with one input parameter).
function doGet(req) {
var doc = SpreadsheetApp.getActiveSpreadsheet();
var sheet = doc.getSheetByName("Sheet1");
var values = sheet.getDataRange().getValues();
var output = [];
for (var i = 0; i < values.length; i++) {
if (i == 0) {
continue;
}
var row = {}; // Create a new row object for each row
for (var j = 0; j < 100; j++) {
if (!values[i][j]) {
break;
}
row[values[0][j]] = values[i][j];
}
output.push(row); // Push the row object to the output array
delete row;
}
return ContentService.createTextOutput(JSON.stringify({ data: output })).setMimeType(ContentService.MimeType.JSON);
}The doGet function receives an event object as an input parameter. This object contains information about the incoming HTTP request, including the URL and path parameters.
Anyway, this function will be called when someone accesses your web app via a URL. It basically retrieves the data in the sheet and transforms them from the tabular form to the JSON format.
The Apps Script editor allows debugging, logging and other cool features. But let’s focus on our goal for the moment. It’s time to deploy your Google Apps Script as a web app.
To deploy the App, click on the Deploy button on the right side of the editor. Then, click on New deployment.
Now a popup window will open. Firstly, we have to select the type of deployment. Click on Select type and then choose Web app.
Now, we have to configure the App deployment. A simple and working configuration is the following.
The relevant part of this configuration is that anyone can access the web app. In this way, we do have not to deal with authentication and other boring stuff.
Ready to deploy?
Click on the Deploy button and authorize the web app to access your data when requested.
Don’t worry if the following message appears (in our case the Big Bang Theory characters are not sensitive data).
Click on the Advanced link.
Now, click on Go to the <Project Name> project (unsafe). Then give the authorization to the App Script project to access your Google Sheets (Allow button).
Congratulation! You have successfully deployed the Script as a Web App.
A popup window will show you the HTTP GET endpoint just created. So, you can test your web app by sending an HTTP GET request to its URL.
In this example, the URL is: https://script.google.com/macros/s/AKfycbxlXoDY5-V0sDnM2xlssXf_fe3sC8xmtiNC2kpvak1RlCuT-b11BHZLTys7B2NexDd6/exec
Try to open it in a browser tab. You should see this output:
Similarly, when you want to manage an HTTP POST request, you must create a doPost function. The doPost function receives an event object as a parameter. This object contains information about the incoming HTTP POST request, including the request body that can be accessed to process its content.
Final considerations
Using Google Sheets to serve data through a REST API can be a practical choice for projects that require an easily accessible, collaborative, and cost-effective data storage and retrieval solution, especially for small to medium-sized applications and prototypes.
Data in a Google Sheet can be updated in real time, and changes are immediately reflected in the API responses. This can be valuable for applications that require up-to-the-minute data.
Google handles the infrastructure and maintenance of Google Sheets. This means you don’t need to worry about server maintenance, backups, or scalability issues, as you might with a traditional database.
However, for larger-scale or highly complex projects, you may need to consider transitioning to a dedicated database solution to meet scalability and performance requirements.
