如何在Google表格中对两列中的更改进行日期戳记 [英] How to date stamp a change in two columns in google sheets

问题描述

我有一张包含2列（A和B）的表格，其中的值来自另一张工作表并链接。我想根据列上次编辑的时间在工作表上盖印日期。换句话说，当源表单更改A或B或两者中的数据点时，日期戳记将放置在C4中作为示例。下次更改时，无论是一天还是一周，日期都会在C4更改为其更改日期。可能是自从我们开始的日期和时间。

解决方案

给定您的数据和您想要的列之间的隔离层时间戳，你可能会需要一些超出简单编辑触发器功能的东西。编辑触发器函数的在值更改是由用户操作引起时很好，但否则它们是有限的（脚本执行不会激活它们）。
$ b

一种可能的解决方案是使用简单的编辑触发器在源表输入被更改时标记，并在报告表中引用每个相应日期单元的相关最大值输入到给定的报告中。



另一种可能的解决方案是实现一种比较方法，该方法由源表单上的任何系统更改数据激活，并比较存储的A& amp ; B的当前值为A& B，如果更改，则更新时间戳和报告的备份。



这个比较&备份功能：



  var reportSheetName =Report，timeStampCell =C4，numReportHeaders = 1; 
函数stampAndBackup（）{
 //由数据插入方法调用/某些on edit触发函数
 //可能导致数据更新和A& B在报告单上。 
 var wb = SpreadsheetApp.getActive（）; 
 var backup = wb.getSheetByName（reportSheetName +_Backup）; 
 var report = wb.getSheetByName（reportSheetName）; 
 if（！report ||！backup）抛出新的错误（Missing required sheet（s）。）; 
 
 //刷新等待写入（例如公式重新计算）。 
 SpreadsheetApp.flush（）; 
 
 //载入来自report&报告的备份。 （假设：两张表都有数据。）
 var db = backup.getDataRange（）。getValues（）; 
 //假设：cols A& B是报表上大多数数据的列。 
 var data = report.getDataRange（）。getValues（）; 
 if（numReportHeaders> 0）data.splice（0，numReportHeaders）; 
 
 //假设：db&数据是有效的数组。 
 if（！hasDifferentElements_（db，data））
 return; 
 
 backup.clearContents（）; 
 SpreadsheetApp.flush（）; 
 
 backup.getRange（1，1，data.length，data [0] .length）.setValues（data）; 
 report.getRange（timeStampCell）.setValue（new Date（））; 
 
函数hasDifferentElements_（a，b）{
 if（a.length！== b.length）
 return true; 
 
 //需要逐元素比较。 
 var comparisonClass = Math.min（2，Math.min（a [0] .length，b [0] .length））; （var c = 0; c  if（a [$ r 
 for（var r = 0; r  return true; 
 
返回false; 
} 
  

我留下了编写调用 stampAndBackup 给读者，因为它很大程度上依赖于实现。

I have a sheet with 2 columns (A and B) that have values that are derived from another sheet and are linked. I would like to date stamp the sheet based on when the columns were last edited. In other words, when the source sheet changes a data point in A or B or both, a date stamp is placed in C4 as an example. The next time it changes, be it a day or a week, the date changes in C4 to the date it changes. Might as well be date and time since we are at it.

解决方案

Given the separation layer between your data and the column you want timestamped, you're going to probably need something beyond a "simple" on edit trigger function. The on edit trigger functions are great when the value changes are due to user actions, but otherwise they are limited (script executions will not activate them).

One possible solution is to use a simple on edit trigger to stamp when your source sheet inputs are altered, and on your report sheet, reference the relevant maximum value of the corresponding date cells for each of the inputs to the given report.

Another possible solution is to implement a comparison method that is activated by whatever system changes data on the source sheets, and compares stored values for A & B with current values for A & B, and if one changes, then updates the timestamp and the backup of the report.

An example of this comparison & backup function:

var reportSheetName = "Report", timeStampCell = "C4", numReportHeaders = 1;
function stampAndBackup() {
  // Called by data insertion method / some "on edit" triggered function
  // that may have resulted in data updates and changes in A & B on the report sheet.
  var wb = SpreadsheetApp.getActive();
  var backup = wb.getSheetByName(reportSheetName + "_Backup");
  var report = wb.getSheetByName(reportSheetName);
  if (!report || !backup) throw new Error("Missing required sheet(s).");

  // Flush pending writes (e.g. formula recalculations).
  SpreadsheetApp.flush();

  // Load data from report & backup of report. (Assumption: both sheets have data.)
  var db = backup.getDataRange().getValues();
  // Assumption: cols A & B are the columns with most data on the report sheet.
  var data = report.getDataRange().getValues();
  if(numReportHeaders > 0) data.splice(0, numReportHeaders);

  // Assumption: db & data are valid arrays.
  if (!hasDifferentElements_(db, data))
    return;

  backup.clearContents();
  SpreadsheetApp.flush();

  backup.getRange(1, 1, data.length, data[0].length).setValues(data);
  report.getRange(timeStampCell).setValue(new Date());
}
function hasDifferentElements_(a, b) {
  if (a.length !== b.length)
    return true;

  // Element-by-element comparison is needed.
  var comparedColumns = Math.min(2, Math.min(a[0].length, b[0].length));
  for (var r = 0; r < a.length; ++r)
    for (var c = 0; c < comparedColumns; ++c)
      if (a[r][c] !== b[r][c])
        return true;

  return false;
}

I leave writing the code which calls stampAndBackup to the reader, since it is heavily implementation-dependent.

这篇关于如何在Google表格中对两列中的更改进行日期戳记的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！