Google apps script for named template copy as list updates?

  javascript

I’m currently successfully running a code to copy multiple google sheets with template variables but am unable to add more sheets once I update the variable list with new names. How can I put in a loop so that it checks for duplicates and then runs the copy function if there aren’t duplicates? Here’s the current code:

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 templateCopier(){
  
  var templateVarsSheet = ss.getSheetByName("Template Variables");
  var templateName = Browser.inputBox('Sheet Repeat', Browser.Buttons.OK_CANCEL);
  var template = ss.getSheetByName('Template');

  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);
    
  }
   for ( var j = 0; j < tagValues.length; j++ ){
      if ( tagValues[j].getName() == templateTags["{{sheet_name}}"], {template: template} ) {
        ok = false;
        Logger.log('duplicate');
      }
    }

    if ( ok ) {
      template.copyTo(ss).setName(templateTags["{{sheet_name}}"], {template: template});
    } 
}

// 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] });
    
}

Source: Ask Javascript Questions

LEAVE A COMMENT