Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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

>Solution :

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()
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading