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

If you haven’t read Part 1 of this article series, make sure you check that out first! We created a spreadsheet (database) with sheets (tables). Now, let’s learn how to use Google Apps Script to get data from sheets we specify and restructure the data from an array of arrays to an array of objects!

Article Series

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

Apps Script Editor

1. Open your spreadsheet and navigate to Extensions > Apps Script.
2. The Apps Script Editor will open with a single script file called Code.gs.
3. An empty function will default in. You can remove it.
4. Give your untitled project a name.

Create A Quick Test Function

To familiarize ourselves with Apps Script, specifically with Google Sheets, let’s create a quick test function in the Code.gs script file.

function test() {

}

Apps Script provides APIs for various Google apps. For Google Sheets, we’re interested in SpreadsheetApp. We want to do the following:

• Use the spreadsheet that this script is associated to
• Select a specific sheet (divisions)
• Get all the rows of data from it
• Log it to the screen to see what it looks like.

Let’s update our test function with the following:

function test() {
  const rows = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('divisons').getDataRange().getValues();
  Logger.log(rows);
}

To run the function, select the Run option in the Apps Script Editor toolbar, making sure the function called test is selected. You’ll be prompted to authorize the script and its permissions. You can learn more at Authorization for Google Services.

The result is an array of arrays. You can see the column headings are also included.

[
  [id, active, name],
  [1.0, 1.0, Accounting],
  [2.0, 1.0, Customer Service],
  [3.0, 1.0, Human Resources],
  [4.0, 1.0, Legal],
  [5.0, 1.0, Manufacturing],
  [6.0, 1.0, Marketing],
  [7.0, 1.0, Operations],
  [8.0, 1.0, Research & Development],
  [9.0, 0.0, Inactive Division]
]

APIs usually return data in JSON format, which is structured data in the form of key/value pairs. This makes it easier to understand what the data represents. For example, I’d like to know that the first value in the array is an id, the second value represents whether or not the division is active, and the third value is the actual name of the division. The column headings are perfect for being a representation of what the data is. We just need to restructure it so we can return it that way for each row. Let’s work on that next.

Restructure The Data

Currently, the data being returned is an array of arrays with no understanding of what they represent.

[
  [1.0, 1.0, Accounting]
]

What we really want is an array of objects like the following:

[
  {
    id: 1,
    active: 1,
    name: "Accounting"
  }
]

We want to use the column headings as the keys for our restructured data. Let’s create a function in the Code.gs script file called buildData. It will accept columnHeadings and rows as arguments.

function buildData(columnHeadings, rows) {

}

Create a variable called data to store a new array, which will eventually hold an array of objects.

const data = [];

Loop through the rows of data we received as an argument.

rows.forEach(row => {

});

For each row of data, we want to start by creating an empty object. Let’s call it entry.

const entry = {};

Within each row, we want to loop through each column of data and build the entry object. The key will be the column heading, which should be at the same index as the column we’re currently on. The value will be the value in the column.

row.forEach((value, index) => {
  entry[columnHeadings[index]] = value;
});

Now that the entry has been built, let’s push the entry into the data array.

data.push(entry);

Finally, let’s return the new array of data.

return data;

Here’s the final code for the buildData function.

function buildData(columnHeadings, rows) {
  const data = [];

  rows.forEach(row => {
    const entry = {};

    row.forEach((value, index) => {
      entry[columnHeadings[index]] = value;
    });

    data.push(entry);
  });

  return data;
}

Let’s use this function to return an array of objects instead of an array of arrays.

Get Divisions

We can continue adding code to the Code.gs script file. However, it’s nice to split up your code to keep it organized. Code related to divisions can go in its own script file. At the top of the Apps Script Code Editor next to Files, select the Plus icon, and choose Script. Give it the name Divisions.

An empty function will default in, you can rename it to getDivisions.

function getDivisions() {

}

Let’s get all the rows of data from the divisions sheet.

const rows = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('divisions').getDataRange().getValues();

The column headings is the first array. We don’t want to return the column headings, but we do need them. We can use the Array shift method to remove them from the rows array, but store them in a separate variable. Let’s call it columnHeadings.

const columnHeadings = rows.shift();

All that’s left to do is return the rows as an array of objects. We already created a helpful function for that called buildData, where it accepts the column headings and rows as arguments!

return buildData(columnHeadings, rows);

You may be asking if calling buildData would cause an error because it lives in a separate file called Code.gs. Apps Script loads all the script files into the same global namespace. So it will not cause an issue, unless you use the same function name across script files!

Here’s the final code for the getDivisions function.

function getDivisions() {
  const rows = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('divisions').getDataRange().getValues();
  const columnHeadings = rows.shift();

  return buildData(columnHeadings, rows);
}

Let’s continue creating separate script files and functions for the other sheets as well.

Get Titles

At the top of the Apps Script Code Editor next to Files, select the Plus icon, and choose Script. Give it the name Titles. Add the following code.

function getTitles() {
  const rows = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('titles').getDataRange().getValues();
  const columnHeadings = rows.shift();

  return buildData(columnHeadings, rows);
}

Get Users

At the top of the Apps Script Code Editor next to Files, select the Plus icon, and choose Script. Give it the name Users. Add the following code.

function getTitles() {
  const rows = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('users').getDataRange().getValues();
  const columnHeadings = rows.shift();

  return buildData(columnHeadings, rows);
}

Next Steps?

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. Next, 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! Continue to Part 3.

Visit our website at https://nightwolf.dev and follow us on Twitter!