CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Roman Kalinkin
Roman Kalinkin

Reputation: 3

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

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 enter image description here

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:

  1. how to activate the script for the checkbox.
  2. 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" enter image description here

Upvotes: 0

Views: 83

Answers (1)

Cooper
Cooper

Reputation: 64100

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:

enter image description here

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.

Upvotes: 1

Related Questions