CODEWITHSUNDEEP
google-sheetsgoogle-apps-scripttriggers
Wouter Olyslagers
Wouter Olyslagers

Reputation: 9

making script run for specific tabs in google spreadsheets

I need some help with my code. I'm new to appscripts. I have a script that works wonderful. But i want to make it work in multiple tabs which will be duplicates of 'Layout 1'.

I only want to make this script work in those 'Layout x' tabs. I have other tabs also. I made this script with help of a youtbe video. Edited to my liking and needs. But can't figure out how to make it work in other Layout Tabs.

var mainwsname = 'Layout 1';
// tabs i want to edit below. Need help ?
//var mainwsname = ['Layout 1', 'Layout 2', 'Layout 3', 'Layout 4', 'Layout 5', 'Layout 6', 'Layout 7', 'Layout 8', 'Layout 9', 'Layout 10', 'Layout 11', 'Layout 12'];
var optionsname = 'Options';

var firstLevelColumn = 8;
var secondLevelColumn = 1;
var thirdLevelColumn = 2;
var fourthLevelColumn = 3;

var ws= SpreadsheetApp.getActiveSpreadsheet().getSheetByName(mainwsname);
var wsOptions= SpreadsheetApp.getActiveSpreadsheet().getSheetByName(optionsname);
var options = wsOptions.getRange(2,1,wsOptions.getLastRow()-1,4).getValues(); //4 is getal aantal kolommen in options

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>2)
  {

    applyFirstLevelValidation(val ,r);
  } else if(wsname === mainwsname && c===secondLevelColumn && r>2)
  {
    applySecondLevelValidation(val ,r);
  }

} // end onEdit

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, thirdLevelColumn).clearContent();
      ws.getRange(r, secondLevelColumn).clearDataValidations();
       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, thirdLevelColumn).clearDataValidations();

      ws.getRange(r, fourthLevelColumn).clearContent();
      ws.getRange(r, fourthLevelColumn).clearDataValidations();

    }
    else{
    ws.getRange(r, thirdLevelColumn).clearContent();
    ws.getRange(r, fourthLevelColumn).clearContent();

    var firstLevelColValue= ws.getRange(r, firstLevelColumn).getValue();
    var filteredOptions = options.filter(function(o){return o[0]=== firstLevelColValue && o[1]=== val});
    var thirdColumnValue= filteredOptions.map(function(o){return o[2]});
    var fourthColumnValue= filteredOptions.map(function(o){return o[3]});

     ws.getRange(r,thirdLevelColumn).setValue(thirdColumnValue);
     ws.getRange(r,fourthLevelColumn).setValue(fourthColumnValue);

    }
}
function applyValidationToCell(list,cell)
{

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

  cell.setDataValidation(rule);
}

Upvotes: 0

Views: 122

Answers (3)

Wouter Olyslagers
Wouter Olyslagers

Reputation: 9

Made it slightly better now with an else if function + only declaring var mainwsname once.

(Posting for anyone in the future needing something simular)

var sheet = SpreadsheetApp.getActiveSheet();
  var mainwsname = ' ';
 
  if (sheet.getName() == "Layout 1"){
  mainwsname = 'Layout 1';}
  else if (sheet.getName() == "Layout 2"){
  mainwsname = 'Layout 2';}
  else if (sheet.getName() == "Layout 3"){
  mainwsname = 'Layout 3';}
  else if (sheet.getName() == "Layout 4"){
  mainwsname = 'Layout 4';}
  else if (sheet.getName() == "Layout 5"){
  mainwsname = 'Layout 5';}
  else if (sheet.getName() == "Layout 6"){
  mainwsname = 'Layout 6';}
  else if (sheet.getName() == "Layout 7"){
  mainwsname = 'Layout 7';}
  else if (sheet.getName() == "Layout 8"){
  mainwsname = 'Layout 8';}
  else if (sheet.getName() == "Layout 9"){
  mainwsname = 'Layout 9';}
  else if (sheet.getName() == "Layout 10"){
  mainwsname = 'Layout 10';}

Upvotes: 0

Wouter Olyslagers
Wouter Olyslagers

Reputation: 9

Thanks for the help, however I was not able to make it work.

I wrote some barbaric script to make it work now.

I wrote:

if (sheet.getName() == "Layout 1"){
var mainwsname = 'Layout 1';}

if (sheet.getName() == "Layout 2"){
var mainwsname = 'Layout 2';}

to

if (sheet.getName() == "Layout 10"){
var mainwsname = 'Layout 10';}

Seems like it makes the response time in my worksheets slower, but it works.

Upvotes: 0

Tanaike
Tanaike

Reputation: 201358

  • You want to run the function of onEdit() for only the sheet name of 'Layout x like Layout 1, Layout 2.

If my understanding is correct, how about this modification? Please think of this as just one of several possible answers.

From:

function onEdit(e)
{
  var activeCell = e.range;

To:

function onEdit(e)
{
  var activeCell = e.range;
  if (!/Layout \d+/.test(activeCell.getSheet().getSheetName())) return;  // Added

Note:

  • If there is the case that between Layout and x has no one space, please modify Layout \d+ to Layout ?\d+.

Reference:

If I misunderstood your question and this was not the direction you want, I apologize.

Upvotes: 1

Related Questions