设置与Google表单中的标签不同的值 [英] Set values different from label in Google Forms

问题描述

是否可以使用Google Forms Apps脚本或Google表格公式在Google表单上的多项选择中设置与标签不同的值?

Is there a way using either Google Forms Apps Script or Google Sheets formulas to set the value different from the label in multiple choice selections on Google Forms?

我正在寻找的东西与此类似，在html中:

What I am looking for is something similar to this in html:

<select name="cartype" form="myform">
  <option value="33">Volvo - $33</option>
  <option value="34">Saab - $34</option>
  <option value="35">Opel - $35</option>
  <option value="36">Audi - $36</option>
</select>

在Apps脚本中(文档)，我可以设置多项选择使用 setChoiceValues(values)的值，但这会为输入值和显示文本提供相同的字符串.

In Apps Script (docs) I can set the multiple choice values using setChoiceValues(values) but this gives the same string for the input value and display text.

值字符串[] 选择值的数组，受访者认为查看表单时使用标签

values String[] the array of choice values, which respondents see as labels when viewing the form

或者，如果可以只从Google表格中的单元格中剥离货币值并用于求和公式中，也可以.

Alternatively, it would be fine if it was possible to strip the currency values only from the cell in Google Sheets and use in a sum formula.

我要寻找的最终结果是一个Google表单，其中列出了带有价格的选项，然后输出到可以自动对价格总计求和的电子表格中.

The end result I am looking for is a Google Form that lists options with prices, then outputs to a spreadsheet that can sum the price totals automatically.

是否可以引用工作表中的列表以获取数字值?

Is it possible to do a reference to a list in sheets to get the number value?

第1张-Google表单电子表格的结果:

Sheet 1 - Results from Google Form spreadsheet:

| Name | Car        | Days |
| Foo  | Saab - $34 | 4    |
| Bar  | Volvo - $33| 2    |

第2张-参考列表:

| Car         | Price |
| Volvo - $33 | 33    |
| Saab - $34  | 34    |
| Opel - $35  | 35    |
| Audi - $36  | 36    |

第3张-总计:

| Name | Car                         | Days | Total |
| Foo  | {price from reference list} | 4    | B1*C1 |

推荐答案

我从响应的另一列中提取价格.编辑该列中数据的脚本将对此进行更改，并且以后对数据进行编辑或重新导入可能会导致冲突.我发现最好将另一列用于修改后的数据.考虑到这一点:

I extract the prices out of the response in a different column. A script editing the data in the column will change this and later edits or re-imports of the data could cause conflicts. I find it better to use another column for the modified data. With that in mind:

如果您使用的单选按钮项仅允许选择一项，则放在适当列的第2单元格中的此功能应该可以正常工作.将两个位置的A2:A替换为包含问题答案的列.只要确保您已经在电子表格中收到一个答复即可:

IF you are using a radio button item allowing only one item to be selected, this function placed in cell 2 of the approriate column should work well. Replace A2:A in both locations with the column containing the question's response. Just make sure you have one response in the spreadsheet already:

=ARRAYFORMULA(IF(ISTEXT( A2:A),  REGEXEXTRACT(A2:A,"\$(\d*)"), ))

ARRAYFORMULA使公式适用于列中的每个单元格.IF()用于将公式应用于适当的行.REGEXEXTRACT()从文本中提取时间.

The ARRAYFORMULA causes the formula to work for every cell in the column. The IF() is used to apply the formula to the appropriate rows. The REGEXEXTRACT() extracts the time from the text.

如果在表单中允许多个选择(复选框)，则需要提取每个值并将它们相加以获得总计.听起来好像不是在执行此操作.有解决方案可以实现，但要使用非常复杂的公式.使用自定义公式拆分所有文本并将它们添加在一起可能会更好.

If you allow for multiple selections (check boxes) in the form, then you need to extract each value and sum them to get a total. It does not sound as if you are doing this. There are solutions to make it happen, but use a very complex formula. A custom formula to split all text and add them together would probably be better.

从评论看来，该表格可能实际上在该字段中填充了空白文本项.为了避开包含文本但不包含数字的单元格，我们使用IFERROR()函数排除错误:

From the comment, it appears the form may actually fill in the field with a blank text item. In order to get around the cell containing text and no numbers, we use the IFERROR() function to exclude the errors:

=ARRAYFORMULA(IF(ISTEXT( A2:A),  IFERROR( REGEXEXTRACT(A2:A,"\$(\d*)")), ))

将结果文本更改为数值，以便我们可以对结果使用数学公式:

EDIT 2: Change the resulting text to a numeric value so we can use mathematical formulas on the results:

=ARRAYFORMULA(IF(ISTEXT( B2:B),  IFERROR( VALUE(REGEXEXTRACT(B2:B,"\$(\d*)"))), ))

这篇关于设置与Google表单中的标签不同的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！