0

I'm currently using the below script which essentially pulls a list of agents from a list then creates new tabs using a template sheet as the basis for the new ones and renames them all as per the list of names.

What I've noticed recently is that when activating the script it appears to hang and not complete yet if I reload the whole web session it has completed.

The Template page has a lot of formulas on it including Query() and Now() statements, could this be slowing it down?

What I want to know is whether the script can be amended in any way to speed things up but I also want the following added to it if possible:

  • Once the creation of tabs has been completed, all tabs will be hidden apart from an ADMIN page and the tabs that have just been created. (I Thought I had managed this once but it seems to now have stopped working and I can't see why currently)
function TemplateDuplication() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(),
    temp = ss.getSheetByName('Template'),
    all = ss.getSheetByName('Admin'),
    sheets = ss.getSheets(),
    vals = all.getRange('A:A').getValues();
  for (var i = 0, len = sheets.length, names = []; i < len; i++)
    names.push(sheets[i].getName());
  for (var i = vals.length - 1; i > 0; i--) {
    var name = vals[i][0];
    if (name != '' && names.indexOf(name) == -1) {
      temp.activate();
      ss.duplicateActiveSheet()
        .setName(vals[i][0]);
      var spreadsheet = SpreadsheetApp.getActive();
      spreadsheet.getRange('B1:B1013').activate();
      spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Stats'), true);
      spreadsheet.getRange('A:A').activate();
      spreadsheet.setCurrentCell(spreadsheet.getRange('A28'));
      spreadsheet.getActiveSheet().insertColumnsBefore(spreadsheet.getActiveRange().getColumn(), 1);
      spreadsheet.getActiveRange().offset(0, 0, spreadsheet.getActiveRange().getNumRows(), 1).activate();
      spreadsheet.getActiveSheet().deleteColumns(spreadsheet.getActiveRange().getColumn(), spreadsheet.getActiveRange().getNumColumns());
      spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Admin'), true);
      spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Template'), true);
      spreadsheet.getActiveSheet().hideSheet();
    }
  }
}

1 Answer 1

0

You can make your script run a bit more efficiently like this:

function duplicateTemplate() {
  const ss = SpreadsheetApp.getActive();
  const temp = ss.getSheetByName('Template');
  const vals = ss.getRange('Admin!A1:A')
    .getDisplayValues()
    .flat()
    .filter(String);
  const sheetNames = ss.getSheets().map(sheet => sheet.getName());
  for (let i = vals.length - 1; i > 0; i--) {
    const name = vals[i];
    if (!sheetNames.includes(name)) {
      temp.copyTo(ss).setName(name);
    }
  }
  sheetNames.forEach(sheetName => {
    if (sheetName !== 'Admin') {
      ss.getSheetByName(sheetName).hideSheet();
    }
  });
}

The bits that insert and delete columns have been left out because it is unclear whether those actions are intentional.

The Template page has a lot of formulas on it including Query() and Now() statements, could this be slowing it down?

The slow performance you mention may indeed be because of the formulas and data in the Template sheet rather than the script. To improve spreadsheet performance, see these optimization tips.

5
  • I've added the script as above but amended it as per below to hide specific sheets as was saying 'Sheet name can not be empty'. However, it still comes up with that error, and the sheets it does duplicate appear to not get renamed as per the list in the Admin sheet. Jan 20, 2023 at 9:52
  • couldn't add the amended to a comment so tried to amend the original Jan 20, 2023 at 9:53
  • Edited the answer to add .filter(String) to weed out blank values in Admin!A1:A. Jan 20, 2023 at 12:17
  • just tried to edit again and this error message comes up: ReferenceError: sheetsNames is not defined Jan 23, 2023 at 10:05
  • Replace sheetsNames with sheetNames. Jan 23, 2023 at 10:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.