Fleet Route Optimizer: a Google Sheets add-on

Delivery routes for your vehicles, planned inside your own spreadsheet.

Coming soon to the Google Workspace Marketplace. Meanwhile the same solver runs on the web: route optimizer.

What it does

You put your depot, your stops and your vehicles in the spreadsheet. The add-on splits the stops across your vehicles and puts each route in a good order. It keeps to each vehicle's capacity (up to three measures, for example weight, volume and boxes), your customers' time windows, the service time at each stop and the longest a route may take. Drive times follow real roads, not straight lines.

The answer goes into a new tab, Routes: each vehicle with its stops in order, the arrival time, the load and a Google Maps link. Stops that cannot be served are listed with their row, so nothing disappears quietly.

I built it on the same engine as the web version (Google OR-Tools). It is free and there is no signup.

How to use it

Open a new spreadsheet and go to Extensions → Fleet Route Optimizer.
Pick an example (Ohio or Mexico City) and press Insert sample.
Replace the data with yours and press Optimize routes.

Your spreadsheet must use the same tabs as the sample: locations, vehicles, parameters and warm_start. The first data row of locations is the depot. If you set StartFromLastSolution to TRUE in parameters, the next run starts from the last routes, which the add-on keeps in the warm_start tab. The columns are explained in the help tab of the sample.

Limits

Up to 200 stops per run.
One depot: every vehicle starts and ends at the same place.
One trip per vehicle.
Deliveries only: no paired pickup and delivery (taking something from A to B).

Looking up an address takes a little time. If you have many stops, give their latitude and longitude and the run is faster.

What data leaves your spreadsheet

The add-on can only read the spreadsheet you open it in, and only sends it when you press Optimize routes. Then it sends the spreadsheet's tabs over HTTPS to my server (routing.kindoflost.com). To compute the routes:

each address without latitude and longitude is looked up with the US Census Bureau geocoder (only when AddressCountry is US), OpenStreetMap Nominatim and Photon;
the coordinates (not the addresses) are sent to the public OSRM server to get drive times.

Addresses that are found are kept on my server with their coordinates, so they are not looked up again. The rest of your spreadsheet is used only to compute the routes and is thrown away within 15 minutes. For each run I keep one anonymous log row, with no addresses or names. Details in the privacy policy and the terms of use.

Support

Email me at mario@kindoflost.com.