Class Session not working in some Google spreadsheets

Question

I have a problem with the class Session in Google Apps Scripts

I wrote a script that records the date and user who edits a sheet in a spreadsheet. It works good with my test page, but when I put it in another spreadsheet (a copy named like "test") it does not work!! I have 8 Spreadsheets with the same number of sheets, all named similar, and the script only works in one of the spreadsheets (and test). This is the script:

function onEdit(event)
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  //Last Session record
  var actSht = event.source.getSheetByName("somepage")
  var actRng = event.source.getActiveRange();
  var lastCell0 = actSht.getRange(2,16);
  var lastCell1 = actSht.getRange(2,17);
  var date = Utilities.formatDate(new Date(), "GMT-6", "dd/MM/yyyy HH:mm");
  var r= actSht.getActiveCell().getRow();

  lastCell0.setValue("Date_Mod");
  lastCell1.setValue("user");

  if(r=='3'){
    actSht.getRange(r,17).setValue(Session.getEffectiveUser());
    actSht.getRange(r,16).setValue(date);
  }

  if(r=='4'){
    actSht.getRange(r,17).setValue(Session.getEffectiveUser());
    actSht.getRange(r,16).setValue(date);
  }
 }

This is the same code for two spreadsheets, in one it works OK, in the other it does not work.

How do I fix this?

in my test spreadsheet the user mail is set OK in cell (r,17), but in another spreadsheet it not work, only set the date (r,16). Thanks — Oswaldo VC
You need to debug the code. Maybe use a JavaScript Try/Catch code block. Just looking at the code, it seems okay, and it's very strange that the if code block would run, and set the date which is a line after the statement that isn't working. If the effective user line of code was killing your code, it would stop there, and not enter the date. — Alan Wells
Does the Session.getEffectiveUser() method need user approval? In other words, if the user is not approving access, it won't work because of permissions? — Alan Wells
Thanks for help! I have two comments about this. first, I don´t execute the script in the spreadsheet. I execute th script and it works OK. Second,the script work with some users, but with anothers it don't work. Testing with others users and it works ok, but the problem persist with someones. — Oswaldo VC

theworldismyoyster theworldismyoyster · Accepted Answer · 2015-01-31T08:02:11

It is most likely a security issue and Session calls are supposed to be avoided in simple triggers due to simple triggers limitations (look at https://developers.google.com/apps-script/guides/triggers/#restrictions, especially in regard to the very vague 5th bullet point - "They may or may not be able to determine the identity of current the user" (mistake on page) and where the link sends you back to the session page). In your case I wonder whether it worked only for the users who shared your domain name.

If you look at https://developers.google.com/apps-script/reference/base/session#getEffectiveUser() there is no reference for simple triggers (which for me is suspicious already) but the statement in regard to installable triggers tells me that it should probably not be used in simple ones.

I just went through a similar exercise myself where both getActiveUser and getEffectiveUser were returning blank when the domain of the script owner did not match the domain of the person who launched the script (in other words this happens when someone shares the script outside the domain and run it under a different username such as their personal Google account). I therefore created an installable trigger instead that calls getEffectiveUser and it works now (note that if you define your installable trigger programmatically rather than through Resources->Current project's triggers make sure the trigger function is in the invoking script file).

Hope this helps.

Class Session not working in some Google spreadsheets

1 Answers