Using an ARRAYFORMULA in Google Sheets

Ask for Help
1

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.

image
image1032×260 25.9 KB

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!

2

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

3

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

4

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.

5

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
6

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!

2 Likes