How to check the type of cell in google spreadsheet?

Question

I'm using google scripts, I want to check the type of a cell and if it's string do something, if a numeric value. do something else.

I can't find a function like isString or isText.

Here is my code, typeof doesn't detect that the cell contains a number

function Results() {

  var activeSheet = SpreadsheetApp.getActiveSpreadsheet();
  var pred = activeSheet.getSheetByName("Copy of Predictions");
  var real = activeSheet.getSheetByName("16 Results");


  var realRes = real.getRange("B7:B61");
  var realVal = realRes.getValues();

  var cell, cellValue, row, col;

  for(col=2;col<16;col++){
    var predRes = pred.getRange(7, col, realVal.length);
    var predVal = predRes.getValues();

    for(row=0;row<predVal.length;row++){
      if(predVal[row] == ""){
        continue;
      }else if(typeof predVal[row] === "number"){
                if((predVal[row][0] == realVal[row][0]) && (predVal[row+1][0] == realVal[row+1][0])){
          cell = pred.getRange(col, 3);
          cellValue = cell.getValue();
          cell.setValue(cellValue + 15);
        }else{
          if(((predVal[row][0] - predVal[row+1][0]) < 0) && ((realVal[row][0] - realVal[row+1][0]) < 0)){
            cell = pred.getRange('B3');
            cellValue = cell.getValue();
            cell.setValue(cellValue + 5);
          }else if(((predVal[row][0] - predVal[row+1][0]) > 0) && ((realVal[row][0] - realVal[row+1][0]) > 0)){
            cell = pred.getRange('B3');
            cellValue = cell.getValue();
            cell.setValue(cellValue + 5);    
          }else if(((predVal[row][0] - predVal[row+1][0]) == 0) && ((realVal[row][0] - realVal[row+1][0]) == 0)){
            cell = pred.getRange('B3');
            cellValue = cell.getValue();
            cell.setValue(cellValue + 5);    
          }
        }
        row++;
      }else{
        continue;
      }
    }
  }
};

Answer 1

If you want to check the number format of the cell or a range, you can use the getNumberFormat() or getNumberFormats() method. NOTE: One has an 's' on the end, and one doesn't. The second one gets a two dimensional array of the formats of multiple cells. If you only want to check one cell, use the singular form. That method works on a range, so you'll first need to get a reference to the cell or range you want to check.

Google Documentation - getNumberFormats()

If you want to evaluate what the data type of the data is once it's in your code, you can use typeof().

Mozilla JavaScript Documentation - typeof

There are situations where JavaScript will coerce one data type into another depending on the usage.

Even though the name is "getNumberFormats()", you can still check for text. But the value returned for text is an empty string. You can use this test function to verify that the Number Format of a cell is formatted as Plain Text.

function test() {
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet();
  var theRange = activeSheet.getRange("A2");
  var theType = theRange.getNumberFormat();
  Logger.log(theType);

  if (theType === "") {
    Logger.log("It's text");
  };
};

Your code is like this:

}else if(predVal[row].getNumberFormat == "number"){

Should be:

}else if(predVal[row].getNumberFormat == ""){

Answer 2

If you are looking for a sheet function look at ISNUMBER(), it seems to know if text can be converted to a number.

If you are in js the code:

if (isNaN(parseFloat(your_data))==false)

seem to work to detect if you can convert the string to a number.