Build An API in Google Sheets and Google Apps Script

Sometimes you need to build an API. Maybe you’re building an app, maybe a Slack bot, or maybe a zap on Zapier. Google Sheets and Google Apps Script has everything you need to build a simple API.

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, or for totally random things, like my startup business idea generator, which is the basis of this example.

Sneak preview: You can see the output of the API by clicking here. Or for a real-world application, see my advertising headline generator!

Actually, there was one time I used a Google Sheets API and Zapier to clone my friend into a Slack bot… that might be my favourite use (/abuse) of an API of all time.

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 Google Sheet that I have, full of building blocks for random startup ideas. It makes startup 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.

The logic: I did the logic for the application in Sheets. In this case, the logic is “get me a random business idea, and update the counter every time someone requests it).

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 NodeJS 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

The goal of this app is to get a random startup idea of one of two types, either short or long.

You have to know your outputs to know how you’re going to structure your data and build your app. So it’s good to think of this beforehand.

To keep it simple, but to have an input and an output (for the purpose of demonstration), I defined the inputs and outputs 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”.

Outputs

For the output of the business type, I provide both the string of the business idea it generates, plus a counter, to show how many my app has generated (so far, thousands! Wow, how many people are clicking on this?)

{counter: Int, biz: String}

OK, now you’re ready to actually code the API in Google Apps Script.

Set up the API in Google Apps Script

This is the fun bit!

Web apps written in Google Apps Script need two functions: doGet() and doPost().

The doGet() function is typically used to “get” data. That’s what we’re doing in this app.

The doPost() function is usually used to “put” data. For example, submitting a forum entry. We’re not doing that in this app.

In this case, I’m only putting code in the doGet function and that’s fine. Here’s the code:

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.

Important code 1: Getting the parameters in your Apps Script app

When you click on Publish → Deploy as a 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.

Every time you deploy a new version, you get a new URL. Make sure you copy that URL.

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

Important code 2: Receive the parameters into the Google Apps Script app

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.

Important code 3: Send 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 mime type. 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.

Using your new Google Apps Script and Google Sheets API

You can use your API basically anywhere where you can plug into a RESTful API.

Your app is just sitting there in the cloud, ready to be called.

This means you can make, using your new Google Apps Script and Google Sheets-driven API:

• A website with a button, where when you press the button, it calls the API and displays the information
• An app that works the same way
• An automatic emailer using Zapier that sends new business ideas every day

The choice is yours about how to use your new API. The way you set up the Google Sheet and Google Apps Script is very similar (if not identical) in each situation.