How to combine 2 on edit scipts in one google sheet

Question

I have added a link to the google sheet that I am working on.Copy of Automated Test ESA record sheet

I have added this code which creates a duplicate tab of the Template sheet and renames it when the Surname and first name are input on the client information sheet (see the below script)

function onEdit(e){
  // Global constants, ensure edit occured on the right sheet/range
  const sh = SpreadsheetApp.getActive();
  const ss = sh.getActiveSheet();
  if (ss.getName() != "Client information" || e.range.columnStart > 2) return;

  // Set the surname and firstname variables, check that both are filled
  let sName = ss.getRange(e.range.rowStart,1).getValue();
  let fName = ss.getRange(e.range.rowStart,2).getValue();
  let name = fName + " " + sName;
  if (sName.length == 0 || fName.length == 0) return;

  // Create the new sheet, rename it, and set the client name on the sheet
  const template = sh.getSheetByName("TEMPLATE");
  let newSheet = template.copyTo(sh);
  newSheet.setName(name);
  newSheet.getRange(1,1).setValue(name);
  
  // Set a hyperlink from the surname on 'Client information' to the new sheet
  ss.getRange(e.range.rowStart,1).setFormula('=HYPERLINK("' + sh.getUrl() + '#gid=' + newSheet.getSheetId() + '","'+sName+'")');
}

I then have this code which allows me to select multiple items from a drop down in google sheets for columns L,M and N.(see the below script)

function onEdit(e) {
  // Spreadsheet object can be retrieved from event via e.source
  var sheetName = e.source.getActiveSheet().getName();
  // Range object can be retrieved from event via e.range
  var range = e.range;
  var column = range.getColumn();
  if (sheetName == "Client information" && column >= 12 && column <= 14 && e.value) {
    var newValue = e.value;
    var oldValue = e.oldValue;
    // first if statement is the default behavior, no need to include
    // the else statement is now combined on the outer if statement
    if (!e.oldValue)
      range.setValue(newValue);
    else {
      if (oldValue.indexOf(newValue) < 0) 
        range.setValue(oldValue + ',' + newValue);
      else
        range.setValue(oldValue);
    }
  }
}

Because these are both onEdit I can't run them at the same time in this sheet as they are conflicting. I don't know how to simplify and combine them both into a single function. Can anyone please help?

user_11075129 · Answer

In all programming languages, it is common to call functions from within other functions

So first, you rename each function with it's own function name. So instead of the first one being called

onEdit(e)

you'll call it maybe

tabMaker(e)

similarly for the second one, you'd call it maybe

dropdownMaker(e)

Then you have a brand new function that just does this:

function onEdit(e){
  tabMaker(e);
  dropdownMaker(e);
}

Now they will both run onEdit().

How to combine 2 on edit scipts in one google sheet

1 Answers