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