Google-sheets – Link in Google Sheets to either create a new document or link to the one that was previously created

My interpretation is that you want to add a link to your spreadsheet that leads to a Google Mail message?

Open the email message in Google Mail. The URL in the address bar is the link you want to use. I do this all the time with my own personal task tracking.

However, if multiple people are using your spreadsheet system and they all have their own non-shared accounts, I'm not so sure how useful that link to an email message will be. I believe only the person who's mailbox contains the message will be able to use the link.

The following piece of code does the trick.

Code

function onOpen() {
  // get active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // create menu
  var menu = [
    {name: "Add active user", functionName: "addUser"}
  ];

  // add to menu
  ss.addMenu("Add user", menu);
}

function addUser() {
  // get active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get first sheet
  var sheet = ss.getSheets()[0];

  // get data
  var user = Session.getActiveUser().getUserLoginId()

  // get active/selected row
  var activeRow = ss.getActiveRange().getRowIndex();

  // set user to cell
  sheet.getRange("B"+activeRow).setValue(user);  
}

Explained

It adds a menu item to the existing menu upon file opening. The selection lets the user decide whether to assign her/his name (e-mail). I've tried it, using my own two accounts in two different browsers.

Example

See example file I've created: add active user