将字符串值格式化为Google Apps脚本中的货币值 [英] Formatting String Value as Monetary Value in Google Apps Script

问题描述

我创建了一个Google表单，其中有一个字段，其中由用户输入数字值（在表单上进行数字验证），然后提交。当数字（例如，34.00）被提交时，它在Google电子表格中显示为34，这是令人讨厌但可以理解的。我已经有了一个脚本，当表单被提交时生成一个脚本，用于生成格式良好的信息，但是我在格式化该值时会遇到问题（例如，34 - > 34.00美元）使用Utilities.formatString函数。谁能帮忙？表单提交事件的值属性 如事件对象中记录的那样是一个字符串数组，如下所示：

  ['2015/05/04 15:00'，'[e-mail]'，'Bob'，' 27'，'Bill'，'28'，'Susan'，'25'] 
  

结果，希望使用这些值中的任何一个作为除String之外的任何值的脚本都需要执行显式类型转换，或使用一元运算符（ + ）将字符串强制转换为数字。

  var numericSalary = + e.values [9]; 
  

或者，您可以利用Sheets的内置类型确定，通过读取提交的值从范围属性也包含在事件中。就像在键盘上键入值时一样，Sheets会尽力解释表单值 - 在这种情况下，J列中的值将被解释为一个数字，因此您可以像这样获取它：

  var numericSalary = e.range.getValues（）[9]; 
  

这会比使用值更慢数组，并且它仍然会提供一个未格式化的值。

格式化

实用程序。 formatString 使用sprintf-like格式化值。如果您搜索interwebs，您会发现很多对 sprint变量的引用，其中有些引用很有帮助。这是一种将浮点数转换为美元格式的字符串的格式：

 '$％。2f'
 
 $  - 没有什么魔力，只是一个美元符号
％ - 魔术从这里开始，格式的开始
 .2  - 定义一个有两位小数的数字，但是在数字之前有未指定的数字基数
f  - 期望浮点数
  

所以这是最简单的代码行做你想要的转换：

  var currentSalary = Utilities.formatString（'$％。2f'，+ e。值[9]）; 
  

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.

解决方案

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', '[e-mail]', '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] );

这篇关于将字符串值格式化为Google Apps脚本中的货币值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！