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

Question

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 :

TheMaster · Accepted Answer · 2021-12-26T00:55:30.293

1

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.

edited Dec 26 '21 at 00:55

answered Dec 25 '21 at 22:17

TheMaster

45,448
6
62
85

it works great thank you! The one thing I did not try :). I'll try now to understand why it work and will be back later. Be well! – Lod Dec 25 '21 at 22:27
Ah. I think I understand now. My wrong `if(c == allColumns.includes(e)){ ` meant I was trying to know if the whole array was contained in the Column. But with your solution `if(allColumns.includes(c)){` it means rather the opposite: if the (whole) column is contained within the array (as a single element of the array, not the whole array)? The includes()'s parameter ('c' for this example) is the array index? It amounts to asking if the c column (any column) is an index of the array? The reference you shared helped a lot thanks! I had a hard time working with the GetColumn(). – Lod Dec 25 '21 at 22:53
1

@Lod `e` is a event object. It contains all the listed objects in [`debug_e` in this answer](https://stackoverflow.com/a/46860052/). `allColumns.includes(e)` would be something like calling `allColumns.includes({range:{},value:''})`. `includes` has nothing to do with index. It just checks if any of the array's value is equal to the parameter passed. `c` would be number column, like 1. It's like calling ``[1,2].includes(1)`` Practice more you'll get it. – TheMaster Dec 26 '21 at 00:54
thanks that helps a lot! I'll practice arrays more next. Be well! – Lod Dec 26 '21 at 11:35

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

1 Answers1