If var isnumber, for script

问题:

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?

回答1:

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

回答2:

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.