Set values different from label in Google Forms

Question

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?

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>

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

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.

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.

Edit:

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

Sheet 1 - Results from Google Form spreadsheet:

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

Sheet 2 - Reference lists:

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

Sheet 3 - Totals:

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

Google Forms outputs the data to a google sheet. It would be nice to dump the google sheet to an excel sheet, but it's not necessary. — mhatch

Karl_S Karl_S · Accepted Answer · 2016-11-28T16:57:35

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:

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*)"), ))

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.

EDIT: 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*)"))), ))

Set values different from label in Google Forms

1 Answers