Update Google Sheets from another sheet and keep the history

Question

I have set of data in Google Sheets that look like this:

Columns B and C are updated manually every day. On the other sheet, I'm trying to append the new data above. If updated, with the previous data, hopefully would look like this;

Anybody can help me?

Answer 1

Use the following script, making sure that the sheet names and the range are what you want. In the Script Editor (found under Tools) there is also Resources menu, using which you can add a trigger that will run the archive function once a day (for example, at night when the changes are already done).

function archive() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var source = ss.getSheetByName('Sheet1');      // name of source sheet
  var sourceRange = source.getRange('A2:C4');    // range to copy
  var destination = ss.getSheetByName('Sheet2'); // name of log sheet
  var lastRow = destination.getLastRow();
  destination.insertRowsAfter(lastRow, sourceRange.getHeight());
  sourceRange.copyTo(destination.getRange(lastRow + 1, 1));
}

The script copies the range A2:C4 of Sheet1 to the bottom of Sheet2.