Create Your Own APIs with Google Sheets and Google Apps Script (Part 3)

March 22, 2023 • , , • Published By • 5 minute read

If you haven’t read Part 1 and Part 2 of this article series, make sure you check them out first! We used Google Apps Script to get data from sheets we specify. We also restructured the data from an array of arrays to an array of objects. Now, let’s learn how to create and deploy a Google Apps Script web app that returns our spreadsheet data as JSON just like an API!

Table of Contents

Article Series

  1. Spreadsheet Setup (Part 1)
  2. Get Data from Sheets (Part 2)
  3. Create and Deploy Apps Script Web App (Part 3)
  4. Filter Data with Search Criteria (Part 4)
  5. Post Data to Sheets (Part 5)

Google Apps Script Web App Requirements

A script can be published as a web app if it meets the following requirements:

We will start with GET requests so we will use the doGet function. We want the web app to return data as JSON so we will use the Content service TextOutput object.

JSON Response

Let’s start with that second requirement of returning a Content service TextOutput object. We need to create a function in the Code.gs script file that will receive data as an argument and return that data in JSON format. Let’s call the function buildResponse.

function buildResponse(data) {

}

When creating text output with the Content service, it needs to be a string. The data we’re passing to this function will be an array of objects. We need to convert it to a string. Luckily, this is easy using JSON.stringify.

const textOutput = JSON.stringify(data);

Now we can return a text output and set its mime type to JSON.

return ContentService.createTextOutput(textOutput).setMimeType(ContentService.MimeType.JSON);

Here’s the final code for the buildResponse function.

function buildResponse(data) {
  const textOutput = JSON.stringify(data);

  return ContentService.createTextOutput(textOutput).setMimeType(ContentService.MimeType.JSON);
}

We’ll use this function to return the data we ask for in a GET request.

GET Request

Now let’s tackle the first requirement of containing a doGet function. Apps Script will run a function called doGet with an HTTP GET request. It will receive an event as an argument, which can contain request parameters. Let’s define that function now in the Code.gs script file and call the event argument request.

function doGet(request) {

}

We need to know which sheet to get data from. So the user must provide a url argument with that information. Let’s call the url argument sheet. We will check for that url argument in the request parameters and immediately make it lowercase if it was provided.

const sheet = request.parameter.sheet?.toLowerCase();

Let’s create a variable called data. We will assign this variable a value based on which sheet the user asked for. 

let data;

Using a switch statement, let’s get the respective data for the sheet in question. Remember, we already built functions for getting data for each sheet in Part 2. We’ll use them now in the switch statement. If the sheet argument was not provided or its value does not exist, let’s return an error in the default switch case. 

switch(sheet) {
  case 'divisions':
    data = getDivisions();
    break;

  case 'titles':
    data = getTitles();
    break;

  case 'users':
    data = getUsers();
    break;

  default:
    data = {
      error: 'A valid sheet name is required.'
    };
}

Finally, let’s return that data as a JSON response using the buildResponse function we created a moment ago.

return buildResponse(data);

Here’s the final code for the doGet function.

function doGet(request) {
  const sheet = request.parameter.sheet?.toLowerCase();
  let data;

  switch(sheet) {
    case 'divisions':
      data = getDivisions();
      break;

    case 'titles':
      data = getTitles();
      break;

    case 'users':
      data = getUsers();
      break;

    default:
      data = {
        error: 'A valid sheet name is required.'
      };
  }

  return buildResponse(data);
}

Deploy Web App

We are ready to deploy the script as a web app!

  1. Find the Deploy button in the top right corner of the Apps Script Editor.
  2. Choose New deployment.
  3. Select Web app for the type.
  4. The Description field can be used to explain what’s included in this deployment. Since this is our first deployment, let’s just call it Initial deployment.
  5. Execute the app as yourself.
  6. Choose Anyone to allow anyone to access the web app, even those without a Google account.
  7. Select Deploy.

Google will create a unique URL for your script’s web app. Try visiting it in the browser. You should see the error response you defined in the default case of the switch statement in the doGet function. 

{
  "error": "A valid sheet name is required."
}

Now try visiting the URL and include the sheet argument with a value of divisions. 

YOUR_SCRIPT_WEB_APP_URL?sheet=divisions

You should see a JSON response of all the divisions!

[
  {
    "id": 1,
    "active": 1,
    "name": "Accounting"
  },
  {
    "id": 2,
    "active": 1,
    "name": "Customer Service"
  },
  {
    "id": 3,
    "active": 1,
    "name": "Human Resources"
  },
  {
    "id": 4,
    "active": 1,
    "name": "Legal"
  },
  {
    "id": 5,
    "active": 1,
    "name": "Manufacturing"
  },
  {
    "id": 6,
    "active": 1,
    "name": "Marketing"
  },
  {
    "id": 7,
    "active": 1,
    "name": "Operations"
  },
  {
    "id": 8,
    "active": 1,
    "name": "Research & Development"
  },
  {
    "id": 9,
    "active": 0,
    "name": "Inactive Division"
  }
]

You may have noticed that after visiting the script’s web app url, it redirected to a different URL. This is expected. Due to security considerations, scripts cannot directly return text content to a browser. Instead, the browser is redirected to googleusercontent.com, which will display it without any further sanitization or manipulation.

Test Deployments

Now that you’ve deployed your web app for the first time, you can use the test deployment url for testing changes prior to deploying.

  1. Find the Deploy button in the top right corner of the Apps Script Editor.
  2. Choose Test deployments.
  3. Copy the web app url.

You can now make changes to your code and visit the test deployment url to verify all is well prior to deployment.

Next Steps?

We created and deployed a Google Apps Script web app that returns our spreadsheet data as JSON just like an API. Next, let’s learn how to filter the returned data using search criteria! Continue to Part 4.

Related Articles
About the Author

Front End Developer

https://nightwolf.dev

   

Copyright © 2024