How to format currency using Google App Script

Question

I have a google sheet that has 16 fields that auto populate into a google doc template. 5 of the fields are currency fields that I would like to show as $USD on the google doc.

The number formatting could vary so I'm looking for a solution that would work for formatting a wide range of numbers (ex $xx.xx - $x,xxx,xxx.xx)

    if (row [13] == 'SPA'){
      const copySPA_CO = spaCOTemplate.makeCopy(`FY25_CO_${row[13]}-${row[4]}-${row[14]}`, destinationFolder);
      const docSPA_CO = DocumentApp.openById(copySPA_CO.getId());
      const body = docSPA_CO.getBody();
      const friendlyDate = new Date(row[6]).toLocaleDateString();
      const friendlyDate2 = new Date(row[7]).toLocaleDateString();
      
      //Adding info from the spreadsheet to the SPA
      body.replaceText('{{CO_NUMBER_1}}', row[2]);
      body.replaceText('{{PO_NUMBER_1}}', row[3]);
      body.replaceText('{{CO_NUMBER_2}}', row[2]);
      body.replaceText('{{PO_NUMBER_2}}', row[3]);
      body.replaceText('{{SUPPLIER}}', row[4]);
      body.replaceText('{{SUPPLIER_ADDRESS}}', row[5]);
      body.replaceText('{{PO_START_DATE}}',friendlyDate);
      body.replaceText('{{PO_NUMBER_3}}', row [3]);
      body.replaceText('{{DESCRIPTION_OF_CHANGE}}', row [8]);
      body.replaceText('{{PO_END_DATE}}', friendlyDate2);
      body.replaceText('{{CO_AMOUNT_1}}', row[10]);
      body.replaceText('{{CO_AMOUNT_1}}', Utilities.formatString("$%'.2f", row [10]));
      body.replaceText('{{PO_AMOUNT_1}}', row [9]);
      body.replaceText('{{PO_AMOUNT_NEW_1}}', row [11]);
      body.replaceText('{{CO_AMOUNT_2}}', row [10]);
      body.replaceText('{{PO_AMOUNT_NEW_2}}', row[11]);
      body.replaceText('{{PO_NUMBER_4}}', row[3]);
      
      docSPA_CO.saveAndClose();
      const urlSPA_CO = docSPA_CO.getUrl();
      sheet.getRange(index + 1,16).setValue(urlSPA_CO);
      } else {
        return;

I've tried body.replaceText('{{CO_AMOUNT_1}}', Utilities.formatString("$%'.2f", row [10])); but that's not a very flexible solution.

Answer 1

There is a library available with javascript called Intl.

Try this:

Code.gs

function test() {
  try {
    // Format the price above to USD using the locale, style, and currency.
    let USDollar = new Intl.NumberFormat('en-US', {
      style: 'currency',
      currency: 'USD',
    });
    let price = 10000;
    console.log(USDollar.format(price));
  }
  catch(err) {
    console.log(err);
  }
}

Execution log

6:04:04 AM  Notice  Execution started
6:04:06 AM  Info    $10,000.00
6:04:05 AM  Notice  Execution completed

Reference

• Format number as currency
• Intl