Best answer

Do trig math or get around google sheets ArrayFormula limitations

  • 25 July 2022
  • 2 replies
  • 24 views

Userlevel 1

I am trying to build a zap wherein a I need some fancy math calculated.  The math allows me to determine which of 6 routes to designate a shipping order to.  The math is a haversine formula and I would love to put it directly into a Python Zap step, but it would require the python math library.  To get around this, I decided to import the order into Google Sheets and apply the math there.  For each new order, one zap creates a new line in a Google Sheet.  The ArrayFormula function lets me calculate the distance between the order and each of 6 lat/longs that represent the center of each route.  But where I run into trouble is then adding a calculation that can then select the route name for the route with the minimum distance.  I can manually add the calculation and it works fine, but because it always involves looking at an array, I run into issues with the ArrayFormula not being applied automatically to subsequent new rows.  

 

So I could use a web hook step, and build a web app that takes the order, does the math and allocates the order to a shipping route. But I’m hoping there’s a simpler way.  Perhaps one of these options: 

  1. Is there some way of using python math library in a zap step?  Really why don’t they just include Math in the step?  What’s the downside?
  2. Is there some simple way of automatically populating the google sheet row that will not be hampered by the limitations of ArrayFormula?  I think a macro triggered by adding a new row is possible, but I cannot find a simple working example.  Alternatively, maybe some way of getting around the ArrayFormula limitation that isn’t too unwieldily?  

Thanks in advance!

 

Thom

icon

Best answer by LauraP 25 July 2022, 20:55

View original

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

2 replies

Userlevel 3
Badge +2

Hey @Thommango, although you can't request external libraries (as per our docs), you could use standard libraries. Would math be a standard library? If so, have you tried importing it in your Python Code step?

I'm not super familiar with Python, so leaning on your knowledge + what our docs offer. Let us know if you've tried that and what was the result!

Userlevel 1

Whew!  That’s the best possible answer!  Working well now.  Thanks!