Google Apps Script - Get a range of cells instead of one cell-CodePudding

I started using Google Apps Script yesterday and I'm struggling in running the code successfully when I attempt to get a range of cells instead of just one cell.

This is the complete code, it will show a pop-up message if the condition is true:

function CheckPlanNumber() {
  // Fetch the monthly sales
  var planNumbersRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Plan Numbers").getRange("B2");
  var planNumbers = planNumbersRange.getValues();
  var ui = SpreadsheetApp.getUi(); 
  // Check totals sales
  if (planNumbers == "XXXX") {
    ui.alert('Contact - Plan Number missing!');
    }
}

However, when I try to extend the range to more cells to go through at least 10 cells and validate them, it doesn't show the pop-up message.

function CheckPlanNumber() {
  // Fetch the monthly sales
  var planNumbersRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Plan Numbers").getRange("B2:B10");
  var planNumbers = planNumbersRange.getValues();
  var ui = SpreadsheetApp.getUi(); 
  // Check totals sales
  if (planNumbers == "XXXX") {
    ui.alert('Contact - Plan Number missing!');
    }
}

CodePudding user response：

Range.getValues return values in 2-dimensional array, so if you have a table like this

|     |  A  |      B       |
| --- | --- | ------------ |
| 1   |     | Plan numbers |
| 2   |     |      123     |
| 3   |     |      XXX     |
| 4   |     |      456     |

Range.getValues( 'B2:B4' ) will return

[
      [ "123" ]
    , [ "XXX" ]
    , [ "456" ]
]

You will have to loop through each value and compare them.

var sheet            = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Plan Numbers");
var planNumbersRange = sheet.getRange("B2:B10");
var planNumbers      = planNumbersRange.getValues();
var ui               = SpreadsheetApp.getUi(); 

for ( var row = 0; row < planNumbers.length; row   ) {
    if ( planNumbers[ row ][ 0 ] == "XXXX" ) {
        ui.alert( 'Contact - Plan Number missing at row '   ( row   2 ) );
    }
}