Copied script not working on different google sheet

Question

I have the below script working fine on my "Order sheet" (project name is "Order sheet code" file is "Code.gs")

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var activeCell = sheet.getActiveCell();
  var col = activeCell.getColumn();
  var row = activeCell.getRow();

  if (e.range.getSheet().getSheetName() == 'Order Sheet') {

    if (col == 6) { // assuming status is in column 6 (E), adapt if needed
      if (col == 6 && sheet.getRange(row, col + 1).isBlank())
        sheet.getRange(row, col + 1).setValue(new Date()).setNumberFormat('DDD, dd MMM'); // change the display format to your preference here  
    }
    if (col == 6 && sheet.getRange(row, col).isBlank())
      sheet.getRange(row, col + 1).setValue("")
  }
}

Now I have a new sheet, "Work Hours" where I want to utilize the same sort of functionality (different column and time instead of date), but even when I copy this script to a new script file (Project "Work Hours code", file name "Code.gs") and change the SheetName (line 7) to 'Work Hours' it doesn't seem to work.

I thought maybe it was because I had 2 projects with the Code.gs file, but changing that to Code2.gs didn't make a difference (changed it back, just in case).

Also, eventually I want this to run even when the sheet is closed, because I need it to record when data comes in from an external source. Is that possible? I presume I'd have the change the variable for "sheet" since it wouldn't be active?

Please add all relevant tags when asking a question, it hurts visibility If you omit them. Always try to include product tag like google-sheets, language (google-apps-script) and API-specific (google-sheets-api) where applicable. Also please avoid chit-chat and “thank you” comments in questions. Finally, avoid using “not working” in title or in body, there is always a specific issue that you should correctly describe instead. — Oleg Valter

Cooper Cooper · Accepted Answer · 2020-07-31T03:19:14

This will work on both sheets:

function onEdit(e) {
  var sh= e.range.getSheet()
  var shts=['Order Sheet','Work Hours']
  if (shts.indexOf(sh.getName()!=-1)) {
  //do whatever you want in here
   
  }
}

or if want to do different things on each sheet:

function onEdit(e) {
  var sh= e.range.getSheet()
  if(sh.getName() == 'Order Sheet') {
  }
  if(sh.getName() == 'Work Order') {
  }
}

Copied script not working on different google sheet

2 Answers