Changing A Cell Value in a Range Only When It Meets A Condition(Google Sheets App Scripts)

Question

I am working on a function for a Google Sheets script which would check to see if a certain value in a cell is equal to "Choice Cube", then override that cell if it returns true. The current function is as follows:

function removeCubeRewards(){
  var ss2=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Rewards")
  var range = ss2.getRange("F2:F20")
  var data = range.getValues()

  var conditional = 0
  for(var i=0;i<data.length; i++){
      if(data[i][0] == "Choice Cube"){
        data[i][0] = "Reward Claimed"
        conditional++
      }
    if(conditional == 6){
      break;
    }
  }
  range.setValues(data)
}

The way it's currently set-up is that range will override all cells in the specified range with the values in data. My problem, is that I have certain cells in the range of my Google Spreadsheet set-up with an if-condition like so:

=IF(E11="X","Choice Cube", "LOCKED")

This would mean when I execute range.setValues(data) those IF conditions are overridden by whatever value is currently displayed.

I am wondering what I could do so that only the cells that are matched in

if(data[i][0] == "Choice Cube")

are overridden.

Perhaps you should be using ss2.getRange(i+1,1).setValue('Reward Claimed'); and then don't use setValues() at all. — Cooper

Unknown Unknown · Accepted Answer · 2017-09-17T15:54:08

I understand you want your script to not affect the cells with an =IF formula (or perhaps any kind of a formula). To do so, use getFormulas along with getValues:

  var data = range.getValues();
  var formulas = range.getFormulas(); 
  // ...
      if (data[i][0] == "Choice Cube" && !formulas[i][0]){
        // do something
      }

The above version excludes any cells with a formula from consideration.

If you wanted to only exclude =if, use, for example, a regular expression (for case-insensitive substring match)

      if (data[i][0] == "Choice Cube" && !/^=if\(/i.test(formulas[i][0])){

Changing A Cell Value in a Range Only When It Meets A Condition(Google Sheets App Scripts)

1 Answers