Using an ARRAYFORMULA in Google Sheets

Hi All -

I am building a feature in my app to track a package status through a tracking number. The best solution I’ve found to do this is via a Google Sheets plugin. It uses a custom formula “=track()” to return the package status, expected delivery, etc. across multiple carriers.

I have built a simple Google Sheet with the following fields:

  1. “Parts Order Identifier”: the Row ID of the order the package is related to
  2. “Tracker #”
  3. “Status”: the delivery status (a result of the =track() function)
  4. “Expected Delivery”: the est. delivery date (a result of the =track() function)
  5. “URL”: the URL to the carrier’s tracking page (a result of the =track() function)
  6. “Last Status Date”: the date corresponding to the Status value (a result of the =track() function)
  7. “Row ID”: a unique identifier from Glide as to assure there aren’t any sync issues

The =track() features requires me to calculate row-level data in Google Sheets for all of the fields above except the first two instead of Glide. I am having a problem trying to build an arrayformula to do this (and unsure if one would even work).

Here is the arrayformula I built for the “Status” field, which I know is incorrect. I’m not sure how to write it so the first parameter of the “track()” formula is specifically its corresponding value in Column B for that row. E.g., the second row would be track($B2,“status”,false), the third row track($B3,“status”,false), and so on.

Any help would be so much appreciated - thank you!

EDIT: If anyone knows of a simpler, API (ideally Zapier-integrated) solution for package tracking please let me know… I searched far & wide!

Get rid of the dollar signs. You’re forcing row and column to remain static by having dollar signs.

Thanks Jeff, the dollar signs just applied to the column but I removed them and still getting the same issue.

What exactly is the issue? The fact that it doesn’t return a valid status? I would need to know more about the plugin you are using. I have no idea what it requires.

I’m pretty sure byrow would be the answer here, but I’m interested in knowing what that formula does to help you replicate that in Glide instead of using Sheet formula.

1 Like

Thank you @Jeff_Hager & @ThinhDinh – I ended up using an entirely different approach via API updates with TrackingMore. The Sheets-based plugin was unreliable for real-time status tracking. After looking up BYROW I agree that was very likely the answer for this, but will never know :slight_smile: . Thanks for the help anyways!