0

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?

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

1
  • Thanks. I just updated the question. I'm summing a range into a variable value, so I can't use getDisplayValues this way.
    – Edward Wu
    Oct 16 '20 at 1:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.