Google-sheets – How to automatically create a templated Google sheet

google sheetsgoogle-apps-scriptgoogle-sheets-addons

I'm trying to figure out how to automatically create a templated Google Sheets worksheet when a new entry is made in a different sheet.

So the flow would be:

  1. Person's name is added into Google sheet A, along with data point X
  2. This automatically creates a new Google sheet B
  3. Google sheet B is already pre-populated from a template with a few data points. It is automatically also populated with Person's name and data point X

I haven't been able to find solutions that include the pre-population. Any examples I could draw on? Is Google add-ons a good approach here?

Best Answer

Try this:

This function get called when 'Create Sheet'!B1 gets edited with a new name.

function onEdit(e) {
  var rg=e.range;
  var sh=rg.getSheet();
  if(sh.getName()=="Create Sheet" && rg.columnStart==2 && rg.rowStart==1){
    var name=sh.getRange('B1').getValue();
    var ss=e.source;
    if(name){
      var ns=ss.insertSheet(name);//create new sheet with name from Create Sheet B1
      var osh=ss.getSheetByName('Original');
      var org=osh.getRange('A1:B3');
      org.copyTo(ns.getRange('A1:A3'));//copy original content to new sheet
      var d=Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MM dd,yyyy");
      var t=Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "HH:mm");
      ns.getRange('B1:B3').setValues([[name],[d],[t]]);//Insert some new values into new sheet
      sh.getRange(1,2).clear();//Clears the name that was originally typed into Create Sheet B1
    }
  }  
}

The Sheet Named 'Create Sheet' Looks Like this:

enter image description here

And you type the name of the new sheet into Create Sheet B1

The Original Sheet Looks like this:

enter image description here

The contents of the Original Sheet get copied into the new sheet contents and format.

The New Sheet is given the name that was typed into Create Sheet B1. This is what the new sheet looks like:

enter image description here

Related Topic