CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
The_Train
The_Train

Reputation: 331

Google Sheets Scripts relating to multiple columns

With the help of a guidance video I was linked to on here, I've been able to write the following script which allows me to operate a dependent dropdown on my spreadsheet

function onEdit(){

  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var datass = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Operators");

  var activeCell = ss.getActiveCell();

  if(activeCell.getColumn() == 10 && activeCell.getRow() > 8){

    activeCell.offset(0, 1).clearContent().clearDataValidations();

    var headers = datass.getRange(1, 1, 1, 2).getValues();

    var headersIndex = headers[0].indexOf(activeCell.getValue()) + 1;

    if(headersIndex != 0){

        var validationRange = datass.getRange(2, headersIndex, datass.getLastRow());
        var validationRule = SpreadsheetApp.newDataValidation().requireValueInRange(validationRange).build();
        activeCell.offset(0, 1).setDataValidation(validationRule);

    }

  }



}

It works!!! which is fantastic. However, I have a question which would relate to the following line of the above script

if(activeCell.getColumn() == 10 && activeCell.getRow() > 8){

Column 10 on my spreadsheet relates to column J, which is where the dependent dropdown in column K pulls its information from to supply the correct info in the dependent dropdown, and with this line included, my script works fine. However, I also have repeats of this in columns N (column number 14), R (column number 18) & V (column number 22) which all supply the same information as for column J for the dependent dropdown in the following column.

So basically my question is can I incorporate column numbers 14, 18 and 22 into this script to work in the same way as column 10 whilst keeping them independent of one another? - ie in that I don't want to pick something relating to column J and it duplicates into column N.

I have tried to simply create the following line:

if(activeCell.getColumn() == 10, 14, 18, 22 && activeCell.getRow() > 8){

and this works in the sense that it allows me to work all of my dependent dropdowns independently of one another as I require. However, this is wreaking havoc with other data validation within my spreadsheet - in fact the next 2 cells after each of my dependent dropdowns are checkboxes and when using this amended line, my selecting something from a dependent dropdown is actually then causing the deletion of the checkbox in the next cell - which I obviously don't want to happen!

Now, I have fully solved the problem by creating multiple functions on my script for each of my columns by creating the following at the top of my script

function onEdit(){
  j();
  n();
  r();
  v();

}

and then setting separate functions for each of these and this works absolutely perfectly. So I have a solution and yet here I am asking for help and that is simply because I would prefer to have everything within one short script than have to have 4 or 5 scripts for each of my columns. Is this possible or do I already have the best solution to this particular problem?

Upvotes: 0

Views: 3574

Answers (1)

ziganotschka
ziganotschka

Reputation: 26836

The issue is cause by the syntax of

if(activeCell.getColumn() == 10, 14, 18, 22 && activeCell.getRow() > 8)

To apply your function simultaneously to several columns you need to

use the logical OR operator ||.

This means: modify

if(activeCell.getColumn() == 10, 14, 18, 22 && activeCell.getRow() > 8)

to

if((activeCell.getColumn() == 10 || activeCell.getColumn() == 14 || activeCell.getColumn() == 18 || activeCell.getColumn() == 22) && activeCell.getRow() > 8)

Additionally: To make your code simpler, rewrite it as following:

  var active = activeCell.getColumn();
  if((active == 10 || active == 14 || active == 18 || active == 22) && activeCell.getRow() > 8)

Upvotes: 1

Related Questions