Google Sheets – Create New Spreadsheets with Timestamp

google sheetsgoogle-apps-scriptgoogle-sheets-timestamp

I'm trying to create a new spreadsheet once we select the specific function from the custom menu item that's selected. I want the new spreadsheet to be "Sheet" and "Timestamp".

I have this created but it's giving an error:

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [{name: "Generate Traffic Sheet", functionName: "newSheet"}];

  ss.addMenu("Generate Traffic Sheet", menuEntries);
}

function newSheet () {
    var ss = SpreadsheetApp.create(name)
    var d = new Date();
    var timeStamp = d.getTime();
    ss.setName(timeStamp)
}

Can anyone point me in the right direction?

Best Answer

Use the following code to accomplish your task:

Code

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [{name: "Sheet 1", functionName: "newSheet1"},
                     {name: "Sheet 2", functionName: "newSheet2"}]
  ss.addMenu("Generate Traffic Sheet", menuEntries);
}

function newSheet1 () {
  var name = "test"; 
  SpreadsheetApp.create(name).getRange("A1").setValue(new Date());
}

function newSheet2 () {
  var name = "test"; 
  var myDate = Utilities.formatDate(new Date(), "GMT+1", "dd.MM.yyyy");
  SpreadsheetApp.create(name).getRange("A1").setValue(myDate);
}

Remarks

  • If you would've ran the debugger, then you would've noticed an error message: "name variable doesn't exist". See sheet 1 example.
  • setName() method isn't applicable here for adding data into the other sheet.
  • If you want to access the new spreadsheet, then you need to create that instance as well.
  • the getTime() method will return the amount of millisecond from 01-01-1970 on.
  • I've added two revised code samples, so that you can see the difference

Good luck and happy coding !!

Related Topic