lundi 8 novembre 2021

Confirmation alert msg for many different columns with checkboxes & automatically input timestamp to 2 of those columns when the checkbox is ticked

I am quite bad at this stuff, I tried searching and have a somewhat workable script (apps script) but I just can't get it to work the way I want it to. I'm sure I added/edited things wrongly.

My Google sheet is a group work tracker and looks like the image below (please see at the end of the post for the imgur link). [Google Sheet image][1]

The primary need is to add an alert message when someone ticks the checkbox in those columns. We are using custom cell values for checked and unchecked as "1" and "0" respectively. The columns with checkboxes are F,K,P,R,S or in numbers 6,11,16,18,19.

If this could be done too it would be great. That is to add a timestamp on columns H,M (or 8,13) when a checkbox in columns F,K is ticked and confirmed "Yes" when the alert comes.

function onEdit(e) {
  const sh=e.range.getSheet();
  if(sh.getName()=='Video Projects' && e.range.columnStart==6,11,16,18 && e.value=='1') {
    var resp=SpreadsheetApp.getUi().alert('Are you sure you want to tick this checkbox?', SpreadsheetApp.getUi().ButtonSet.YES_NO);
    if(resp==SpreadsheetApp.getUi().Button.YES) {
       var range = e.range;
  var sheet = range.getSheet();
  if(sheet.getSheetName() == "Video Projects") {
    if(range.columnStart == 6) {
      var nextCell = range.offset(0, 2);
      nextCell.setValue(new Date());
    } else if(range.columnStart == 11 && range.isChecked()) {
      var nextCell = range.offset(0, 2);
      nextCell.setValue(new Date());
    }
  }
};
    }else{
      e.range.setValue("0");
    }
  }

If anyone knows how to fix this, it would be really helpful! :) [1]: https://i.stack.imgur.com/nARxl.png




Aucun commentaire:

Enregistrer un commentaire