Getting NaN return

[Ben A]

Hi there,

Getting rather confused here, I have a script to email alerts about tablet spend.  I keep getting NaN for the tab_30_cost_share  variable for some campaigns and for other campaigns the variable works perfectly.  I've logged the tab_cost_30 and total_cost_30 and they both produce the right numbers.

I can't see any reason why.. I've isolated the slice of code that is causing the problem ran it in a new script and getting the same problem.  

I calculate tab_30_impr_share in roughly the same way and this works all the time.

Any help would be gratefully received

function main() {

  var tab_report_30 = AdWordsApp.report(

      ' SELECT CampaignName, Impressions, Cost, Device' +

      ' FROM CAMPAIGN_PERFORMANCE_REPORT' +

      ' WHERE CampaignName = "DCO Beta German"' +

      ' During LAST_30_DAYS')

    var rows=tab_report_30.rows()

    while (rows.hasNext()){

      var row=rows.next()

      var device = row['Device']

      var camp_name = row['CampaignName']

      if ( device === 'Tablets with full browsers') {

        var tab_cost_30 = row['Cost']

        var tab_imp_30 = row['Impressions']

        //Logger.log(camp_name + ' ' + tab_cost_30 + ' ' + tab_imp_30 )

      }

    }

        var total_report_30 = AdWordsApp.report(

      ' SELECT CampaignName, Impressions, Cost' +

      ' FROM CAMPAIGN_PERFORMANCE_REPORT' +

      ' WHERE CampaignName = "DCO Beta German"' +

      ' During LAST_30_DAYS')

    var rows=total_report_30.rows()

    while (rows.hasNext()){

      var row=rows.next()

      var camp_name = row['CampaignName']

        var total_cost_30 = row['Cost']

        var total_imp_30 = row['Impressions']

        //var total_cost_30n = Number(total_cost_30)

        //Logger.log(camp_name + ' ' + tab_cost_30 + ' ' + total_cost_30 )

    }

   var tab_30_impr_share = Math.round((tab_imp_30/total_imp_30)*1000)/1000

   var tab_30_cost_share = Math.round((tab_cost_30/total_cost_30)*1000)/1000

   Logger.log(tab_30_impr_share + ' ' + tab_30_cost_share )

}

 

[Matt Greenland]

Hi Ben,

My best guess as to what's going on here is that your script is trying to divide a number by some non-numeric text, or vice versa. When you use reports, all the values that come back are strings, but Javascript doesn't have a particularly strong type system, and so it treats them as numbers in certain contexts, so things like 4 / '2.5' or '11' / 3 or '3209' / '3132' will all do numeric calculations. When the text in question can't be parsed as a number ('banana' / 4), it spits out NaN (not a number). So I'm guessing that some row of your report is giving back some non-numeric value like '--'.

What you could do to test out this hypothesis is to add some debug logging whenever you run into a NaN:

var tab_30_cost_share = Math.round((tab_cost_30/total_cost_30)*1000)/1000;
if (isNaN(tab_30_cost_share)) {
  Logger.log('tab_30_cost_share is not a number!');
  Logger.log('tab_cost_30: [' + tab_cost_30 + ']');
  Logger.log('total_cost_30: [' + total_cost_30 + ']');
}

[Ben A]

HI Matt, 

Thanks for your quick reply.  I have already looked at those variables before using them in tab_30_cost_share and they look like numbers (i.e. they are no '--' or any other characters that could produce a NaN) and doing a typeof() of the variables does return 'string', as you suggest. However this happens to every campaign and some return NaN and others don't.. I'm not to familiar with Javascript, is there anyway to force it read the text as a number?

Thanks again for your help

Ben

[Matt Greenland]

Hi Ben,

You can use the built-in parseFloat function to convert strings into numbers. If the string in question can't be treated as a number, though, parseFloat will return NaN and then you have the same problem. E.g. parseFloat('4.82') works; parseFloat('3.4a32') returns 3.4; parseFloat('banana') returns NaN.

To get some extra help debugging, it might help to write a function that tries to call parseFloat, and logs a warning if it fails:

function parse(stat) {
  var result = parseFloat(stat);
  if (isNan(result)) {
    Logger.log('Unparseable number: "' + stat + '"');
  }
  return result;
}

Then, in your script, call this function whenever you try to get a number out of the report. E.g. var total_cost_30 = parse(row['Cost']); 

(Sorry for the late reply this time -- was out for the holidays)

[Ben A]

Hi Matt,

Just for completeness (and for anyone else who comes across this problem) I found that using parseFloat didn't work for number bigger than 999 this is because of the comma outputted by the script i.e. 3,312 which the parseFloat would read as 3.  I fixed it by removing the comma before the parseFloat i.e. parseFloat(row['Stat'].replace("," , "")).

This is fine but a bit annoying, is there a way to stop reports from outputting the comma?

Thanks,

Ben