Reputation: 73
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 appreciated
Upvotes: 1
Views: 3623
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);
}
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