Some Useful Reusable Functions in Google Sheets Scripts

When working on the simple work order system for my friend, I found a few places where the built-in Google Apps Script classes didn’t have methods that did what I needed. There were also situations where I was making the same long chain of method calls over and over. So I wrote a couple of helper functions to facilitate simplifying these other parts of the script.

Get a Sheet

Over and over I needed to get a Sheet object for the sheet which served as the primary data source in my spreadsheet. 

I realized it didn’t make much sense to continue to write out SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Work Orders"); every time I needed it. So I simply wrapped it in a function.

/**
 * Return Work order sheet object
 */
function getWorkOrderSheet() {
  return SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Work Orders");
}Code language: JavaScript (javascript)

getWorkOrderSheet() is much easier to read and understand than the long chain I posted above. It also makes chaining together additional calls easier to follow. 

For example: getWorkOrderSheet().getRange(2,2).setValue("Done");

You could also generalize it a bit to allow a parameter of the sheetname. That would make it essentially the same as getSheetByName(), but pushes it up to the global scope and defaults to the current spreadsheet. That makes sense for simple apps like this to make it a shorter call.

/**
 * Return sheet object by name
 */
function getSheet(name) {
  return SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name);
}Code language: PHP (php)

Get a list of values from a column

I was surprised there wasn’t an existing method for doing this, since it’s seems like it would be a common need. You can run getValues() on a Range object, but it has a two shortcomings.

1. You need to know and supply the end of the range when creating the Range object.
2. It returns a two-dimensional array of values, which means you will have to loop through it to get a simple list of the values in the column. 

So I wrote a simple function which takes care of those things for you!

/**
 * Returns an arbitrary list of values in a column as an array
 * 
 * @param {string} sheetName Name of the sheet to get the values from
 * @param {number} column    Number representing the column to grab values from
 * @param {number} headers   Number of rows containing headers to skip when looking for data
 */ 
function getColumnValues(sheetName, column, headers) {
  // Make column and headers optional parameters
  column = (column ? column : 1);
  headers = (headers ? headers : 0);
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var valueArray = sheet.getRange(headers + 1, column, sheet.getMaxRows() - headers, 1).getValues();
  var values = [];
  for(var i = 0; i < valueArray.length; i++) {
    if(valueArray[i][0] != "") {
      values[i] = valueArray[i][0];
    }
  }
  return values;
}Code language: JavaScript (javascript)

A few points about this function:

First, the two lines at the beginning allow us to set default values for the column and headers parameters. Google Apps Script doesn’t have a concept of required vs optional parameters. Essentially everything is optional. So by specifically setting them in the event of falsey values, we make sure they have a default value that we can use later. 

Second let’s look at getRange(headers + 1, column, sheet.getMaxRows() - headers, 1)

• We add 1 to the headers parameter passed for our starting row of the range because we want to begin with the first row after the number of header rows we specified.
• The column parameter is the column we want the range to begin on.
• For the number of rows to use in the range, we want to get the total count of rows and subtract our count of headers.

The last notable thing is the conditional within the loop.

The getValues() method on a Range object with multiple parameters returns a 2-dimensional array—an array of arrays. In this case, each one of those second-level arrays only has one item, so we use valueArray[i][0] to make sure we’re getting the individual cell value each time.

From there, we check to make sure it’s not empty, then add it to the array we ultimately return at the end of the function. 

Wrapping Up

These are obviously not groundbreaking concepts, but hopefully they are helpful to someone new to Google Apps Script and/or programming in general.