Moving rows with an updated timestamp to another Google Sheet

Question

I'm using two scripts to achieve my goal. The first script inserts a timestamp in the adjacent column when an update is made to a field. This works great. The next script monitors the timestamp column and when the timestamp changes, copy entire row to a "recent updates" sheet. I'm then going to use the Awesome Table plugin to create a news feed for all the recent updates.

When the timestamp column is blank and an edit is made the timestamp is appropriately entered into the timestamp column. The second script picks it up and crops it into my "recent updates" sheet...

...but if a previous update was made and the timestamp field is already present the script runs without error, but does not copy the new row to "recent updates". How can I get the row to paste every time the timestamp field changes?

/**
 * @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
    });
  }
}

NightEye · Answer 1 · 2021-01-21T15:49:11.650

The script already is working properly upon testing. Maybe there is something that interferes with your trigger which can be caused by the first function. Thus you will need to merge them.

I renamed it to onEdit(e) instead. I merged them since they are actually a subset of onEdit(e), just having different conditions. It should be fine to merge them under the same function.

Code:

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

Sample Data:

Sample Testing:

 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)

Sample Data outcome:

RecentUpdates outcome:

Moving rows with an updated timestamp to another Google Sheet

1 Answers1