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：

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);
  })
})

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：

getValues() does NOT include the position of the value on the sheet as it's just an 2D array containing the values of the given range.

A way to go would be to create the array containing the whole sheet (I got the idea from here):

  var sheet = SpreadsheetApp.getActiveSheet();
  var rangeData = sheet.getDataRange();
  var lastColumn = rangeData.getLastColumn(); 
  var lastRow = rangeData.getLastRow();
  var searchRange = sheet.getRange(1, 1, lastRow, lastColumn);
  var rangeValues = searchRange.getValues();

So then we can iterate the 2D array of values, and because it contains the whole sheet, the position of a value at the 2 array of values is the same than the sheet... With an important detail: the array start by [0][0] whereas the sheet starts by (1)(A):

  for ( i = 0; i < lastColumn - 1; i  ){
    for ( j = 0 ; j < lastRow - 1; j  ){
      
      currentColumnInOriginalSheet = i 1;
      currentValueAtThisForLoop = rangeValues[j][i];          
      doSomethingOnThatColumn(currentColumnInOriginalSheet, currentValueAtThisForLoop);

    };
  };

If we don't want to include the whole sheet on the 2D array of values, then we can use a variable (normally called offset) to indicate how many positions we have to add to calculate the position in the original sheet.

So if we want to iterate the values from the column D to column F, we create a variable named offset with the number of columns from A to D (4 positions):

  var columnOffset = 4;
  var searchRange = sheet.getRange(1, columnOffset, lastRow, lastColumn);

  for ( i = columnOffset; i < lastColumn - 1; i  ){
    for ( j = 0 ; j < lastRow - 1; j  ){
      
      currentColumnInOriginalSheet = i 1;
      currentValueAtThisForLoop = rangeValues[j][i-columnOffset];          
      doSomethingOnThatColumn(currentColumnInOriginalSheet, currentValueAtThisForLoop);

    };
  };