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:
>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).