Why is batch operating in Google Sheets App Script such as "setFontWeights(Array)" functio-CodePudding

I found out that there was an issue in my Google App Script. The use of setFontWeights(Array) function did not work. I did another test on setValues(Array) function and it did not work either.

What I want to do:

Choose the option "Yes" to start setting the font weights.
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"

Issue: To test the error result, COMMENT line 46 to line 50 and UNCOMMENT line 53. The codes from line 46 to line 50 are the current solution I have as line 53 does not work.

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 {

    let textFormats = new Array(500);
    for (x = startRowLoop; x <= lastRow; x  ) {
      const testStatus = sheet1.getRange(x, 3).getValue();
      textFormats[x] = new Array(3);
     
      if (testStatus == "") {
        for (y = 1; y <= 3; y  ) {
          textFormats[x][y] = "bold";
        }
      } else {
        for (y = 1; y <= 3; y  ) {
          textFormats[x][y] = "normal";
        }
      }
    }

    //LINE 46 to 50 WORKS
    for (x = startRowLoop; x <= lastRow; x  ) {                   //LINE 46
      for (y = 1; y <= 3; y  ) {                                  //LINE 47
        sheet1.getRange(x, y).setFontWeight(textFormats[x][y]);   //LINE 48
      }                                                           //LINE 49
    }                                                             //LINE 50

    //LINE 53 DOES NOT WORK
    // sheet1.getRange(startRowLoop, 1, lastRow - startRowLoop   1, 3).setFontWeights(textFormats); //LINE 53

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

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

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

BATCH OPERATING - ARRAY - Google Sheets File

CodePudding user response：

In your script, let textFormats = new Array(500); is declaread, and you are using this array in the loop of for (x = startRowLoop; x <= lastRow; x ) {,,,}. In this case, the top 5 elements are empty. And, each element of textFormats is created in the loop of for (y = 1; y <= 3; y ) {,,,}. In this case, each element has 4 elements. I think that in this case, an error occurs at sheet1.getRange(startRowLoop, 1, lastRow - startRowLoop 1, 3).setFontWeights(textFormats);. I think that these are the reason of your issue.

When these points are reflected to your script, it becomes as follows.

From:

let textFormats = new Array(500);
for (x = startRowLoop; x <= lastRow; x  ) {
  const testStatus = sheet1.getRange(x, 3).getValue();
  textFormats[x] = new Array(3);
 
  if (testStatus == "") {
    for (y = 1; y <= 3; y  ) {
      textFormats[x][y] = "bold";
    }
  } else {
    for (y = 1; y <= 3; y  ) {
      textFormats[x][y] = "normal";
    }
  }
}

To:

var values = sheet1.getRange(startRowLoop, 1, lastRow - 4, 3).getValues();
var textFormats = values.map(([a,b,c]) => c == "" ? Array(3).fill("bold") : Array(3).fill("normal"));

In this case, please use sheet1.getRange(startRowLoop, 1, lastRow - startRowLoop 1, 3).setFontWeights(textFormats); instead of for (x = startRowLoop; x <= lastRow; x ) {,,,}.

References:

map()

CodePudding user response：

If you reorganize it like this it seems to run okay but I only copied Sheet1

function onEdit(e) {
}
function onOpen() {
  loopOptionRange.setValue("Choose");
  sheet1.getRange(4, 5).setValue(5);
  loopStatusRange.setValue("");
}
//I ran this function with no errors
function myfunk() {
  let ss = SpreadsheetApp.getActive();
  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);
  if (sheetName === "Sheet1") {
    if (loopOption == "Yes") {
      loopStatusRange.setValue("Processing ...");
      loopTest();
    } else if (loopOption === "Cancel") {
      loopOptionRange.setValue("Choose");
    }
  }
}
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 {
    let textFormats = new Array(500);
    for (x = startRowLoop; x <= lastRow; x  ) {
      const testStatus = sheet1.getRange(x, 3).getValue();
      textFormats[x] = new Array(3);
      if (testStatus == "") {
        for (y = 1; y <= 3; y  ) {
          textFormats[x][y] = "bold";
        }
      } else {
        for (y = 1; y <= 3; y  ) {
          textFormats[x][y] = "normal";
        }
      }
    }
    for (x = startRowLoop; x <= lastRow; x  ) {                   //LINE 46
      for (y = 1; y <= 3; y  ) {                                  //LINE 47
        sheet1.getRange(x, y).setFontWeight(textFormats[x][y]);   //LINE 48
      }                                                           //LINE 49
    }                                                             //LINE 50
    sheet1.getRange(startRowLoop, 1, lastRow - startRowLoop   1, 3).setFontWeights(textFormats); //LINE 53
    loopStatusRange.setValue("Done");
    loopOptionRange.setValue("Choose");
  } catch (error) {
    app.getUi().alert(`An error occurred.`);
  }
}