I am getting frustrated with the ever-evolving capabilities/ restrictions of Google Script.
I would like to accomplish the seemingly simple task of writing a timestamp and the editor's username to an adjacent cell whenever they edit a cell within a column.
Here is the code I've tried so far:
/**
* Get current user's name, by accessing their contacts.
*
* @returns {String} First name (GivenName) if available,
* else FullName, or login ID (userName)
* if record not found in contacts.
*/
function getOwnName(){
var email = Session.getEffectiveUser().getEmail();
var self = ContactsApp.getContact(email);
// If user has themselves in their contacts, return their name
if (self) {
// Prefer given name, if that's available
var name = self.getGivenName();
// But we will settle for the full name
if (!name)
name = self.getFullName();
return name;
}
// If they don't have themselves in Contacts, return the bald userName.
else {
var userName = Session.getEffectiveUser().getUsername();
return userName;
}
}
However, getEffectiveUser only grabs my username, as opposed to the current editor. getActiveUser returns nothing.
The alternative is not pretty. I saw some example where the current user would select their email from a list of all editors when they opened the spreadsheet. The email would be stored somewhere on a scratch sheet and somehow pasted into the cell when the user would make an edit, though the email storage and retrieval were left out of the example.
As a horrible and incredibly slow/ inefficient hack I insert the timestamp, then popup a page with a listbox and button, grab the usernames from a cell-range, and when the editor selects their name it should paste the value into the adjacent cell. However that seems to randomly run into authorization issues for some as yet undetermined reason, not to mention being insanely slow:
function show() {
var doc = SpreadsheetApp.getActiveSpreadsheet();
var specialSheet = doc.getSheetByName("ADDL. INFO");
var app = UiApp.createApplication().setTitle('Select Name').setWidth(150).setHeight(150);
var panel = app.createVerticalPanel();
var lb = app.createListBox(true).setId('myId').setName('myLbName');
// add items to ListBox
lb.setVisibleItemCount(4);
// Getting a known named range from spreadsheet object.
empLocRange = specialSheet.getRange("A28:A32");
//.getRangeByName('EmployeeLocations');
empLocs = empLocRange.getValues();
empLocs.sort()
for (i=0; i < empLocs.length; ++i) {
empLoc = empLocs[i][0].toString().trim();
if (!(empLoc=='')) {
lb.addItem(empLoc);
}
}
panel.add(lb);
var button = app.createButton('Select');
var handler = app.createServerClickHandler('click').addCallbackElement(panel);
button.addClickHandler(handler);
panel.add(button);
app.add(panel);
doc.show(app);
}
function click(eventInfo) {
var app = UiApp.getActiveApplication();
// get values of ListBox
var value = eventInfo.parameter.myLbName;
// multi select box returns a comma separated string
var n = value.split(',');
var s = SpreadsheetApp.getActiveSheet();
var r = s.getActiveCell();
var p = r.getRow();
var nextCell = r.offset(0, 2);
var app = UiApp.getActiveApplication();
nextCell.setValue(value);
app.close();
return app;
}
I read that publishing the script as a stand-alone app you can access the current username but it does not seem like that would be the correct solution. I thought I may be able to install this script for each user as a solution, but it does not seem to operate in that manner.
Any assistance or would be greatly appreciated.
Edit. Forgot to include the function that calls this:
function timestamp() {
var s = SpreadsheetApp.getActiveSheet();
var r = s.getActiveCell();
var p = r.getRow();
if( r.getColumn() == 14 ) //checks the column
{
var nextCell = r.offset(0, 1);
nextCell.setValue(new Date());
//s.setActiveSelection(p + "15");
show();
//SpecialonOpen();
//nextCell = r.offset(0,2);
//nextCell.setValue(getOwnName());
}
}
I have the above timestamp function set to trigger on edit - timestamp, from spreadsheet, on edit.
UPDATE:
I have been banging my head against this some more.
This is the result, though with odd, unexplained behavior.
My current scripts:
function getOwnName(){
var email = Session.getEffectiveUser().getEmail();
//Browser.msgBox("getOwnName");
//var self = ContactsApp.getContact(email);
var self = false;
// If user has themselves in their contacts, return their name
if (self) {
// Prefer given name, if that's available
var name = self.getGivenName();
// But we will settle for the full name
if (!name)
{
name = self.getFullName();
}
//Browser.msgBox(name);
return name;
}
// If they don't have themselves in Contacts, return the bald userName.
else {
var userName = Session.getEffectiveUser().getUsername();
//Browser.msgBox(userName);
return userName;
}
}
function onEdit() {
var s = SpreadsheetApp.getActiveSheet();
var r = s.getActiveCell();
var p = r.getRow();
if( r.getColumn() == 14 ) //checks the column
{
var nextCell = r.offset(0, 1);
nextCell.setValue(new Date());
//Browser.msgBox("hey!");
//s.setActiveSelection(p + "15");
//show();
//SpecialonOpen();
var nCell = r.offset(0,2);
//Browser.msgBox("olah:" + getOwnName());
nCell.setValue(getOwnName());
}
}
I commented out //var self = ContactsApp.getContact(email); as it seemed to be crashing the script.
After doing so I went to one of my users and asked them to test. Still no username written to the cell. I told him to open up the script and execute the function with the play-button. This popped up an authorization message. He agreed to authorize the script and upon returning to the spreadsheet his name was successfully inserted into the cell.
However, the script still will not write his user name to the cell when triggered by onEdit. So I really have no idea what is going on with GS. We are both on the same domain.
