2

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?

  • You don't need an addon, an onedit trigger is enough. The prepopulation is probably something you have to hardcore / create a template that you copy and alter as needed. – Robin Gertenbach Nov 29 '16 at 7:47
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.