Home > Net >  if array != to compare array to cell value
if array != to compare array to cell value

Time:04-24

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

CodePudding user response:

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()

  • Related