Create fresh duplicate of Master Google Sheet on a daily basis

Question

I'm trying to hack together a paperless data-entry solution for my company using Google Sheets. Basically, our employees are required to fill out daily logs on paper. Needless to say, this is extremely wasteful and inefficient. I'd like to automate the process by doing the following:

1. Manually create a Master Sheet with proper formatting, etc.
2. Run a script that duplicates the said master sheet and places an editable copy in a separate folder on Google Drive once a day (at a specific time).

Ideally, each copy would have a timestamp of its creation in a cell somewhere, although this isn't absolutely needed.

I've looked all over the place and haven't found any scripts that do exactly this (the closest thing I found was a messy Zapier hack).

Does anyone know how I would go about doing this?

Any scripts laying around that do this?

If not, are there any resources you could point to that would allow me do figure it out myself?

Answer 1

Here is one approach. The script should be attached to the master spreadsheet (Tools > Script editor) and set to run on daily basis (Edit > Current project's triggers). To run, it needs the destination folder Id (the part of folder URL after /folders/).

The new copy is named like "original file name 2017-07-25 19:11:23" which also serves the purpose of timestamping. This is actually UTC time... there are other options, such as

var timestamp = new Date().toLocaleString(); (returns July 25, 2017 3:17:45 PM EDT)

or

var timestamp = new Date().toDateString(); (returns Tue Jul 25 2017).

Both of the above depend on local date conventions of the owner of master sheet.

function duplicateSpreadsheet() {
  var folder = DriveApp.getFolderById("folder Id here");
  var timestamp = new Date().toISOString().replace("T", " ").split(".")[0];
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var file = DriveApp.getFileById(ss.getId());
  file.makeCopy(folder).setName(file.getName() + " " + timestamp);
}

Further reading

DriveApp and SpreadsheetApp