How to plan a route from Google Sheets

No. Google Sheets stores and sorts text, but it doesn't know where an address is or how the roads between two addresses connect, so it can't put stops in driving order. Sorting by ZIP code or street name groups some neighbors together and still sends you back and forth across town.

That leaves four ways to get from a sheet to a route. Most people want the first.

A route planner needs one thing from each row: an address it can find on a map. When a stop lands in the wrong place, the cause is almost always in the sheet, not the planner, so a few minutes here saves a wrong turn later.

Need a single address column? =TEXTJOIN(", ", TRUE, B2, C2, D2&" "&E2) joins Street, City, State and ZIP in Sheets. Combining address columns has the one-cell ARRAYFORMULA version that fills the whole column.

Copy and paste. If the whole address sits in one column, you can go from sheet to optimized route in about a minute.

1. 1

   Select the address column

   Click the first address below the header, then press Ctrl+Shift+Down (Cmd+Shift+Down on a Mac) to select down to the last row.

2. 2

   Copy it

   Ctrl+C, or Cmd+C on a Mac.

3. 3

   Paste it into EZRoutePlanner

   Open the planner, choose Quick Paste Multiple and paste. Every address becomes a pin on the map, and you can check them before you save.

4. 4

   Optimize

   Create a route from the stops, set where it starts and press Optimize Route.

Download the sheet as a file and import that. The import reads the columns as they are, so there's nothing to merge first.

1. 1

   Download the sheet

   In Google Sheets, choose File → Download → Microsoft Excel (.xlsx). Comma-separated values (.csv) works too, but it saves only the tab you're on.

2. 2

   Import it

   In EZRoutePlanner, choose Spreadsheet Import and drop in the file, up to 20 MB. AI finds the address columns on its own, even when street, city and ZIP are split.

3. 3

   Pick a label

   Choose one column, usually the customer or business name, as each stop's label. Phone numbers, notes and prices stay in your sheet.

4. 4

   Check the pins

   Every row shows on the map before anything is saved, and rows with an uncertain match are listed first so you can fix them.

Repeated rows and addresses you've already saved are skipped, so importing next week's version of the sheet adds only the new customers. The free plan keeps 30 saved addresses; paid plans keep 200 to 10,000.

Press Optimize Route. EZRoutePlanner keeps your start where it is, can bring you back there for a round trip or pin a last stop, and reorders every stop between into the shortest drive it finds.

• More stops than one route holds? Free routes take 30 stops and paid routes up to 200. For several drivers or days, lasso each area into its own route; planning routes for multiple drivers shows how.
• Driving it in Google Maps? The Google Maps export splits a long route into links of 10 stops, in the optimized order.
• Sending it to someone else? Share the route as a link or QR code. It opens in the phone's browser with no app or login, and every stop has a Navigate button.

Export the optimized route, add it to the sheet as a new tab, and look up each row's stop number. Then sort by that number. Your sheet keeps every column, notes and phone numbers included, now in the order the driver will visit.

1. 1

   Export the route

   In EZRoutePlanner, choose Export and download Excel or CSV. Each row has the Route Name, Stop Number, Label and Address, then the coordinates, the distance from the previous stop and a Google Maps link.

2. 2

   Add it to the sheet as a tab

   In Google Sheets, choose File → Import → Upload, pick the file and choose Insert new sheet(s). Rename the new tab Route.

3. 3

   Look up each stop number

   In an empty column on your list, use the formula below. It finds the customer's name in the export and returns their stop number.

4. 4

   Sort by the stop number

   Select the data, choose Data → Sort range → Advanced range sorting options, tick Data has header row and sort by the new column, A to Z.

=XLOOKUP(A2, Route!C:C, Route!B:B, "Not on route")

=IFERROR(INDEX(Route!B:B, MATCH(A2, Route!C:C, 0)), "Not on route")

Planning several routes at once? Export them together, and the Route Name column says which driver or day each stop belongs to. =XLOOKUP(A2, Route!C:C, Route!A:A, "") fills a Route column, and sorting by route, then stop number, gives each driver their own block of rows.

Yes, for a short list that's already in order. Google's Maps URLs take a start (origin), an end (destination) and up to 9 waypoints between, so a formula can join the rows into one directions link. It can't reorder them.

=HYPERLINK("https://www.google.com/maps/dir/?api=1&origin=" & ENCODEURL(A2) & "&destination=" & ENCODEURL(A6) & "&waypoints=" & ENCODEURL(TEXTJOIN("|", TRUE, A3:A5)) & "&travelmode=driving", "Open route")

• The link follows the rows. It drives them top to bottom, so sort the sheet into route order first, or the link zigzags.
• 11 places at most: the start, 9 waypoints and the end. Links longer than 2,048 characters stop working too.
• Phone browsers keep 3 waypoints. Opened in Safari or Chrome instead of the Maps app, the same link shows only 5 places.

Longer than that, the link has to become several. Adding more than 10 stops in Google Maps covers chaining them.

Only if the route has to be built inside the sheet itself. Route planner add-ons from the Google Workspace Marketplace write route columns into your sheet, and Apps Script, Google's scripting tool for Sheets, can ask Google's directions service to reorder a list of waypoints. Both work, with trade-offs:

• Access to your data. An add-on asks to see and edit the spreadsheets it runs on, and many send the rows to their own service. Read the permission screen before you click Allow.
• Limits and prices change. Stop caps, monthly runs and paid tiers differ by add-on and move often, so check the Marketplace listing before you build a routine on one.
• Apps Script is code you maintain. Its Maps service has a setOptimizeWaypoints option that reorders stops, but you write and fix the script yourself, and Google limits how much a script can do each day.
• You still can't see the route. A column of numbers doesn't show a pin in the wrong town or a leg that doubles back. A map does.

For most lists, a planner that takes the sheet as it is gets you there faster: nothing to install, no code, and every stop on a map before the route goes out.

Keep the sheet as the master list and the planner as the map. Add new customers to the sheet, import the updated file, and only the new rows are added.

• Saved routes stay put. Last week's Monday route is still there: add the new customer to it and optimize again.
• Write the route back. A Route column filled from the export shows who visits which customer, right in the sheet.
• Archive customers who pause for the season, so they're out of the way until they come back.

Visiting the same customers on a cycle? Planning weekly service routes covers giving each area a day, and the Excel route planner page covers every file the import takes.