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

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!

Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    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.

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    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.

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • leon_mc
    leon_mc ✭✭

    Thank you will try this during the week!