How to enable Google Maps - "Distance Matrix API" on Smartsheet?

leon_mc

Hi - I don't want to spend money on the vendor/partner that Smartsheet are using for mapping.

Does anyone have a working setup they can share which leverages the Google Maps "Distance Matrix API" ?

Similar to this article - https://mateuszwiza.medium.com/get-driving-distance-between-multiple-points-using-google-maps-distance-matrix-api-32b6feaa0d18

Thanks!

Bassam Khalil

Hi @leon_mc,

Integrating Google Maps Distance Matrix API directly with Smartsheet to calculate distances between multiple points without using a paid vendor or partner service can be accomplished through a combination of Smartsheet's API, Google Maps Distance Matrix API, and a bit of custom scripting. Below is a high-level overview of how you can set this up:

Step 1: Obtain Google Maps API Key

1. Go to the Google Cloud Console.
2. Create a new project or select an existing one.
3. Navigate to the "APIs & Services" dashboard and enable the "Distance Matrix API".
4. Go to the "Credentials" tab and create an API key.

Step 2: Set Up Smartsheet

1. Ensure your Smartsheet has columns for the origin and destination addresses, and a column to store the calculated distance.
2. Obtain an API key from Smartsheet by going to Account > Apps & Integrations > API Access.

Step 3: Create a Script

You will need to write a script that:

• Reads the origin and destination addresses from your Smartsheet.
• Makes a request to the Google Maps Distance Matrix API with these addresses.
• Parses the response to get the distance.
• Updates the corresponding row in Smartsheet with the distance.

This script can be written in any language that can make HTTP requests, but Python is commonly used for its simplicity and readability.

Sample Python Script

Below is a simplified example script in Python. This script does not handle all possible errors and is meant for educational purposes.

import requests
import smartsheet


# Your API keys
SMARTSHEET_API_KEY = 'your_smartsheet_api_key'
GOOGLE_API_KEY = 'your_google_api_key'


# Smartsheet details
SHEET_ID = 'your_sheet_id'


# Initialize Smartsheet client
ss_client = smartsheet.Smartsheet(SMARTSHEET_API_KEY)


# Function to get distances using Google Maps API
def get_distance(origin, destination):
    params = {
        'origins': origin,
        'destinations': destination,
        'key': GOOGLE_API_KEY,
        'units': 'imperial', # or 'metric'
    }
    response = requests.get('https://maps.googleapis.com/maps/api/distancematrix/json', params=params)
    distance_data = response.json()
    distance = distance_data['rows'][0]['elements'][0]['distance']['text']
    return distance


# Function to update Smartsheet
def update_smartsheet(sheet_id, row_id, column_id, distance):
    row = ss_client.models.Row()
    row.id = row_id
    cell = ss_client.models.Cell()
    cell.column_id = column_id
    cell.value = distance
    row.cells.append(cell)
    ss_client.Sheets.update_rows(sheet_id, [row])


# Main process
def main():
    sheet = ss_client.Sheets.get_sheet(SHEET_ID)
    for row in sheet.rows:
        origin = None
        destination = None
        row_id = row.id
        for cell in row.cells:
            if cell.column_id == 'column_id_for_origin':
                origin = cell.value
            elif cell.column_id == 'column_id_for_destination':
                destination = cell.value
        if origin and destination:
            distance = get_distance(origin, destination)
            update_smartsheet(SHEET_ID, row_id, 'column_id_for_distance', distance)


if __name__ == '__main__':
    main()

Step 4: Run Your Script

Execute your script regularly to update the distances in your Smartsheet. You could run this script manually, schedule it to run at regular intervals on a server, or use a cloud function service like AWS Lambda or Google Cloud Functions.

Considerations

• Rate Limits: Be aware of the Google Maps API and Smartsheet API rate limits to avoid exceeding them.
• API Costs: While avoiding costs from Smartsheet vendors, keep in mind that the Google Maps API is not free beyond certain usage limits.
• Script Hosting: If you want your script to run automatically at scheduled times, consider hosting it on a cloud platform.

