I have one sheet (Sheet1) which I consider the template. In it I have a header with fields like "Student's name", "Parent" and "Phone number", which are personal info. Bellow it there's other stuff that are irrelevant to this question.
The idea is to have another sheet (Sheet2) listing the students, followed by the other info on other columns. From there I would like to automatically create multiple sheets, one for each student, based on the template, where the headers would auto fill based on Sheet2.
I have zero experience with spreadsheets so I don't even know if it's possible. I have tried googling but I don't know the terminology so I'm having a hard time figuring this out. Any help is appreciated. Thank you very much.
1 Answer
I ended up having to program a script on Google Apps Script
The script creates a menu item called Gerar Planilihas that when clicked creates another spreadsheet on the Google Drive folder and add one new sheet for each row on the sheet Lista. It copies the sheet Template and then copies each respective field as desired. Here's the code:
function onOpen() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var menuEntries = [{name: "Gerar planilhas", functionName: "gerarPlanilhas"} ]; ss.addMenu("Pagamento Alunos", menuEntries);
}
function gerarPlanilhas() { var sheetTemplate = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Template'); var sheetLista = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Lista'); var ssGerada = SpreadsheetApp.create("Planilhas Alunos"); var tamanhoLista = sheetLista.getLastRow(); var indice = 1; while (indice <= tamanhoLista) { var nomeAluno = sheetLista.getRange(indice, 1).getValue() var nomeResp = sheetLista.getRange(indice, 2).getValue() var CPF = sheetLista.getRange(indice, 3).getValue() var telefone = sheetLista.getRange(indice, 4).getValue() values = [[indice], [nomeAluno], [nomeResp], [CPF], [telefone]] var newSheet = sheetTemplate.copyTo(ssGerada) newSheet.setName("Aluno" + indice) var range = newSheet.getRange(1, 2, 5, 1).setValues(values) indice = indice + 1; } ssGerada.deleteSheet(ssGerada.getSheetByName("Sheet1"))
}
