Google-sheets – Editable Google Spreadsheet on Google site – assign username

google sheetsgoogle-sites

So here is what I am trying to do:

Have a Google site that has an embedded editable Google Spreadsheet on that allows end users (if they have been given permission) to edit the spreadsheet.

Here is the purpose, I am trying to create a tasks list application that allows authorised users to view a list of currently available tasks (from Google Spreadsheet) and then assign themselves a task (which will be one of the columns in the spreadsheet "assigned". Is there a way to have that column (assigned) a drop down box of the current user signed in? I.e. so when user 1 logs in to the site, any task they want to assign themselves will have a drop down option of blank or their name. The same goes for all other users.

Is that possible?

Best Answer

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

Related Topic