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

4 thoughts on “Retrieve Rows from Google Spreadsheet with Google Apps Script”

  1. Jeff Tan says:

    hi,
    thanks for the tutorial!
    i managed to return the rows with certain condition, however how do i perform formatting of that row?
    eg setbackground to the row.
    i tried row.setbackground(“red”);
    i got type error

    1. BrownBearWhatDoYouSee says:

      Thanks for reading and reaching out. I can’t be certain without looking at your code, which you are more than welcome to post here for review, but my guess is they Type error is because using SpreadsheetApp.getActiveSheet().getDataRange().getValues(); gets the values from the Spreadsheet, where you need to call setBackground on a Range object. You can consider a Range a reference to spreadsheet cells, whereas the values are what is inside of them.

      So, if you wanted to change the background on the entire data range, just create a variable called var range = SpreadsheetApp.getActiveSheet().getDataRange(); and then call setBackground on that. If you want a more specific range, you can use one of the methods like getLastRow to find a particular range.

      Thanks for reading. Feel free to post back with more questions or a code sample, which always helps.

  2. Fraser says:

    Hi
    Thanks for the tutorial.

    I am trying to implement a piece of code that will send an email, with information from a row. That row is only selected if data in a row matches a condition.

    Currently my code script is as follows:function Due_Date() {
    //Date Info
    var One_Day = 1000 * 60 * 60 * 24;
    var Today = new Date();
    var Warning_Date = new Date(Today.getTime() + (One_Day * 4));
    var DataSet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Brand & Upweight”).getDataRange().getValues();

    //Get Due Date Data
    var filteredDataSets = DataSet.filter(function(row){
    if (row[2] = (Warning_Date -4)) {
    return row;

    // Fetch the email address
    var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Brand & Upweight”).getRange(“B1”);
    var emailAddress = emailRange.getValues();

    // Send Alert Email.
    var message = ‘Just a heads up that we have content that is due in four days! Go look at the calendar to see what it is.’; // Second column
    var subject = ‘Content is due!’;
    MailApp.sendEmail(emailAddress, subject, message);
    }})}

    How would I go about getting the information from the returned row into the email?

    Any help would be smashing

    1. BrownBearWhatDoYouSee says:

      Thanks for reading and leaving a comment. First off, I notice in your filter function you are using a single equals sign =, which is used to assign a variable in JS, and not the double or triple equals == || === , which EVALUATE equality or strict equality. I’d recommend reading this article on equality to learn more. Since you aren’t comparing anything, none of the code in the if block gets run.

      Second, assuming you fix the equality issue above, you return the row and then execute some additional code. Using the return statement inside of a function means that nothing after it will get run. Returning effectively ends the execution of that function, which is all we want to do inside of a filter loop.

      To do what you are asking, I would remove all of the logic below the return statement so that all the filter loop does is generate a new array of data called filteredDataSets. After that, you can use the forEach method to loop over your filtered array and call the code to send each email, something like filteredDataSets.forEach(sendEmail(data)).

      Whew! That is a lot to write in one comment reply, so be sure to write back if you have any questions. Thanks for reading.
      Jeff

Leave a Reply

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