Using a For Loop to Insert Checkboxes with TRUE/FALSE value based on presence of a value

EDIT: I immediately noticed after posting I spelled counter wrong. Loop works fine, but if someone could explain the wildcard issue that would be appreciated.

Working on assembling a sheet in our list of clients/lines of business that logs our new business. I have individual sheets for each month, each with a column, with checkboxes, that indicates if something is ‘new business.’ When a checkbox is selected, a row is appended to a separate sheet (NEW BUSINESS) which contains columns for the majority of carriers we work with. I intend to make this work so that the new business sheet inserts a checkbox valued at ‘true’ under the respective carrier column when that carrier is selected. I’m running into two problems:

  • The wildcard I’m using in ‘carrier array’ does not seem to be functioning (when I use the same function with set string value instead of a wildcard, it works fine)
  • The loop function seems to only loop one time. AIG is the first carrier. If a checkbox is selected under the new business column on a separate sheet, a row will appear with a checkbox underneath AIG only.

Here is what I’m working with so far:

function onEdit2(e) {
  const sh = e.range.getSheet();
  if (sh.getName() != "QUOTES IN PROCESS" && sh.getName() != "NEW BUSINESS" && e.range.columnStart == 2 && e.value == "TRUE") {
    e.source.toast("New Business Entered");
    const [a,b,c] = sh.getRange(e.range.rowStart,3,1,3).getValues()[0];
    var d = sh.getRange(e.range.rowStart, 15).getValue();
    const dsh = e.source.getSheetByName("NEW BUSINESS");
    var data = [d,a,b,c];
    
    dsh.appendRow(data);

    var lastRow = dsh.getLastRow();

    var carrier = sh.getRange(e.range.rowStart, 6).getValue();

    const carrierArray = [/.*AIG*./, /.*Amwins.*/, /.*American Bankers.*/, /.*Chubb.*/, /.*Nationwide Private Client.*/, /.*Orchid.*/, /.*Progressive.*/, /.*Jewelers Mutual.*/, /.*Quirk.*/, /.*Lemonade.*/, /.*RLI.*/, /.*RT Specialty.*/, /.*Safeco.*/, /.*Travelers.*/, /.*TWIA.*/, /.*.*/];

    for(var counter = 0; counter <= 16; counter = couter + 1) {
      if (carrier == carrierArray[counter]) {
        dsh.getRange(lastRow, counter+5, 1, 1).insertCheckboxes().check();
      } else if (carrier != carrierArray[counter]) {
        dsh.getRange(lastRow, counter+5, 1, 1).insertCheckboxes();
      };
    }
  } 
}

Here is an example of the one of the individual month sheets, as well as the new business sheet with my desired output:

enter image description here

enter image description here

>Solution :

for the wildcard issue, if you expect something like "FOO AIG BAR" (string) to be equal to /.*AIG.*/ (regular expression), it will not work. You have to use a regular expression method for that.

In your case that would be the .test() method I’d say, which returns a bool if there is a match in the string (cf MDN doc)

Rewriting the for loop.

for(var counter = 0; counter <= 16; counter = counter + 1) { // careful, you wrote 'couter' instead of 'counter'
      if (carrierArray[counter].test(carrier)) {
        dsh.getRange(lastRow, counter+5, 1, 1).insertCheckboxes().check();
      } else { // no need to explicit the condition here
        dsh.getRange(lastRow, counter+5, 1, 1).insertCheckboxes();
      };
}

Finally, having a quick look at your regular expressions, please note that if you have a string like " Sthg Chubb and AIG", it will match the Chubb expression, the AIG one, and the last one (every entry will actually match the last one of your array, I guess it’s on purpose).

Leave a Reply