Skip to content

7. Get Locations from Spreadsheet

Playback Speed:

At this point, your code gets 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.

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.

In the script editor, rename “myFunction” so that you can easily type into your spreadsheet.

Name it something descriptive, like “drivingDistance.”

Then, save your program.

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

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” and a semi-colon to close the statement.

Save the code, and return to your spreadsheet.


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 can pass values from your spreadsheet into your program.

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

Return to the script editor.

Type the names of the two location variables, “origin” and “destination” within the function parameters.

Rather than delete the variables, “comment out” this information.

Type two forward slashes at the beginning of these lines.

The computer skips over comments when it runs the program.

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

That 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 other place that you would visit frequently.

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 the distances 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?

Did you capitalize the right letters and include all punctuation marks?

If you’ve checked the code and can’t identify your bug, 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.

Next arrow_forward
  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.
  6. Pass variables in from the spreadsheet.
  7. Replicate the formula in the rest of the column.