I tried the "for loop" and "do ... while ...", both of them stopped in the middle of the looping process, and the rest of the codes, which come after the loop, did not run. This becomes an issue when I loop through hundreds of rows.

I know that the use of array is a better solution as the code execution is faster, but I have a difficulty in setting the borders in batch as there is no ".setBorders(Array)" function in Google Sheets.

The sheet provided here has been simplified only to show the looping issues. The actual sheet is written to automatically create hundreds of tables with different values, font weights and horizontal alignments.

What I want to do:

1. Choose the option "Yes" to start the looping (the current row of looping is also tracked and recorded in "CURRENT ROW" and the "STATUS" shows "Processing ...")
2. The program will check the "SCORE" column, if "SCORE" is empty (""), than the font weight for that row is set to "bold", else, the font weight is set to "normal"
3. If the looping is done until the last row, the "STATUS" shows "Done".

The following is the copy of the Google App Script:

let app = SpreadsheetApp;
let ss = app.getActiveSpreadsheet();
let activeSheet = ss.getActiveSheet();
let sheetName = activeSheet.getName();

let sheet1 = ss.getSheetByName("Sheet1");

let loopOptionRange = sheet1.getRange(4, 4);
let loopOption = loopOptionRange.getValue();
let loopStatusRange = sheet1.getRange(4, 7);

function onEdit(e) {
}

function onOpen() {
  loopOptionRange.setValue("Choose");
  sheet1.getRange(4, 5).setValue(5);
  loopStatusRange.setValue("");
}

function loopTest() {
  const startRow = 4; //table head
  const lastRow = sheet1.getLastRow();
  sheet1.getRange(4, 6).setValue(lastRow);
  const startRowLoop = startRow   1; //first row of looping

  try {
    for (i = startRowLoop; i <= lastRow; i  ) {
      const testStatus = sheet1.getRange(i, 3).getValue();
      sheet1.getRange(4, 5).setValue(i);

      if (testStatus == "") {
        sheet1.getRange(i, 1, 1, 2).setFontWeight("bold");
      } else {
        sheet1.getRange(i, 1, 1, 3).setFontWeight("normal");
      }
    }

    loopStatusRange.setValue("Done");
    loopOptionRange.setValue("Choose");

  } catch (error) {
    app.getUi().alert(`An error occured.`);
  }
}

if (sheetName === "Sheet1"){
  if (loopOption == "Yes") {
    loopStatusRange.setValue("Processing ...");
    loopTest();
  } else if (loopOption === "Cancel") {
      loopOptionRange.setValue("Choose");
  }
}

LOOP TEST - Google Sheets file

CodePudding user response：

When I saw your script, getValue, setValue and setFontWeight are used in a loop. In this case, the process cost will become high. I thought that this might be the reason for your issue. In order to reduce the process cost of your script, how about the following modification?

From:

for (i = startRowLoop; i <= lastRow; i  ) {
  const testStatus = sheet1.getRange(i, 3).getValue();
  sheet1.getRange(4, 5).setValue(i);

  if (testStatus == "") {
    sheet1.getRange(i, 1, 1, 2).setFontWeight("bold");
  } else {
    sheet1.getRange(i, 1, 1, 3).setFontWeight("normal");
  }
}

To:

const range = sheet1.getRange(startRow, 3, lastRow - startRowLoop);
const values = range.getDisplayValues().map(([c]) => [c ? null : "bold"]);
range.offset(0, -1).setFontWeights(values);

Note:

• About Is it really because of "the high process cost" you mentioned in your answer?, when I saw your script for the 1st time, I thought that the reason for your issue might be due to the process cost. Because, when the script is run by OnEdit trigger, the maximum execution time is 30 seconds. And, when I tested your script and your Spreadsheet, when I saw the log after the script was stopped, an error related to the over of the maximum execution time. By this, I resulted that the reason for your issue is due to the process cost of the script.

References:

• map()
• setFontWeights(fontWeights)