Retrieve Rows from Google Spreadsheet with Google Apps Script

After answering 100’s of Google Apps Script questions, I realized how many projects people are working on that start with a Google Spreadsheet as a base. A lot of my existing examples involve using a Google Form to trigger scripts that do things to data being written to a spreadsheet, so I typically have to describe what to do using quick code snippets or links to other docs.

This post should know give me a place to send people when their desired task starts with an already created spreadsheet. Spreadsheets are also a great tool to use if you want to coordinate lots of work that may take a long time to do or if you already have the data you need from another source.

Understanding Data Range

There are a ton of different methods you can use to select values in a Google Sheet, so I’m just going to focus on the easiest one that will work for the most people.

Let’s say we start with a spreadsheet that looks like this:

google sheet with three columns and seven rows

Our data range for this spreadsheet, which means the range of cells in which data is present, is 3 columns wide and 7 columns long including our headers. The concept of data range is important since some rows may not have data in all columns, so blank values will be included where there is nothing present. In other words, if you have 3 rows of data and 15 columns, that will construct the bounds of your data range even if all rows don’t have those cells filled in.

So, how do we do we get all of those values in Google Apps Script?

The following line of Apps Script will access the current active sheet in your spreadsheet, find the data range of your sheet (i.e. the maximum extent of values in cols/rows), and then return those values as a two dimensional array:

var rows = SpreadsheetApp.getActiveSheet().getDataRange().getValues();

The better you understand how to work with arrays in JavaScript, the more sense this data structure will make. At the end of the day, this is what our rows variable looks like after calling the above methods:

rows = [
    ['ID','Event','Date'],
    [1, 'Meeting', '02/03/2019'],
    [2, 'Presentation', '02/05/2019'],
    ...additional rows here
  ]

The 2D array structure of rows is one outer array that corresponds to the data range of the sheet. This outer array contains an array for each row in the data range. And this inner array contains all of the values available for that particular row.

Accessing Rows and Values using Array Index

The most important thing to know about working with arrays in this context is how to access the items inside of them. Arrays in JavaScript are zero indexed, meaning the first item inside of each array actually has an index of zero, the second item has an index of one and so on:

rows[0]
// First row, index zero
// ['ID','Event','Date']

rows[1]
// Second row, index one
// [1, 'Meeting', '02/03/2019']

From here, the process is similar to access the values inside of each row array.

rows[0][0]
// First row, index zero
// First value, index zero 
// 'ID'

rows[1][1]
// Second row, index one
// Second value, index one
// 'Meeting'

If this seems annoying, it certainly can be. My best advice is that if you find yourself getting a value you don’t expect, double check your array indexes. The good news is that there are other patterns we can use to work with arrays depending on what kinds of things we want to make happen.

Do Something For Each Row | forEach loop

Once we get all of the rows and values in a spreadsheet, a very common thing is to perform the same operation on all of the rows. Maybe we have a list of clients, and we want to send them all the same templated email (i.e. mail merge type operation), or maybe we have a list of people signed up for a 5K and we want to generate PDF waivers for each of them. The possibilities are endless.

We can fairly easily accomplish this pattern using a forEach loop on the result we get back from the data selection function above.

/****
rows = [
  ['Jeff', 'jeff@email.com', 'Cool guy...']
]
****/

rows.forEach(function(row) {
 var email = row[1];
 MailApp.sendEmail(email, 'Hey you!', 'This is the body of the message');

});

The forEach method is super powerful and helpful. For starters, it lets you remove one time consuming step of individual selecting rows using array indices. From there, you just have to worry about passing in the row and accessing the values within each row.

If you need to skip a row or a few based on some condition, we can also do something like this:

/****
rows = [
  ['Name', 'Email', 'Bio'],
  ['Jeff', 'jeff@email.com', 'Cool guy...']
]
****/

rows.forEach(function(row, index) {
 if (index !== 0) { 
   var email = row[1];
   MailApp.sendEmail(email, 'Hey you!', 'This is the body of the message');
 }
});

In the above example, we have a row of headers that we need to skip, so we can pass the index parameter into our forEach loop so that we can write some conditional logic to check if the index is zero, which means we are looking at our header row.

The forEach loop is super powerful, and I imagine most scripts of this type will make use of the forEach loop in some capacity. I’d recommend reading the JavaScript docs above to get a good sense for how it can and should be used.

Filtering Rows that Meet a Condition | filter

Another question I get asked a lot is how to do something when a particular value in a row meets a condition. For example, when filling out an event registration form, I want to send an email only to people who selected ‘Vegetarian’ as the dinner option.

Technically, we could also do that similar to the conditional logic in the example above, i.e. if row[3] === 'Vegetarian', and just execute our code, but that can easily lead to a ton of nested code inside a forEach loop. There is a better array method to filter results out of an array:

/***
rows = [
  ['Email', 'Number of Guests', 'Dinner Choice'],
  ['jeff@email.com', 2, 'Vegetarian'],
  ['someone@email.com', 1, 'Beef'],
  ['else@email.com', 3, 'Vegetarian'],
]
****/

var filteredRows = rows.filter(function(row){
  if (row[2] === 'Vegetarian') {
    return row;
  }
});


/***

Now, filteredRows looks like this

filteredRows = [
  ['jeff@email.com', 2, 'Vegetarian'],  
  ['else@email.com', 3, 'Vegetarian'], 
]
***/

We call the filter method on our array of rows, and then we pass in a callback function that takes the row as a parameter. Inside the callback function, you check for whatever value you are looking for or not looking for, and return the row if it meets your condition. If you want the row filtered out, just don’t return anything.

From there, we can call forEach on the filteredRows array to do the things we want to do on the subset of rows:

filteredRows.forEach(function(row) {
  //Do something here
}

Wrapping Up

There are several other ways to get data out of the spreadsheet that are slightly more complex than what I’ve talked about here, but again this method should work for most people doing most things. If you have a particular thing you are trying to accomplish that these methods might not support, feel free to leave a comment with more details.

I'm working on building the most comprehensive course available on building workflows with Google Apps Script.

Join the Course Waiting List for a Huge Discount!

* indicates required

Leave a Reply

Your email address will not be published. Required fields are marked *