Create and share customised templates linked to a master collection sheet

There are lots of times when it would be useful to create batches of sheets with customised information, share these with specified people automatically, and have any changes that those users make, automatically collected and visible from one master sheet.

I have created a variant of this script to collect a large volume of information from 200+ organisations, but am going to demo a simpler version here, which aims to create individual annual leave sheets for all staff, populating each one with data relevant to each person, sharing these with them individually, and allowing the main administrator to see at a glance what the remaining balances of holidays are for everyone as time goes on and the staff update their sheets.

To start, I created a simple sheet - shown here with some sample data:

The Spreadsheet IDs and Email Sent cells will get populated automatically once the script runs.
The Current Balances and Link to Spreadsheets are generated using formulae (importrange and a hyperlink concatenation respectively)

I also created a separate template sheet workbook (below), which the script will customise using the information on the master sheet above, make a copy, then share with the appropriate staff members.

For the Scripts, I created a couple of menu entries that will appear when the master sheet loads

function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [{
    name: "Generate Annual Leave Sheets",
    functionName: "CreateAnnualLeaveSheets"
  }, 
    {
    name: "Send Emails",
    functionName: "sendEmails"
    }];
  ss.addMenu("Run Annual Leave Scripts", menuEntries);
}

Next I created the main script, which will create a customised copy of each template and share it with the user whose email address is in column B

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetName = 'Master'; //I renamed Sheet1! to Master
var SheetIds = 3;  //this is the column to store the generated sheet IDs

function CreateAnnualLeaveSheets(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  //use the id of the template sheet
  var sstemplate = SpreadsheetApp.openById("**************************"); //replace with your template key
  var data = sheet.getDataRange().getValues();
  for(var i = 1; i < data.length; i++){
    if(data[i][0] != '' && (data[i][SheetIds-1] == '' || !data[i][SheetIds-1])) {
    
     var reportname = data[i][0];  // this is column A (A=0, B=1, C=2 etc.)
     var AdditionalEditor = data[i][1];  //column B  - the email that template will be shared with
     var Name = data[i][5];  // column F   - their name
      var Department = data[i][6];  //column G  - their department
     
      var Basic = data[i][7];  //column H   - number of days annual leave
      var cfwd = data[i][8];  //column I   - number of days carried forward
     
      
       sstemplate.getSheetByName("Sheet1").getRange('B2').setValue(Name);   // append name to the template
       sstemplate.getSheetByName("Sheet1").getRange('B3').setValue(Department); // append department 
        sstemplate.getSheetByName("Sheet1").getRange('B4').setValue(Basic);  // append no. days basic
         sstemplate.getSheetByName("Sheet1").getRange('B5').setValue(cfwd);  //append no days carried forward
      
      var newsheet = sstemplate.copy(reportname).getId();  //create a copy of the template and save it with the report name
           sheet.getRange(i+1, SheetIds).setValue(newsheet);  // write the newly created key to the master sheet
        var newsheeteditors = SpreadsheetApp.openById(newsheet);  // open each sheet
        newsheeteditors.addEditors([AdditionalEditor]);   // assign edit rights to the named person
    
    }
    
  } //loop
 
}

When this script is run, the spreadsheet keys all fill in automatically on the master sheet. This allows us also to reference each of the keys in a formula, to bring in selected data to the master.  In this case I just wanted to see the outstanding holiday balance for each staff member.  For this I just needed a formula such as:

=if(C2<>"",importrange("https://docs.google.com/a/****/spreadsheets/d/"&C2,"Sheet1!$G$2"),"")

This formula allows cell G2 from each of the staff sheets to be brought through to the master sheet.
Whereas old sheets limited you to 50 Importrange formulae, new sheets has no limit, but it does require you to authorise each data connection. You only have to do this once for each of the linked sheets, so you can have multiple importranges to the same child sheet, which will all work as soon as just one of them is authorised.

I just used conditional formatting for the cell to go red if there is an error (such as if the sheets are not yet connected) Once you have clicked on "Allow Access"  the data should come through from then on.

I also have an email script that is designed to contact each of the staff members, informing them that a sheet has been created in their 'Shared with me' folder, with a link direct to the sheet for convenience.

//allow specific text to be written to sheet to prevent duplicate emails being sent
var EMAIL_SENT = "EMAIL_SENT";

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 21;   // Number of rows to process
  // Fetch the range 
  var dataRange = sheet.getRange(startRow, 1, numRows, 12)
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[1];  // seccond column  (Column B)
    var message = row[10];       // column K
    var emailSent = row[9];     // column J  
    if (emailSent != EMAIL_SENT) {  // Prevents sending duplicates
      var subject = row[0];     //  column (A)
      Logger.log(emailAddress);
      MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(startRow + i, 10).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}


function SheetFlush(worksheet) {
    worksheet = worksheet || SpreadsheetApp.getActive();
    var sheets = worksheet.getSheets();
    SpreadsheetApp.flush();
}

You can customise the body and title using text and formulae on the master sheet, on mine, the end result looks something like this.

Variations of this script can be used to send regular customised reports out to large numbers of users, or to send complex data collection sheets that may need to be worked on over longer periods of time.

Hope it is of use, any questions or feedback welcomed