(Solution) Using ZIP to get Per Diem M&EI Rates with Python

SteyJ

I've been testing new things with the Smartsheet API via python. Somebody down the road might find value with this if they want to build their own expense report system.

It works by checking a column called "ZIP Code", and then looking at the bottom of the sheet for the latest entry, then gets the row ID. Once it has the latest zip code, it then parses the ZIP through GSA.gov open API for the M&EI rates for 2023 in that area. For simplicity, the year 2023 is hard coded in, but you can easily convert the Created column in Smartsheet to a variable which automatically knows the current year.

You can also do this with a ton of other rates, such as lodging by month. To get a GSA API token, you can fill out the form in the link above, and it will get emailed to you!

import smartsheet
import requests
import json

# Initialize the Smartsheet client
smartsheet_client = smartsheet.Smartsheet('Smartsheet Token')
sheet_id = 'Sheet ID'
sheet = smartsheet_client.Sheets.get_sheet(sheet_id)

# column ID if the column name is "ZIP Code"
for zipColumn in sheet.columns:
    if zipColumn.title == "ZIP Code":
        break

# column ID if the column name is "Meal Rate"
for mrColumn in sheet.columns:
    if mrColumn.title == "Meal Rate":
        break

# Get row that is at bottom of sheet
max_row_number = -1
latestEntry = None
for row in sheet.rows:
    if row.row_number > max_row_number:
        max_row_number = row.row_number
        latestEntry = row.id

# Print the IDs we just found
print(f'BottomRow {latestEntry}')
print(f'MealID {mrColumn.id}')
print(f'ZipID {zipColumn.id}')

# Assign the ZIP code entered to a variable
for row in sheet.rows:
    if row.id == latestEntry:
        for cell in row.cells:
            if cell.column_id == zipColumn.id:
                get_zip = (int(cell.value))

# Use ZIP code to get per diem rate
api_key = "gsa.gov api token"
url = f"https://api.gsa.gov/travel/perdiem/v2/rates/zip/{get_zip}/year/2023"

headers = { "X-API-KEY": api_key }
response = requests.get(url, headers=headers)

# Check if response was successful from gsa.gov, if so, get the meal rate off JSON response
if response.status_code == 200:
    try:
        data = response.json()
        meals_rate = int(data['rates'][0]['rate'][0]['meals'])
        print("Meal rate found!")
    except json.JSONDecodeError:
        print("Invalid JSON response")
else:
    print("Failed to update cell value")

# Specify new cell value
new_cell = smartsheet.models.Cell()
new_cell.column_id = mrColumn.id
new_cell.value = meals_rate

# Specify row to update
row = smartsheet.models.Row()
row.id = latestEntry
row.cells.append(new_cell)

# Update row
updated_row = smartsheet_client.Sheets.update_rows(sheet_id, [row])

# Check if update was successful
if updated_row.message == 'SUCCESS':
    print('Cell Updated!')
else:
    print('Failed to update Cell.')

Tbarnard

Do you have a sample sheet you could share for this? I'd love to integrate it into ours but I need a visual to refer to.