formatting currency to be used in an email

Question

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);

(also, it's important to note that I'm summing a range with this:

function sum( obj, colindex ) {
var sum = 0;
for( var el in obj ) {
//      sum += parseFloat( obj[el][colindex] );
sum += obj[el][colindex];
}
return sum;
}

...so I can't use something like getDisplayValue to use Google Sheet's own formatting)

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?

Answer 1

If you are getting the values from a spreadsheet set the formatting that you want in Google Sheets, then on Google Apps Script instead of getValues() use getDisplayValues().

Related

• Embed Google Spreadsheet table Withing Google Document when data contains dates