Formatting String Value as Monetary Value in Google Apps Script

Question

I have created a Google Form that has a field where a numeric value is entered by the user (with numeric validation on the form) and is then submitted. When the number (e.g., 34.00) gets submitted, it appears as 34 in the Google spreadsheet, which is annoying but understandable. I already have a script that runs when the form is submitted to generate a nicely-formatted version of the information that was submitted on the form, but I'm having trouble formatting that value as a monetary value (i.e., 34 --> $34.00) using the Utilities.formatString function. Can anyone help? Thanks in advance.

Mogsdad Mogsdad · Accepted Answer · 2014-12-12T11:01:55

The values property of a form submission event as documented in Event Objects is an array of Strings, like this:

['2015/05/04 15:00', '[email protected]', 'Bob', '27', 'Bill', '28', 'Susan', '25']

As a result, a script that wishes to use any of these values as anything but a String will need to do explicit type conversion, or coerce the string to number using the unary operator (+).

var numericSalary = +e.values[9];

Alternatively, you could take advantage of the built-in type determination of Sheets, by reading the submitted value from the range property also included in the event. Just as it does when you type in values at the keyboard, Sheets does its best to interpret the form values - in this case, the value in column J will have been interpreted as a Number, so you could get it like this:

var numericSalary = e.range.getValues()[9];

That will be slower than using the values array, and it will still provide an unformatted value.

Formatting

Utilities.formatString uses "sprintf-like" formatting values. If you search the interwebs, you'll find lots of references for sprint variables, some of which are helpful. Here's a format that will turn a floating-point number into a dollar-formatted string:

'$%.2f'

$ - nothing magic, just a dollar sign
% - magic begins here, the start of a format
.2 - defines a number with two decimal places, but unspecified digits before the radix
f - expect a floating point number

So this is your simplest line of code that will do the conversion you're looking for:

var currentSalary = Utilities.formatString( '$%.2f', +e.values[9] );

Formatting String Value as Monetary Value in Google Apps Script

2 Answers

Formatting