结合两个onEdit函数 [英] combining two onEdit Functions

问题描述

以下2个代码执行以下操作:

the following 2 codes does the following:

第一个代码:创建一个名为(changeLog)的更改日志表，以跟踪我的团队在名为(Sheet1)的表中对Google表格中每个单元格所做的更改.

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).

第二个代码:防止更新单元格.当用户编辑工作表上的单元格(名为changeLog)时，将对照帮助工作表上的同一单元格对其进行检查.如果帮助表上的值为空，则新值将存储在两张表上;如果帮助表上的值不为空，则将其复制到主表上的单元格中，从而有效地撤消更改(以保护数据)已经由changeLog表中的第一个代码输入)

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)

问题:这两个代码都是onEdit函数，我如何才能使它们一起工作?

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

(请注意，项目触发器已设置为onEdit，并且每个功能都在各自起作用)

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

第一个代码:

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()]);
}

第二个密码:

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);
  }
}

推荐答案

重命名您的2个函数，并进行1个onEdit:

rename your 2 functions, and make 1 on

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

这篇关于结合两个onEdit函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！