How to replace formulas with values when enabling the checkbox through scripts. (Google Sheets)

Question

I am an absolute zero in programming Scripts for Google tabs. Sorry if I ask a strange question, or fill it out incorrectly. I am really newbie on this site.

There is an example of a sheets: Columns A and B are just numbers Column C and D contain formulas. Like C2=(A2+B2), D2=(A2-B2)

Example

A task: I want that when I click on the button or the checkbox next to the line. All values on this line were inserted as values. (I mean, formulas were deleted and replaced with values) and the checkbox was deleted.

I tried to do it through recording a macro and got the code, but I still couldn't figure it out:

how to activate the script for the checkbox.
how to make each line have its own checkbox and it works.

function _111() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('E2').activate();
  spreadsheet.getCurrentCell().setValue('TRUE');
  spreadsheet.getRange('A2:D2').activate();
  spreadsheet.getRange('A2:D2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('E2').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
};

Please help me if you can.

UPD Special for MetaMan: My TAB Name of sheet is "Sell tab"

Cooper Cooper · Accepted Answer · 2021-05-07T00:13:56

function onEdit(e) {
  //e.source.toast('entry');//debug
  const sh = e.range.getSheet();
  //sh.getRange('P1').setValue(JSON.stringify(e));//debug
  if(sh.getName()=='Sheet1' && e.range.columnStart==5 && e.range.rowStart>1 && e.value == "TRUE") {
    //e.source.toast('sheet');//debug
    sh.getRange(e.range.rowStart,3,1,2).setValues(sh.getRange(e.range.rowStart,3,1,2).getValues());
    sh.getRange(e.range.rowStart,e.range.columnStart).clearDataValidations().clearContent();
  }
}

Demo:

onedit trigger

clearDataValidations

setValues(),getValues()

It might be helpful for you to remove the comments from my debug lines. If you do not have anything special in P1 that's where I have the event object for the onEdit trigger being displayed. I like to look at it while I debug the problem.

How to replace formulas with values when enabling the checkbox through scripts. (Google Sheets)

1 Answers