CODEWITHSUNDEEP
google-sheets
shrikantha nayak
shrikantha nayak

Reputation: 91

how to loop through each rows and column values in google sheet using app script?

enter image description here

am trying to loop through all the amount values in google sheet using app script , but am when i use for loop am only able to get "aoumnt 1" column values only ,

  var sheetSource1 = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
for (var i = 1; i <= 3; i++) {
var activecell = sheetSource1.getRange(i + 2, 2).getValue();
Logger.log(activecell);
}

when i run Logger.log() i get like below

but i want to reesult like

how to achive this result.?

Upvotes: 0

Views: 2050

Answers (1)

lestra
lestra

Reputation: 312

So, when you iterate over using getValue you need to Loop twice:

function myFunction() {
  var sheetSource1 = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  for (var i = 1; i <= 2; i++) {
    for (var j = 1; j <= 3; j++) {
      var activecell = sheetSource1.getRange(j + 3, i + 1).getValue();
      Logger.log(activecell);
    }
  }
}

But a nested for is not the best perfomance or practice. Then, we can use getValues() to return an array of arrays (multi dimensional 2d array). And from there iterate over the entire range as an unique source:

function myFunctionT() {
  var data = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  let numCols = 2;
  let numRows = 3;
  var range = data.getRange(4, 2, numRows, numCols).getValues(); // <- using getValues(), and getRange can have multiple types of parameters to get ranges
  let col1 = [];
  let col2 = [];
  
  for (var i = 0; i < numRows; i++) {
    var row = range[i];
    var firstColValue = row[0];
    var secondColValue = row[1];
    col1.push(firstColValue);
    col2.push(secondColValue);
  }

  let list = [...col1, ...col2];
  Logger.log(list)

}

Upvotes: 1

Related Questions