Using an onEdit trigger to create a sheet from a template and fill in relevant information.
Important notes:
* Do not run the script manually. Like all onEdit() scripts, the ‘e’ parameter passes necessary information to the script and only exists when run automatically from an edit.
* Watch the ranges carefully.
* You can call the values from the data variable directly where needed rather than making individual variables.
Sheet (to copy – use File then “Make a copy” to get a full copy in your Drive):
https://docs.google.com/spreadsheets/d/1CQUeyVZkjK0zI4ioNe04Bct5gqrN69XQQW1j0iy0opQ/edit#gid=471177604
Script:
function onEdit(e){
if (e.source.getActiveSheet().getName() != “Roster” || e.range.columnStart != 4) return;
const sh = SpreadsheetApp.getActive();
const template = sh.getSheetByName(“TEMPLATE”);
let data = e.range.offset(0,-3,1,4).getValues();
let fName = data[0][1];
let lName = data[0][0];
let sDate = data[0][2];
let position = data[0][3];
let newSheet = template.copyTo(sh).setName(fName + ” ” + lName);
newSheet.getRange(“A1″).setValue(fName + ” ” + lName);
newSheet.getRange(“H2”).setValue(sDate);
newSheet.getRange(“J2”).setValue(position);
}
Connect with me:
• spencer.farris@gmail.com
• spencerfarris.me
• www.linkedin.com/in/spencer-farris/
• Twitter @FarrisSpencer
• Google Product Expert support.google.com/docs/profile/12305