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

March 27, 2023 • , , • Published By • 8 minute read

If you haven’t read Part 1, Part 2, and Part 3 of this article series, make sure you check them out first! We created and deployed a Google Apps Script web app that returns our spreadsheet data as JSON just like an API. Now, let’s learn how to filter the returned spreadsheet data using search criteria!

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)

Base Search Criteria

All our sheets of data have two things in common:

  • id – Unique identifier for the entry
  • active – Whether the entry is active (1) or inactive (0)

Let’s start with those as a base for our search criteria. Let’s update our doGet function in the Code.gs script file. Create a variable called searchCriteria, which will be an empty object.

const searchCriteria = {

};

Let’s say we want to provide a way to get all active (1) or inactive (0) entries. The user can provide that as a url argument. Let’s call it active. We will check for that url argument in the request parameters, just like we do when checking for the sheet argument. Let’s add that to our searchCriteria object.

const searchCriteria = {
  active: request.parameter?.active
};

Let’s say we want to provide a way to get an entry by its unique id. The user can provide that as a url argument. Let’s call it id. We will check for that url argument in the request parameters. Let’s add that to our searchCriteria object.

  const searchCriteria = {
    active: request.parameter?.active,
    id: request.parameter?.id
  };

Divisions Search Criteria

Divisions have names. We could provide a way to search for divisions by name. Let’s add that to our searchCriteria object. However, because it’s specific to divisions, we will only do that if we’re asking for data from the divisions sheet. We need to update the divisions case in the switch statement.

case 'divisions':
  searchCriteria.name = request.parameter?.name;
  data = getDivisions();
  break;

The getDivisions function currently gets all divisions. Let’s pass the searchCriteria into the function as an argument. We will update the getDivisions function to check for the searchCriteria in a moment.

case 'divisions':
  searchCriteria.name = request.parameter?.name;
  data = getDivisions(searchCriteria);
  break;

Here’s the updated doGet function.

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

  const searchCriteria = {
    active: request.parameter?.active,
    id: request.parameter?.id
  };

  let data;

  switch(sheet) {
    case 'divisions':
      searchCriteria.name = request.parameter?.name;
      data = getDivisions(searchCriteria);
      break;

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

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

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

  return buildResponse(data);
}

Within the Divisions.gs script file, we can check for the searchCriteria argument and begin filtering the data before it’s returned. Also, notice the rows variable changed from const to let. That’s because we’re going to reassign a new value to it. You can’t do that if it was const.

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

  // Filter data based on searchCriteria here...

  return buildData(columnHeadings, rows);
}

If the user provided the id url argument, let’s filter the rows and find the row where the id column (index 0 in the row array) matches the id value provided. Keep in mind that url arguments are always strings. Because we’re trying to find a number, we’re using the plus operator to change the string we received to a number.

if (searchCriteria.id) {
  rows = rows.filter(row => row[0] === +searchCriteria.id);
}

If the user provided the active url argument, let’s filter the rows and find the rows where the active column (index 1 in the row array) matches the active value provided. Again, we’re using the plus operator to change the string we received to a number.

if (searchCriteria.active) {
  rows = rows.filter(row => row[1] === +searchCriteria.active);
}

If the user provided the name url argument, let’s filter the rows and find the rows where the name column (index 2 in the row array) includes the name value provided. We’re converting both to lowercase because the includes method is case sensitive.

if (searchCriteria.name) {
  const name = searchCriteria.name.toLowerCase();
  rows = rows.filter(row => row[2]?.toLowerCase().includes(name));
}

Here’s the updated getDivisions function.

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

  if (searchCriteria.id) {
    rows = rows.filter(row => row[0] === +searchCriteria.id);
  }

  if (searchCriteria.active) {
    rows = rows.filter(row => row[1] === +searchCriteria.active);
  }

  if (searchCriteria.name) {
    const name = searchCriteria.name.toLowerCase();
    rows = rows.filter(row => row[2]?.toLowerCase().includes(name));
  }

  return buildData(columnHeadings, rows);
}

Titles Search Criteria

The search criteria for titles is the same as divisions.

Here’s the updated code for the doGet function in the Code.gs script file.

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

  const searchCriteria = {
    active: request.parameter?.active,
    id: request.parameter?.id
  };
  
  let data;

  switch(sheet) {
    case 'divisions':
      searchCriteria.name = request.parameter?.name;
      data = getDivisions(searchCriteria);
      break;

    case 'titles':
      searchCriteria.name = request.parameter?.name;
      data = getTitles(searchCriteria);
      break;

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

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

  return buildResponse(data);
}

