Copy Multiple Google Sheets with Template Variables

Business and education administration often involves an overwhelming amount of repetitive tasks. We work in cycles of days, weeks, months, and years with multiple students, clients, employees, or team members. This adds up to multiples of multiple tasks. Luckily, automating repetitive and work-intensive tasks is just one of the many things Google Apps Script was made for.

In this Google Apps Script example, let’s automate all but the individualized aspects of working with Google Spreadsheets. We will create a template sheet and make multiple copies of that sheet with individualized variables for each sheet.

With a click of a button, this script will copy multiple versions of a sheet for each time period, each project, or each person.

How to Use the Script

Using the script is just as easy as using any other. Just copy the code below and paste it into the Google Apps Script code editor. For a working example, make a copy of this demo sheet.

• Save the script
• Run the onOpen() function or refresh the spreadsheet to instantiate the drop down menu
• Select Create template variables sheet if the Template Variables Sheet does not already exist. 

• Fill in your template variables. The template variable names can any combination of letters, numbers, and underscores (_) enclosed with curly braces  eg: {{1st_place}}

• To add more template variables, click Add Template Variable. To remove template variables, just delete that template variable’s column.
• Before you make copies, ensure that the template variables sheets are completely filled out, including the {{sheet_name}} variable (the name of the tab). Else the variables will be left blank. Also, make sure that all cells outside of the template variable names columns are left empty.
• To create copies, click the Create copies on the Sheet drop-down menu. This will create a new copy of the template sheet with unique variables for each row of template variable definitions.

How The Script Works

There are three parts to this script: the menu, the template variables sheet, and the sheet copier.

The menu follows a common and obvious pattern; it is built by the onOpen function. The onOpen function is called every time the spreadsheet is opened so whenever you open the spreadsheet, the menu is ready to use. Learn more about menus in Google Apps Script.

To make it as easy as possible for the user to avoid errors, there is a function called createTemplateVarsSheet() that adds a sheet labeled “Template Variables” and adds an example header row and an example variable row. This function is called whenever the user clicks Create template variables sheet on the menu.

With the same intent to avoid any user error, the function addTemplateVariable() adds a new template variable column to the template variable sheet. This is can be done manually quite easily, but this function, called when the user clicks Add template variable, walks the user through the process and keeps the same consistent formatting or the Template Variables sheet.

The real value of this script comes in the templateCopier() function. This function iterates through each row of the Template Variables range and creates a copy of the template sheet with the values for each row.

The magic happens in a call to the replaceTags() function. This function is called in every cell of the copied template sheet and uses a regular expression to find and replace a template tag with its corresponding value in for the copy of the template.

Once every cell in every copied sheet has been updated the script is done and just like that, there are a number of new replica sheets.

Here is the script:

Google Spreadsheet Copier

var ss = SpreadsheetApp.getActiveSpreadsheet();
var templateVarsSheet;
var templateTags;


function onOpen(){
  var menu = SpreadsheetApp.getUi().createMenu('Sheet Repeat')
  
  menu.addItem('Create template variables sheet', 'createTemplateVarsSheet')
  .addItem('Add template variable', 'addTemplateVariable')
  .addSeparator()
  .addItem('Copy Sheets', 'templateCopier')
  .addToUi(); 
}


function createTemplateVarsSheet(){
  
  templateVarsSheet = ss.insertSheet("Template Variables",0);
  
  var templateVarsSheetHeader = [ ["{{sheet_name}}","{{variable_1}}","{{variable_2}}","{{variable_3}}"] ];
  var templateVarsSheetRow = [ ["sheet name","variable 1","variable 2","variable 3"] ];  
  
  var header = templateVarsSheet.getRange(1, 1, 1, 4);
  header.setValues(templateVarsSheetHeader).setFontWeight("bold");
  
  var row1 = templateVarsSheet.getRange(2, 1, 1, 4)
  row1.setValues(templateVarsSheetRow);
  
  templateVarsSheet.activate();
  
}


function addTemplateVariable(){
  
  var templateVarsSheet = ss.getSheetByName("Template Variables");
  
  if (templateVarsSheet !== undefined){
    
    var varName = Browser.inputBox('Sheet Repeat', 'Enter template variable name', Browser.Buttons.OK_CANCEL);
    
    var formatted = "{{" + varName.replace(' ','_') + "}}";
    
    templateVarsSheet.insertColumnAfter(templateVarsSheet.getDataRange().getLastColumn());
    
    var changeRange = templateVarsSheet.getRange(1, templateVarsSheet.getDataRange().getLastColumn() + 1, 2, 1)
    changeRange.setValues([[formatted],["variable value"]]);
    
  } else {
    
    Browser.msgBox("First create a template variables sheet");
  
  }
  
}


function templateCopier(){
  
  var templateVarsSheet = ss.getSheetByName("Template Variables");
  var templateName = Browser.inputBox('Sheet Repeat', 'Enter template sheet name', Browser.Buttons.OK_CANCEL);
  var template = ss.getSheetByName(templateName);

  var tagValues = templateVarsSheet.getDataRange().getValues();
  var tagNames = tagValues.shift(); // first row
  // tagValues is now rows [1:n]
  
  for (var i = 0; i < tagValues.length; i++){  
        
    templateTags = makeTagsObj(tagNames,tagValues[i]);
    
    var copy = ss.insertSheet(templateTags["{{sheet_name}}"], {template: template});
        
    var searchRng = copy.getDataRange();
    
    updateTemplateRange(searchRng);
    
  }
}

// Searches in each cell in the range for a match,
// replaces the input range's vales with new range with template tags replaced

function updateTemplateRange(searchRng){

  var searchValues = searchRng.getValues()
  var updatedRange = [];

  for (var i = 0; i < searchValues.length; i++){
    
    var row = searchValues[i];
    
    var updatedRow = row.map(function(val){
      return replaceTags(val);
    });
    
    updatedRange.push(updatedRow)
  
  }
    searchRng.setValues(updatedRange);
}


// Create an object from an array of template tags and an array of values
function makeTagsObj(tagNames, tagValues){
  
  var pairs = {};
  
  for (var i = 0; i < tagNames.length; i++){
    pairs[tagNames[i]] = tagValues[i] === undefined ? "" : tagValues[i];
  }

  return pairs
}

// Take an input string
// Returns that string with template tags replaced with corresponding variables
function replaceTags(val) {

  return val.replace(/{{[a-z0-9_]*}}/g, function(str){ return templateTags[str] });
    
}

I hope you find it useful for automating your work in Google Spreadsheets. If you think it could be improved to serve your purpose, let me know in the comments.