samedi 4 juin 2016

GoogleSheet script editor - onEdit event with conditions / if statement

guys!

I'm new to this website and also not good with coding. So I would really appreciate some help.

Right now I'm in need of a specific code to make a google sheet work perfectly.

To further explain: I have a google sheet that a few information will be input by other co-workers. What I need is a code that will register the date in a specific cell and by whom the input was made on another cell.

So far this is what I have:

function onEdit(event) { 

  var sheet = event.source.getSheetByName("Input");

  // Note: actRng = return the last cell of the row modified
  var actRng = event.source.getActiveRange();
  var index = actRng.getRowIndex();
  var cindex = actRng.getColumnIndex();

  // Note: date = return date
  // Note: user = return the user email
  var userCell = sheet.getRange(index,14);
  var dateCell = sheet.getRange(index,2);
  var inputdate = Utilities.formatDate(new Date(), "GMT+0200", "yyyy-MM-dd");
  // Note(with hour): var inputdate = Utilities.formatDate(new Date(), "GMT+0200", "yy-MM-dd HH:mm");


  //var user = event.user;  // Note: event.user will not give you collaborator's Id
  var user = Session.getEffectiveUser();

  // Note: setValue = Insert in the cell the date when this row was modified
  if (userCell.Value == null) {
    userCell.setValue(user); 
    dateCell.setValue(inputdate)
  } 

}

My main problems/questions are:

  • I don't exactly need the last modifier, but the person who first input info on the cells. Therefore I tried that last IF (If the cell that is supposed to have the last modifier e-mail is blank, it means that nobody changed that row before, so the code should add the user on the userCell), although it is not working since every change I make it ignores the verification.

  • I also want to add that the event will only happen if you add values, if you delete them, nothing happens. (so far even when I delete cells, it counts as modification)

  • Most of the sheet is protected to avoid that people by accident erase some of the formulas, so the cells that this code changes are also protected. Is there a way to make the code bypass cell protection?

Please, help me identify what I'm doing wrong and hopefully I'll get this working perfectly! Thanks for the help !

Aucun commentaire:

Enregistrer un commentaire