Create a Google Maps direction URL that sorts addresses based on the Priority

AppSheet AppSheet Q&A
1

I have a table called DispatchStatus. I have created a virtual column that produces a URL for all Dispatches that are: Not Complete & Driver = Current Driver.

I would like to be able to sort the URL so the Postcodes are sorted by Dispatch Priority Column. The current Key/Label is the Unique DispatchID.

This is what I have gotten so far however, I get the “ORDERBY has invalid inputs” error.

IF(
  [Status] <> "Completed",
  "https://www.google.com/maps/dir/" &
  ENCODEURL("Starting Postcode") & "/" &
  SUBSTITUTE(
    CONCATENATE(
    ORDERBY(
      SELECT(
        DispatchStatus[PostCode],
        AND(
          ([Status] <> "Completed"),
          ([Driver] = [_THISROW].[Driver])
        )
       ),[Priority],FALSE
      )),
    ",",
    "/"
  ),
  ""
)
2

ORDERBY() requires a list of key values as input. You can do this:

  1. Create a separate column, for example sortedDispatches with the following formula:

    ORDERBY(
  FILTER( "DispatchStatus",
    AND([Status] <> "Completed", [Driver] = [_THISROW].[Driver])
  ),
  [Priority]
)

  2. Replace the ORDERBY() statement in your expression by:

    [sortedDispatches][PostCode]
1 Like