Google-sheets – Autofill Google sheets template based on new entries in column A of “master” sheet

google sheets

I am a teacher and I keep my student data on a Google sheet. I have a "Student Data Report" template that will automatically copy as a new sheet with the student id as the sheet name as I enter new students to my roster on my master sheet. So, "sheet1" is the roster of students with their IDs in column A, "sheet2" is the report template, and sheets are automatically added as I enter new students to my roster (with the student ID as the sheet name).

I have added formulas to the cells in the template so that when I add the student ID# the rest of the report will automatically fill in the student's information from sheet1 (for example column B is the student's name, column C is the student's date of birth, etc.). Is it possible when a new sheet is created for the student ID to automatically fill in which would then cause the rest of the cells in the report autopopulate based on the student ID?

I have tried the scripts below and then put =sheetName() in the corresponding cell, but the cell just said "copy of template" even though the sheet was named with the student id #.

function sheetName() {
  return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
}

function sheetName(idx) {
  if (!idx)
    return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
  else {
    var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
    var idx = parseInt(idx);
    if (isNaN(idx) || idx < 1 || sheets.length < idx)
      throw "Invalid parameter (it should be a number from 0 to "+sheets.length+")";
    return sheets[idx-1].getName();
  }
}

Best Answer

You have a custom function that retrieves the sheet name. This is essential in the use of a template however when the template is duplicated and the new sheet is renamed, the new sheet name does not appear but displays "Copy of Sheet2". Refreshing the spreadsheet has no effect, nor does closing the spreadsheet and then opening it again.

The underlying problem concerns the Google cache. Over time the methods of overcoming this problem have changed. For an excellent summary, read the answer by Rubén at How to make that a custom function update its result immediately.

The following answer implements a technique proposed by @Tanaike in Automatic Recalculation of Custom Function on Spreadsheet Part 2 at https://tanaikech.github.io/. This uses the Class TextFinder (doc ref) method.

In this case, I have also created a custom menu, so that the sheet names can be updated by selecting "Refresh, Update Sheet Names" from the Custom menu.

Step#1

Use your existing, least complicated function.

function sheetName() {

  var thisName = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();

  return thisName;
}

Step#2

Open the Script editor and add the following. Save the project; close the spreadsheet; open the spreadsheet, select "Refresh, Update Sheet Names" from the Custom menu

function onOpen() {
  var ui = SpreadsheetApp.getUi(); 
  ui.createMenu('Refresh')
      .addItem('Update Sheet Names', 'updatesheetNames')
      .addToUi();
}


function updatesheetNames() {
  var customFunctions = ["sheetName"]; // Please set the function names here.

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var temp = Utilities.getUuid();
  Logger.log(" the tenmp id is "+temp);
  
  customFunctions.forEach(function(e) {
    ss.createTextFinder("=" + e)
      .matchFormulaText(true)
      .replaceAllWith(temp);
    ss.createTextFinder(temp)
      .matchFormulaText(true)
      .replaceAllWith("=" + e);
  });
}

Related Solutions

Google-sheets – Auto create and name new sheet in Google Spreadsheet

The following piece of script will create a menu item in the spreadsheet named Maintenance. There are three menu option present named:

Add Student one by one, Add Student's at once (FIFO), Add Student's at once (LIFO), Delete Sheets

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [{name: "Add Student one by one", functionName: "addSheet"},
    {name: "Add Student's at once (FIFO)", functionName: "addSheets"},
    {name: "Add Student's at once (LIFO)", functionName: "addSheetsLIFO"},
    {name: "Delete Sheets", functionName: "delSheets"}
  ];
  ss.addMenu("Maintenance", menuEntries);
}

The next piece of code will create a new sheet, only if all information is entered properly. Once the information is entered, select the student that needs to be added and press Add Student:

function addSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var row = sh.getActiveRange().getRowIndex();
  var rData = sh.getRange(row, 1, 1, 3).getValues();

  if (row == 1) {
    ss.toast("This is the header");    
    return
  }

  if(rData[0][0] != null || rData[0][1] != null || rData[0][2] != null) {
    try {      
      ss.insertSheet(rData[0][2]);
      ss.setActiveSheet(ss.getSheets()[0]);
      sh.getRange("D"+(row)).setValue(new Date());
    } catch(e) {
      throw 'This student allready has a sheet. Try another sheet name.';      
    }
  }
}

The third piece of code will create new sheets, based on the sheet names, press Add Student's at once (FIFO):

function addSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var rData = sh.getDataRange().getValues();

  var message = [];    
  for(var i=1, len=rData.length; i<len; i++) {
    if(rData[i][3] == null || rData[i][3] == "") {   
      if(rData[i][0] != null || rData[i][1] != null || rData[i][2] != null) {
        try {      
          ss.insertSheet(rData[i][2]);
          ss.setActiveSheet(ss.getSheets()[0]);
          sh.getRange("D"+(i+1)).setValue(new Date());
        } catch(e) {
          message.push("row " + (i+1));
        }
      }    
    }
  }
  ss.toast("These sheets allready exist: " + message);
  ss.setActiveSheet(ss.getSheets()[0]);
}

