Create Your Own APIs with Google Sheets and Google Apps Script (Part 2)
Tags: googleappsscript, googlesheets, api, tutorial
Published: 2023-04-02 23:16:54

If you haven’t read [Part 1](https://dev.to/nightwolfdev/create-your-own-apis-with-google-sheets-and-google-apps-script-5a) 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)](https://dev.to/nightwolfdev/create-your-own-apis-with-google-sheets-and-google-apps-script-5a)
* **Get Data from Sheets (Part 2)**
* [Create and Deploy Apps Script Web App (Part 3)](https://nightwolf.dev/create-your-own-apis-with-google-sheets-and-google-apps-script-part-3/)
* [Filter Data with Search Criteria (Part 4)](https://nightwolf.dev/create-your-own-apis-with-google-sheets-and-google-apps-script-part-4/)
* [Post Data to Sheets (Part 5)](https://nightwolf.dev/create-your-own-apis-with-google-sheets-and-google-apps-script-part-5/)

## Apps Script Editor

![Apps Script Editor](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dv8l3devgtqr70xcloir.png)

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](https://developers.google.com/apps-script), specifically with Google Sheets, let’s create a quick test function in the **Code.gs** script file.

```javascript
function test() {
 
}
```

Apps Script provides APIs for various Google apps. For Google Sheets, we’re interested in [SpreadsheetApp](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app). 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:

```javascript
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](https://developers.google.com/apps-script/guides/services/authorization).

![Run Test](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3ljinpj6gpeyjwkdrrbi.png)

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

```javascript
[
  [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.

```javascript
[
  [1.0, 1.0, Accounting]
]
```

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

```javascript
[
  {
    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.

```javascript
function buildData(columnHeadings, rows) {
 
}
```

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

```javascript
const data = [];
```

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

```javascript
rows.forEach(row => {
 
});
```

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

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

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

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

```javascript
data.push(entry);
```

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

```javascript
return data;
```

Here’s the final code for the **buildData** function.

```javascript
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**.

![Add File](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1yj9ufpizbcm0vn22jsj.png)

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

```javascript
function getDivisions() {
 
}
```

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

```javascript
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](https://www.w3schools.com/jsref/jsref_shift.asp) method to remove them from the rows array, but store them in a separate variable. Let’s call it **columnHeadings**.

```javascript
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!

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

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

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

```javascript
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](https://nightwolf.dev/create-your-own-apis-with-google-sheets-and-google-apps-script-part-3/).

---
**Visit our website at [https://nightwolf.dev](https://nightwolf.dev) and follow us on [Twitter](https://twitter.com/nightwolfdev)!**