Here’s the updated code for the getTitles function in the Titles.gs script file.

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

  if (searchCriteria.id) {
    rows = rows.filter(row => row[0] === +searchCriteria.id);
  }

  if (searchCriteria.active) {
    rows = rows.filter(row => row[1] === +searchCriteria.active);
  }

  if (searchCriteria.name) {
    const name = searchCriteria.name.toLowerCase();
    rows = rows.filter(row => row[2]?.toLowerCase().includes(name));
  }

  return buildData(columnHeadings, rows);
}

Users Search Criteria

The search criteria for users has more fields you can search on, but the concepts are the same.

Here’s the updated code for the doGet function in the Code.gs script file.

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

  const searchCriteria = {
    active: request.parameter?.active,
    id: request.parameter?.id
  };

  let data;

  switch(sheet) {
    case 'divisions':
      searchCriteria.name = request.parameter?.name;
      data = getDivisions(searchCriteria);
      break;

    case 'titles':
      searchCriteria.name = request.parameter?.name;
      data = getTitles(searchCriteria);
      break;

    case 'users':
      searchCriteria.first_name = request.parameter?.first_name;
      searchCriteria.last_name = request.parameter?.last_name;
      searchCriteria.email = request.parameter?.email;
      searchCriteria.phone = request.parameter?.phone;
      searchCriteria.division_id = request.parameter?.division_id;
      searchCriteria.title_id = request.parameter?.title_id;
      data = getUsers(searchCriteria);
      break;

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

  return buildResponse(data);
}

Here’s the updated code for the getUsers function in the Users.gs script file.

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

  if (searchCriteria.id) {
    rows = rows.filter(row => row[0] === +searchCriteria.id);
  }

  if (searchCriteria.active) {
    rows = rows.filter(row => row[1] === +searchCriteria.active);
  }

  if (searchCriteria.first_name) {
    const firstName = searchCriteria.first_name.toLowerCase();
    rows = rows.filter(row => row[2]?.toLowerCase().includes(firstName));
  }

  if (searchCriteria.last_name) {
    const lastName = searchCriteria.last_name.toLowerCase();
    rows = rows.filter(row => row[3]?.toLowerCase().includes(lastName));
  }

  if (searchCriteria.email) {
    const email = searchCriteria.email.toLowerCase();
    rows = rows.filter(row => row[4]?.toLowerCase().includes(email));
  }

  if (searchCriteria.phone) {
    rows = rows.filter(row => row[5]?.includes(searchCriteria.phone));
  }

  if (searchCriteria.division_id) {
    rows = rows.filter(row => row[6] === +searchCriteria.division_id);
  }

  if (searchCriteria.title_id) {
    rows = rows.filter(row => row[7] === +searchCriteria.title_id);
  }    

  return buildData(columnHeadings, rows);
}

Deploy A New Version

We have completed making all the changes for the search criteria functionality. It’s time to deploy again! However, 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 Search Criteria.
  6. All other settings remain the same.
  7. Select Deploy

Let’s search for all active divisions with the letter M in the name.

YOUR_SCRIPT_WEB_APP_URL?sheet=divisions&active=1&name=M

You should see a JSON response with the following:

[
  {
    "id":2,
    "active":1,
    "name":"Customer Service"
  },
  {
    "id":3,
    "active":1,
    "name":"Human Resources"
  }, 
  {
    "id":5,
    "active":1,
    "name":"Manufacturing"
  }, 
  {
    "id":6,
    "active":1,
    "name":"Marketing"
  },
  {
    "id":8,
    "active":1,
    "name":"Research & Development"
  }
]

Let’s search for all users with a division id of 8.

YOUR_SCRIPT_WEB_APP_URL?sheet=users&division_id=8

You should see a JSON response with the following:

[
  {
    "id":1,
    "active":1,
    "first_name":"Jayson",
    "last_name":"Afre",
    "email":"jafre@domain.com",
    "phone":1111111111,
    "division_id":8,
    "title_id":9
  },
  {
    "id":4,
    "active":0,
    "first_name":"Inactive",
    "last_name":"Inactive",
    "email":"inactive@domain.com",
    "phone":4444444444,
    "division_id":8,
    "title_id":2
  }
]

Next Steps?

We learned how to filter the returned spreadsheet data using search criteria. Next, let’s learn how to post data to sheets we specify! Continue to Part 5.

Related Articles
About the Author

Front End Developer

https://nightwolf.dev

   

Copyright © 2024