Build An Entire API in Google Sheets

If you’re like me, you’ve made APIs for all kinds of random things. Noble causes, like a back-end to the app you built (e.g. Lyft’s back-end, sending pricing to Google Maps, or Pilgrim Coffee, my now sadly defunct map of the best coffee in the world). Or totally random things, like my random startup business generator which I did on my blog, The Vanity Metric.

Here’s how to build a simple API that you can send a HTTP GET request to and get an answer from. It runs off a database based entirely in Google Sheets.

Set up your Google Sheet with your data and basic logic.

Here’s the example I have. It makes startups ideas like “Tinder for dogs” or “VR seasickness pills for Stevedors using IoT”. The data is three columns that form the random startup using the structure “X for Y” or “X for Y using Z”. Yeah, it’s awesome

I did the logic in Sheets. I wrote a formula to choose the three columns and spit it out into two cells (one cell is “X for Y”, the other is “X for Y using Z”). It’s just much easier to code and debug most things in Sheets than to write Javascript code, unless you’re a total Javascript wizard (in which case, why are you here? Go spin up a Node.js server!).

One of the cells:

=INDEX(Data!$A:$A,RANDBETWEEN(1,COUNTA(Data!$A:$A)),1)&" for "&INDEX(Data!$B:$B,RANDBETWEEN(1,COUNTA(Data!$B:$B)),1)&" using "&INDEX(Data!$C:$C,RANDBETWEEN(1,COUNTA(Data!$C:$C)),1)

Define inputs and outputs

To keep it simple, but to have an input and an output, I defined them as follows:

Inputs

{type: Int}

I send it a ‘1’ or a ‘2’, and depending on what I send, I return a different kind of business type. For ‘1’, I return “X for Y”. For ‘2’, I return “X for Y using Z”. I didn’t need to, this is just for the purpose of the exercise.

Outputs

{counter: Int, biz: String}

Set up the API

This is the fun bit! Web apps written in scripts need two functions: doGet() and doPost(). In this case, I’m only putting code in the doGet function and that’s fine. Here’s the code:

/*
* API for Business Model Generator
*/
function doGet(e) {
// get parameter to understand what to return
var bizType = e.parameter.type;
var bizRange = 'B2'; // default cell for biz idea
if (bizType === '2') {
bizRange = 'B3';
}
var sheet = SpreadsheetApp.getActiveSheet();

// update the counter
var counterCell = sheet.getRange('C2');
var counterVal = counterCell.getValue();
sheet.getRange('C2').setValue(counterVal + 1);

// get the data
var biz = sheet.getRange(bizRange).getValue();

// return the content
var bizReturner = {'idea': biz,
'counter': counterVal
};
var myJSON = JSON.stringify(bizReturner);
return ContentService.createTextOutput(myJSON).setMimeType(ContentService.MimeType.JSON);
}
function doPost(e) {
return true;
}

Most of this code is not important, just logic to make the silly web app work. Here are the important bits.

Pass the data in using the URL with parameters

When you click on Publish → Deploy as web app and publish it, you get a URL. Note that every time you make a change to code, you have to save it and publish it again with a new project version each time. Also make sure you specify ‘Anyone, even anonymous’ can access the app.

You can then send parameters via the URL, i.e. append ‘&type=2’ to the end.

https://script.google.com/macros/s/AKfycbwYDuosLVjMU2GTL7tvsOAfZyEwtyJPoQpvtKVjwcZCD2DGchI/exec?type=2

The parameters

Read the parameters (in this case just one, the ‘type’)

function doGet(e) {
var params = e.parameter;
}

Note: Use ‘parameter’, without the ‘s’ (i.e. not ‘parameters’). If you have the plural form, then the structure is a little different — each key has an array of values. See here for more details.

The output JSON

As you construct your output, you have to format it into a string and send it back the right way so that the receiver receives it as JSON.

Whatever object you create, you first have to make it into a string, and then you have to send it as the JSON mimetype. Like this:

var myJSON = JSON.stringify(bizReturner);
return ContentService.createTextOutput(myJSON).setMimeType(ContentService.MimeType.JSON);

And there you have it. Try it! It’s a little slow (response time is about 2 seconds) but it works.