0

Prints the current time and date of any cell in column A and the user name of any cell in column B when entering data in any cell in column c only

Note: - Start printing the current time, date and user name from the second row - Do not update the current time and date only in column A when modifying any cell in column c - Deletes the current date, time and user name when deleting data in any cell in column cExplanation

ammaeln
  • 61
  • 8
  • Would you please summarise the research that you've already done - we wouldn't want to double up on that. – Tedinoz Oct 27 '19 at 21:40

1 Answers1

1

This requires an installable onEdit() trigger. I provide an onOpen() function with a createMenu method to use to install the onEdit() trigger from a menu. I also provided the isTrigger function that checks to make sure that there isn't one already installed so that you don't end up with multiple triggers. So be sure to change the name of the function and remove the old onEdit() function. The old onEdit() function didn't have permission to get user information.

function onInstallableEdit(e) {
  if(e.range.getSheet().getName()=='Sheet188') {
    if(e.range.columnStart==3 && e.range.rowStart>1) {
      if(e.range.offset(0,-2).isBlank()) {
        e.range.offset(0,-2).setValue(Utilities.formatDate(new Date(), Session.getScriptTimeZone(), " HH:mm:ss E MMM dd,yyyy"));
      }
      e.range.offset(0,-1).setValue(Session.getActiveUser().getEmail());
    }
  }else{
    return;
  }
}

function installonEdit() {
  if(!isTrigger('onInstallableEdit')) {
    ScriptApp.newTrigger('onInstallableEdit').forSpreadsheet(SpreadsheetApp.getActive()).onEdit().create();
  }
}

function isTrigger(funcName){
  var r=false;
  if(funcName){
    var allTriggers=ScriptApp.getProjectTriggers();
    for(var i=0;i<allTriggers.length;i++){
      if(funcName==allTriggers[i].getHandlerFunction()){
        r=true;
        break;
      }
    }
  }
  return r;
}

function onOpen() {
  SpreadsheetApp.getUi().createMenu('My Menu')
  .addItem('Install On Edit Trigger','installonEdit')
  .addToUi();
}
Cooper
  • 59,616
  • 6
  • 23
  • 54
  • Thank you for your effort ... but the username is not printed when you enter data in any cell in column c, in addition to the date is updated when any change in the data previously entered in column c – ammaeln Oct 28 '19 at 02:16
  • This version will keep the already entered date from changing. As for the user name all we have access to is the users email and not always that. You can try an installable onEdit() and that may work for you. – Cooper Oct 28 '19 at 04:38
  • User's email is not printed in column b ... And finally. Delete data in column A and data in column b when you delete data in column C ... Thanks – ammaeln Oct 29 '19 at 10:55
  • Try it again. I turned it into an installable onEdit(). Simple triggers can't access anything that requires permission. So the user may have to authorize the script first time they run it. I'm not sure because I didn't have to on my other accounts but it's hard to say for sure because my other accounts have numerous connections to my main account. – Cooper Oct 29 '19 at 16:03
  • @Tanaike I could use your help on this one. I don't know how to get the users email address. – Cooper Oct 29 '19 at 17:15
  • Cooper - [link](https://stackoverflow.com/questions/31662671/automatically-stamp-username-who-made-changes-to-google-sheet/31691667#31691667) - There is a code to print the user email ... Could you combine this code with your answer ... Thanks – ammaeln Oct 31 '19 at 20:18
  • Thanks for the link but I already have that code in my answer. But it's not working. – Cooper Oct 31 '19 at 20:21