Ever needed to grab the name of the user that edited a line of a Sheet? Sure you can comb through the version history but isn't it better to just see the the information right on the sheet? Google Scripts provides a way to capture the user that edited. Let's take it one step further and make a bathroom log. Students logged in to their Google Account on the same domain as you will be able to click an icon and create a record that they are going to the restroom. No one will be able to mark the sheet while they are out.
Sample
So the code we need to know to identify the user currently editing the sheet would be Session.getActiveUser().getEmail()
To break this down, the Session refers to the period a user is logged in. A session can be time limited or limited to the particular device being used. We use the Session to access the method of
getActiveUser()
The active user is the user logged in. From here the only selection is
getEmail()
Stringing these together and we can identify who is using the sheet.
We begin our function creating our variables to represent the Spreadsheet and the currently active sheet.
function bathroom() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
}
Because we will be adding the user to the last empty row we want to identify which row that is. sheet.getLastRow() returns the value of the last filled row. So the next available row would be sheet.getLastRow() + 1.
function bathroom() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var lastRow = sheet.getLastRow() + 1;
}
We need to consider the structure of our spreadsheet. We'll create a sheet with the following headers.
We can use the function getColumnByName used in the previous post
here. But I'll keep it very simple and we'll use the column numbers. We're going to want to know if a student has come back from the bathroom first. To do this we want to check the last filled row and the 3rd column which is labeled "In".
function bathroom() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var lastRow = sheet.getLastRow() + 1;
var lastcell = sheet.getRange(lastRow-1, 3).getValue();
Logger.log("Last Row " + lastRow + " Last Cell Value " + lastcell)
}
I've thrown a loger line here to test the code and see what values it returns. It's helpful to do this as your write. I've torn my hair out and spent days staring at code not realizing I only thought I knew the value of a particular variable. Don't assume, debug.
The last entered user is also going to be needed. We can get this in the same manner we retrieved the lastcell variable.
function bathroom() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var lastRow = sheet.getLastRow() + 1;
var lastcell = sheet.getRange(lastRow-1, 3).getValue();
var user = sheet.getRange(lastRow-1, 1).getValue();
Logger.log("Last Row " + lastRow + " Last Cell Value " + lastcell + " User " + user)
}
Now we start doing some comparisons to know the condition our sheet is in.
if (lastcell.length == 0 && user == Session.getActiveUser().getEmail())
Here we are checking if the number of characters in the lastcell variable is equal to 0 AND the user variable has the same email in it as the current user in the sheet then we will do something. If these two things are true then it means the student has come back from the bathroom and is checking in. We will need to enter the current timestamp in the "In" column and we can set the formula to calculate how long they were in the bathroom.
We initialize and set a variable to the result of setting the timestamp. Assigning this to a variable can help to identify errors if things are going wrong but not absolutely needed. We grab the range using the last row variable which we already advanced by 1. To get the last filled row we reduce the variable by 1. The column is column number 3. We set the value to a new date object which we typically read as a timestamp.
we also move forward with setting a formula in Row and Column format. The formula here is relative to the cell the formula is entered in. In this case R[0] stands for the current cell's row. C[-1] is one column less. since we are putting our formula in column 4 one column back would be column 3 which we just put our In timestamp in. We then subtract the C[-2] column or column 2 of the same row. This gives us a duration of how long they were in the bathroom.
function bathroom() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var lastRow = sheet.getLastRow() + 1;
var lastcell = sheet.getRange(lastRow-1, 3).getValue();
var user = sheet.getRange(lastRow-1, 1).getValue();
if (lastcell.length == 0 && user == Session.getActiveUser().getEmail()){
var col2 = sheet.getRange(lastRow-1, 3).setValue(new Date());
var formula = sheet.getRange(lastRow-1, 4).setFormulaR1C1("=R[0]C[-1] - R[0]C[-2]")
}
}
We now have the if statement identifying when a user returns. But what if the user clicking the icon is not the user in the bathroom. They need to know that they can't go yet. We again check if the lastcell length is 0 meaning it's blank. We also check to determine the user in the last row is not the current user. The usage of != means not equal. If both conditions are true we assign the variable ui an alert with a message.
function bathroom() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var lastRow = sheet.getLastRow() + 1;
var lastcell = sheet.getRange(lastRow-1, 3).getValue();
var user = sheet.getRange(lastRow-1, 1).getValue();
if (lastcell.length == 0 && user == Session.getActiveUser().getEmail()){
var col2 = sheet.getRange(lastRow-1, 3).setValue(new Date());
var formula = sheet.getRange(lastRow-1, 4).setFormulaR1C1("=R[0]C[-1] - R[0]C[-2]")
}else if(lastcell.length ==0 && user != Session.getActiveUser().getEmail()){
var ui =SpreadsheetApp.getUi().alert('Sorry, someone is currently out.')
}
}
}
Finally at the end if the user is not the one returning and if the user is not still out and we can add a new user we do so. The else statement is our default statement. If 1 then 1a, else if 2 then 2a, else default. We can think of the progression like this. In our action for the default response we use the variable col1 to capture the setting of the user. The range is gotten of the last row variable which represents a new row and the column of 1. We set this cell to the value of the current users email address. We then set the second column on the same row as a new date object which we know of as a timestamp.
function bathroom() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var lastRow = sheet.getLastRow() + 1;
var lastcell = sheet.getRange(lastRow-1, 3).getValue();
var user = sheet.getRange(lastRow-1, 1).getValue();
if (lastcell.length == 0 && user == Session.getActiveUser().getEmail()){
var col2 = sheet.getRange(lastRow-1, 3).setValue(new Date());
var formula = sheet.getRange(lastRow-1, 4).setFormulaR1C1("=R[0]C[-1] - R[0]C[-2]")
}else if(lastcell.length ==0 && user != Session.getActiveUser().getEmail()){
var ui =SpreadsheetApp.getUi().alert('Sorry, someone is currently out.')
}else{
var col1 = sheet.getRange(lastRow, 1).setValue(Session.getActiveUser().getEmail());
var col2 = sheet.getRange(lastRow, 2).setValue(new Date());
}
}
This function can then be assigned to an image or a menu. Consider using some of these techniques for an onEdit trigger to track who changes a line on a spreadsheet.
