Imagine we have the following Sheet:

Then we want to iterate the row 1:

var sheet = SpreadsheetApp.getActiveSheet();

var peopleNamesRangeWithEmptyCells = sheet.getRange('A1:F1'); // ← Here are the name of the people like John, Juan...
var peopleNamesRange = [];
peopleNamesRangeWithEmptyCells.forEach(function (cell) {
   if (cell.value() != "") {
     doSomethingOnThatColumn(cell.value(), cell.getColumn());
   }
});

But it seems I cannot iterate a range with foreach as I get the error: TypeError: peopleNamesRangeWithEmptyCells.forEach is not a function

I know the common way to go for it is using getValues():

var peopleNamesRangeWithEmptyCells = sheet.getRange('A1:F1').getValues(); // ← Here are the name of the people like John, Juan...

But then I would loose the original row and column, so I could not do something like calling a function with the column as param:

doSomethingOnThatColumn(cell.value(), cell.getColumn());

It seems a basic question, but I'm struggling: How to iterate a range and not only its values?

CodePudding user response：

I don't know of any way to iterate a Range object. And I don't understand your comment "But then I would loose the original row and column".

Here is an example of how to iterate through the first row.

function test() {
  try {
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spread.getSheetByName("Sheet2");
    let peopleNamesRangeWithEmptyCells = sheet.getRange('A1:F1').getValues()[0]; // [0] since its a single row
    let peopleNamesRange = [];
    peopleNamesRangeWithEmptyCells.forEach( cell => {
        if( cell !== '' ) peopleNamesRange.push(cell);
      }
    );
    console.log(peopleNamesRange);
  }
  catch(err) {
    console.log(err);
  }
}

Execution log

9:13:49 AM  Notice  Execution started
9:13:50 AM  Info    [ 'John', 'Juan', 'Pepe' ]
9:13:50 AM  Notice  Execution completed

Here is an example of the same thing with a traditional for loop. peopleNamesRangeWithEmptyCells.length is the number of rows and peopleNamesRangeWithEmptyCells[0].length is the number of columns

function test() {
  try {
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spread.getSheetByName("Sheet2");
    let peopleNamesRangeWithEmptyCells = sheet.getRange('A1:F1').getValues();
    let peopleNamesRange = [];
    for( let i=0; i<peopleNamesRangeWithEmptyCells.length; i   ) {  // do for each row
      for( let j=0; j<peopleNamesRangeWithEmptyCells[0].length; j   ) {  // do for each column
        if( peopleNamesRangeWithEmptyCells[i][j] !== '' ) {
          peopleNamesRange.push(peopleNamesRangeWithEmptyCells[i][j]);
        }
      }
    }
    console.log(peopleNamesRange);
  }
  catch(err) {
    console.log(err);
  }
}

Execution log

10:45:46 AM Notice  Execution started
10:45:47 AM Info    [ 'John', 'Juan', 'Pepe' ]
10:45:47 AM Notice  Execution completed

Finally how to get the column associated with one of the names.

function test() {
  try {
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spread.getSheetByName("Sheet2");
    let peopleNamesRangeWithEmptyCells = sheet.getRange('A1:F1').getValues()[0]; // [0] since its a single row
    let peopleNamesRange = [];
    peopleNamesRangeWithEmptyCells.forEach( cell => {
        if( cell !== '' ) peopleNamesRange.push(cell);
      }
    );
    console.log(peopleNamesRange);
    let name = "Juan";
    let column = peopleNamesRangeWithEmptyCells.indexOf(name) 1;
    console.log("column = " column);
    console.log("column = " String.fromCharCode(64 column));
  }
  catch(err) {
    console.log(err);
  }
}

Execution log

3:00:42 PM  Notice  Execution started
3:00:43 PM  Info    [ 'John', 'Juan', 'Pepe' ]
3:00:43 PM  Info    column = 5
3:00:43 PM  Info    column = E
3:00:43 PM  Notice  Execution completed

CodePudding user response：

The original column is the starting offset array index. You need to store the starting offset somewhere in a variable.

const rowOffset=3,
    columnOffset=4,
    D3E4 = sh.getRange(3,4,2,2),
    D3E4Values=D3E4.getValues();

Now when you loop over rows, the original row is equal to i rowOffset 1, where i is the outer index. For eg,

D3E4Values.forEach((row,i) => {
  row.forEach((col,j) => {
    console.log(`The original row of this value ${col} is ${i   rowOffset   1}`);
    const currentColumn = j   columnOffset   1;
    console.log(`The original column of value ${col} is ${currentColumn}`)
    //equivalent to doSomethingOnThatColumn(cell.getValue(),cell.getColumn())
    doSomethingOnThatColumn(col, currentColumn);
  })
})