If var isnumber, for script

Question

I need to run a script which contains the logic: If isNumber, Then DoSomething.

I've run other such if then tests such as if blank, and if cell contains "x". It should be simple but I can't find a solution. I've tried getNumberFormat, innum, isnumber, etc.

function ifIsNumber() {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getActiveSheet();

var substring1 = s.getRange("A1").getNumberFormat();

s.getRange("B1").setValue(substring1);

}

This code checks cell A1 and returns in cell B1 the string "0.###############", for both cells containing numbers AND text. How can I identify which cells are numbers?

score 23 · Accepted Answer · edited Oct 01 '19 at 18:52

23

This will check if A1 is a number:

function ifIsNumber() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var s = ss.getActiveSheet();
    var substring1 = s.getRange("A1").getValue();
    if (!isNaN(parseFloat(substring1)) && isFinite(substring1)) {
        s.getRange("B1").setValue("Is a number");
    } else {
        s.getRange("B1").setValue("Not a number");
    }
}

edited Oct 01 '19 at 18:52

cmaher

5,100
1
22
34

answered Feb 07 '16 at 12:55

Ed Nelson

10,015
2
27
29

Works perfectly! I'm a bit surprised they've not implemented such a go-to Excel feature. Thank you @Ed! – MinneapolisCoder9 Feb 07 '16 at 13:43
3

ISNUMBER() is already a built in formula in Google Sheets. Just as per Excel. https://support.google.com/docs/answer/3093296?hl=en – JSDBroughton Feb 07 '16 at 15:31

score 12 · Answer 2 · answered Feb 17 '16 at 10:47

Google Apps Script could use most of the JavaScript methods and operators.

One alternative to check if an object is a number is to use typeof.

The following code will return the type of the value of the cell specified by the reference.

function typeofcellvalue(reference) {
  var ss = SpreadsheetApp.getActive();
  var rng = ss.getRange(reference);
  var value = rng.getValue();
  return typeof value;
}

Example of use as a custom function

=typeofcellvalue("A1")

If cell A1

has a number, the result will be number.
is empty, the result will be string.

This answer is better because it works with different language settings, i.e. for countries with comma between decimal and integer parts of number. For dates it will returns 'object', for images (formula `Image`) it gives 'string', but I expected 'object'. Hyperlinks are also strings. — Max Makhrov, Sep 19 '16 at 07:40

score 4 · Answer 3 · answered Jun 04 '19 at 01:34

If you want to do number stuff if the value is a number or a string representation of a number you can do the following:

if (!isNaN(myVar)){//myVar is either a number or a string representing a number
    myNumericVar = +myVar;//convert myVar to number if necessary
    //do number stuff
} else {
   //do non-numeric stuff
}

See:

(Built-in) way in JavaScript to check if a string is a valid number

If var isnumber, for script

3 Answers3

Linked