3

So I have a spreadsheet that is supposed to automatically fetch every users' emails and assign custom usernames for their emails. I use this data to display a per-row "last edited by ---" on a sheet. This also allows me to list all the users viewing the spreadsheet and log when they last opened it.

However, the Session.getActiveUser().getEmail() returns a blank string for anyone else other than me.

I've seen that you could make it so that the scripts run under the user's account. But that is only for deployed web apps. I need to integrate it to spreadsheets. Any ideas on how I should do that?

Rubén
  • 34,714
  • 9
  • 70
  • 166
Jerome Indefenzo
  • 967
  • 6
  • 25
  • have you tried .getEffectiveUser().getEmail()? Or, what are the settings you used when you published the script? Did you set it to run as you, or to run as the user accessing the email? Look at the documentation here https://developers.google.com/apps-script/reference/base/session – thoughtcrime Mar 11 '14 at 05:57
  • getEffectiveUser() only returns my own email, even if other people are opening the sheet. – Jerome Indefenzo Mar 11 '14 at 11:46
  • Then your app is published to run as you, not as user accessing the app. If you can change it, and retain functionality, try it that way. If not, you'll have to add an interface for editing the sheet that collects their email as part of the Ui. I will look at putting something together in a couple of hours after I get the kids to school. – thoughtcrime Mar 11 '14 at 12:12
  • 1
    Thanks, but afaik, deploying the script as a web app wouldn't allow me to use the native google spreadsheet interface. And I can only set the script to run as the user if I deploy it as a web app. I'm dealing entirely with spreadsheets, since all the data I need are already there. Thanks, I'll be waiting for your mockup. – Jerome Indefenzo Mar 11 '14 at 13:48
  • 1
    are you using a regular gmail account or a domain account ? In the first case this is normal behavior for quite a long time (it was working a few years ago but has been intentionally removed) doc here : https://developers.google.com/apps-script/reference/base/session?hl=en – Serge insas Mar 11 '14 at 19:06
  • I'm using a normal account. (didn't know what domain accounts are) So if it's normal behavior, there's no workaround? – Jerome Indefenzo Mar 12 '14 at 04:15

2 Answers2

0

Deploying as a web app (publishing) lets you set under what "authority" or entity the script runs as. If you set the script to run as user accessing the app, and then use the code below, you will get "Row updated 3/11/14 by " with the actual user name there. If you run the app as you, then it will treat you as the effective user, and it will always put your email in there. If the app runs as the user, not author, and it's published as the user accessing, then the app will write the user's email in the updated string. I tested this in my own domain, but can't link to the test sheet outside of our domain.

function onEdit(event){
  Logger.log(event.source.parameters);
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var actSht = event.source.getActiveSheet();
  var actRng = event.source.getActiveRange();
  var index = actRng.getRowIndex();
  var user = Session.getEffectiveUser().getEmail();
  Logger.log(index);
  var dateCol = actSht.getLastColumn();
  var lastCell = actSht.getRange(index,dateCol);
  var date = Utilities.formatDate(new Date(), "GMT-6", "MM/dd/yyyy HH:mm:ss");
  lastCell.setValue("Row Updated " +date+" by "+user);

}
thoughtcrime
  • 293
  • 3
  • 9
  • I don't understand your answer. How can you deploy an onEdit function as a webapp? Could you explain the workflow you imagine starting from the spreadsheet? – Serge insas Mar 11 '14 at 18:46
  • Sorry, that was a poorly worded response. I was thinking of the onEdit being used within the context of a larger script that has additional functions. Also, when I was talking about publishing, I was referencing the options that come up when you click the publish option from the script menu (Publish > deploy as webapp... > version > execute as > who has access), but was thinking of it within the context of a spreadsheet. As far as workflow goes for this, he has a spreadsheet, users edit it, last column gets an updated on X by user Y added. I use this type of thing for form responses a lot. – thoughtcrime Mar 11 '14 at 20:17
  • I'm sorry but I don't follow you. Deployed app cant be used in the context of a spreadsheet using onEdit trigger. The only way for a spreadsheet to use a deployed app would be using an urlFetch to that service app but that's completely different and won't bring a solution to the getEmail issue.A comment from the post author already mentioned that above. – Serge insas Mar 11 '14 at 20:48
  • The menu option that you select to set "who" the web app runs as is called "deploy as web app", and it is found in the script menu bar (file, edit, view, run, *publish*, resources, help) under the Publish menu. If the terminology is wrong, that's on google's end because that is what the menu option is called. I put this exact piece of code in a container-bound script in a spreadsheet and ran it from two different user accounts in our business domain. It collected my address when I logged as author and when logged as a user because I published the app to run as user accessing the app. – thoughtcrime Mar 11 '14 at 21:00
  • I don't feel like arguing here but I'd suggest you "disable that webapp" (or change the "run as" parameters) and you'll notice that the code will still continue to work. The reason why it is working in your code is because you have a domain account. nothing else. – Serge insas Mar 11 '14 at 21:10
  • I'm sorry. I assumed it was being used within a domain since public sheets show everyone as anonymous (or, as has been my experience). I just read the documentation for getActiveUser and assumed it was a domain sheet with the run as.. option set to himself, which the doc says will give the results he reported (either his email or blank). getEffective's doc seems to say that it behaves similarly to getActive, but returns different values with different run as... settings (who is the effective user the script is acting on behalf of). I'm not arguing, I'm learning, and answering helps me learn++. – thoughtcrime Mar 11 '14 at 21:41
  • As I've mentioned, deploying the spreadsheet script wouldn't affect how the spreadsheet works. Afaik it's only used for deloying web apps (which means I wouldn't be using Google's default spreadsheet interface) Also I think the script only applies to the web app that gets deployed after you publish. (the link that google gives you after you publish) And yeah, I don't have a domain account. – Jerome Indefenzo Mar 12 '14 at 04:13
  • That was my misunderstanding from the beginning - I thought you were using in a domain. The only other way I can think of doing it would be to use an onOpen custom UiDialog to request the user's email address to enable editing. If they provide it, you temporarily allow them to edit for that session, and if they don't, you set them to view only. That's a very roughly thought out process, so I'm sure it's not that simple, but it's a general concept. See the custom dialog doc here: https://developers.google.com/apps-script/guides/dialogs#custom_dialogs. That's the only thing I can think of. – thoughtcrime Mar 12 '14 at 04:23
0

you should first run your onEdit function in the Script Editor and authorize it, then it should show the current user.

Jan
  • 1