Identifying number in an array and empty values in google apps script

Question

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

Sno_values is a 2D array of 1 column. So all your refereces to array should be array[i][0]. — TheWizEd
– TheWizEd, Commented Feb 1, 2023 at 16:02

Giselle Valladares · Accepted Answer · 2023-02-01 20:00:34Z

Adding to the comment of @TheWizEd, one of the issues of the code is how the array has been called. I made other changes in the code to make sure that both validations are completed.

Make sure if there are any empty cells.
Make sure that all the values are numbers.

Here is the table I made for testing:

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:

Ramón López de Andújar · Accepted Answer · 2023-02-01 19:56:48Z

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();

toString()

It should be just

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

Besides, as TheWizEd remarked you are working with a 2D array, so your function should be:

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

Collectives™ on Stack Overflow

Identifying number in an array and empty values in google apps script

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related