My first Apps Script - can this be more efficient?

Viewed 93

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:

  1. 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.
  2. The script collects the StudentID numbers from DATA and creates a new Google Sheet for each student based on the TEMPLATE Google Sheet.
  3. 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'].
  4. Add the student as a viewer.
  5. 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);
            
  }
}
0 Answers
Related