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 clicks 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 to be populated 2 columns away when any of the checkboxes in columns F&K are checked (and confirms the alert message).

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

CodePudding user response：

function onEdit() {

  var now = new Date()
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  var row = ss.getActiveRange().getRow() 

  ss.getRange(row, 1).setValue("value for first Col")
  ss.getRange(row, 2).setValue("value for second Col")
  ss.getRange(row, 3).setValue(now) //third Col with the current date/Time
}

The code above fires every time you edit any cell in the doc. To prevent this you can add a if/else statement to check the Column like so.

  var col = ss.getActiveRange().getColumn()

  if (col == 2){//replace the 2 with the checkbox Column.
     //do this
  }

Now the Code fires every time you edit a cell in the second col and adds values to the same row.

CodePudding user response：

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]; // columns F,K,P,R,S
  const { range } = e;
  const sheet = range.getSheet();
  if (sheet.getSheetName() != 'Video Projects' || !columns.includes(range.columnStart) || !range.isChecked()) 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 && range.isChecked())) {
      range.offset(0, 2).setValue(new Date());
    }
    return;
  }
  range.uncheck();
}

References:

• Simple Triggers
• Event Objects