Google Apps Script: How to get all changes during a time frame-CodePudding

I have a spreadsheet that my colleagues update fairly frequently. Those changes need to be reflected in a separate system, and until now I've been manually copying the changes over manually, but I'm trying to figure out if I can automate this process.

I understand that you can use installable triggers to get each time an edit or change event happens, but sometimes a single change event would put the sheet into an invalid state for the other system, so I need to only update the other system after all of the changes are made and the sheet is back to a valid state.

So my question is, is there some way to do this, and what would be the best way? From what I've read, there's no way to access the revision history programmatically. Should I just record the change events on a different sheet? That seems like it would work, but feels really clunky. Is there some way I can get all of the changes that happened during a time frame (especially helpful if it was in a sort of "squashed commit" form so I didn't have to worry about all the intermediate stuff)? Ideally I would be able to just run a scheduled event that would collect and send off the changes once a week or so.

CodePudding user response：

Recording Changes between 10AM and 12Noon

function onEdit(e) {
  e.source.toast('Entry');
  const sh = e.range.getSheet();
  const dt = new Date();
  const dtv = dt.valueOf();
  const th1 = new Date(dt.getFullYear(),dt.getMonth(),dt.getDate(),10).valueOf();
  const th2 = new Date(dt.getFullYear(),dt.getMonth(),dt.getDate(),12).valueOf();
  if (sh.getName() == "Sheet1" && dtv > th1 && dtv < th2) {
    e.source.toast("Gate")
    let log = PropertiesService.getScriptProperties().getProperty("log");
    Logger.log(log);
    PropertiesService.getScriptProperties().setProperty("log",log   `~~~${e.range.getA1Notation()},${e.oldValue},${e.value}`);
  }
}

   function seeLog() {
  const log = PropertiesService.getScriptProperties().getProperty("log").split('~~~').join('\n');
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(`<textarea cols="50" rows="20">${log}</textarea>`),"Log");
}

Sample Log: