Categories
User Help Web Apps

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

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

Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *