History-gs.gs

// TODO
//  1. test
//  2. error handler
//  3. instructions on trigger installation

// AKfycbz7GZDZb3Yt1usqUkIu_lNOp4tW0xUKuFR9K_fIksIGuep2TeJ5RU0J


// the script assumes this structure of historic table:
//  ______________________________________
// |     A     |     B     |      C       |
//  ======================================
// | Date From | Date To   | Original Data|


const dict = {
  source: {  // you'll need view access to this file
    fileId: 'ZZZZZZZZZZZZZZZZZZZ2yfBcMmC-rmQawvuHWqr7qGA0',
    range: "'From'!A2:A"
  },
  history: { // you'll need to have editing access to this file
    fileId: 'ZZZZZZZZZZZZZZZZZZZ2yfBcMmC-rmQawvuHWqr7qGA0',
    sheet: 'History',
    rowStart: 2
  },
  lastDay: new Date('3100-12-31'), // some far away day in the future
  notifications: {
    emailForErrors: '[email protected]', // errors will be sent here
    emailsForNews: ['[email protected]'], // add new emails for news
  }
}

function test() {
  update_();
  // var sets = dict;
  // Logger.log('Error in script ' + SpreadsheetApp.openById(sets.history.fileId).getName())
}

function update() {
  var sets = dict;
  try {
    call_(update_);// use gas retry
  } catch (err) {
    var id = ScriptApp.getScriptId();
    var url = 'https://script.google.com/d/' + id + '/edit';
    GmailApp.sendEmail(
      sets.notifications.emailForErrors,
      'Error in script: ' + SpreadsheetApp.openById(sets.history.fileId).getName(),
      'body', {
      htmlBody: err + '<br>' + url,
    }
    );
  }

}

function update_() {
  var sets = dict;

  // 1. Read data from source
  var sourceData = [];
  var sourceKeys = {}; // unique list of source kays. Source data must be unique for check
  var sourceFile = SpreadsheetApp.openById(sets.source.fileId);
  var sourceRange = sourceFile.getRange(sets.source.range);
  var sd0 = sourceRange.getValues(); // the data with empty cells
  for (let i = 0; i < sd0.length; i++) {
    let row = sd0[i];
    let key = row.join('');
    if (!(key in sourceKeys) && key !== '') {
      sourceData.push(row);
      sourceKeys[key] = row; // add to dictionary
    }
  }
  if (sourceData.length === 0) { return -1; } // no source data :()

  // 2. Read historic data
  const today = new Date(); // for history
  var histFile = SpreadsheetApp.openById(sets.history.fileId);
  var histSheet = histFile.getSheetByName(sets.history.sheet);
  var histLastRow = histFile.getLastRow();
  if (histLastRow < sets.history.rowStart) {
    // no data was entered previously
    // enter the whole range
    let dataOut = [];
    for (let i = 0; i < sourceData.length; i++) {
      let row = [today, sets.lastDay, ...sourceData[i]];
      dataOut.push(row);
    }
    histSheet.getRange(sets.history.rowStart, 1, dataOut.length, dataOut[0].length).setValues(dataOut);
  }
  else {
    // 3. Compare history and new fact
    var histData = [];
    var hd0 = histSheet.getRange(sets.history.rowStart, 1, histLastRow - sets.history.rowStart + 1, 2 + sourceData[0].length).getValues();
    var histKeys = {};
    for (let i = 0; i < hd0.length; i++) {
      // see only active elements in history
      let row = hd0[i];
      const z = 'GMT';
      const df = 'yyyy-MM-dd';
      var compareDate = '';
      try {
        compareDate = Utilities.formatDate(row[1], z, df);
      } catch (err) { }
      let key = row.slice().splice(2).join(''); // use slice to clone row, not modify histData
      if (key !== '') {
        histData.push(row); // push each row of historic data with not empty values
      }
      if (compareDate === Utilities.formatDate(sets.lastDay, z, df) && row) {
        histKeys[key] = i; // add index to dict in order to change in future
      }
    }
    var newRows = []; // array of rows to add behind data
    var removedRows = [];
    // find news
    for (let key in sourceKeys) {
      if (!(key in histKeys)) {
        // new row appeared in source
        newRows.push([today, sets.lastDay, ...sourceKeys[key]]);
      }
    }
    // find removed
    for (let key in histKeys) {
      if (!(key in sourceKeys)) {
        // change date To
        let hKey = histKeys[key];
        histData[hKey][1] = today; // set expiration date of this row to today!
        removedRows.push(key);
      }
    }
    let newData = histData;
    if (newRows.length > 0) { newData = newData.concat(newRows); }
    // update history is smth changed
    if (newRows.length > 0 || removedRows.length > 0) {
      histSheet.getRange(sets.history.rowStart, 1, newData.length, newData[0].length).setValues(newData);
      sendNotificationNew_(newRows, removedRows);
    }
  }
  return 0; // success
}

