Search code examples
javascriptgoogle-apps-scriptgoogle-sheetstypesnumber-formatting

Convert number to currency in apps script?

I'm wanting to convert a specific range of numbers to currency. I'm not sure if such formatting exists in google apps script. I notice that when defining one cell value with a "$", it automatically declares any other numbers in that range with a "$". So I assume that there is some kind of currency type.

If so, is there some type of fancy method to convert a number to this currency format or am I to simply convert each number to a string and add a "$" in front of each one.

Any help would be appreciated, thank you.

Solution

  • Google Apps Script uses JavaScript, as the later doesn't have a currency data type, Google Apps Script either. The V8 engine (default for new project) supports Internationalization API, through the Intl object, but the old engine (Rhino) doesn't.

    If your project is using the default settings, you might use Intl.NumberFormat (examples taken from MDN)

    const number = 123456.789;

console.log(new Intl.NumberFormat('de-DE', { style: 'currency', currency: 'EUR' }).format(number));
// expected output: "123.456,79 €"

// the Japanese yen doesn't use a minor unit
console.log(new Intl.NumberFormat('ja-JP', { style: 'currency', currency: 'JPY' }).format(number));
// expected output: "￥123,457"

    It's worthy to note that Google Sheets stores "currency" values as numbers. The "$" is added by the automatic number formatting feature. If you want that your script adds this formatting use setNumberFormat.

    Related