4

I am writing my first script for an invoice template based on a Google Doc. It works fine. I need to use it in Germany so I need to be able to format the prices in the way they are shown in Germany.

I have written a short function to do this but am interested in whether there is a better or more obvious way of achieving this.

// this formats numbers as used in Germany
function numberToGerman(number){
  number = Utilities.formatString("%d.%d,%02d", number/1000, number%1000, number%1*100);
  return number;
}

It has a problem above 999.999,99 or below 1.000,00 at the moment but I hope there is a better solution out there.

Neill Hogarth
  • 79
  • 5

1 Answers1

3

Here is a slight modification of the answer by Elias Zamaria, replacing separators and ensuring exactly two decimals:

function numberToGerman(number){
  var parts = number.toString().split(".");
  parts[0] = parts[0].replace(/\B(?=(\d{3})+(?!\d))/g, ".");
  parts[1] = ((parts[1] || "") + "00").slice(0,2);  
  return parts.join(",");
}

The parts are the integer and fractional parts. Groups of three digits are processed by the regex, which places dots accordingly. The fractional parts is expanded/truncated to two places.

Remarks

  1. Unfortunately, number.toLocaleString("de-DE") is not yet supported by Google Apps Script. One can use number.toLocaleString() but then the performance depends on someone having set the correct locale. Also, options concerning decimal places aren't supported either, so this doesn't help that much.

  2. If this was to be used in a spreadsheet, you can apply appropriate formatting to cells either manually from the menu, or from a script: for example cell.setNumberFormat("#,###.00"). This looks like U.S.-style formatting, but once the locale of the spreadsheet is changed to Germany, it changes accordingly, e.g. 1.234.567,89.

Community
  • 1
  • 1
  • The function works perfectly and produces exactly the result I was hoping for. I nearly understand most of it. :-) Thank you for the help. Call me up for a beer next time you are skiing the Alps :-) – Neill Hogarth Nov 28 '16 at 13:49
  • 1
    I have been using the function since it was suggested and it works exactly as hoped. Today I had to add an extra line at the top so that it would work as expected even when I passed a value of 121.399999 to it. I know I could round before calling the function but this seemed "safer". I added number = number.toFixed(2); – Neill Hogarth Dec 31 '16 at 16:41