// TODO: send email notifications 



function test_sendNotificationNew() {
  var newRows = [["2021-02-17T13:35:06.688Z","3100-12-31T00:00:00.000Z","KILLME6"]];
  var removedRows = 	['BOOM', 'FOO'];
  sendNotificationNew_(newRows, removedRows);

}
function sendNotificationNew_(newRows, removedRows) {
  var sets = dict;
  var f = SpreadsheetApp.openById(sets.history.fileId);

  var subject = f.getName() + '.';
  var added = [], deleted = [];
  var msg = '';
  for (let i = 0; i < newRows.length; i++) {
    added.push(newRows[i][2]);
  }
  deleted = removedRows;

  if (added.length > 0) {
    subject +=  ' Added: ' + added.join(', ');
    msg += 'Added: ' + added.join(', ') + '<br><br>';
  }
  if (deleted.length) {
    msg +=  'Deleted: ' + deleted.join(', ') + '<br><br>';
    subject +=  ' Deleted: ' + deleted.join(', ');
  }
  msg += 'Email from file:<br>' + f.getUrl();

  GmailApp.sendEmail(
    sets.notifications.emailsForNews.join(),
    subject.substring(0, 250),
    'body', {
    htmlBody: msg
  });
  return 0;
}


/**
* Invokes a function, performing up to 5 retries with exponential backoff.
* Retries with delays of approximately 1, 2, 4, 8 then 16 seconds for a total of 
* about 32 seconds before it gives up and rethrows the last error. 
* See: https://developers.google.com/google-apps/documents-list/#implementing_exponential_backoff 
* <br>Author: [email protected] (Peter Herrmann)
<h3>Examples:</h3>
<pre>//Calls an anonymous function that concatenates a greeting with the current Apps user's email
var example1 = GASRetry.call(function(){return "Hello, " + Session.getActiveUser().getEmail();});
</pre><pre>//Calls an existing function
var example2 = GASRetry.call(myFunction);
</pre><pre>//Calls an anonymous function that calls an existing function with an argument
var example3 = GASRetry.call(function(){myFunction("something")});
</pre><pre>//Calls an anonymous function that invokes DocsList.setTrashed on myFile and logs retries with the Logger.log function.
var example4 = GASRetry.call(function(){myFile.setTrashed(true)}, Logger.log);
</pre>
*
* @param {Function} func The anonymous or named function to call.
* @param {Function} optLoggerFunction Optionally, you can pass a function that will be used to log 
to in the case of a retry. For example, Logger.log (no parentheses) will work.
* @return {*} The value returned by the called function.
*/
function call_(func, optLoggerFunction) {
  for (var n = 0; n < 6; n++) {
    try {
      return func();
    } catch (e) {
      if (optLoggerFunction) { optLoggerFunction("GASRetry " + n + ": " + e) }
      if (n == 5) {
        throw JSON.stringify(catchToObject_(e), null, 4); // stringify with 4 spaces at each level;
      }
      Utilities.sleep((Math.pow(2, n) * 1000) + (Math.round(Math.random() * 1000)));
    }
  }
}
// For catching errors
function catchToObject_(error) {
  var errInfo = {};
  if (typeof error !== 'string') {
    for (var prop in error) {
      errInfo[prop] = error[prop];
    }
  }
  errInfo.value = error.toString();
  errInfo.fileName = SpreadsheetApp.getActive().getName();
  errInfo.fileUrl = SpreadsheetApp.getActive().getUrl();
  errInfo.scriptUrl = 'https://script.google.com/macros/d/' + ScriptApp.getScriptId() + '/edit';
  return errInfo;
}
// function test_callWithError()
// {
//   call_(test_withError_);
// }
// function test_withError_()
// {
//   SpreadsheetApp.openById('Boooo');  
// }

// /// measuring time elapsed
// // usage
// function test_functionTime() {
//   var t = new Date();
//   // YOUR CODE
//   Browser.msgBox('Done! The time to run function is ' + getTimeEllapse_(t));
// }

function getTimeEllapse_(t) {
  var dif = new Date() - t;
  if (dif < 1000) { return dif + ' ms.'; }
  var mm = parseInt(dif / 1000), respo = '';
  if (mm < 60) {
    respo = mm + ' sec.';
  }
  else {
    var min = parseInt(mm / 60);
    var sec = mm - min * 60;
    respo = min + ' min. ' + sec + ' sec.';
  }
  return respo;
}