Google Sheets script - code optimization - one script, two sheets, same workbook

Question

I have one workbook with multiple sheets and I've been running some simple scripts on two of those sheets (those sheets are basically copies of one another).

Once script creates a dropdown based on the value entered in one of the cells, while the second script adds a timestamp based on when the new dropdown was edited.

Initially I had two onEdit functions running on the first sheet. Then I created a copy of these functions, with small amendments, to run on another sheet(same workbook)

Given that I don't really know how to have a separate script per worksheet I now have a one script with 4 similar onEdit functions.

Since I'm a total newbie I'm sure there's a better, more efficient way to write this code. I'd appreciate your help in optimizing this.

function onEdit(e) {

addTimestamp(e);
addTimestamp2(e);
Dropdown(e);
Dropdown2(e);

}

function addTimestamp(e) {

  var startRow = 2;
  var targetColumn = 10;
  var ws = "Tracker1";

var row = e.range.get.Row();
var col = e.range.getColumn();

if(col === targetColumn && row >= startRow && e.source.getActiveSheet().getName() === ws) {

var currentDate = new Date();
e.source.getActiveSheet().getRange(row,11).setValue(currentDate);

}
}

function addTimestamp2(e) {

  var startRow = 2;
  var targetColumn = 10;
  var ws = "Tracker2";

var row = e.range.get.Row();
var col = e.range.getColumn();

if(col === targetColumn && row >= startRow && e.source.getActiveSheet().getName() === ws) {

var currentDate = new Date();
e.source.getActiveSheet().getRange(row,11).setValue(currentDate);

}
}

function Dropdown(){
 var tabLists = "StatusFlow";
 var tabValidation = "Tracker1"
 var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 var datass = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(tabLists);

 var activeCell = ss.getActiveCell();

 if(activeCell.getColumn() == 9 && activeCell.getRow() > 1 && ss.getSheetName() == tabValidation){
  
  activeCell.offset(o,1).clearContent().clearDataValidations();

  var makes = datass.getRange(1,1,1, datass.getLastColumn()).getValues();

  var makeIndex = makes[0].indexOf(activeCell.getValue()) +1;

  if(makeIndex !=0) {

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

}
}
}

function Dropdown2(){
 var tabLists = "StatusFlow";
 var tabValidation = "Tracker2"
 var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 var datass = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(tabLists);

 var activeCell = ss.getActiveCell();

 if(activeCell.getColumn() == 9 && activeCell.getRow() > 1 && ss.getSheetName() == tabValidation){
  
  activeCell.offset(o,1).clearContent().clearDataValidations();

  var makes = datass.getRange(1,1,1, datass.getLastColumn()).getValues();

  var makeIndex = makes[0].indexOf(activeCell.getValue()) +1;

  if(makeIndex !=0) {

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

}
}
}

ADW ADW · Accepted Answer · 2021-01-23T12:12:00

here is a more efficient way to replace the two addTimestamp functions:

function onEdit(e) {
  var activeSheet = e.source.getActiveSheet();
  var activeSName = activeSheet.getName();
  var row = e.range.get.Row();
  var col = e.range.getColumn();

  if ((activeSName == 'Tracker1' || activeSName == 'Tracker2') && col === 10 && row >= 2) { 
    activeSheet.getRange(row, 11).setValue(new Date());
  }
}

You could give the two Dropdown functions a shot and see if you can do something similar. Nothing like learning-by-doing. :-D

Just be careful that in onEdit you are passing e to these Dropdown functions. But not using them in these functions.

Google Sheets script - code optimization - one script, two sheets, same workbook

1 Answers