IF I click one cell email or name needs to be captured in Gsheet

Question

if someone clicks on the column Ay checkbox his/her email needs to be captured in a different column cell

[

Answer 1

You are not always going to be able to get access to user and the only trigger that works on a click is onSelectionChange() which is a simple trigger which means it can't do anything that requires permissions and Session.getActiveUser() requires https://www.googleapis.com/auth/userinfo.email. I don't think there is a solution for what you want.

You can try this function to see if it can work for you.

  function onSelectionChange(e) {
  if (e.user) {
    const ss = SpreadsheetApp.getActive();
    const sh = ss.getSheetByName("Log");//You need to create this
    sh.appendRow([new Date(),e.user.email,e.user.nickname])
    Logger.log('email: %s', e.user.email);
    Logger.log('nickName: %s', e.user.nickname);
  }
  Logger.log(JSON.stringify(e));
}

Trying it on yourself doesn't count.

Using installable onedit function:

function onInstallableEdit(e) {
  //e.source.toast("Entry");//debug
  //Logger.log(JSON.stringify(e));//debug
  const sh = e.range.getSheet();
  if(sh.getName() == "Audit sheet May" && e.range.columnStart == 51 && e.range.rowStart > 1 && e.value == 1 ) {
    //e.source.toast('Flag1');//debug
    if(e.user) {
      //e.range.offset(0,1).setValue(`${e.user.email}\n${new Date()}`);
      e.range.offset(0,1,1,2).setValues([[e.user.email,new Date()]]);
    }
  }
}

function createTrigger() {
  if(ScriptApp.getProjectTriggers().filter(t => t.getHandlerFunction() == "onInstallableEdit").length == 0)  {
    ScriptApp.newTrigger("onInstallableEdit").forSpreadsheet(SpreadsheetApp.getActive()).onEdit().create();
  }
}