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

March 29, 2023 • , , • Published By • 10 minute read

If you haven’t read Part 1, Part 2, Part 3, and Part 4 of this article series, make sure you check them out first! We learned how to filter the returned spreadsheet data using search criteria. Now, let’s learn how to post data to sheets we specify!

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)

Post A Division

Before we learn about the doPost function, we need to create functions to post data to the sheet we specify. Let’s start with a division. In the Divisions.gs script file, create a function called postDivision. It will receive a body as an argument.

function postDivision(body) {

}

We need a reference to the divisions sheet. This will provide us with a Sheet object, which provides various methods.

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('divisions');

Create a variable called data. It will be assigned the value we return.

let data;

We’re now ready to add a new row to the sheet. The sheet object provides a method called appendRow. It expects an array where each entry in the array represents a column of data. For a division, column 1 should be an id, column 2 should be 1 for active, and column 3 should be the division name.

If this was a true database system, the id column can be set to auto increment, meaning it would keep track of the last id created and just add 1 for a new row. We’ll just use the ROW function to get the current row number and subtract 1 from it (to account for the column headings row). We’re assuming nobody is deleting rows. If they were, then the ids would keep changing.

For the division name, we can get that from the body. It should be a property called name. In the real world, APIs would validate the data being passed in. Validation means checking if the user is passing in what’s expected, like required fields, valid property names, a value that’s supposed to be a string instead of a number, etc. We’re keeping it simple here and assuming the user is passing in what they are supposed to! Don’t do that if you’re using this for real! Always validate what the user is giving you! Never trust user input!

sheet.appendRow([
  '=ROW()-1',
  1,
  body.name
]);

Let’s return a successful message that the division was added.

data = {
  message: 'Division added.'
};

Finally, let’s return the data.

return data;

Here’s the final code for the postDivision function.

function postDivision(body) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('divisions');
  let data;

  sheet.appendRow([
    '=ROW()-1',
    1,
    body.name
  ]);

  data = {
    message: 'Division added.'
  };

  return data;
}

Edit A Division

There are various HTTP methods. Google Apps Script only accepts GET and POST. Typically, an edit could be a PATCH (edit a specific field) or PUT (edit all the fields). For our use case, we’re assuming the user will provide all fields, so this will act more like a PUT.

Before we make an edit, we need to find the row we want to edit based on the id and sheet in question. In the Code.gs script file, let’s create a function called findRow. It will receive the entry id and sheet name as arguments.

function findRow(entryId, sheetName) {

}

Let’s get all the rows of data from the sheetName we specify.

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

Create a variable called entry. This will be assigned the matching row data if found.

let entry;

Let’s loop through the rows. If the entryId passed to the function matches the value in the id column, assign it to our entry. We want the row number and the row of data as well. We’re adding 1 to the row number because we started at zero, but the sheet rows start at 1.

for (let i = 0; i < rows.length; i++) {
  const row = rows[i];
  const id = row[0];

  if (entryId === id) {
    entry = {
      rowNum: i + 1,
      data: row
    };
    break;
  }
}

Finally, return the entry.

return entry;

Here’s the final code for the findRow function.

function findDivisionRow(entryId, sheetName) {
  const rows = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName).getDataRange().getValues();
  let entry;

  for (let i = 0; i < rows.length; i++) {
    const row = rows[i];
    const id = row[0];

    if (entryId === id) {
      entry = {
        rowNum: i + 1,
        data: row
      };
      break;
    }
  }

  return entry;
}

We want to check if the id property was provided in the post body. If it was, the user is trying to edit the entry. If the id was not provided, the user is trying to add a new entry. Let’s update the postDivision function in the Divisions.gs script file.

function postDivision(body) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('divisions');
  let data;

  if (body.id) {

  } else {
    sheet.appendRow([
      '=ROW()-1',
      1,
      body.name
    ]);

    data = {
      message: 'Division added.'
    };
  }

  return data;
}

If we do have an id, let’s try finding the row based on the id provided. 

const editRow = findRow(body.id, 'divisions');

If a row was not found, let’s return an error message.

if (!editRow) {
  return {
    error: 'Division could not be found.'
  };
}

If we did find a row, we want to overwrite that row’s columns of data. First, we need to get the range of columns in question. The sheet object provides a method called getRange. It expects the row and column to start at, as well as the number of rows and columns.

Our starting row is the rowNum in editRow. Our starting column is 1. The number of rows is 1. The number of columns is the length of the data array in our editRow. We don’t want to overwrite the exiting id value for the row. So we pass in the existing id from the data in editRow. Finally, we return a success message that the division was edited.

function postDivision(body) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('divisions');
  let data;

  if (body.id) {
    const editRow = findRow(body.id, 'divisions');

    sheet.getRange(editRow.rowNum, 1, 1, editRow.data.length).setValues([
      [
        editRow.data[0],
        body.active,
        body.name
      ]
    ]);

    data = {
      message: 'Division edited.'
    };
  } else {
    sheet.appendRow([
      '=ROW()-1',
      1,
      body.name
    ]);

    data = {
      message: 'Division added.'
    };
  }

  return data;
}

