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.

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

Google sheets api business idea generator screenshot
The data that goes into the Google Sheets-based API. Sheet source here

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:


{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”.


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 web app and publish it, you get an 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.

New depoyment of a web app in Google Apps Script

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.

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.

Similar Posts

Leave a Reply

One Comment

  1. Avatar Shae Murphy says:

    I am looking for a way to import not so much the data but primarily the logic from a Google or Excel spreadsheet into my RoR application. This is so that my users can perform the spreadsheet calculations without having to re-enter their data or risking “stepping on each other” if two of them are using the spreadsheet at the same time.

    The spreadsheet changes over time so we want to avoid having a copy of it for each user.

    The data that drives the spreadsheet’s formulas are in a database, and we’d like to avoid the time consuming and error prone task of rekeying them into the spreadsheet.

    My people use this spreadsheet while on the phone with customers, so speed is a consideration.

    The spreadsheet has lots of formulas but they are not that complex. It does not use advanced features that would be hard to recreate in Ruby.
    I’ve prototyped a RoR program to programmatically insert the data into the spreadsheet and get the results back. That avoids the re-keying problem. It works and is cool, but is slow and does not address the concurrent user problem.

    I’ve considered using semaphores to prevent the concurrent usage problem, but that does not address the speed problem and it could possibly slow the process down or produce a deadlock situation.

    I prefer this be a dynamic import into the app that would occur each time the server restarts. Alternatively, this could be a one-time import resulting in RoR code, but that is suboptimal given that would require server redeployment or interpreting ruby strings.

    I primarily want the calculation results. If I can also display the spreadsheet’s tab gui that would be a significant plus.

    The spreadsheet is currently a Google Sheet, but could be moved into Excel if that provides a superior solution.

    I know that through the Google API I could access the logic – but how do I do that en mass and also execute it?

    Does you know of something that would do this? What suggestions do you have?