CODEWITHSUNDEEP
javascriptgoogle-apps-script
Norbert
Norbert

Reputation: 43

How to use a function's value in another function onEdit

I am a rookie to programming and I realized it is probably a dumb one, but I cant seem to find a solution to this. I am using a onOpen trigger and a onEdit trigger in Google Sheets. The onOpen function only gets a name from the user through the input box. Now with the onEdit function I want to timestamp all the changes that the user makes to the cells while adding his name to the timestamp. I hope I am clear with my objective.

Here is the supposed code:

function onOpen() {
  var id = Browser.inputBox('ID Check', 'Enter your name', Browser.Buttons.OK);

  return id
}

function onEdit() {
  // Set a comment on the edited cell to indicate when it was changed.
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getSheets()[0]
  var range = sheet.getActiveRange()

  // var name = onOpen().value;  .... this what i would like to get

  range.setNote('Last modified: ' + new Date() + name);
}

So instead of typing my name after every action, I want it to take my name at the beginning of the session and use it throughout the whole session to add it to the timestamp.

Upvotes: 2

Views: 132

Answers (1)

Alan Wells
Alan Wells

Reputation: 31300

If the users of the spreadsheet are all logged into their Google account, then you can save their name to "User Properties" service. If multiple users are using the spreadsheet at the same time, then User Properties will get the correct name for that user, even though the code is using the same key name.

If the users are not signed into a Google account, then User Properties can not be used. You could save each user name to the Document Properties of the spreadsheet, but if there were multiple users of the same spreadsheet NOT SIGNED INTO A GOOGLE ACCOUNT, then the spreadsheet can not "know" who is using the spreadsheet, and therefore can not know what name to retrieve.

So, if multiple users will be using the same spreadsheet at the same time, then the only way to know what name to assign is if the users have a Google account and they are logged in.

The following code shows how to save and then retrieve the name from User Properties.

function onOpen() {
  var usrProps,sessionName;

  sessionName = Browser.inputBox('ID Check', 'Enter your name', Browser.Buttons.OK);

  if (!sessionName) {//The user did not enter a name
    sessionName = Session.getActiveUser().getEmail();//Get the logged in users email address

    sessionName = sessionName.slice(0,sessionName.indexOf("@"));//Remove domain part of email address
  }

  usrProps = PropertiesService.getUserProperties();//Get User Properties

  usrProps.setProperty('usrName', sessionName);//Save the user name with a key name

  return sessionName;
}

function onEdit() {
  var currentNote,newNote,sessionName,usrProps;

  // Set a comment on the edited cell to indicate when it was changed.
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getSheets()[0]
  var range = sheet.getActiveRange()

  // var name = onOpen().value;  .... this what i would like to get
  usrProps = PropertiesService.getUserProperties();//Get User Properties

  sessionName = usrProps.getProperty('usrName');//Get the user name for this user that was saved in onOpen

  currentNote = range.getNote();//Get current note

  if (currentNote) {
    newNote = currentNote + ' - Last modified: ' + new Date() + sessionName;
  } else {
    newNote = 'Last modified: ' + new Date() + sessionName;
  }

  range.clear({commentsOnly: true});//Clear existing comment
  range.setNote(newNote);
}

Upvotes: 2

Related Questions