Save email to Drive add-on - get student details

Overview blog post.

The following Google Apps Script code queries the relevant Google sheet and proceeds to lookup the given username to find the corresponding user details (eg name, folder ID). It also maintains an error code value which is used to inform the user of any missing data in the spreadsheet as well as helping to prevent the script from encountering an error.

We start by getting all of the student data from the spreadsheet via the global ssId variable:

var ss = SpreadsheetApp.openById(ssId);
var dataSheet = ss.getSheetByName('data');
var list = dataSheet.getDataRange().getValues();

Next the variables used in the loop are reset so they do not hold any leftover values each time the add-on script runs. The spreadsheet data is then converted 'toString' so that 'indexOf' can be used to search the (array) of data for the userId value - so it checks this exists before proceeding to gather student-specific data.

If the userId exists we then loop through the array of data and pick up: forename, surname, folder ID. An if/else statement is used for each so that if a corresponding value is not found then we increment the detailsError variable to determine the error code value so we can display a specific message to the user informing them of which piece of data is missing:

if (list[i][3] != '') {
          folderId = list[i][3];
          Logger.log('Folder ID is: ' + folderId);
}
else {
          //error increase by 3 for missing folder ID
          detailsError+=3;
}

At the end of the script an array of values is returned to the getUserInfo function.