About Google Sheets that is shared with users

Question

I have a Google Sheets shared with others. My sheet name is "OrderList". I am looking for a script that grabs the user's email address and inputs into another cell when a user edits the sheet. I have written the following code, however it only returns the sheet owner's Gmail address:

var COLUMNTOCHECK1 = 5;
var COLUMNTOCHECK2 = 6;

var DATETIMELOCATION1 = [0,2];
var DATETIMELOCATION2 = [0,3];

var SHEETNAME = 'OrderList'

function onEdit(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();

if( sheet.getSheetName() == SHEETNAME ) { 
var selectedCell = ss.getActiveCell();

if( selectedCell.getColumn() == COLUMNTOCHECK1) { 
    var email = Session.getActiveUser().getEmail();

  var dateTimeCell = 
  selectedCell.offset(DATETIMELOCATION1[0],DATETIMELOCATION1[1]);
  dateTimeCell.setValue(new Date());
  var dateTimeCell1 = 
  selectedCell.offset(DATETIMELOCATION2[0],DATETIMELOCATION2[1]);
  dateTimeCell1.setValue(email);
  }
   if( selectedCell.getColumn() == COLUMNTOCHECK2) { 
  var dateTimeCell = 
  selectedCell.offset(DATETIMELOCATION2[0],DATETIMELOCATION2[1]);
  dateTimeCell.setValue(new Date());
   }

   }
   }

ross ross · Accepted Answer · 2019-05-22T08:10:18

Since you're running this onEdit(e) you can use Event Objects, these are great for capturing the data you're trying to achieve. You'll need to set up an onEdit trigger for your project to capture the email address though. The code below should achieve what you're expecting.

var COLUMNTOCHECK1 = 5;
var COLUMNTOCHECK2 = 6;

var DATETIMELOCATION1 = [0,2];
var DATETIMELOCATION2 = [0,3];

var SHEETNAME = 'OrderList'

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  if( sheet.getSheetName() == SHEETNAME ) { 
    var selectedCell = ss.getActiveCell();

    if( selectedCell.getColumn() == COLUMNTOCHECK1) { 
      var email = e.user.getEmail();

      var dateTimeCell = 
          selectedCell.offset(DATETIMELOCATION1[0],DATETIMELOCATION1[1]);
      dateTimeCell.setValue(new Date());
      var dateTimeCell1 = 
          selectedCell.offset(DATETIMELOCATION2[0],DATETIMELOCATION2[1]);
      dateTimeCell1.setValue(email);
    }
    if( selectedCell.getColumn() == COLUMNTOCHECK2) { 
      var dateTimeCell = selectedCell.offset(DATETIMELOCATION2[0],DATETIMELOCATION2[1]);
      dateTimeCell.setValue(new Date());
    }

  }
}

As you can see, I've change your var email to use an event object to capture the email address of the user that is editing the spreadsheet:

var email = e.user.getEmail();

About Google Sheets that is shared with users

1 Answers