The fourth piece of code will create new sheets, based on the sheet names, but by itterating backwards, press Add Student's at once (LIFO):

function addSheetsLIFO() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var rData = sh.getDataRange().getValues();

  var message = [], i=rData.length; 
  while(i--) {
    if(rData[i][3] == null || rData[i][3] == "") {   
      if(rData[i][0] != null || rData[i][1] != null || rData[i][2] != null) {
        try {      
          ss.insertSheet(rData[i][2]);
          ss.setActiveSheet(ss.getSheets()[0]);
          sh.getRange("D"+(i+1)).setValue(new Date());
        } catch(e) {
          message.push("row " + (i+1));
        }
      }    
    }
  }
  ss.toast("These sheets allready exist: " + message);
  ss.setActiveSheet(ss.getSheets()[0]);
}

The last script is a bonus to delete all the created sheets, execpt the first sheet, press Delete Sheets:

function delSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var shs = ss.getNumSheets();

  for(var i=shs-1;i>0;i--){
    ss.setActiveSheet(ss.getSheets()[i]);
    ss.deleteActiveSheet();
  }
  ss.setActiveSheet(ss.getSheets()[0]);
  ss.getRange("D2:D").clear();
}

I've prepared an example file, to see things happening: Add Student to New Sheet.

Google-sheets – Rearranging Google form data in a new sheet

Since you are going to be using a script anyway (for email generation), might as well use one for the rearrangement.

Here's a simplified example of form input data:

+---+------------+-------+---------------+-------+-------+-------+
|   |     A      |   B   |       C       |   D   |   E   |   F   |
+---+------------+-------+---------------+-------+-------+-------+
| 1 | Date       | Job   | Details       | cat 1 | cat 2 | cat 3 |
| 2 | 10/2/2015  | Job 1 | some details  | 2     | 3     | 1     |
| 3 | 10/10/2015 | Job 2 | other details | 0     | 2     | 3     |
+---+------------+-------+---------------+-------+-------+-------+

This will be rearranged on another sheet, called "Job List" below, by this script (which can be triggered by form submission):

function processJobs() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var values = sheet.getDataRange().getValues();
  var output = [];
  for (var i = 1; i < values.length; i++) {
    for (var j = 0; j < 3; j++) {
      output = output.concat(repeat(values[i], values[0][j+3], values[i][j+3]));
    }
  }
  outputSheet = ss.getSheetByName("Job List");  
  outputSheet.getRange(2, 1, output.length, output[0].length).setValues(output);
}

function repeat(row, category, quantity) {
  var arr = [];
  for (var i = 0; i < quantity; i++) {
    arr.push([row[0], category, quantity].concat(row.slice(1,3)));
  }
  return arr;
}

Explanation: the line with values[i][j+3] refers to various category counts within the row; j+3 is set so that j=0,1,2 correspond to 3,4,5 zero-based column indices (which mean the columns D,E,F above). For each count, the function repeat is called, which produces the necessary number of repetitions.

The function repeat inserts the date, category name, quantity of workers for that category (not sure why, but you wanted it), and the rest of the job description.

The whole thing is then recorded in sheet "Job List", like so:

+----+------------+----------+----------+-------+---------------+
|    |     A      |    B     |    C     |   D   |       E       |
+----+------------+----------+----------+-------+---------------+
|  1 | Date       | Category | Quantity | Job   | Details       |
|  2 | 10/2/2015  | cat 1    | 2        | Job 1 | some details  |
|  3 | 10/2/2015  | cat 1    | 2        | Job 1 | some details  |
|  4 | 10/2/2015  | cat 2    | 3        | Job 1 | some details  |
|  5 | 10/2/2015  | cat 2    | 3        | Job 1 | some details  |
|  6 | 10/2/2015  | cat 2    | 3        | Job 1 | some details  |
|  7 | 10/2/2015  | cat 3    | 1        | Job 1 | some details  |
|  8 | 10/10/2015 | cat 2    | 2        | Job 2 | other details |
|  9 | 10/10/2015 | cat 2    | 2        | Job 2 | other details |
| 10 | 10/10/2015 | cat 3    | 3        | Job 2 | other details |
| 11 | 10/10/2015 | cat 3    | 3        | Job 2 | other details |
| 12 | 10/10/2015 | cat 3    | 3        | Job 2 | other details |
+----+------------+----------+----------+-------+---------------+

Column F can be used for student names.

Best Answer

Related Solutions

Google-sheets – Auto create and name new sheet in Google Spreadsheet

Google-sheets – Rearranging Google form data in a new sheet

Related Topic