Automate Costs in Google Sheets from Google Form Checkbox Input

Question

I'm trying to use Google Forms to collect some information on my clients. The application here is a clinical setting, so the form asks what treatments they would like in the form of a checkbox option. I would then like to automate the sum total of the treatment costs using the forms output.

The issue I'm having is that Google Forms outputs a list of strings in a single cell for this response. I'll add more detail below, but I don't know how to split the string into individual values, lookup that value in a separate column, get the cost, and display only the sum in a separate cell.

I've made a minimal working example in the form of a GSheet, you can find it here.

In that master sheet, you'll find three other sheets; Costs, Form Responses, and Overview.

1. The Costs sheet is static and only contains a list of items and their costs. This sheet will change on occasion (price updates, removal/addition of items)

2. The Form Responses sheet will contain the raw output from a Google Form. The column of note here is the Choose Things from the List column, which contains a list of responses.

3. The Overview sheet will house some redundant info, but it's meant to be a cleaned-up sheet with information. You'll notice a Cost of Things ($) column. In this column, I would like the total sum of all items the response list from the Form Responses sheet.

I can do this in Python easy. I would do it something like this:

costs = {'a': 1, 'b': 2, 'c': 3}
input_items = ['a', 'b', 'c']

x = []
for item in input_items:
   x.append(costs[item])

total_sum = sum(x)

How would I do this with Google Sheets? I want to

1. split a list embedded in a cell
2. check each list item for its cost in a separate sheet
3. sum the costs of each item

Please let me know if I need to clarify, I'm not quite sure how to pose the problem using Google Sheets language.

==============

EDIT: Sorry, I just updated the GSheet permissions. It should be viewable to everyone now.

Answer 1

In your situation, how about the following sample script?

Sample script:

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet1 = ss.getSheetByName("Costs");
  const sheet2 = ss.getSheetByName("Forms Responses");
  const sheet3 = ss.getSheetByName("Overview");

  // 1. Retrieve values from "Costs" sheet and create an object.
  const values1 = sheet1.getRange("A2:B" + sheet1.getLastRow()).getValues();
  const obj1 = values1.reduce((o, [a, b]) => (o[a.trim()] = b, o), {});

  // 2. Retrieve values from "Forms Responses" sheet and create an object.
  const values2 = sheet2.getRange("A2:C" + sheet2.getLastRow()).getValues();
  const obj2 = values2.reduce((o, [a, b, c]) => (o[a.trim() + b.trim()] = c.split(",").reduce((n, e) => (n += obj1[e.trim()] || 0, n), 0), o), {});

  // 3. Retrieve values from "Overview" sheet and create an array.
  const values3 = sheet3.getRange("A2:B" + sheet3.getLastRow()).getValues();
  const res = values3.map(([a, b]) => [obj2[a.trim() + b.trim()] || null]);

  // 4. Put array to "Overview" sheet.
  sheet3.getRange(2, 3, res.length, 1).setValues(res);
}

• In this sample script, the following flow is run.

  1. Retrieve values from "Costs" sheet and create an object.
  2. Retrieve values from "Forms Responses" sheet and create an object.
  3. Retrieve values from "Overview" sheet and create an array.
  4. Put array to "Overview" sheet.
     • In this case, the result values are put to the column "C".

Note:

• This sample script is for your sample Spreadsheet. So, when you change the Spreadsheet, the script might not be able to be used. Please be careful about this.

References:

• getValues()
• setValues(values)
• reduce()
• map()