Can't figure out how to finish my loop (how to reset loop?)-CodePudding

I have a spreadsheet with one column that has a small number in every row (for example: 1, 1, 3, 4, 5, 5 etc)

The goal is to use a loop to count each row until the count gets to 3, then highlight that row, and start over. So my loop can successfully count to three and highlight, but then it stops. How can I "reset" the loop so it continues?

Here's my code currently:

function forloop () {

let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
let sheet = spreadsheet.getSheetByName("Sheet1");

for (let i = 1; i < 9; i  ) {

let currentCell = sheet.getRange(i, 1).getValue();
let nextCell = sheet.getRange(i 1, 1).getValue();

if (currentCell   nextCell === 3) {
  sheet.getRange(i,1).setBackground("#8e7cc3");
}

} // loop ends
} // function ends

CodePudding user response：

If you simply want the loop to continue until it reaches the end of your sheet, you can use:

function forloop() {

  let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = spreadsheet.getSheetByName("Sheet1");
  var rows = sheet.getDataRange().getValues().length;

  for (let i = 1; i < rows   1; i  ) {
    let currentCell = sheet.getRange(i, 1).getValue();
    let nextCell = sheet.getRange(i   1, 1).getValue();

    if (currentCell   nextCell === 3) {
      sheet.getRange(i, 1).setBackground("#8e7cc3");
    }
  } // function ends
}

With the code snippet you gave, you will highlight the box that color only when the values of currentCell and nextCell sum up to 3.

But what I believe you are trying to achieve is to highlight every third row, so here is the solution to that as well if it is what you are looking for:

function forloop() {

  let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = spreadsheet.getSheetByName("Sheet1");
  var rows = sheet.getDataRange().getValues().length;

  for (let i = 1; i < rows   1; i  ) {
    if (i%3 === 0) {
      sheet.getRange(i, 1).setBackground("#8e7cc3");
    }
  } // function ends
}

This is my first time answering a question on overflow :), hope it helps.

CodePudding user response：

To highlight rows by 3, you need to use modulo.

Modulo is a math operation that finds the remainder when one integer is divided by another

If the modulo of a number is 0, it means the number is divisible by the divisor. In your case 3, 6, 9, 12... are divisible by 3 with zero remainder. Using this will ensure that the row will only get highlighted every 3 rows.

Here in my sample code, I gather all row numbers that are divisible by 3 starting from A1 and store it in an array. The array will be used as a parameter for getRangeList() which can highlight the given cells in the array. It will set multiple cells background in one operation.

Code:

function forloop() {
  let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = spreadsheet.getSheetByName("Sheet1");
  let lastRow = 9; //set last row
  let startRow = 1 //set starting row
  let column = "A"; //set column
  let rangeList = []; //create empty array
  for (let i = startRow; i <= lastRow; i  ) { //loop from start row to last row
    if(i % 3 == 0){ //get the modulo of iterator, if it is 0 add a1 notation to the array
      rangeList.push(column i) 
    }
  }
  sheet.getRangeList(rangeList).setBackground("#8e7cc3"); //set background
}

Output:

Reference:

RangeList