Confirmation alert msg for many different columns with checkboxes & automatically input timestamp to a few columns when the checkbox is ticked

Question

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.

1. The primary need is to add an alert message when someone checks or unchecks any of the checkboxes in those columns (to prevent any misclicks - like so ). 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.

2. In addition to that, I would like a timestamp (format dd/mm/yyyy) to be populated 2 columns away when any of the checkboxes in columns F&K are checked (and confirms the alert message). However, when an already ticked checkbox is unticked, the date should not be removed or updated.

The below script works to a certain extent, but I don't know how to amend it.

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! :)

Answer 1

I believe your goal is as follows.

• You want to check the checkboxes of the columns F,K,P,R,S.
• When the checkboxes of columns "F" and "K" are clicked, you want to put the date to the column "H" and "M", respectively.

Modification points:

• When e.range.columnStart==6,11,16,18 is used as the if statement, all values of e.range.columnStart are true. I thought that this might be the reason of your issue.

When this point is reflected in your script, it becomes as follows.

Modified script:

function onEdit(e) {
  const columns = [6, 11, 16, 18, 19]; // columns F,K,P,R,S
  const { range, value } = e;
  const sheet = range.getSheet();
  if (sheet.getSheetName() != 'Video Projects' || !columns.includes(range.columnStart) || value == "0") return;
  const ui = SpreadsheetApp.getUi();
  const res = ui.alert('Are you sure you want to tick this checkbox?', SpreadsheetApp.getUi().ButtonSet.YES_NO);
  if (res == ui.Button.YES) {
    if (range.columnStart == 6 || (range.columnStart == 11 && value == "1")) {
      range.offset(0, 2).setValue(new Date());
    }
    return;
  }
  range.uncheck();
}

References:

• Simple Triggers
• Event Objects

Added 1:

About your following 2 new requests,

Request 1: could we also add the alert message when unchecking a checkbox too?

Request 2: Is it possible for it to input only the date as dd/mm/yyyy?

The sample script is as follows.

Sample script:

function onEdit(e) {
  const columns = [6, 11, 16, 18, 19]; // columns F,K,P,R,S
  const { range, value } = e;
  const sheet = range.getSheet();
  if (sheet.getSheetName() != 'Video Projects' || !columns.includes(range.columnStart)) return;
  const ui = SpreadsheetApp.getUi();
  const res = ui.alert('Are you sure you want to tick this checkbox?', SpreadsheetApp.getUi().ButtonSet.YES_NO);
  if (res == ui.Button.YES) {
    if (range.columnStart == 6 || (range.columnStart == 11 && value == "1")) {
      range.offset(0, 2).setValue(Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "dd/MM/yyyy"));
    }
    return;
  }
  range.uncheck();
}

Added 2:

About your following a new request,

Request 1: can be changed to "Are you sure you want to untick this checkbox?"

The sample script is as follows.

Sample script:

function onEdit(e) {
  const columns = [6, 11, 16, 18, 19]; // columns F,K,P,R,S
  const { range, value } = e;
  const sheet = range.getSheet();
  if (sheet.getSheetName() != 'Video Projects' || !columns.includes(range.columnStart)) return;
  const ui = SpreadsheetApp.getUi();
  if (value == "0") {
    const res = ui.alert("Are you sure you want to untick this checkbox?", SpreadsheetApp.getUi().ButtonSet.YES_NO);
    if (res == ui.Button.NO) range.check();
    return;
  }
  const res = ui.alert('Are you sure you want to tick this checkbox?', SpreadsheetApp.getUi().ButtonSet.YES_NO);
  if (res == ui.Button.YES) {
    if (range.columnStart == 6 || (range.columnStart == 11 && value == "1")) {
      range.offset(0, 2).setValue(Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "dd/MM/yyyy"));
    }
    return;
  }
  range.uncheck();
}