Apps Script Course: Loops (continued)

Although the for loop is probably the one you'll use in most cases, there are others (and we'll see some more complex ones later in the course).

While

The while loop is executed as long as the condition in () is true.

In this example, the loop is executed as long as the number of values in the array is less than 10:

function loops() {

  const array = [];

  while (array.length < 10) {
    array.push(0);
  }

  console.log(array); // Displays: [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ]
}

Do while

The do while loop is equivalent to the while loop except that the condition is tested after executing the instructions, which means that the instructions are executed at least once in any case:

function loops() {

  const array = [];

  do {
    array.push(0);
  } while (array.length < 10);

  console.log(array); // Displays: [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ]
}

ForEach

The forEach loop allows you to go through each value of an array (but we'll return to this later in the course).

Exercise

In a new sheet, enter the number 55 multiple times in column A (between rows 1 and 1000).

Then use a for loop to go through all the values of the cells in column A and color all those that contain the number 55. Complete this function before moving to the solution a little further down:

function color55() {

  const sheet = SpreadsheetApp.getActiveSheet();

  // To be completed...

}

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

Here is a commented solution:

function color55() {

  const sheet = SpreadsheetApp.getActiveSheet();

  // Loop from row 1 to 1000
  for (let i = 1; i <= 1000; i++) {

    // Cell to test
    const cell = sheet.getRange(i, 1);

    // If the cell value is 55
    if (cell.getValue() == 55) {
      cell.setBackground('#dce3f9');
    }
  }
}

This code works well and may seem very correct to you, but it's actually what you should avoid doing...

Its execution is terribly slow (about 6 seconds) and it's simply due to the fact that the methods getRange and getValue are executed 1000 times (instead of once), which are as many requests to process for Google!

Here is a more optimized solution that retrieves the values only once:

function color55() {

  const sheet = SpreadsheetApp.getActiveSheet();
  const valuesArray = sheet.getRange('A:A').getValues().flat();

  // Loop from row 1 to 1000
  for (let i = 1; i < 1000; i++) {

    // If the tested value of the array is 55
    if (valuesArray[i - 1] == 55) {
      sheet.getRange(i, 1).setBackground('#dce3f9');
    }
  }
}

To make the exercise a bit more challenging, now add some 55 in a few cells of the other columns then, drawing inspiration from the last solution, do what's necessary so it takes into account the range of the sheet that contains data (with getDataRange) and not just the first column.

With an added difficulty: this time, you do not know in advance the number of rows and columns of the sheet.

function color55() {

  const sheet = SpreadsheetApp.getActiveSheet();
  const valuesArray = sheet.getDataRange().getValues(); // getDataRange = range of the sheet containing data
  
  // To be completed...
  
}

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

Did you succeed?

If not, this might help:

• In the previous code, the loop performs an action for the entire column. You need to add a second loop to repeat the action of the first one over all the columns.
• To get the dimensions of the range, use the length property on the array returned by getValues (or on one of the arrays it contains).

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

Here is a commented solution:

function color55() {

  const sheet = SpreadsheetApp.getActiveSheet();
  const valuesArray = sheet.getDataRange().getValues(); // getDataRange = range of the sheet containing data

  // Loop from column 1 to valuesArray[0].length (valuesArray[0] corresponds to the array of the first row)
  for (let j = 0; j < valuesArray[0].length; j++) {

    // Loop from row 1 to valuesArray.length
    for (let i = 0; i < valuesArray.length; i++) {

      // If the tested value of the array is 55
      if (valuesArray[i][j] == 55) {
        sheet.getRange(i + 1, j + 1).setBackground('#dce3f9');
      }
    }
  }
}