google-apps-script - 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());
   }

   }
   }

标签： google-apps-scriptgoogle-sheets

Answer 1

由于您正在运行它，因此onEdit(e)您可以使用Event Objects，这些非常适合捕获您尝试获取的数据。不过，您需要为您的项目设置一个 onEdit 触发器来捕获电子邮件地址。下面的代码应该达到您的预期。

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());
    }

  }
}

如您所见，我已将您更改var email为使用事件对象来捕获正在编辑电子表格的用户的电子邮件地址：

var email = e.user.getEmail();