Does anyone have any sample code for this? Trying to set up a daily checklist for a team to use to note that they completed a task. Trying to get it so they would just have to check the box in the Google sheet next to the task and in the field to the right, it would auto-insert their name by looking it up in our Google Workforce Directory. Any help would be appreciated.

CodePudding user response：

Insert Users Name

function onEdit(e) {
  //e.source.toast("Entry");
  Logger.log(JSON.stringify(e));
  const sh = e.range.getSheet();
  const checkboxrow = ;//Enter checkbox row
  const checkboxcol = ;//enter checkbox col
  const destinationRange = "";//Enter destination range in a1 notation
  if (sh.getName() == " Enter Your Sheet Name" && e.range.rowStart == checkboxrow && e.range.columnStart == checkboxcol && e.value == "TRUE") {
    sh.getRange(destinationRange).setValue(e.user.nickname);
  }
}

CodePudding user response：

This simple task took surprisingly many lines of code:

// global variables
var SS = SpreadsheetApp.getActiveSpreadsheet(); // spreadsheet
var checkbox_col = 1;                           // column with checkboxes
var sheet_name = 'Sheet1';                      // sheet name

// main function
function insert_user(e) {
  try {
    var {rowStart, columnStart} = e.range;      // get row and column that was edited
    if (columnStart != checkbox_col) return;    // do nothing if it was not column with checkboxes
    var sheet = e.source.getActiveSheet();      // get current sheet
    if (sheet.getName() != sheet_name) return;  // do nothing if the sheet has another name
    if (e.value != 'TRUE') return;              // do nothing if the checkboxs was unchecked
    var user = getOwnName();                    // get the user name

    sheet.getRange(rowStart,checkbox_col 1).setValue(user); // set user name into the next cell
  } 
  catch(err) { SpreadsheetApp.getUi().alert(err) }
}


// additional functions ------------------------------------------------------

// remove all triggers
function remove_all_triggers() {
  ScriptApp.getProjectTriggers().forEach(t => ScriptApp.deleteTrigger(t));
  SS.toast('All triggers were remoded');
}

// install onEdit trigger
function install_onEidt_trigger() {
  ScriptApp.newTrigger('insert_user').forSpreadsheet(SS).onEdit().create();
  SS.toast('Trigger was installed');
}

// custom menu to install and remove triggers
function onOpen() {
  SpreadsheetApp.getUi().createMenu('⚙️ Scripts')
  .addItem('Install trigger', 'install_onEidt_trigger')
  .addItem('Remove all triggers', 'remove_all_triggers')
  .addToUi();
}

// get user name, credits: https://stackoverflow.com/a/15628478/14265469
function getOwnName(){
  var email = Session.getEffectiveUser().getEmail();
  var self = ContactsApp.getContact(email);
  if (self) {
    var name = self.getGivenName();
    if (!name) name = self.getFullName();
    return name;
  }
  else {
    var userName = Session.getEffectiveUser().getUsername();
    return userName;
  }
}

First you will need to reload your spreadsheet and install the trigger via custom menu.

After that whenever one click on checkboxes in column 'A' on the sheet with a name 'Sheet1' it will add user name in column 'B':