How to fix Zapier integration issue with Google Sheets?

Shopify Apps
,

Topic summary

Main issue: Orders sent from Shopify via Zapier arrive in Google Sheets as values like “558:00:00” (instead of “558”), which Sheets doesn’t recognize as a number. A screenshot is attached illustrating the formatting problem.

Proposed workaround (Zapier):

  • Add a Formatter/Filter step to clean the value before it hits Google Sheets.
  • Use a Truncate action with a dynamic Length (LEN) function to remove the trailing characters (e.g., last 3 for “.00”).
  • Example logic: keep only the first LEN(field)−3 characters to strip the extra “:00”/“.00” segment(s), turning something like “558.00.00” into “558.00” (and adjust further if “558” is desired). Reference the cleaned value for the rest of the Zap.

Status/outcome:

  • Root cause for Shopify adding the extra zeros/segments is unknown.
  • Actionable workaround provided within Zapier; no confirmation yet that the underlying issue is resolved. Discussion remains open.
Summarized with AI on November 25. AI used: gpt-5.
1

Hi,

We have been having some issues with our Zapier integration and I wanted to reach out to see if you could help us troubleshoot. Basically, the orders are being sent as “558:00:00” instead of just “558” from Shopify.

This is causing problems because Google Sheets doesn’t recognize it as a number.

Do anyone have any idea why this might be happening?

Any help you can provide would be greatly appreciated. Thank you in advance!

Best, Benjamin

Screenshot 2022-10-27 at 09.24.25.png
Screenshot 2022-10-27 at 09.24.25.png1752×882 37.6 KB

2

Hey Benjamin:

Not sure why Shopify is throwing the .00 at the end, but you can add a ‘filter’ step in your Zap that can remove those extra zeros. You can use a Truncate filter with a Length function.

Set up the truncate to truncate the field to LEN(field)-3.

That will basically say that there are 9 characters in the field, but you only want the first 6.

That will turn 558.00.00 into 558.00 and then reference that number throughout the rest of the ZAP.

Hope that helps.

Jeff