combining two onEdit Functions

Question

the following 2 codes does the following:

1st Code: Creates a change log sheet named (changeLog) to track the changes that my team does in each cell in Google Sheets in a sheet called (Sheet1).

2nd Code: Prevents cells from being updated. When a user edits a cell on the sheet (named changeLog), it is checked against the same cell on a helper sheet. If the value on the helper sheet is empty, the new value is stored on both sheets.If the value on the helper sheet is not empty, it is copied to the cell on the master sheet, effectively undoing the change (to protect the data already entered by the 1st code in the changeLog sheet)

The Question: both of these codes are onEdit Functions, how can i get them to work together?

(P.s the project trigger is already set to be onEdit, and each function is working by its own)

1st Code:

function onEdit() {
  // This script records changes to the spreadsheet on a "Changelog" sheet.
  // The changelog includes these columns:
  // "Timestamp", "Sheet name", "Cell address", "Column label", "Row label", "Value entered"

  var sheetsToWatch = ['Sheet1', 'add more'];
  // name of the sheet where the changelog is stored
  var changelogSheetName = "Changelog";

  var timestamp = new Date();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var cell = sheet.getActiveCell();
  var sheetName = sheet.getName();

  // if it is the changelog sheet that is being edited, do not record the change
  if (sheetName == changelogSheetName) return;

  // if the sheet name does not appear in sheetsToWatch, do not record the change
  var matchFound = false;
  for (var i = 0; i < sheetsToWatch.length; i++) {
   if (sheetName.match(sheetsToWatch[i])) matchFound = true;
  }
  if (!matchFound) return;

  var columnLabel = sheet.getRange(/* row 3 */ 3, cell.getColumn()).getValue();
  var rowLabel = sheet.getRange(cell.getRow(), /* column B */ 2).getValue();

  var changelogSheet = ss.getSheetByName(changelogSheetName);
  if (!changelogSheet) {
    // no changelog sheet found, create it as the last sheet in the spreadsheet
    changelogSheet = ss.insertSheet(changelogSheetName, ss.getNumSheets());
    // Utilities.sleep(2000); // give time for the new sheet to render before going back
    // ss.setActiveSheet(sheet);
    changelogSheet.appendRow(["Email","Timestamp", "Sheet name", "Cell address", "Column label", "Row label", "Value entered"]);
    changelogSheet.setFrozenRows(1);
  }
  changelogSheet.appendRow([Session.getActiveUser().getEmail(), timestamp, sheetName, cell.getA1Notation(), columnLabel, rowLabel, cell.getValue()]);
}

2nd Code:

function onEdit() {


  var masterSheetName = "Changelog" // sheet where the cells are protected from updates
  var helperSheetName = "Helper" // sheet where the values are copied for later checking
  var firstDataRow = 1; // only take into account edits on or below this row
  var firstDataColumn = 1; // only take into account edits on or to the right of this column

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var masterSheet = ss.getActiveSheet();
  if (masterSheet.getName() != masterSheetName) return;

  var masterCell = masterSheet.getActiveCell();
  if (masterCell.getRow() < firstDataRow || masterCell.getColumn() < firstDataColumn) return;

  var helperSheet = ss.getSheetByName(helperSheetName);
  var helperCell = helperSheet.getRange(masterCell.getA1Notation());
  var newValue = masterCell.getValue();
  var oldValue = helperCell.getValue();

  if (oldValue == "") {
    helperCell.setValue(newValue);
  } else {
    masterCell.setValue(oldValue);
  }
}

Max Makhrov Max Makhrov · Accepted Answer · 2017-12-27T13:00:24

rename your 2 functions, and make 1 onEdit:

function onEdit()
{
  onEdit1(); // run 1-st
  onEdit2(); // run 2-nd
}

combining two onEdit Functions

1 Answers