I have a simple column in column A in a sheet by name Sno. (serial number). I am trying to read the column and identify -

-if there are any empty cells from first cell in the column to the last filled row and

-if the values which are present are numbers only

This will help me do 2 validations, to identify empty cells in between the cell values, like if there are 1 - 200 numbers entered then in between there are no misses in the series and if the values which are present all numbers

I tried the below to check that but not getting it right-

unction siteShieldmaps() {
  SS = SpreadsheetApp.getActiveSpreadsheet();
  var SS_m = SS.getSheetByName("Cleanup sheet");
  var LAST_ROW = SS_m.getLastRow();
  console.log(LAST_ROW);
  var Sno_values = SS_m.getRange(`A1:A${LAST_ROW}`).getDisplayValues().toString();
  console.log(typeof Sno_values);
  
  var result = isNumberOrEmpty(Sno_values);
  console.log(result);
    
}

  function isNumberOrEmpty(array) {
  var result = [];
  for (var i = 0; i < array.length; i  ) {
    if (array[i] === "") {
      result.push("empty");
    } else if (!isNaN(array[i])) {
      result.push("number");
    } else {
      result.push("not a number");
    }
  }
  return result;
}

Please guide

CodePudding user response：

I would really comment instead of posting but I don't have enough reputation.

In this line of code you're not actually getting a 2-D array but a string.

var Sno_values = SS_m.getRange(`A1:A${LAST_ROW}`).getDisplayValues().toString();

Here is the sample code:

function siteShieldmaps() {
  ss = SpreadsheetApp.getActiveSpreadsheet();
  let ss_m = ss.getSheetByName("Sheet1");
  let last_row = ss_m.getLastRow();
  console.log(last_row);
  // change how the range is call from "A1:A${LAST_ROW} to "2,1,last_row"
  // The range "2,1,last_row" will exclude the "A1" cell
  // Also, I change "getDisplayValues().toString();" to "getValues()"
  // if you keep "getDisplayValues().toString();" 
  //it will show some cells as not number when they are
  let sno_values = ss_m.getRange(2,1,last_row).getValues();
 
  let result = isNumberOrEmpty(sno_values);
  console.log(result);
    
}

  function isNumberOrEmpty(array) {
  let result = [];
  for (let i = 0; i < array.length; i  ) {
    // create the variable row instead of using array[i][0]
    // so I use row[0] in the if statement
    let row = array[i]
    if (row[0] === "") {
      result.push("empty");
    } else if (!isNaN(row[0])) {
      result.push("number");
    } else {
      result.push("not a number");
    }
  }
  return result;
}

And the result will be: