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);
}
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 sum up the values using sum += cellValue
. We then display the sum using Logger.log()
.