Decimal divider - change from dot to comma

[Ralf Kollosche]

Hi there,

I just started with AdWords Scripts and already got a script up and running to deliver a simple weekly report right into google spreadsheet. All of that works fine, only the decimal divider is always a dot. Here in Germany we use a comma as decimal divider and that's what I'd like to have in my spreadsheet.

I sure could go into the spreadsheet and find and replace all dots with commas but I would love to have that done by the script.

There are probably quite some different ways to get there.

Next step would be formatting the columns into the right number format.

I'm really looking forward to your answers.

Thanks in advance

Ralf

/**************************************

* Store Account Performance Report in a Google Doc

* Version 1.1

* Changelog v1.1 - Removed apiVersion, Removed get spreadsheet

* Created By: Russ Savage

* FreeAdWordsScripts.com

**************************************/

function main() {

  var spreadsheet_url = "...";

  var date_range = 'LAST_WEEK';

  var columns = ['Week',

                 'Impressions',

                 'Clicks',

                 'Ctr',

                 'AverageCpc',

                 'AveragePosition',

                 'ConversionRate',

                 'Conversions',

                 'ConversionValue',

                 'Cost',

                 'CostPerConversion',

                 'ValuePerConversion',

                ];

  var columns_str = columns.join(',') + " ";

   

  var sheet = SpreadsheetApp.openByUrl(spreadsheet_url).getActiveSheet();

  if(sheet.getRange('A1:A1').getValues()[0][0] == "") {

    sheet.clear();

    sheet.appendRow(columns);

  }

   

  var report_iter = AdWordsApp.report(

    'SELECT ' + columns_str +

    'FROM ACCOUNT_PERFORMANCE_REPORT ' +

    'DURING ' +date_range).rows();

   

  while(report_iter.hasNext()) {

    var row = report_iter.next();

    var row_array = [];

    for(var i in columns) {

       row_array.push(row[columns[i]]);

    }

    sheet.appendRow(row_array); 

  }

}

[Anash Oommen]

Hi Raif,

You could use the setNumberFormat method to set the right formatting to the cell.

Cheers,

Anash P. Oommen,

AdWords Scripts Team.

[Ralf Kollosche]

Hi Anash,

I have tried that, but I don't get it to work properly. How do I set a cell with for example the value 0.23 to 0,23?

Thanks a lot.

Ralf

[Matt Greenland]

Hi Ralf,

It looks like commas versus periods for numbers is controlled by the spreadsheet's locale setting. This can be changed in the spreadsheet by going to File > Spreadsheet settings, or through code by calling:

spreadsheet.setSpreadsheetLocale('de');

If your spreadsheet's locale isn't already set to German, this might be the way to fix that.

[Ralf Kollosche]

Hi Matt,

that did the trick.

In the beginning of the script I now change the sheet to US and convert it at the end of the script to de. 

Great. Thank you.