Kick off the new school year with lessons from our Back to School 2022 collection to help students express themselves, build relationships, and stay organized.

7. Get Locations from Spreadsheet

Playback Speed:

At this point, your code pulls a route from the Google Maps API, calculates the distance between the two points, and logs the distance.

In this video, you will create code that gets the addresses from your spreadsheet, so you won’t have to hard code every potential housing option.

By the end of this video, your program will return the calculated distance between two points on your spreadsheet.

This will help you make a decision about the best housing option.

First, rename “My Function” so that you can easily type it into your spreadsheet.

Name it something descriptive, like “driving Distance.”

Then, save your program.

Switch back to your spreadsheet, and add a column for driving distance.

Then, add the function you coded to these cells, so that the spreadsheet will automatically return the distance between your housing option and a specified location.

To do that, click on the first cell in the driving distance column.

Then, type an equals sign, the name of your function, and a set of parentheses.

Press Enter.

Nothing happens!

That’s because your function is not complete.

The code finds the distance, which you checked in the log.

It even converts meters to miles.

But right now, it does not return any values to the spreadsheet.

Go back to the script editor.

At the bottom of the function, before the last curly brace, type “return miles.”

Save the code, and return to your spreadsheet.

In the cell below distance, type an equals sign, then the name of your function followed by open and closed parentheses.


It works.

You’ve returned the distance between the two locations specified in your code.

The value in your spreadsheet should match the distance in the code log.

You could hard code every address into your program like this, but that wouldn’t be very helpful or efficient.

You’ve already learned how to define a variable in a function.

You did this when you typed the “origin” and “destination” addresses into your program.

You can also pass values from your spreadsheet into your program.

In this case, pass the origin address and the destination address from the spreadsheet into the function.

Within the function parameters, type the names of the two location variables, “origin” and “destination.”

Rather than delete the hardcoded variables, type two forward slashes at the beginning of those lines.

These slashes indicate that what follows is a comment, rather than code.

The computer skips over comments when it runs the program.

This is called “commenting out” information that isn’t part of the computer’s instructions.

When you add the two slashes, the whole line changes color.

This shows that this section of your program will not run.

Save your program and return to your spreadsheet.

Next, add the values to the driving distance parameters.

Click inside the parentheses of the driving distance function.

Select the cell with the address of your first housing option.

The function will use this address as the “origin” value in your code.

Then, type a comma and click the cell with your destination address.

This is the address of the school, workplace, or hangout that you would visit frequently.

This example uses a local community college.

Press Enter.


The distance between your potential home and your frequent destination appears in the cell.

Select the cell, and drag the handle down to copy the formula.

Paste the destination address into the other cells in the destination column.

Now, your program should calculate the distance between all the addresses and return that distance in this column.

If your program doesn’t work, return to the script editor to check the code.

See if you can figure out how to fix the problem.

For example: Do the parameters you passed in match the names used in your program?

Did you comment out the variables, or is the program still reading them?

Does the program return the “miles” variable to the spreadsheet?

If you’ve checked the code and are still struggling, ask a neighbor or your teacher for help.

Now, it’s your turn Rename your function so you can use it in your spreadsheet.

Add a column in your spreadsheet for Driving Distance.

Add the formula to this column.

Add “return miles” to your code.

Comment out the variables in your code, and pass them in from the spreadsheet instead.

When your program works, replicate the formula in the rest of the Driving Distance Column.

When you’ve finished, move on to the next video to wrap up this activity.


  1. Rename your function.
  2. Add a column in your spreadsheet for Driving Distance.
  3. Add =drivingDistance() into a cell in this column.
  4. Add return miles to your code.
  5. Comment out the variables and pass the variables in from the sheet.
  6. Pass variables in from the spreadsheet.
  7. Replicate the formula in the rest of the column.