How to Iterate Through Every Cell in a Range in Google Sheets Using Apps Script

Google Sheets is a powerful tool for organizing and analyzing data, and sometimes you need to iterate through every cell in a range to perform specific tasks or calculations. In such cases, it’s important to know how to iterate through every cell in a range in Google Sheets using Apps Script. In this article, we’ll show you how to do that with some examples.

Example 1: Looping Through a Range and Displaying Data

To iterate through every cell in a range in Google Sheets using Apps Script, you can use a nested for loop to iterate through each row and column in the range and display the data. Here’s an example code snippet:

function displayRange() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("A1:C3");
  var numRows = range.getNumRows();
  var numCols = range.getNumColumns();

  for (var i = 1; i <= numRows; i++) {
    for (var j = 1; j <= numCols; j++) {
      var cellValue = range.getCell(i, j).getValue();
      Logger.log(cellValue);
    }
  }
}

In this code, we first get the active sheet using getActiveSheet(), and then get the range of cells using getRange(). We then get the number of rows and columns in the range using getNumRows() and getNumColumns(). Finally, we loop through each row and column in the range using nested for loops, get the value of the current cell using getCell().getValue(), and display the value using Logger.log().

Example 2: Calculating a Sum of a Range

To iterate through every cell in a range and calculate a sum, you can use a nested for loop to iterate through each row and column in the range and sum up the values of the cells. Here’s an example code snippet:

function sumRange() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("A1:C3");
  var numRows = range.getNumRows();
  var numCols = range.getNumColumns();
  var sum = 0;

  for (var i = 1; i <= numRows; i++) {
    for (var j = 1; j <= numCols; j++) {
      var cellValue = range.getCell(i, j).getValue();
      sum += cellValue;
    }
  }

  Logger.log("Sum of range: " + sum);
}