google sheets: Apps Script to create new sheets from template

user_14606808 0
Viewed 250

I have a google sheets doc that contains a Start and Template sheet. The idea is to enter the name of an employee in Start and press the Generate button. For each employee entered, the Template sheet should be duplicated, named similar to the employee (e.g.,McCormick, Peter) and the name of the employee should be written in the new sheet in cell A2. The name of each employee should then also be added to the Start sheet with an indirect reference to Cell B2 of each employee sheet.

That's probably possible with Apps Script, but I'm struggling a bit to write the code. Any suggestion would be highly appreciatedenter image description here

google-apps-script google-sheets
1 Answers

Here is the possible solution:

function create_sheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Start');
  var name = sheet.getActiveCell().getDisplayValue();
  var template = ss.getSheetByName('Template');
  var new_sheet = template.copyTo(ss).setName(name);
  new_sheet.getRange('A2').setValue(name);
}

This script duplicates the 'Template' sheet and change its name to anything from the current cell on the sheet 'Start'.

But I don't understand what do you mean 'indirect reference to Cell B2'. Can you clarify it? How it should look like?

Here is the variant of the same code that converts employer names on the 'Start' sheet into links to the corresponding sheets:

function create_sheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Start');
  var cell = sheet.getActiveCell();
  var name = cell.getDisplayValue();
  var template = ss.getSheetByName('Template');
  var new_sheet = template.copyTo(ss).setName(name);
  new_sheet.getRange('A2').setValue(name);

  var url = ss.getUrl() + '#gid=' + new_sheet.getSheetId();
  var link = SpreadsheetApp.newRichTextValue().setText(name).setLinkUrl(url).build();
  cell.setRichTextValue(link);
}

enter image description here

Here is the sheet.

Update

Here is the updated code:

function add_sheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var start_sheet = ss.getSheetByName('Start');
  var template_sheet = ss.getSheetByName('Template');

  // get list of names from the 'Start'sheet 
  var names = start_sheet.getRange('A2:A').getValues().flat().filter(String);

  while(names.length) {
    var name = names.shift();

    // try to pick existed sheet with the name
    try { var dest_sheet = ss.getSheetByName(name); dest_sheet.getRange('A1') }

    // create the sheet if there is no sheet with this name
    catch(e) { var dest_sheet = template_sheet.copyTo(ss).setName(name) } 

    dest_sheet.getRange('A2').setValue(name);
  }
}

It creates sheets from the A column of the sheet 'Start' and the sheet 'Template'.

Here is the sheet

user_142654690
Related