google sheets – formatting currency to be used in an email


I’m summing a range from a Google Sheet, then putting the results in the body of an email as currency.

But I’m running into formatting issues that seems surprisingly hard to deal with.

The range of the sum can be anywhere from a small amt (e.g. $1.23) to a large amt (e.g. $98,123.45).

I would like the comma to be added when necessary, and the decimal fixed to 2 places.

I found this code from another thread:

var dollars = Utilities.formatString("$%d,%02d%1.2f", totalOfOrders/1000, totalOfOrders%1000/10,totalOfOrders%10);

And that works well when there is supposed to be a comma, but for smaller amts, it still adds the comma.

e.g. if the amt is 981.97, it shows $0,981.97

If the amt is 1981.97, it shows $1,981.97

Here’s the code snippet:


var values = range.getValues();
var totalOfOrders = +sum(values,1);
var dollars = Utilities.formatString("$%d,%02d%1.2f", totalOfOrders/1000, totalOfOrders%1000/10,totalOfOrders%10);

Do I actually need to add something like an if statement to handle sums under 1000.00? Or is there a better way to do this?