How should I calculate net revenue, total cost and margin using Shopify and Google Sheets values in Make?

[Pistamiglio_Omar]

What is your goal?

I am building a Shopify order-items logger in Make.

For each Shopify line item, I retrieve the product cost and VAT rate from a Google Sheets table called Product_Costs, calculate the financial values, and then add a row to another Google Sheet.

I need to calculate:

1. line_revenue_net
2. total_cost_net
3. estimated_margin_net
4. estimated_margin_pct

The expected calculation for this test item is:

Quantity: 1
Shopify discounted unit price: 209.00
VAT rate: 22
Unit cost net: 105.40

Expected results:

line_revenue_net = 209 / 1.22 = 171.31
total_cost_net = 1 × 105.40 = 105.40
estimated_margin_net = 171.31 − 105.40 = 65.91
estimated_margin_pct = 65.91 / 171.31 = approximately 0.385

What is the problem & what have you tried?

The scenario structure is:

Google Sheets Search Rows
→ Shopify Get an Order
→ Iterator
→ Google Sheets Search Rows on Product_Costs
→ Tools / Set Multiple Variables
→ Tools / Set Multiple Variables for margin percentage
→ Google Sheets Add a Row

The modules complete successfully and appear green, but the Tools module returns empty values or NaN.

Google Sheets currently returns these values:

vat_rate = 22
unit_cost_net = 105,40
unit_cost_gross = 128,588
selling_price_gross = 239,00

The Google Sheets Search Rows module currently uses:

Value render option: FORMATTED_VALUE
Date and time render option: FORMATTED_STRING

Therefore, some decimal values may be returned as formatted strings with a comma decimal separator.

We tried using native green Make math operators instead of typing *, /, + and - manually.

We also tried parseNumber(value; ,), but the output was still empty or NaN.

We initially tried:

round(calculation; 2)

but were then told that Make round() may accept only one argument, so we tried:

round(calculation × 100) ÷ 100

We are unsure about the correct syntax and the best way to handle values coming from Google Sheets. line_revenue_net:

(Quantity × Discounted unit price after all discounts: Amount)
÷
(1 + VAT rate ÷ 100)

total_cost_net:

Quantity × unit_cost_net

estimated_margin_net:

line_revenue_net − total_cost_net

estimated_margin_pct:

estimated_margin_net ÷ line_revenue_net Could someone please clarify:

1. Should the Google Sheets Search Rows module use UNFORMATTED_VALUE instead of FORMATTED_VALUE so that 105,40 is returned as a numeric 105.4?

2. If UNFORMATTED_VALUE is used, should parseNumber() be removed completely?

3. What is the exact valid Make formula for rounding a result to two decimal places?

4. Can Set Multiple Variables safely perform these calculations directly, or would separate Set Variable modules be more reliable?

5. What exact formula would you recommend for each of the four calculations above?

A screenshot of the scenario, the Tools formulas, and the Google Sheets output is attached.

Thank you.

Error messages or input/output bundles

Tools module output:

line_revenue_net_calc: empty
total_cost_net_calc: empty
estimated_margin_net_calc: NaNNaN); 2

In other runs, Google Sheets received:

NaN
or the calculation expression as plain text instead of a calculated number.

Screenshots (scenario flow, module settings, errors)

[johnai.tech]

Set your Google Sheets > Search Rows module for Product_Costs to:

• Value render option: Unformatted value

This should return numeric values like 105.4 instead of 105,40. Once you do that, remove parseNumber() from the Product_Costs values.

Use these formulas, replacing each placeholder with the actual mapped Make token from your modules.

1. line_revenue_net

{{round(({{QUANTITY}} * {{DISCOUNTED_UNIT_PRICE}}) / (1 + ({{VAT_RATE}} / 100)) * 100) / 100}}

Test result: 171.31

2. total_cost_net

{{round({{QUANTITY}} * {{UNIT_COST_NET}} * 100) / 100}}

Test result: 105.4

3. estimated_margin_net
Create this after the first two variables:

{{round(({{LINE_REVENUE_NET}} - {{TOTAL_COST_NET}}) * 100) / 100}}

Test result: 65.91