This approach requires some development effort but offers flexibility and can save costs associated with third-party mapping services.

Bassam Khalil

Hi @leon_mc,

Integrating Google Maps Distance Matrix API directly with Smartsheet to calculate distances between multiple points without using a paid vendor or partner service can be accomplished through a combination of Smartsheet's API, Google Maps Distance Matrix API, and a bit of custom scripting. Below is a high-level overview of how you can set this up:

Step 1: Obtain Google Maps API Key

1. Go to the Google Cloud Console.
2. Create a new project or select an existing one.
3. Navigate to the "APIs & Services" dashboard and enable the "Distance Matrix API".
4. Go to the "Credentials" tab and create an API key.

Step 2: Set Up Smartsheet

1. Ensure your Smartsheet has columns for the origin and destination addresses, and a column to store the calculated distance.
2. Obtain an API key from Smartsheet by going to Account > Apps & Integrations > API Access.

Step 3: Create a Script

You will need to write a script that:

• Reads the origin and destination addresses from your Smartsheet.
• Makes a request to the Google Maps Distance Matrix API with these addresses.
• Parses the response to get the distance.
• Updates the corresponding row in Smartsheet with the distance.

This script can be written in any language that can make HTTP requests, but Python is commonly used for its simplicity and readability.

Sample Python Script

Below is a simplified example script in Python. This script does not handle all possible errors and is meant for educational purposes.

import requests
import smartsheet


# Your API keys
SMARTSHEET_API_KEY = 'your_smartsheet_api_key'
GOOGLE_API_KEY = 'your_google_api_key'


# Smartsheet details
SHEET_ID = 'your_sheet_id'


# Initialize Smartsheet client
ss_client = smartsheet.Smartsheet(SMARTSHEET_API_KEY)


# Function to get distances using Google Maps API
def get_distance(origin, destination):
    params = {
        'origins': origin,
        'destinations': destination,
        'key': GOOGLE_API_KEY,
        'units': 'imperial', # or 'metric'
    }
    response = requests.get('https://maps.googleapis.com/maps/api/distancematrix/json', params=params)
    distance_data = response.json()
    distance = distance_data['rows'][0]['elements'][0]['distance']['text']
    return distance


# Function to update Smartsheet
def update_smartsheet(sheet_id, row_id, column_id, distance):
    row = ss_client.models.Row()
    row.id = row_id
    cell = ss_client.models.Cell()
    cell.column_id = column_id
    cell.value = distance
    row.cells.append(cell)
    ss_client.Sheets.update_rows(sheet_id, [row])


# Main process
def main():
    sheet = ss_client.Sheets.get_sheet(SHEET_ID)
    for row in sheet.rows:
        origin = None
        destination = None
        row_id = row.id
        for cell in row.cells:
            if cell.column_id == 'column_id_for_origin':
                origin = cell.value
            elif cell.column_id == 'column_id_for_destination':
                destination = cell.value
        if origin and destination:
            distance = get_distance(origin, destination)
            update_smartsheet(SHEET_ID, row_id, 'column_id_for_distance', distance)


if __name__ == '__main__':
    main()

Step 4: Run Your Script

Execute your script regularly to update the distances in your Smartsheet. You could run this script manually, schedule it to run at regular intervals on a server, or use a cloud function service like AWS Lambda or Google Cloud Functions.

Considerations

• Rate Limits: Be aware of the Google Maps API and Smartsheet API rate limits to avoid exceeding them.
• API Costs: While avoiding costs from Smartsheet vendors, keep in mind that the Google Maps API is not free beyond certain usage limits.
• Script Hosting: If you want your script to run automatically at scheduled times, consider hosting it on a cloud platform.

This approach requires some development effort but offers flexibility and can save costs associated with third-party mapping services.

leon_mc

Thank you will try this during the week!