0

My programming knowledge is weak at best. I would like to be able to run the below script over a few of my sheets in google-sheets. The sheets names would be "Liquor Room", "Mezz" "Cooler", "Bar", "Events", "Knots". They all would pull from the "codes" sheet. Thank you to anyone who can help me and explain it to me in simpleton terms.


var mainWsName = "Liquor Room";
var optionsWsName = "codes";
var firstLevelColumn = 1;
var secondLevelColumn = 2;
var thirdLevelColumn = 3;

var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(mainWsName);
var wsOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(optionsWsName);
var options = wsOptions.getRange(2,1,wsOptions.getLastRow()-1,3).getValues();




function onEdit(e){
  var activeCell = e.range;
  var val = activeCell.getValue();
  var r = activeCell.getRow();
  var c = activeCell.getColumn();
  var wsName = activeCell.getSheet().getName();
  if(wsName === mainWsName && c === firstLevelColumn && r > 1){
    applyFirstLevelValidation(val,r);
  } else if(wsName === mainWsName && c === secondLevelColumn && r > 1){
    applySecondLevelValidation(val,r);
  }


}

function applyFirstLevelValidation(val,r){

    if(val === ""){
      ws.getRange(r, secondLevelColumn).clearContent();
      ws.getRange(r, secondLevelColumn).clearDataValidations();
      ws.getRange(r, thirdLevelColumn).clearContent();
      ws.getRange(r, thirdLevelColumn).clearDataValidations();
    } else {
      ws.getRange(r, secondLevelColumn).clearContent();
      ws.getRange(r, secondLevelColumn).clearDataValidations();
      ws.getRange(r, thirdLevelColumn).clearContent();
      ws.getRange(r, thirdLevelColumn).clearDataValidations();
      var filteredOptions = options.filter(function(o){ return o[0] === val });
      var listToApply = filteredOptions.map(function(o){ return o[1]})
      var cell = ws.getRange(r,secondLevelColumn);
      applyValidationToCell(listToApply,cell);
    }

}



function applySecondLevelValidation(val,r){

    if(val === ""){
      ws.getRange(r, thirdLevelColumn).clearContent();
      ws.getRange(r, secondLevelColumn).clearDataValidations();
    } else {
      ws.getRange(r, thirdLevelColumn).clearContent();
      var firstLevelColValue = ws.getRange(r,firstLevelColumn).getValue();
      var filteredOptions = options.filter(function(o){ return o[0] === firstLevelColValue && o[1] === val });
      var listToApply = filteredOptions.map(function(o){ return o[2]});
      var cell = ws.getRange(r,thirdLevelColumn);
      applyValidationToCell(listToApply,cell);
    }

}

function applyValidationToCell(list,cell){

  var rule = SpreadsheetApp
  .newDataValidation()
  .requireValueInList(list)
  .setAllowInvalid(false)
  .build();

  cell.setDataValidation(rule);
  }
1
  • Welcome to Web Applications. Please briefly describe what your script does, what you have tried to adapt to your needs as well a brief description of your search efforts as is suggested in How to Ask.
    – Rubén
    Aug 10 at 23:40
0

You can use a regular expression to match multiple sheet names. Change lines 1, 7, 20 and 22 as follows:

const mainWsName = /^(Liquor Room|Mezz|Cooler|Bar|Events|Knots)$/i;
const ws = SpreadsheetApp.getActiveSheet();
  if (wsName.match(mainWsName) && c === firstLevelColumn && r > 1) {
  } else if (wsName.match(mainWsName) && c === secondLevelColumn && r > 1) {

You are using an onEdit(e) trigger which means that the code will run every time any value in the spreadsheet is hand edited. The options global variable is initialized each time with Range.getValues() which hurts your spreadsheet performance. You may want to move the definitions of wsOptions and options to inside of applyFirstLevelValidation() and applySecondLevelValidation().

Some of the best resources for learning Google Apps Script include the Beginner's Guide, the New Apps Script Editor guide, the Fundamentals of Apps Script with Google Sheets codelab, the Extending Google Sheets pagejavascript.infoMozilla Developer Network and Apps Script at Stack Overflow.

1
  • THANK YOU SOOOOOOO MUCH!!!! YOU DID IT!
    – Steve
    Aug 11 at 23:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.