How to Iterate Through Rows in Google Sheets

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

Example 1: Looping Through Rows and Displaying Data

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

function displayRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var numRows = sheet.getLastRow();
  var dataRange = sheet.getRange(1, 1, numRows, sheet.getLastColumn());
  var data = dataRange.getValues();

  for (var i = 0; i < data.length; i++) {
    var row = data[i];
    Logger.log(row[0] + ' ' + row[1] + ' ' + row[2]);
  }
}

In this code, we first get the active sheet using getActiveSheet(), and then get the number of rows in the sheet using getLastRow(). We then get the range of data using getRange(), and get the values of the range using getValues(). Finally, we loop through each row in the data array and display the data using Logger.log().

Example 2: Calculating a Sum of a Column

To iterate through rows and calculate a sum of a specific column, you can use a for loop to iterate through each row in the sheet and sum up the values of the column. Here’s an example code snippet:

function sumColumn() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var numRows = sheet.getLastRow();
  var sum = 0;

  for (var i = 1; i <= numRows; i++) {
    var value = sheet.getRange(i, 1).getValue();
    sum += value;
  }

  Logger.log('Sum of column A: ' + sum);
}

In this code, we first get the active sheet using getActiveSheet(), and then get the number of rows in the sheet using getLastRow(). We then loop through each row in the sheet using a for loop, and get the value of the cell in the first column using getRange().getValue(). Finally, we sum up the values of the column and display the sum using Logger.log().

By using these examples, you can easily iterate through rows in Google Sheets using Apps Script.