If var isnumber, for script

MonkeySeeMonkeyDo picture MonkeySeeMonkeyDo · Feb 7, 2016 · Viewed 23.3k times · Source

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?

Answer

Ed Nelson picture Ed Nelson · Feb 7, 2016

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");
    }
}