CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
hunter-gatherers
hunter-gatherers

Reputation: 73

google sheets: Apps Script to create new sheets from template

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

Upvotes: 1

Views: 3629

Answers (1)

Yuri Khristich
Yuri Khristich

Reputation: 14537

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 changes 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? What 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 a 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

Upvotes: 4

Related Questions