Google-sheets – Trying to run two functions onEdit

google sheetsgoogle-apps-scriptgoogle-apps-script-triggers

I have found two scripts that work independently, but fail when combined. I get this error message

ReferenceError: myfunction2 is not defined onEdit @ Code.gs:3.

I want one field to populate the date time a record was created and another field to populate the date and time of the last edit, basically to track how long it took to close the status on that record. Obviously, I am new to this, so please use lingo I can understand. Here is my code:

function onEdit(e) { 
  myfunction1(e);
  myfunction2(e);
}
  var SHEET_NAME = 'Sheet1';
  var DATETIME_HEADER = 'Call Time';

function myfunction1(e) {

function getDatetimeCol(){
  var headers = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME).getDataRange().getValues().shift();
  var colindex = headers.indexOf(DATETIME_HEADER);
  return colindex+1;
}
 
  var ss = SpreadsheetApp.getActiveSheet();
  var cell = ss.getActiveCell();
  var datecell = ss.getRange(cell.getRowIndex(), getDatetimeCol());
  if (ss.getName() == SHEET_NAME && cell.getColumn() == 1 && !cell.isBlank() && datecell.isBlank()) {      
    datecell.setValue(new Date()).setNumberFormat("MM/dd/yyyy hh:mm:ss A/P");
  }

function myfunction2(e) {
  
  var range = e.range;
  var spreadSheet = e.source;
  var sheetName = spreadSheet.getActiveSheet().getName();
  var row = range.getRow();  
  
  if(sheetName == 'Sheet1')
  {
    var new_date = new Date();
    spreadSheet.getActiveSheet().getRange(row,11).setValue(new_date).setNumberFormat("MM/dd/yyyy hh:mm:ss A/P"); 
  }
  
}

};

Best Answer

You are getting myfunction2 is not defined because myfunction2 is embedded within myfunction1. The outer onEdit() function cannot see it because it is local to myfunction1. Move myfunction2 out of myfunction1 to make it work.

All this becomes easier to manage if you use more standard code style and apply the indentation features of the script editor. Press F1 and choose Format document to indent the code.

Here is your code in a format that is a bit easier to read:

const SHEET_NAME = 'Sheet1';
const DATETIME_HEADER = 'Call Time';

function onEdit(e) {
  myfunction1(e);
  myfunction2(e);
}

function myfunction1(e) {
  const sheet = e.range.getSheet();
  const datecell = sheet.getRange(e.range.rowStart, getDatetimeCol(sheet));
  if (ss.getName() === SHEET_NAME && e.range.columnStart === 1 && !e.range.isBlank() && datecell.isBlank()) {
    datecell
      .setValue(new Date())
      .setNumberFormat("MM/dd/yyyy hh:mm:ss A/P");
  }
}

function myfunction2(e) {
  const sheet = e.range.getSheet();
  if (sheet.getName() === 'Sheet1') {
    sheet.getRange(e.range.rowStart, 11)
      .setValue(new Date())
      .setNumberFormat('MM/dd/yyyy hh:mm:ss A/P');
  }
}

function getDatetimeCol(sheet) {
  const headers = sheet.getDataRange().getValues().shift();
  return headers.indexOf(DATETIME_HEADER) + 1;
}

You may also want to take a look at the many timestamping scripts that make things like this easier to manage, such as the timestampMultipleColumns_ script.

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 page, javascript.info, Mozilla Developer Network and Apps Script at Stack Overflow.

Related Topic