Search code examples
google-apps-scriptgoogle-sheetscopyrenamegoogle-workspace

Copy Sheet to new workbook from template based on date

I have a template sheet inside a workbook that I want to copy to a new workbook that is not yet created. I want this to run on the 29th of each month and do the following:

  • Take a Spreadsheet names "Template" and the sheet named “Template..2020.”
  • Create a new Spreadsheet called "December 2020" for example, and rename the first tab to 12.01.20, which I can then copy and rename for every day of the month.

I have tried a few copy functions with time triggers but they have required a blank workbook to be open already.

Solution

  • Example Script

    This script

    • Function createSheetFromTemplate
      • Uses the date object to get information about today's date and extracts the year, month, day etc.
      • Gets the template from another sheet using SpreadsheetApp.openById
      • Creates a new Sheet based on information from today's date.
      • Copies the template sheet and renames it based on the date.
    • Function createTrigger

    Disclaimer: At the moment this script just creates a new Spreadsheet with today's month and a new sheet named with today's date. I understand you may be looking to create a sheet for next month. This problem is probably best served by another question - perhaps look here.

    function createSheetFromTemplate() {
  
  // Getting details of today's date
  let date = new Date()
  let year = date.getFullYear()
  let month = date.getMonth() + 1
  let monthName = date.toLocaleString('default', { month: 'long' });
  let day = date.getDate()
  console.log(year, monthName, day)

  // Get template based on year
  let templateSpread = SpreadsheetApp.openById("1H7GBVxK4f0nmYR5xfZawy6nreDTCZaj76-dKE9eOtUE")
  let templateSheet = templateSpread.getSheetByName("Template_" + year)
  
  // Create new Spreadsheet with renamed template
  let newSpread = SpreadsheetApp.create(monthName + " " + year)
  let newSheet = templateSheet.copyTo(newSpread).setName(year.toString().substring(2,4) + "." + month + "." + day)
  let sheetToDelete = newSpread.getSheetByName("Sheet1")
  newSpread.deleteSheet(sheetToDelete)
}

// Create trigger to run on 29th of each month.
function createTrigger() {
  ScriptApp.newTrigger('createSheetFromTemplate')
  .timeBased()
  .onMonthDay(29)
  .create();
}

    References