Post and Edit a Title

Here is the final code for the postTitle function in the Titles.gs script file.

function postTitle(body) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('titles');
  let data;

  if (body.id) {
    const editRow = findRow(body.id, 'titles');

    if (!editRow) {
      return {
        error: 'Title could not be found.'
      };
    }     
 
    sheet.getRange(editRow.rowNum, 1, 1, editRow.data.length).setValues([
      [
        editRow.data[0],
        body.active,
        body.name
      ]
    ]);

    data = {
      message: 'Title edited.'
    };
  } else {
      sheet.appendRow([
        '=ROW()-1',
        1,
        body.name
      ]);

      data = {
        message: 'Title added.'
      };
  }

  return data;
}

Post and Edit a User

Here is the final code for the postUser function in the Users.gs script file.

function postUser(body) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('users');
  let data;

  if (body.id) {
    const editRow = findRow(body.id, 'users');

    if (!editRow) {
      return {
        error: 'User could not be found.'
      };
    }    
      
    sheet.getRange(editRow.rowNum, 1, 1, editRow.data.length).setValues([
      [
        editRow.data[0],
        body.active,
        body.first_name,
        body.last_name,
        body.email,
        body.phone,
        body.division_id,
        body.title_id
      ]
    ]);

    data = {
      message: 'User edited.'
    };
  } else {
      sheet.appendRow([
        '=ROW()-1',
        1,
        body.first_name,
        body.last_name,
        body.email,
        body.phone,
        body.division_id,
        body.title_id
      ]);

      data = {
        message: 'User added.'
      };
  }

  return data;
}

POST Request

Apps Script will run a function called doPost with an HTTP POST 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 doPost(request) {

}

We need to know which sheet to post data to. 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();

When you perform a POST request, a body is sent as part of the request, which is the data you want to post.

let body = request.postData.contents;

Let’s create a variable called data. It will be assigned the value we return.

let data;

If the user did not provide a body with the POST request, let’s return an error message using the buildResponse function.

if (!body) {
  return buildResponse({
    error: 'A valid post body is required.'
  });
}

The body we received will be a JSON string. We need to convert it back to a JSON object. We can use the JSON.parse method for that.

body = JSON.parse(body);

Using a switch statement, let’s post the body to the sheet in question. Remember, we already built functions for posting data. 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 = postDivision(body);
    break;

  case 'titles':
    data = postTitle(body);
    break;

  case 'users':
    data = postUser(body);
    break;

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

Finally, let’s return the data as a JSON response using the buildResponse function.

return buildResponse(data);

Here’s the final code for the doPost function.

function doPost(request) {
  const sheet = request.parameter.sheet?.toLowerCase();
  let body = request.postData.contents;
  let data;

  if (!body) {
    return buildResponse({
      error: 'A valid post body is required.'
    });
  }

  body = JSON.parse(body);

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

    case 'titles':
      data = postTitle(body);
      break;

    case 'users':
      data = postUser(body);
      break;

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

  return buildResponse(data);
}

Deploy A New Version

We have completed making all the changes for posting data to sheets. It’s time to deploy again! Again, we have to be careful with how we deploy. We don’t want a new deployment, which would result in a new web app url. We want to edit the existing deployment, which keeps our existing web app url, and create a new version.

  1. Find the Deploy button in the top right corner of the Apps Script Editor.
  2. Choose Manage deployments.
  3. Select the Edit icon.
  4. At the Version field, choose New version.
  5. The Description field can be used to explain what’s included in this deployment. Let’s call it Post Data.
  6. All other settings remain the same.
  7. Select Deploy

Using Postman For POST Requests

When performing a GET request, you can use the browser for testing. For POST requests, you need to send data. You can’t do that in the browser url. Postman is a popular tool for testing HTTP requests. I also have an article on how to Test API Requests with Postman.

Remember that Apps Script uses a redirect to a different url when returning content. We want to make sure Postman is set up to follow redirects. You can find that in the settings.

Let’s post a new division. Make sure your method is set to POST and define your app url including the sheet name.

YOUR_SCRIPT_WEB_APP_URL?sheet=divisions

In the Body tab, select raw and the type of data as JSON. Define the name property with a value of Testing.

You should see your success response!

{
  "message": "Division added."
}

Now let’s edit the entry. Send the following body for the request:

{
  "active": 1,
  "id": 10,
  "name": "Testing Edit"
}

You should see your success response!

{
  "message": "Division edited."
}

Try sending an invalid id or no post body at all and you should see your error responses!

Congratulations on completing this article series on how to create your own APIs using Google Sheet and Google Apps Script!

Related Articles
About the Author

Front End Developer

https://nightwolf.dev

   

Copyright © 2024