Google-sheets – Copy and paste information once a day form a Google spreadsheet

google sheetsgoogle-apps-scriptgoogle-apps-script-triggers

I have a Google spreadsheet that is being modified during the day, and need to copy and paste the final information (at 8 pm) in a different tab, each day, pasting the new information under the old one so the data will be growing each day.

Is this possible? And if yes, how can I do it?

Best Answer

This is similar to my answer to IMPORTHTML at a certain time of day in Google Spreadsheets, but I guess the question isn't a duplicate, so:

  1. Enter the script (below) in the Script Editor (under Tools)
  2. Change the name of sourceSheet and logSheet to what you have.
  3. Create a trigger under Resources > This project's triggers, so it runs daily between 8pm and 9pm (Google will pick some random time between these, and then run it at that time every day).

Here is the script. In addition to copying everythings from one sheet to another, it inserts a timestamp on top of copied data. If you don't want this, remove the line with // insert timestamp.

function dailyLog() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName('Sheet1');
  var logSheet = ss.getSheetByName('Sheet2');
  var lastRow = logSheet.getLastRow();
  logSheet.getRange(lastRow + 1, 1).setValue(new Date());  // insert timestamp
  var range = sourceSheet.getDataRange();
  range.copyTo(logSheet.getRange(lastRow + 2, 1), {contentsOnly: true});
}
Related Topic