Google-sheets – Check button in Google Spreadsheets specific for each user

data validationgoogle sheetsgoogle-drive-sharing

I have a Google Spreadsheet with different tasks. I want to implement a check button in the following way:

My intention is that the users can't edit the spreadsheet but they have to be able to use the check button at each task. The difficult part is that it is the intention that these check buttons are user specific. So if one user checks or unchecks a button this doesn't change the check buttons of the other users. Further, it is the intention that when a user closes the spreadsheets and opens it the next day the same buttons have to be checked as when he closed the spreadsheet.

The farthest I got is to make checkboxes that only I as the creator of the spreadsheet can edit. Does anybody have an idea how I could make this or if it is even possible to make?

Best Answer

If you need a checkbox to be user specific, meaning that each user see different value (checked/unchecked), then you should use a different sheet for each user or to use a Menu/Dialog/Sidebar/Web App together with the Properties Service of Google Apps Script to set/get user properties.

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

Google Sheets – Email Specific Spreadsheet Row Data on Event

Have an additional column, "flag" (leave it blank). Now, in your onEdit event:

Look for rows that have a "Closed" status and an empty flag field. (Search the matrix generated by SpreadsheetApp.getActiveSheet().getDataRange().getValues())
Now, for each row, do the following:
Find the email cell in that row (you know the column number), send the emails
Set the flag cell in that row to 1 or something

That's it :)

Best Answer

Related Solutions

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

Code

Explained

Example

Google Sheets – Email Specific Spreadsheet Row Data on Event

Related Topic