CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Bruno Carvalho
Bruno Carvalho

Reputation: 181

if array != to compare array to cell value

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

Upvotes: 2

Views: 56

Answers (1)

Nikola Pavicevic
Nikola Pavicevic

Reputation: 23480

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

Upvotes: 2

Related Questions