Student Feedback system - get active spreadsheet user

The following Google Apps Script code was developed to determine if a tutor accessing a Google sheet was tutor 1 or 2, which would later display options on a form they could edit. The tool achieves this by getting their email address from the active session, looking up their name in a table, then checking this against a particular column. The result is a variable set to You are 1 or You are 2 which I then used in an if statement for the rest of the script.

We start by getting the email address from the active tutor ('getActiveUser') logged into the spreadsheet. We then get details from the Tutors sheet (which was populated during the Bulk add Drive permissions post). Now the relevant tutor name can be acquired from an email address if match (see Lookup email address post for further details). Back on the Group Feedback tab it is necessary to 'split' the Tutor 1: and Tutor 2: strings in turn so that we just have the names (so John Smith rather than Tutor 1: John Smith as in my example). A simple if can now determine whether there is a match and return the tutorName variable.

	function getActiveUser(sheet) {
	// get active Tutor email address
	var user = Session.getActiveUser().getEmail();
	Logger.log('Active Tutor email is: ' + user);
	// get data from Tutors sheet to lookup name from email address
	var tutorsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Tutors');
	var tutorsSheetData = tutorsSheet.getRange(2, 1, 2, 2).getValues();
	// lookup active Tutor email address for corresponding name in Tutors sheet
	var tutorName = ''; // create empty variable
	if (user == tutorsSheetData[0][0]) {
	Logger.log(user + ' matches ' + tutorsSheetData[0][0]);
	var tutorName = tutorsSheetData[0][1];
	Logger.log('Tutor name is therefore: ' + tutorName);
	}
	else if (user == tutorsSheetData[1][0]) {
	Logger.log(user + ' matches ' + tutorsSheetData[1][0]);
	var tutorName = tutorsSheetData[1][1];
	Logger.log('Tutor name is therefore: ' + tutorName);
	}
	// get Tutor 1 name from sheet, split just for name
	var tutor1Name = sheet.getRange(1, 3).getValue().split(': ');
	Logger.log('Tutor 1 name is: ' + tutor1Name[1]);
	// get Tutor 2 name from sheet, split just for name
	var tutor2Name = sheet.getRange(1, 7).getValue().split(': ');
	Logger.log('Tutor 2 name is: ' + tutor2Name[1]);
	// find out if user is Tutor 1 or Tutor 2
	if (tutorName == tutor1Name[1]) {
	Logger.log(tutorName + ' matches ' + tutor1Name[1]);
	var tutorName = 'You are 1';
	}
	else if (tutorName == tutor2Name[1]) {
	Logger.log(tutorName + ' matches ' + tutor2Name[1]);
	var tutorName = 'You are 2';
	}
	else {
	Logger.log('No one matches');
	}
	return tutorName;
	}

view raw 1) Get active spreadsheet user.gs hosted with ❤ by GitHub

	function testGetActiveUser() {
	var ss = SpreadsheetApp.getActiveSpreadsheet();
	var groupSheet = ss.getSheetByName('Group Feedback');
	// run function to determine if Tutor 1 or 2
	var whoAmI = getActiveUser(groupSheet);
	Logger.log('Value of whoAmI is: ' + whoAmI);
	}

view raw 2) Get active spreadsheet user.gs hosted with ❤ by GitHub

Get active spreadsheet user.xlsx