How To Use parseFloat Function?

[Patrick Gilbert]

Hi, I'm attempting to pull data from a spreadsheet and use it to create an ROAS variable inside a larger script, but it is returning the data as a string value and therefore returned NaN. I've read elsewhere that the following parseFloat function converts strings into numbers, but I can't seem to figure out how to place it inside my script. 

parseFloat script: 

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

The portion of my script is below. You'll see that I'm just trying to take the line 'rOAS': r[tableColumns['ConversionValue']] / r[tableColumns['Cost']] and log the value. 

function getRoasData() {

   var targetROAS = "4"

   var SPREADSHEET_URL = "";

   var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);

   var sheet = spreadsheet.getSheetByName('Sheet1')

   var sheetValues = sheet.getRange(1, 1, 5000 ,10).getValues();

    tableColumns = {

    "Campaign Name": 0,

    "Product Group": 1,

    "AdGroupId":2,

    "Id":3,

    "CPC Bid": 4,

    "Average CPC": 5,

    "Impressions": 6,

    "Clicks": 7,

    "Cost": 8,

    "Converted Clicks": 9,

    "Search Impression Share": 10,

    "ConversionValue": 11,

    }

    var roasData = [];

    

    for (var row in sheetValues) {

        var r = sheetValues[row]

        

        var campaignData = {

    'campaignName': r[tableColumns['Campaign Name']], 

    'productGroup': r[tableColumns['Product Group']],

    'adGroupId': r[tableColumns['AdGroupId']],

    'id': r[tableColumns['Id']],

    'cpcBid': r[tableColumns['CPC Bid']],

    'avgCPC': r[tableColumns['Average CPC']],

    'impressions': r[tableColumns['Impressions']],

    'clicks': r[tableColumns['Clicks']],

    'cost': r[tableColumns['Cost']],

    'conversions': r[tableColumns['Converted Clicks']],

    'searchImpressionShare': r[tableColumns['Search Impressions Share']],

    'convValue': r[tableColumns['ConversionValue']],

    'rOAS': r[tableColumns['ConversionValue']] / r[tableColumns['Cost']]

}

        //Logger.log(campaignData['rOAS'])

 }

Would someone be able to show me how I'd place this parseFloat to get it to correctly return my rOAS variables? 

Thanks!

[AussieWeb Conversion]

Hi Patrick

I think you'd be wanting this:
'rOAS': parse(r[tableColumns['ConversionValue']]) / parse(r[tableColumns['Cost']])

Regards
Nigel

[Paul Justine de Honor]

Thank you Nigel!

Hi Patrick,

I can confirm that the code provided by Nigel is correct. Also, you can place the parseFloat function outside of your main function (before or after). Additionally, it seems that you missed the closing curly brace of your getRoasData function.

Below is the updated script based on your code:

        'rOAS': parse(r[tableColumns['ConversionValue']]) / parse(r[tableColumns['Cost']])
      }

      //Logger.log(campaignData['rOAS'])

  }
}

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

Thanks,

Paul Justine De Honor

AdWords Scripts Team

[Patrick Gilbert]

Hi all,

Thanks for responding! I've set this up as you've described and am returning a similar error. When I preview the script, it returns this 100,000 times and then times out. 

   Unparseable number: "undefined"

   Unparseable number: "Cost"

   NaN

   Unparseable number: "undefined"

   NaN

   Unparseable number: "undefined"

The "Cost" is the only one that is different. It only appears once. The fact that it logs 100,000x and then times out would tell me that it is looking to parse more than just the 5000 rows that are in my spreadsheet. The updated script is below. Any idea what might be causing this? 

Thanks!

    'rOAS': parse(r[tableColumns['ConversionValue']]) / parse(r[tableColumns['Cost']])

}

        

       

       Logger.log(campaignData['rOAS'])

        

       roasData.push([campaignData['adGroupId'],campaignData['id']])

    }

    

    return roasData;

 }

function parse(stat) {

          var result = parseFloat(stat);

          if (isNaN(result)) {

[Paul Justine de Honor]

Hi Patrick,

Sorry to hear that the script is returning an error. Could you please provide the CID along with the name of the script so we can further investigate? Please use reply privately to author. Also, kindly grant me permission to access the spreadsheet.

Thanks,

Paul Justine De Honor

AdWords Scripts Team

On Tuesday, November 22, 2016 at 6:00:50 AM UTC+8, Patrick Gilbert wrote:

[Patrick Gilbert]

Hey Paul,

Thanks.... I just sent the info via private message. Let me know if you didn't receive it. 

Best,

Patrick