Thursday 3 January 2019

Student Feedback system - bulk add Drive permissions

Following on from Bulk create Group spreadsheets we can now look at adding permissions to allow the relevant tutors to have access to the files. The following Google Apps Script code is designed to:
  • Lookup the two tutors belonging to a Group and find their email address.
  • Extract the file ID from the Url that links to this Groups spreadsheet and add them as editors.
  • Open the Group spreadsheet and add the tutors to the relevant column headings and Tutors sheet tab.

Lookup the tutors
The first part of the script starts by looping through a Group at a time (so 'A1' then 'B1', etc) and it creates another loop inside this to handle the two tutors in-turn, it then creates a third loop that looks-up a matching tutor name to find their corresponding email address (a process detailed in this blog post). Once we have the tutor name / email address they can be pushed into an array for later use.

Get the file and add editors
By 'getFormula' we can get the Url of the Group spreadsheet from the Group File Link column and then proceed to extract the ID part via a regex (detailed in this blog post). Using 'DriveApp' we can now 'getFileById' and loop through the above tutor array to addEditor via their email address. At this point each tutor will receive an automated email inviting them to edit this file.

Add tutor details
The final part of the script adds the tutors details to various parts of the Group spreadsheet (a process important for a future blog post, as well as completing the process of creating the Groups). With the extracted file ID we can use the 'SpreadsheetApp' to 'openById' and 'setValue' a number of targeted cells (again with values from the above tutor array).

Bulk add Drive permissions.xlsx

No comments:

Post a Comment