Search code examples
arraysif-statementgoogle-apps-scriptoperators

How To Shorten a List of OR || Operators in an Google Apps Script


I'm trying to customize the following GAS script from ziganotschka to add background colors to all columns of the sheet when the event (manually entering a value in a cell) is triggered.

I thought of using an array instead of the multiple || OR statements, but I'm not sure how to do it.

ziganotschka script (with my custom multiple || OR statements):

function onEdit(e) {
  var c = e.range.getColumn();
  if(c == 1 || c == 2){
    var text = e.value;
    var sheet = SpreadsheetApp.getActive().getActiveSheet();
    var range = sheet.getRange(1,1,sheet.getLastRow(),2);
    var values = range.getValues();
    var array = [];
    var row = e.range.getRow();
    for (var i =0; i <values.length; i++){
      if(row!=(i+1))
      {
        array.push(values[i][0]);
      }
    }
    if(array.indexOf(text)==-1){
      var backgrounds = range.getBackgrounds();
      var color = getRandomColor();
      while(backgrounds.indexOf(color)>-1){
        color = getRandomColor();
      }
      buildConditionalFormatting(text, color)
    }
  } 
}

function getRandomColor() {
  var letters = '0123456789abcdef';
  var color = '#';
  for (var i = 0; i < 6; i++) {
    color += letters[Math.floor(Math.random() * 16)];
  }
  return color;
}


function buildConditionalFormatting(text, color){
  var sheet = SpreadsheetApp.getActiveSheet();
  var formattingRange = sheet.getRange("A:B");
  var rule = SpreadsheetApp.newConditionalFormatRule()
  .whenTextEqualTo(text)
  .setBackground(color)
  .setRanges([formattingRange])
  .build();
  var rules = sheet.getConditionalFormatRules();
  rules.push(rule);
  sheet.setConditionalFormatRules(rules);

}

My request in short, how to get any index (the ???) from the allColumns array below to use as Column number in the if statement beneath?

function onEdit(e) {
  const allColumns = [1, 2];
  var c = e.range.getColumn();
  if(c == ??? ){

My goal would be to find a way to avoid having to use multiple || OR statements for each column (potentially an indefinite number of columns, depending on the trigger even involving entering text in any cell).

I've searched for a way to use an array in place of the || OR statements, and I've found so far the array.includes() method from this article How to Easily Shorten Long Lists of OR(||) Operators in Your Code.

But my code doesn't work, here's what I've tried:

function onEdit(e) {
  const allColumns = [1, 2];
  var c = e.range.getColumn();
  if(c == allColumns.includes(e)){

I've also considered Getting a random value from a JavaScript array, and How to create an array containing 1...N. But to not avail too.

My code:

function onEdit(e) {
  const allColumns = [1, 2];
  const random = Math.floor(Math.random() * allColumns.length);
  var c = e.range.getColumn();
  if(c == random ){

My code:

function onEdit(e) {
  const allColumns = _.range(1, 2);
  const random = Math.floor(Math.random() * allColumns.length);
  var c = e.range.getColumn();
  if(c == random ){

The sample Sheet :


Solution

  • e is the event object {} with various properties

    c is the column number.

    if(c == allColumns.includes(e)){ is incorrect. The script is asking to check whether allColumns includes object e. That'll always return false. The correct syntax is:

    if(allColumns.includes(c)){
    

    Practice Array.includes.