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!');
}
}
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" || planNumbers[ row ][ 0 ] == "__" ) {
ui.alert( 'Contact - Plan Number missing at row ' + ( row + 2 ) );
}
}