4. estimated_margin_pct
Create this after estimated_margin_net:

{{if({{LINE_REVENUE_NET}} = 0; 0; round(({{ESTIMATED_MARGIN_NET}} / {{LINE_REVENUE_NET}}) * 1000) / 1000)}}

Test result: 0.385, which is approximately 38.5%.

Let us know how it goes

-John

@johnai.tech

[Pistamiglio_Omar]

Hi John, thank you for your previous help.

We followed your instructions and made some progress, but we are still having a problem when writing the calculated values into Google Sheets.

Current setup

The Product_Costs Search Rows module now uses:

• Value render option: Unformatted value

It correctly returns:

• vat_rate = 22
• unit_cost_net = 105.4

In the first Tools module, the calculations now work correctly:

• line_revenue_net_calc = 171.31
• total_cost_net_calc = 105.4

For the VAT denominator, we used:

sum(1; vat_rate / 100)

because Make was not reliably saving the native 1 + vat_rate / 100 expression.

The Tools output is now correct:

line_revenue_net_calc: 171.31
total_cost_net_calc: 105.4

Remaining problem

The issue happens in the Google Sheets Add a Row module.

Our spreadsheet uses Italian locale settings. When we map the calculated value directly, Google Sheets sometimes stores:

171.31

as text or interprets it as a time/duration.

For example, it has appeared as:

171:31:00

or:

171.31.00

We also tried:

replace(line_revenue_net_calc; "."; ",")

with USER_ENTERED, but Google Sheets still interpreted the value incorrectly.

Using RAW avoids some automatic conversion, but the value can remain text instead of a real numeric value.

Our question

What is the correct and most reliable configuration for writing these values as real numbers into a Google Sheet with Italian locale?

We need the cells to contain real numeric values:

171.31
105.4
65.91
0.385

so they can later be summed and used for KPI calculations.

Could you please indicate:

1. whether Value input option should be RAW or USER_ENTERED;
2. whether we should map the Tools output directly or use another function;
3. whether we should use parseNumber(), formatNumber(), replace(), or another conversion;
4. whether the Google Sheet locale should remain Italian or be changed;
5. the exact Make expression you would use in the Google Sheets field.

At this point, the Tools calculations are correct. The only problem is ensuring Google Sheets stores the results as actual numbers and not as text, dates, times, or durations.

Thank you again for your help.

[TinyOpsStudio]

Use RAW and keep the spreadsheet locale set to Italian. The key is to send a numeric value to Sheets, not a formatted decimal string.

1. In the destination sheet, select the four output columns and set Format > Number > Number. For estimated_margin_pct, use Number if you want to store/display 0.385, or Percent if you want the same value displayed as 38.5%. Test in fresh blank rows after changing the format; old Duration formatting can remain on existing cells.
2. In Google Sheets > Add a Row, set Value input option to RAW.
3. Map the arithmetic outputs directly. Do not use formatNumber() or replace() here because both produce formatted text.

If the Add a Row module’s raw input still shows the value in quotes, force the type at the last step with:

parseNumber(toString(<line_revenue_net_calc>); ".")

Use the same pattern for the other three mapped values:

parseNumber(toString(<total_cost_net_calc>); ".")

parseNumber(toString(<estimated_margin_net_calc>); ".")

parseNumber(toString(<estimated_margin_pct_calc>); ".")

Replace each placeholder with the mapped token from the Tools module. parseNumber() returns a number; RAW then sends that number without asking Google Sheets to reinterpret a locale-formatted string. USER_ENTERED is the mode that invokes the sheet’s locale parser and can turn a string such as 171.31 into an unintended date or duration.

You should not need to change the spreadsheet locale. After one test run, verify the destination cell with =TYPE(A2) (expected result: 1) or =ISNUMBER(A2) (expected result: TRUE). If it still displays as a time while TYPE returns 1, the payload is correct and only the destination cell’s number format needs to be reset.

[johnai.tech]

Try this for example on every expression:
=ROUND({{round(({{QUANTITY}} * {{DISCOUNTED_UNIT_PRICE}}) / (1 + ({{VAT_RATE}} / 100)) * 100) / 100}},2)

Put:
=ROUND({Input expression here},2)