How to check cell type in google spreadsheet?

Question

I use google scripts, I want to check the cell type, and if it builds something, if a numeric value. do something else.

I cannot find a function like isString or isText.

Here is my code, typeof does not 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 format of the cell number or range, you can use the getNumberFormat() or getNumberFormats() method. NOTE. At the end there is an “s” and the other not. The second is a two-dimensional array of formats of several cells. If you want to check only one cell, use a single form. This method works in a range, so you first need to get a link to the cell or range that you want to check.

Google Documentation - getNumberFormats ()

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

Mozilla JavaScript documentation - typeof

There are situations when JavaScript will force one data type to another depending on usage.

Even if the name is "getNumberFormats ()", you can still check the text. But the value returned for the text is an empty string. You can use this test function to verify that the cell number format 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 text"); }; }; 

Your code is as follows:

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

Must be:

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

Answer 2

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

If you are in js the code:

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

seems to work to determine if you can convert string to number .