if array != to compare array to cell value

Question

example https://docs.google.com/spreadsheets/d/10tJRbuG3-psGNasRGFRXGc4LMqlrVh68UC2Rgi4Wd-M/edit#gid=380960712

currently im comparing value individually against serror values, and its working

function test() 
{
  const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test');

  var A2 = ss.getRange('A2').getValue();  

  if( A2 != "#N/A" && A2 != "#REF!" && A2 != "#ERROR!" && A2 != "" )  // check if error
  {
    ss.getRange(2,2,1,1).setValue("different");
  }
  else
  {
    ss.getRange(2,2,1,1).setValue("equal");
  }
};

but i want to set a global constant, to be used on multiple funcions, so i can add/remove itens and make code cleaner not repeting same stuff all over

const ErrorValues = ["#NULL!", "#DIV/0!", "#VALUE!", "#REF!", "#NAME?", "#NUM!", "#N/A","#ERROR!"];

so i attempted to make use of array to compare, but i couldnt make the results i wanted, i tried

function test2() 
{
  const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test');

    var A2 = ss.getRange('A2').getDisplayValue();  

  if (ErrorValues.indexOf() != A2)  // tried != -1
  {
    ss.getRange(2,2,1,1).setValue("different");
  }
  else
  {
    ss.getRange(2,2,1,1).setValue("equal");
  }
};

function test3() 
{
  const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test');

  var A2 = ss.getRange('A2').getDisplayValue();  

  for (var i = 0; i < ErrorValues; i ++)  // check if error
  {
    if(ErrorValues[i] != A2)
    {
      ss.getRange(2,2,1,1).setValue("different");
    } 
    else
    {
      ss.getRange(2,2,1,1).setValue("equal");
    }
  }
};

tried some variations and other examples i found around here, but so far having trobles

edited to add example

not quite sure what u meant but added exemple, to try to show what i need — Bruno Carvalho
– Bruno Carvalho, Commented Apr 23, 2022 at 22:35

Nikola Pavicevic · Accepted Answer · 2022-04-23 22:31:00Z

2

Did you try with includes:

const ErrorValues = ["#NULL!", "#DIV/0!", "#VALUE!", "#REF!", "#NAME?", "#NUM!", "#N/A","#ERROR!"];

function test() {
  var A2 = "#NULL!"
  if (!ErrorValues.includes(A2)) {
    console.log("different")
  } else {
    console.log("equal")
  }
};

test()

answered Apr 23, 2022 at 22:31

Nikola Pavicevic

23.6k9 gold badges30 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

if array != to compare array to cell value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related