How To Get Google Spreadsheet Values, Formulas, or Both

When working with Google Spreadsheets, most of the time you’re interested in the values in each cell. However, there may be circumstances when you need the formulas. What if you needed both the values and the formulas? In this tutorial, we’ll be covering all three scenarios!

Let’s begin with the following spreadsheet example:

As you can see, we have four columns of data, with the last column containing a formula that multiplies the Product Price with the Units Sold.

How To Get The Values

If we were interested in just the values in each cell, we’d use the following code:

function values() {
 var ss = SpreadsheetApp.getActiveSpreadsheet(),
     sheet = ss.getActiveSheet(),
     range = sheet.getDataRange(),
     values = range.getValues();
  for (var r=1; r<values.length; r++) {
   var row = values[r],
       name = row[0],
       price = row[1],
       units = row[2],
       total = row[3];
   Logger.log(name);
   Logger.log(price);
   Logger.log(units);
   Logger.log(total);
  }
}

Let’s explain what’s going on in that code:

1. We get the active spreadsheet and assign to the variable ss.
2. We get the active sheet within the spreadsheet and assign it to the variable sheet.
3. We get the range of data within the sheet and assign it to the variable range.
4. We get the values in each cell within the range and assign it to the variable values.
5. Remember that the first row starts at zero, which is the column headings. We’re not interested in those, so we start the loop at row 1 (the row number is represented by the variable r).
6. We assign the row of data to the variable row.
7. We assign variables for each column of data, remembering that the first column starts at zero.
8. We log each variable for review.

If you were to run this function and check the log, you’d see the following:

Perfect, we got all the values in each cell for every row, not including the column headings.

How To Get The Formulas

If we were interested in just the formulas in each cell, we’d use the following code:

function formulas() {
 var ss = SpreadsheetApp.getActiveSpreadsheet(),
     sheet = ss.getActiveSheet(),
     range = sheet.getDataRange(),
     formulas = range.getFormulas();
  for (var r=1; r<formulas.length; r++) {
   var row = formulas[r],
       name = row[0],
       price = row[1],
       units = row[2],
       total = row[3];
   Logger.log(name);
   Logger.log(price);
   Logger.log(units);
   Logger.log(total);
  }
}

Let’s explain what’s going on in that code:

1. We get the active spreadsheet and assign to the variable ss.
2. We get the active sheet within the spreadsheet and assign it to the variable sheet.
3. We get the range of data within the sheet and assign it to the variable range.
4. We get the formulas in each cell within the range and assign it to the variable formulas.
5. Remember that the first row starts at zero, which is the column headings. We’re not interested in those, so we start the loop at row 1 (the row number is represented by the variable r).
6. We assign the row of data to the variable row.
7. We assign variables for each column of data, remembering that the first column starts at zero.
8. We log each variable for review.

If you were to run this function and check the log, you’d see the following:

As you can see, there are no formulas in the first three columns so those are blank. The formulas are only in the last column of each row.

How To Get Both Values And Formulas

Now it gets a little trickier, if we were interested in both the values and the formulas in each cell, we first have to look for formulas. Then we have to look at each row and column to see if a formula actually exists. If a formula does not exist, we want the value in that cell instead. We’d use the following code:

function both() {
 var ss = SpreadsheetApp.getActiveSpreadsheet(),
     sheet = ss.getActiveSheet(),
     range = sheet.getDataRange(),
     formulas = range.getFormulas();
  for (var r=1; r<formulas.length; r++) {
   for (var c=0; c<formulas[r].length; c++) {
    var formula = formulas[r][c];
     if (formula) {
      Logger.log(formula);
     } else {
      var value = sheet.getRange(r+1,c+1).getValue();
      Logger.log(value);
     }
   }
  }
}

Let’s explain what’s going on in that code:

1. We get the active spreadsheet and assign to the variable ss.
2. We get the active sheet within the spreadsheet and assign it to the variable sheet.
3. We get the range of data within the sheet and assign it to the variable range.
4. We get the formulas in each cell within the range and assign it to the variable formulas.
5. Remember that the first row starts at zero, which is the column headings. We’re not interested in those, so we start the loop at row 1 (the row number is represented by the variable r).
6. We create a second loop and check if a formula exists in each column (the column number is represented by the variable c).
7. If a formula does not exist, we get the value instead. To get the value, we need to reference where it actually is on the spreadsheet. The spreadsheet rows and columns start at 1. Because of that, we need to add one to the row (i) and column (j) numbers.
8. We log the value and formula for review.

If you were to run this function and check the log, you’d see the following:

As you can see, the values are appearing for the first three columns and the formulas are appearing for the last column.

In summary, you learned how to get values, formulas, or both from a Google Spreadsheet. Have fun using these concepts in your next Google Apps Script project!

Share This: