Google Sheets and Apps Script – Add a Sheet from a Template

google sheetsgoogle-apps-script

Is there a way to add a new sheet to a spreadsheet from a template? If I'm using a template for time, I'd like to add a new sheet from the same template every two weeks.

Best Answer

Yes, using the sheet.copyTo(destination) method. In the spreadsheet where you want to add the new sheets:

function copyFromTemplate(){
  var templateSpreadsheet = SpreadsheetApp.openById(TEMPLATE_ID);
  var template = templateSpreadsheet.getSheets()[0]; //Assuming it is the first sheet

  //The default name will be "Copy of [original name]". We can use today's date to rename it
  var today = (new Date()).toDateString();

  var currentSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  template.copyTo(currentSpreadsheet).setName(today);
}

You can set up a time-driven trigger for every week, or set up two monthly triggers to run the function every two weeks.

Related Solutions

Google-sheets – Auto create and name new sheet in Google Spreadsheet

The following piece of script will create a menu item in the spreadsheet named Maintenance. There are three menu option present named:

Add Student one by one, Add Student's at once (FIFO), Add Student's at once (LIFO), Delete Sheets

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [{name: "Add Student one by one", functionName: "addSheet"},
    {name: "Add Student's at once (FIFO)", functionName: "addSheets"},
    {name: "Add Student's at once (LIFO)", functionName: "addSheetsLIFO"},
    {name: "Delete Sheets", functionName: "delSheets"}
  ];
  ss.addMenu("Maintenance", menuEntries);
}

The next piece of code will create a new sheet, only if all information is entered properly. Once the information is entered, select the student that needs to be added and press Add Student:

function addSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var row = sh.getActiveRange().getRowIndex();
  var rData = sh.getRange(row, 1, 1, 3).getValues();

  if (row == 1) {
    ss.toast("This is the header");    
    return
  }

  if(rData[0][0] != null || rData[0][1] != null || rData[0][2] != null) {
    try {      
      ss.insertSheet(rData[0][2]);
      ss.setActiveSheet(ss.getSheets()[0]);
      sh.getRange("D"+(row)).setValue(new Date());
    } catch(e) {
      throw 'This student allready has a sheet. Try another sheet name.';      
    }
  }
}

The third piece of code will create new sheets, based on the sheet names, press Add Student's at once (FIFO):

function addSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var rData = sh.getDataRange().getValues();

  var message = [];    
  for(var i=1, len=rData.length; i<len; i++) {
    if(rData[i][3] == null || rData[i][3] == "") {   
      if(rData[i][0] != null || rData[i][1] != null || rData[i][2] != null) {
        try {      
          ss.insertSheet(rData[i][2]);
          ss.setActiveSheet(ss.getSheets()[0]);
          sh.getRange("D"+(i+1)).setValue(new Date());
        } catch(e) {
          message.push("row " + (i+1));
        }
      }    
    }
  }
  ss.toast("These sheets allready exist: " + message);
  ss.setActiveSheet(ss.getSheets()[0]);
}

The fourth piece of code will create new sheets, based on the sheet names, but by itterating backwards, press Add Student's at once (LIFO):

function addSheetsLIFO() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var rData = sh.getDataRange().getValues();

  var message = [], i=rData.length; 
  while(i--) {
    if(rData[i][3] == null || rData[i][3] == "") {   
      if(rData[i][0] != null || rData[i][1] != null || rData[i][2] != null) {
        try {      
          ss.insertSheet(rData[i][2]);
          ss.setActiveSheet(ss.getSheets()[0]);
          sh.getRange("D"+(i+1)).setValue(new Date());
        } catch(e) {
          message.push("row " + (i+1));
        }
      }    
    }
  }
  ss.toast("These sheets allready exist: " + message);
  ss.setActiveSheet(ss.getSheets()[0]);
}

The last script is a bonus to delete all the created sheets, execpt the first sheet, press Delete Sheets:

function delSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var shs = ss.getNumSheets();

  for(var i=shs-1;i>0;i--){
    ss.setActiveSheet(ss.getSheets()[i]);
    ss.deleteActiveSheet();
  }
  ss.setActiveSheet(ss.getSheets()[0]);
  ss.getRange("D2:D").clear();
}

I've prepared an example file, to see things happening: Add Student to New Sheet.

Google-sheets – Automating Calls to New Google Sheets Add-ons

I've looked in these places on the Google Apps Developer site:

but wasn't able to find a solution. Therefore I created an feature request in the issue tracker: 3803

Best Answer

Related Solutions

Google-sheets – Auto create and name new sheet in Google Spreadsheet

Google-sheets – Automating Calls to New Google Sheets Add-ons

Related Topic