I am a maths teacher who is learning programming and this is my first attempt at using Apps Script. I have used this site to find various bits of code and put them together to form a script that works (thank you to the numerous posters on this site who have unknowingly helped me already!).
My question is: can it be polished and made more efficient? I'm assuming it probably can - I'm grateful for help anyone is kind enough to provide.
Outline:
- There are 2 existing Google Sheets - "DATA", containing a list of students and their data; "TEMPLATE", used to create a separate Google Sheet for each student.
- The script collects the StudentID numbers from DATA and creates a new Google Sheet for each student based on the TEMPLATE Google Sheet.
- Put the studentID in the filename and copy the studentID to cell U1 in the first tab [TEMPLATE contains 2 tabs] then hide the 2nd tab [called 'import'].
- Add the student as a viewer.
- Restrict copy/download/print for the viewer.
Many thanks,
Dan
function createSheets()
{
// ID of the Google Sheet to be used as a template for the student Sheets
var templateId = '###TEMPLATE###';
// Collect all the studentIDs from DATA and store in a variable called 'students'
var students = Sheets.Spreadsheets.Values.get('###DATA###', '###SheetName###');
for(var i = 0; i < students.values.length; i++)
{
var studentId = students.values[i][0];
// Make a copy of the template file
var documentId = DriveApp.getFileById(templateId).makeCopy().getId();
// Rename the copied file
DriveApp.getFileById(documentId).setName(studentId + ' Learner Map');
// Insert the studentId in cell U1 (of the first tab?)
SpreadsheetApp.openById(documentId).getRange('U1').setValue(studentId);
// Hide the 'import' tab
SpreadsheetApp.openById(documentId).getSheetByName('import').hideSheet();
// Add the student as a viewer
SpreadsheetApp.openById(documentId).addViewer(studentId + '@##EMAIL EXTENSION##');
// Restrict copy/download/print for the viewer
// get the file with the Advanced Drive API (REST V2)
var file = Drive.Files.get(documentId);
Logger.log('File "%s", restricted label was: %s', file.title, file.labels.restricted);
// set the restricted label
file.labels.restricted = true;
//update the file
Drive.Files.update(file, documentId);
// check the updated file
var updatedFile = Drive.Files.get(documentId);
Logger.log('File "%s", restricted label is: %s', updatedFile.title, updatedFile.labels.restricted);
}
}