Autofill Google sheets template based on new entries in column A of "master" sheet

Question

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();
  }
}

Answer 1

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);
  });
}