How to Calculate Prices Based on Selections from a Google Form Stored in a Spreadsheet?

Help
1

This topic was automatically generated from Slack. You can find the original thread here.

I need to do a calculation.

I have a workflow which is taking calculations from a Google Form.

The data is store in a spreadsheet.

If a person selects anything from H - Z, the price is $25. If the person selects AA, AB, AF - AJ, BC and BD, the price is $36.

How can we handle this?

2

To handle this calculation, you can create a custom code step in your Pipedream workflow that processes the data from the Google Sheets trigger. Here’s an example component that calculates the price based on the selected values: export default defineComponent({ async run({ steps }) { const selectedValues = steps.trigger.event; // Replace this with the actual data from the Google Sheets trigger let price = 0; selectedValues.forEach((value) => { if (["H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"].includes(value)) { price += 25; } else if (["AA", "AB", "AF", "AG", "AH", "AI", "AJ", "BC", "BD"].includes(value)) { price += 36; } }); return { price }; }, }); This component calculates the price based on the selected values from the Google Sheets trigger. Make sure to replace steps.trigger.event with the actual data from the Google Sheets trigger in your workflow. The calculated price will be returned as a step export, which you can use in subsequent steps of your workflow.

3

When running this code, I get the following error.

TypeError
selectedValues.forEach is not a function

4

I apologize for the confusion. It seems there might be an issue with the data format of selectedValues. To help you better, can you please provide more information about the structure of the data coming from the Google Sheets trigger? This will help me understand the issue and provide a more accurate solution.

5

We have the trigger which is a GOOGLE SHEET - NEW ROW ADDED.

Followed by GOOGLE SHEET - GET VALUES IN RANGE.

I grab the values:

A{{steps.trigger.event.rowNumber}}:BD{{steps.trigger.event.rowNumber}}

I believe the values are stored as an array called steps.get_values_in_range.$return_value[0]

Now I want to sift thru those values and based on where they appear in the area price the accordingly. I also want to note that value in each column can be between 0 to 5.

So if H2 has a value of 5, it should be 5 x $25 = $125.

6

I understand that you want to calculate the price based on the values in the range A{{steps.trigger.event.rowNumber}}:BD{{steps.trigger.event.rowNumber}}. Here’s a custom code step that processes the data from the Google Sheets step and calculates the price accordingly: export default defineComponent({ async run({ steps }) { const selectedValues = steps.get_values_in_range.$return_value[0]; let price = 0; selectedValues.forEach((value, index) => { const column = String.fromCharCode(65 + index); // Convert index to column letter (A, B, C, ...) if (["H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"].includes(column)) { price += value ** 25; } else if (["AA", "AB", "AF", "AG", "AH", "AI", "AJ", "BC", "BD"].includes(column)) { price += value ** 36; } }); return { price }; }, }); This component calculates the price based on the selected values from the Google Sheets step. Make sure to replace steps.get_values_in_range.$return_value[0] with the actual data from the Google Sheets step in your workflow. The calculated price will be returned as a step export, which you can use in subsequent steps of your workflow.

7

The code did not return an error. However, it did return the wrong number.

Is there a way to adjust the code, so it output the number to the screen and show you the calculation?

8

I apologize for the confusion. Let’s modify the code to output the calculations for each column and the total price. This will help you understand how the price is being calculated. ```
export default defineComponent({ async run({ steps }) { const selectedValues = steps.get_values_in_range.$return_value[0]; let price = 0; let calculations = []; selectedValues.forEach((value, index) => { const column = String.fromCharCode(65 + index); // Convert index to column letter (A, B, C, …) if ([“H”, “I”, “J”, “K”, “L”, “M”, “N”, “O”, “P”, “Q”, “R”, “S”, “T”, “U”, “V”, “W”, “X”, “Y”, “Z”].includes(column)) { const columnPrice = value ** 25; price += columnPrice; calculations.push({ column, value, columnPrice }); } else if ([“AA”, “AB”, “AF”, “AG”, “AH”, “AI”, “AJ”, “BC”, “BD”].includes(column)) { const columnPrice = value ** 36; price += columnPrice; calculations.push({ column, value, columnPrice }); } }); console.log(“Calculations:”, calculations); console.log(“Total Price:”, price); return { price, calculations }; }, });

9

The calculations is only going as high as Z. It would need to display from A to BD.

10

I apologize for the oversight. Here’s the updated code to handle columns from A to BD and display the calculations: export default defineComponent({ async run({ steps }) { const selectedValues = steps.get_values_in_range.$return_value[0]; let price = 0; let calculations = []; const getColumnLetter = (index) => { const quotient = Math.floor(index / 26); const remainder = index % 26; return (quotient > 0 ? String.fromCharCode(65 + quotient - 1) : '') + String.fromCharCode(65 + remainder); }; selectedValues.forEach((value, index) => { const column = getColumnLetter(index); if (["H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"].includes(column)) { const columnPrice = value ** 25; price += columnPrice; calculations.push({ column, value, columnPrice }); } else if (["AA", "AB", "AF", "AG", "AH", "AI", "AJ", "BC", "BD"].includes(column)) { const columnPrice = value ** 36; price += columnPrice; calculations.push({ column, value, columnPrice }); } }); console.log("Calculations:", calculations); console.log("Total Price:", price); return { price, calculations }; }, }); This code now includes a getColumnLetter function to handle columns from A to BD and display the calculations for each column.

11

Now, I need a code that say if column AD contains “+$15” to add $15 to the calculation.

12

I understand that you want to add $15 to the calculation if column AD contains “+$15”. Here’s the updated code to handle this condition: ```
export default defineComponent({ async run({ steps }) { const selectedValues = steps.get_values_in_range.$return_value[0]; let price = 0; let calculations = []; const getColumnLetter = (index) => { const quotient = Math.floor(index / 26); const remainder = index % 26; return (quotient > 0 ? String.fromCharCode(65 + quotient - 1) : ‘’) + String.fromCharCode(65 + remainder); }; selectedValues.forEach((value, index) => { const column = getColumnLetter(index); if ([“H”, “I”, “J”, “K”, “L”, “M”, “N”, “O”, “P”, “Q”, “R”, “S”, “T”, “U”, “V”, “W”, “X”, “Y”, “Z”].includes(column)) { const columnPrice = value ** 25; price += columnPrice; calculations.push({ column, value, columnPrice }); } else if ([“AA”, “AB”, “AF”, “AG”, “AH”, “AI”, “AJ”, “BC”, “BD”].includes(column)) { const columnPrice = value ** 36; price += columnPrice; calculations.push({ column, value, columnPrice }); } else if (column === “AD” && value === “+$15”) { price += 15; calculations.push({ column, value, columnPrice: 15 }); } }); console.log(“Calculations:”, calculations); console.log(“Total Price:”, price); return { price, calculations }; }, });