!= "TEXT" not working Google Sheet scripts

Question

I have a script trying to identify if "HRZ" is found in Column F18:F, but it doesn't seem to work... It never identifies "HRZ" even if that is all thats in the column... Anyone able to help?

function exportNotifyHinges() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Hinge'); //source sheet
  var testrange = sheet.getRange('F18:F'); //range to check
  var testvalue = (testrange.getValues());
  var ui = SpreadsheetApp.getUi();

  var shouldExport = true;
  for (i=0; i<testvalue.length;i++) {
    if ( testvalue[i] != "HRZ") {
      var response = ui.alert("Vertical conformance data identified in report. \n\n Do you want to cancel the export?",ui.ButtonSet.YES_NO);
      Logger.log(response);
        
      if (response == ui.Button.YES) {
        SpreadsheetApp.getActive().toast("Export Cancelled...");
        shouldExport = false;
        }
      break;
    }
  }

What do you see if you log `testvalue[i]` to the console? Do you see a string? Or maybe something else - such as an array of data `[ ... ]`? — andrewJames, Jun 23 '21 at 21:23
It's in the ExportHinges.gs https://docs.google.com/spreadsheets/d/1G6xLca7K9Tmvo0Kf2HxwnZUnLccF5ldP435rB1DPZ0s/edit?usp=sharing — Jon Bowles, Jun 23 '21 at 21:25
Side note: I would also recommend using `===` and `!==` in preference to `==` and `!=` - [background](https://stackoverflow.com/questions/359494/which-equals-operator-vs-should-be-used-in-javascript-comparisons). — andrewJames, Jun 23 '21 at 21:31
OK - you are trying to compare an array containing a string to a string. Just as a test, try: `testvalue[i][0] !== "HRZ"`. Consider that a range of cells is typically a 2-dimensional array - and when you use `testvalue[i], that reduces it to a 1-dimensional array. — andrewJames, Jun 23 '21 at 21:35
And you do want them to be _not_ equal, right? In which case there is no logic to be performed...? — andrewJames, Jun 23 '21 at 21:40
I want it to bring up the ui.alert if there is a value in F18:F that is NOT "HRZ" — Jon Bowles, Jun 23 '21 at 21:52

score 1 · Accepted Answer · answered Jun 23 '21 at 22:03

1

The key here is to use the debugger and inspect the array testvalues

This reveals each element is a 2 dimensional array, so change this line of code

if ( testvalue[i] != "HRZ") {

to this and it works fine

if ( testvalue[i][0] != "HRZ") {

answered Jun 23 '21 at 22:03

JohnA

1,058
6
12

You are welcome! I have banged my head against the wall many times for simple mistakes. I always use the debugger and set breakpoints so I can make sure my scripts are doing what I expect them to be doing. – JohnA Jun 23 '21 at 23:02
I would also recommend using `===` and `!==` in preference to `==` and `!=` - [background](https://stackoverflow.com/questions/359494/which-equals-operator-vs-should-be-used-in-javascript-comparisons). – andrewJames Jun 24 '21 at 12:55

!= "TEXT" not working Google Sheet scripts

1 Answers1