CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Deepak
Deepak

Reputation: 843

Why is my Google spreadsheet script not capturing emails on getUser()?

I'm writing my first Google apps script on google sheets and am pretty excited about the possibilities that it opens up. However, I'm still trying to figure out a few things: such as moving data between different tabs of a spreadsheet and across independent spreadsheets (with different spreadsheet Ids): Can someone suggest how this can be done?

But my immediate concern is that, my script is capturing timestamps but I also want it to record the emails of the users. I read in the docs that there is a complex criteria that determines why an email can be captured or not - what is that criteria? Because strangely while testing the code initially, the my own email was getting captured but only in about 50% of the cases. I'm not sure I understand entirely whats going on here?

My code:

function onEdit(e){
  // Set a comment on the edited cell to indicate when it was changed.
  var range = e.range;
  var activeSheet = e.source.getActiveSheet();
  range.setNote('Last modified: ' + new Date() + new Date().toLocaleTimeString() + ' sheet: ' + activeSheet.getName());
  if (activeSheet.getName() == "KYCEntrySheet"){
    var r = e.source.getActiveCell();
    if( r.getColumn() == 10 ) { //Any changes within this column set the date below and the email on the 2nd call
      //if( r.offset(0, 1).getValue() === '' ) //is empty?
        r.offset(0, 5).setValue(new Date().toLocaleTimeString());//will only put date, format "123/Date and time" if time needed
      //if( r.offset(0, 2).getValue() === '' ) //is empty?
        r.offset(0, 9).setValue(Session.getUser());//inserts current users email
    }}}

Thanks!

Upvotes: 0

Views: 83

Answers (1)

user6655984
user6655984

Reputation:

You can't get emails of users that did not explicitly allow you to know their emails. For example, imagine someone sharing a spreadsheet for everyone to edit, and invisibly collecting their emails using an onEdit trigger. Google does not allow such a thing to happen.

Google didn't want to tell us what the "complex criteria" are, so we don't know. To my understanding, a simple trigger onEdit should not have access to anyone's personal information, even yours. An installable trigger (one that you create through Resources menu) has access to your personal information, but not of others. It's possible you mixed up both of those.

moving data between different tabs of a spreadsheet

Use getSheetByName() to get a handle on the sheet you want, select a range there, and use source.copyTo(target) to copy data.

across independent spreadsheets (with different spreadsheet Ids)

Use range.getValues() in the source spreadsheet and target.setValues() in the target spreadsheet. You'll need SpreadsheetApp.openById to get a handle on the latter.

Upvotes: 1

Related Questions