[Solved] Combine search for characters in two columns [closed]

by


DRY (don’t repeat yourself) code version looks like

var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var containsKey = function(range) {
    var column = sh.getRange(range).getValues();
    var values = column.reduce(function (accumulator, currentValue) {
        return accumulator.concat(currentValue);
    }, []);
    return values.some(function (value) { return /[MWFmwf]/.test(value); });
}

if (!(containsKey("Imported!E2:E50") || containsKey("Imported!I2:I50"))) {
    genderMatch();
    SpreadsheetApp.flush();
}
ageCategories();

If you want more than two ranges to check

var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var containsKey = function(range) {
    var column = sh.getRange(range).getValues();
    var values = column.reduce(function (accumulator, currentValue) {
        return accumulator.concat(currentValue);
    }, []);
    return values.some(function (value) { return /[MWFmwf]/.test(value); });
}

var ranges = ["Imported!E2:E50", "Imported!I2:I50"];
if (!ranges.some(containsKey)) {
    genderMatch();
    SpreadsheetApp.flush();
}
ageCategories();

Though can be even more succinct using ES6+ syntax – but as per comment below Google Apps Script doesn’t support ES6+ by default so this may be redundant

ES6+ version, includes code that would allow more than 2 ranges to be checked for the key (not sure that’s relevant to this code, but may be useful for similar situations)

const sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

const containsKey = range => sh.getRange(range).getValues().flat().some(value => /[MWFmwf]/.test(value));

const ranges = ["Imported!E2:E50", "Imported!I2:I50"]; // allows more than two ranges if that's likely to happen
if (!ranges.some(containsKey)) {
    genderMatch();
    SpreadsheetApp.flush();
}
ageCategories();

8

solved Combine search for characters in two columns [closed]