Connecting Google Maps and Google Sheets to Solve the Traveling-Salesman Problem

Connecting Google Maps and Google Sheets can help us solve important and difficult problems. A typical problem is when we have a list of addresses in a Google spreadsheet, and we want to find the shortest possible route that visits each place exactly once. Finding the shortest route visiting a list of addresses is known as the Traveling-Salesman Problem. How can we solve this problem without coding a complex algorithm? Keep reading!

We know that Google Maps is capable of calculating a solution to this classical optimization problem. The key question is how to use Google Maps and Google Sheets to solve the Traveling-Salesperson Problem? Google Sheets doesn’t offer any predefined function callable from a cell to compute the solution. The only option available is to go to the Google Script Editor, write our own Javascript function, and call it from the spreadsheet. Luckily, the Javascript version used by Google Sheets already includes a reference to the Google Maps object, so our task will be relatively simple. We will not need to dig into Google Maps API.

First, we create a spreadsheet with a list of addresses we want to visit visit. I suggest placing the stops in a column. For our example, let’s use column B, cells B1-B16. I randomly selected 15 addresses from Google Maps. I don’t know who lives at these addresses if it’s a house, a gas station, or an empty lot. For the purpose of this example doesn’t matter.

Leave a Comment