Student Feedback system - bulk create Group spreadsheets

The following Google Apps Script code was developed as part of a larger student feedback system and is designed to create a number of Group spreadsheets (eg 'A1', 'B1', 'C1') with the relevant students (listed in a spreadsheet) contained within them. So at its peak it went down a list of 400+ students, creating a spreadsheet for 36 individual Groups, then pulling through only the students that belonged in each of those Groups.

Students and their Groups

Group spreadsheets containing relevant students

The script starts by getting the data from each sheet as individual arrays (as this is the fastest way to then access such data later on). You will need to set the relevant Google Drive ID on the Config sheet for the folder to save the Group spreadsheets into.

A loop is then created to cycle through each Group ID on the Groups sheet. It checks that the Group File Link column is empty at this point - otherwise a file may already exist and hence it will not run for that Group. It can now 'makeCopy' of the Group Template file with a suitable name, 'getUrl' to then 'setFormula' for a link to the file for easy access.

Another loop is created inside the above to now search through students and check to see if they belong in the current Group. When a student is found their name and email address are pushed into an array (see this blog post for details of this process).

Once the students have been collated the Group spreadsheet is now opened ('openByUrl') and a loop is used to 'setValue' for each name and email address pair.

Bulk create Group spreadsheets.xlsx
Student Feedback Group Template.xlsx