CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Matt Williamson
Matt Williamson

Reputation: 7095

Auto date duplicated tab

First off, I know a little bit of Javascript but I have no clue to where I would even start on this. I did lots of searching but didn't turn up anything close to what I want. I have a daily task list in google sheets and I want to duplicate each sheet with the next days date when I right click and choose duplicate. Is this even possible? Can it be done in another way?

Upvotes: 0

Views: 40

Answers (1)

user3717023
user3717023

Reputation:

It is impossible to change what the "duplicate" item of the context menu does. But one can use the following script to provide an alternative way of duplication, via the menu. It adds a new menu item "Custom > New Day" every time the spreadsheet is opened. Invoking this item will duplicate the current sheet with an incremented date as a name, provided that the name follows the ISO format yyyy-mm-dd.

General reference: starting with Google Apps Script.

function duplicate() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var sheetDate = new Date(sheet.getName() + 'T00:00:00.000Z');
  if (!sheetDate.valueOf()) {
    Browser.msgBox('Did not recognize "' + sheet.getName() + '" as a date. Use the format yyyy-mm-dd.');
  }
  else {
    sheetDate.setDate(sheetDate.getDate() + 1);
    var newSheetName = sheetDate.toISOString().slice(0,10);
    ss.insertSheet(newSheetName, {template: sheet});
  }
}

function onOpen() {
  SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", [{name: "New Day",  functionName: "duplicate"}]);
}

The bit with + 'T00:00:00.000Z' is there because the implementation of date parsing in Google Apps Script is peculiar: it requires the time component, including milliseconds.

Upvotes: 2

Related Questions