Google Sheets using Apps Script: Iterate a Range and set a value in each Cell
A common use case when engaging with Google Sheets is to iterate through a range. Once we know how to set or inspect a cell in a range we can manipulate the sheet's data. In the following code samples we are going to use two techniques to set the cell values in a range.
First technique we will use is to iterate one cell at a time in a range. Second technique we will use to set all the cells in a range in a single function call.
Let's start with creating a two dimensional array that will hold the user's data. This could be any data that you will need represented in a spreadsheet. The data could be from a database or other sources you are working with.
The following code creates a two dimensional array. We fill the array with a row and column number that we convert to a string, separated by a colon. For example row one (1) and cell two (2) will contain the following value, “1:2”. The function will take two parameters: first number of rows and second number of columns to create in an array.
function crt2DimArray(rows, cols) {
var myArray = [];
for ( var row=0; row < rows; row++) {
myArray[row]=[];
for ( var col=0; col < cols; col++) {
myArray[row][col] = (row+1).toString() + ":" + (col+1).toString();
};
};
return(myArray);
};
Using the first technique, we will use the array created to display the data in a spreadsheet. Let's start by iterating through the array, row and column at a time. When we get to our data element we will set the appropriate cell in a range that corresponds to the array's row and column.
function writeToSheetByCell(pValues, pSheet) {
var myRange = pSheet.getRange(1,1, pValues.length, pValues[0].length);
for (var row=0; row < pValues.length; row++) {
for (var col=0; col < pValues[0].length; col++) {
myRange.getCell(row+1,col+1).setValue(pValues[row][col]);
};
};
};
Using the second technique, we will first calculate the range size based on the size of the array. Once the range is equal to the size of the array data, we can set all the cells in the range by calling the function 'setValues()'.
function writeToSheetByRange(pValues, pSheet) {
var myRange = pSheet.getRange(10,1, pValues.length, pValues[0].length);
myRange.setValues(pValues);
};
For fun we can use a function 'forEach()' to iterate a dataset and display back the data that was written into the spreadsheet.
function logDataInEveryCell(pSheet) {
var range = pSheet.getRange(1,1,2,3);
var values = range.getValues();
values.forEach(function(row) {
row.forEach(function(col) {
Logger.log(col);
});
});
}
Putting it all together, full code example: --------------------------------------------------------------
function crt2DimArray(rows, cols) {
var myArray = [];
for ( var row=0; row < rows; row++) {
myArray[row]=[];
for ( var col=0; col < cols; col++) {
myArray[row][col] = (row+1).toString() + ":" + (col+1).toString();
};
};
return(myArray);
};
function writeToSheetByRange(pValues, pSheet) {
var myRange = pSheet.getRange(10,1, pValues.length, pValues[0].length);
myRange.setValues(pValues);
};
function writeToSheetByCell(pValues, pSheet) {
var myRange = pSheet.getRange(1,1, pValues.length, pValues[0].length);
for (var row=0; row < pValues.length; row++) {
for (var col=0; col < pValues[0].length; col++) {
myRange.getCell(row+1,col+1).setValue(pValues[row][col]);
};
};
};
function logDataInEveryCell(pSheet) {
var range = pSheet.getRange(1,1,2,3);
var values = range.getValues();
values.forEach(function(row) {
row.forEach(function(col) {
Logger.log(col);
});
});
}
function myMain() {
var ssheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var myArray = crt2DimArray(5, 3);
writeToSheetByCell(myArray, ssheet);
myArray = crt2DimArray(7, 5);
writeToSheetByRange(myArray, ssheet);
logDataInEveryCell(ssheet);
};