Several of us in the Cloudbakers office are in the process of moving to different places – some in Chicago and others around the United States. Sites like Padmapper, Craigslist, and apartments.com are great for helping you discover open rooms, but there’s something missing in the ease of comparing the potential rooms that you find. The physical move itself is hard enough, so why should the planning stages be too?
What if you could view all of your choices in a simple spreadsheet? With a tool like Google Sheets, you can even collaborate with potential roommates. Using a template sent to me from Jordan, I put together a sheet that not only utilizes some of Google Sheets advanced features, but is also helpful at organizing your search.
Two templates (Google Sheets) depending on your living situation
Start by making a copy of one of the sheets listed above. Fill the first tab with any apartments, condos, or houses you find promising. Then fill the second tab (Setup) with information about yourself (name, work address, rent). There are some generic columns included to help you determine rent and the amenities that are included. The most exciting section within these sheets are the last set of columns, Distance and Travel Times.
It would be a pain to search Google Maps for the individual distances and travel times for each of your listings, so I’ve created two custom formulas that can help you with your search: DISTANCE and TRAVELTIME. Below are screenshots of the two formulas.
Distance is going to calculate the distance in miles between two points. It uses the Google Maps API, so even typing in a start address of “Wrigley Field” will yield results. Tip: I’ve found in my testing that using a full address seems to work best.
The travel time formula calculates the travel time between two points, also using the Google Maps API. The third argument in the formula is the method of travel (Bike, Drive, Walk, Public Transit). Just like in the distance formula, full addresses work best.
To make the search a little easier, the spreadsheet will automatically read from your home address in the “Setup” tab of the spreadsheet. There’s a bit of a catch. If you look at the formulas in the example, you’ll notice that instead of referencing the cell Setup!B1, I referenced Setup!$B$1 (Absolute Referencing). This essential “locks” the Setup!B1 value into the formula, no matter how you drag the existing formula in the spreadsheet. In the example shown below (without an Absolute Refernce), if I were to drag the formula across a cell, A6 would turn to A7 and Setup!B1 would turn into Setup!B2.
This is a nice trick when you are creating constant variables throughout your spreadsheets.
Lastly, if you want to visualize all of your apartments on a map, you can create a map in Google Drive by navigating to Create -> More -> Google My Maps. (Pictured below)
When creating a map, select the “import” option and you can then choose a sheet from your Drive. From there, you can display the points on a map and share it with your future roommates.
These use cases are great examples of the extensibility and interconnectedness of Google Apps. I was able to create a special formula for calculating distances and travel times while simultaneously creating a custom map to share with my potential roommates.
Disclaimer: Every once in a while, the custom formulas will return an Error. If this happens, please delete the formula and rewrite it.
Enjoy!Originally published on June 27, 2015