javascript - 将具有更新时间戳的行移动到另一个 Google 表格

Question

我正在使用两个脚本来实现我的目标。当对字段进行更新时，第一个脚本会在相邻列中插入时间戳。这很好用。下一个脚本监视时间戳列，当时间戳更改时，将整行复制到“最近更新”表中。然后，我将使用 Awesome Table 插件为所有最近的更新创建新闻提要。

当时间戳列为空白并进行编辑时，时间戳将适当地输入到时间戳列中。第二个脚本将其拾取并将其裁剪到我的“最近更新”表中......

...但是如果进行了先前的更新并且时间戳字段已经存在，则脚本运行没有错误，但不会将新行复制到“最近更新”。每次时间戳字段更改时，如何让行粘贴？

/**
 * @file Copy row to new cell when date value changes
 * {@link https://support.google.com/docs/thread/13191603}
 */
/**
 * Runs the snippet.
 * Please, register this function for EDIT event
 * once from the owner of the Spreadsheet
 *
 * @param {GoogleAppsScript.Events.SheetsOnEdit} e
 */
function CopyUpdates(e) {
  if (!e) return;
  var currentSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var currentRange = currentSheet.getActiveRange();
  var currentRow = currentRange.getRow();
  if (
    e.value &&
    currentSheet.getName() == "Open Actions - Cutover Punchlist" , "Open Actions - FSA Interfaces" , "Open Actions - General" &&
    currentRow > 2 &&
    currentRange.getColumn() == 9
  ) {
    var dataRange = currentSheet.getRange(currentRow + ':' + currentRow);
    var destinationSheet = currentSheet.getParent().getSheetByName("RecentUpdates");
    var destinationRow = destinationSheet.getLastRow() + 1;
    dataRange.copyTo(destinationSheet.getRange(destinationRow, 1), {
      contentsOnly: true
    });
  }
}

标签： javascriptgoogle-apps-scriptgoogle-sheetsawesometable

Answer 1

该脚本在测试后已经正常工作。也许有些东西会干扰您的触发器，这可能是由第一个功能引起的。因此，您将需要合并它们。

我将其重命名为onEdit(e)。我合并了它们，因为它们实际上是 的子集onEdit(e)，只是具有不同的条件。将它们合并到同一个函数下应该没问题。

代码：

var currentSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

function onEdit(e) {
  if (!e) return;
  var currentRange = currentSheet.getActiveRange();
  var currentRow = currentRange.getRow();

  if ( e.value &&
    currentSheet.getName() == "Open Actions - Cutover Punchlist" , "Open Actions - FSA Interfaces" , "Open Actions - General" &&
    currentRow > 2 ) {
    if (currentRange.getColumn() == 8) { // adjacent column (first function conversion, if H column is edited)
      var adjacentCell = currentSheet.getRange('H' + currentRow);
      var timestampCell = adjacentCell.offset(0, 1);

      timestampCell.setValue(new Date());

      // since H is edited, timestamp column is updated
      // so we copy (regardless if the old value is blank or a timestamp)
      copyUpdates(currentRow);
    }
    if (currentRange.getColumn() == 9) { // timestamp column (second function conversion, if I column is edited)
      // edited timestamp manually, copy
      copyUpdates(currentRow);
    }
  }
}

function copyUpdates(currentRow) {
  var dataRange = currentSheet.getRange(currentRow + ':' + currentRow);
  var destinationSheet = currentSheet.getParent().getSheetByName("RecentUpdates");
  var destinationRow = destinationSheet.getLastRow() + 1;

  dataRange.copyTo(destinationSheet.getRange(destinationRow, 1), {
    contentsOnly: true
  });
}

样本数据：

样品测试：

 1. Wrote "add timestamp" to "H3" (Should trigger your first function)
 2. Wrote "add timestamp" to "H4" (Should trigger your first function)
 3. Edited "H4" to "change timestamp" (Should trigger your first function)
 4. Edited "I4" to "1/22/2021" (Should trigger your second function)

样本数据结果：

最近更新结果：