Skip to content

Instantly share code, notes, and snippets.

@zsarge

zsarge/Google Apps Script for Spreadsheet.js

Created July 31, 2025 01:14
Show Gist options
  • Save zsarge/87f652a10cdb0b4980e9fd2320bc0bf6 to your computer and use it in GitHub Desktop.
Save zsarge/87f652a10cdb0b4980e9fd2320bc0bf6 to your computer and use it in GitHub Desktop.
Download ZIP
Scripts I used to plan my 2025 road trip
Raw
create_calendar.py
"""
Spreadsheet to ICAL converter
Converts a CSV itinerary of road trips into an iCalendar (.ics) file with three events per trip:
1. Driving event with calculated duration
2. Lodging check-in window
3. Lodging check-out window
Input Requirements:
-------------------
CSV file must contain these columns (case-sensitive):
- 'Driving Date': Date in format 'Monday, January 01, 2023'
- 'Days to Stay': Integer number of lodging nights
- 'Driving Time': Duration string (e.g., '5 hours 30 minutes')
- 'Miles': Distance in miles
- 'Est. Cost': Trip cost estimate
- 'Trip Start'/'Trip End': Location names
- 'Start Lat'/'Start Long'/'Dest Lat'/'Dest Long': Coordinates
- 'Google Maps Link'/'Directions URL': Navigation URLs
- 'State': 2-letter US state abbreviation
- 'Check In'/'Check Out': Times in 'HH:MM AM/PM' format
Generated Events:
-----------------
1. DRIVING EVENT:
- Starts at 7:00 AM local time on driving date
- Duration calculated from 'Driving Time'
- Includes trip details and coordinates in description
2. CHECK-IN EVENT:
- 2-hour window around specified check-in time
- On driving date (day of arrival)
3. CHECK-OUT EVENT:
- 2-hour window around specified check-out time
- Date calculated: driving date + stay days + (1 extra day if driving time ≥4 hours)
Timezone Handling:
------------------
- Uses state-specific timezones based on US state abbreviations
- Events show in local time of destination state
Usage:
------
Run from command line with:
$ python create_calendar.py <file_number>
Example:
$ python trip_calendar.py 1
Takes 'input1.csv' and outputs 'driving_schedule_1.ics'
"""
import csv
from icalendar import Calendar, Event
import pytz
import uuid
import re
import datetime
from zoneinfo import ZoneInfo
def parse_driving_time(driving_time):
"""Convert 'X hours Y minutes' string to timedelta"""
hours = 0
minutes = 0
hours_match = re.search(r'(\d+)\s+hours?', driving_time)
minutes_match = re.search(r'(\d+)\s+minutes?', driving_time)
if hours_match:
hours = int(hours_match.group(1))
if minutes_match:
minutes = int(minutes_match.group(1))
return datetime.timedelta(hours=hours, minutes=minutes)
def get_description(row) -> str:
return f"""<b>Trip Details:</b><br/>
Driving Date: {row['Driving Date']}<br/>
Days to Stay: {row['Days to Stay']}<br/>
Driving Time: {row['Driving Time']}<br/>
Distance: {row['Miles']} miles<br/>
Estimated Cost: {row['Est. Cost']}<br/>
<a href="{row['Google Maps Link']}">Google Maps</a><br/>
<a href="{row['Directions URL']}">Directions</a>
"""
# Mapping of state abbreviations to their most prominent time zones
STATE_TIMEZONES = {
"AL": "America/Chicago",
"AK": "America/Anchorage",
"AZ": "America/Phoenix", # Arizona doesn't observe DST
"AR": "America/Chicago",
"CA": "America/Los_Angeles",
"CO": "America/Denver",
"CT": "America/New_York",
"DE": "America/New_York",
"FL": "America/New_York", # Most of Florida
"GA": "America/New_York",
"HI": "Pacific/Honolulu",
"ID": "America/Boise", # Most populated part uses Boise time
"IL": "America/Chicago",
"IN": "America/Indiana/Indianapolis",
"IA": "America/Chicago",
"KS": "America/Chicago", # Most of Kansas
"KY": "America/New_York", # Eastern is more populated
"LA": "America/Chicago",
"ME": "America/New_York",
"MD": "America/New_York",
"MA": "America/New_York",
"MI": "America/Detroit",
"MN": "America/Chicago",
"MS": "America/Chicago",
"MO": "America/Chicago",
"MT": "America/Denver",
"NE": "America/Chicago",
"NV": "America/Los_Angeles",
"NH": "America/New_York",
"NJ": "America/New_York",
"NM": "America/Denver",
"NY": "America/New_York",
"NC": "America/New_York",
"ND": "America/Chicago",
"OH": "America/New_York",
"OK": "America/Chicago",
"OR": "America/Los_Angeles",
"PA": "America/New_York",
"RI": "America/New_York",
"SC": "America/New_York",
"SD": "America/Chicago",
"TN": "America/Chicago",
"TX": "America/Chicago", # Majority of the population
"UT": "America/Denver",
"VT": "America/New_York",
"VA": "America/New_York",
"WA": "America/Los_Angeles",
"WV": "America/New_York",
"WI": "America/Chicago",
"WY": "America/Denver"
}
def get_state_timezone(state_abbr: str) -> datetime.timezone:
"""Returns the most prominent datetime.timezone for a U.S. state as a datetime.datetime.timezone object."""
state_abbr = state_abbr.upper()
if state_abbr not in STATE_TIMEZONES:
raise ValueError(f"Unknown state abbreviation: {state_abbr}")
tz_str = STATE_TIMEZONES[state_abbr]
return ZoneInfo(tz_str)
def create_driving_time(row):
# Create event
event = Event()
event.add('uid', uuid.uuid4().hex + '@example.com')
# Determine event-local timezone
tz = get_state_timezone(row['State'])
event.add('dtstamp', datetime.datetime.now(tz))
# Parse date
date = datetime.datetime.strptime(row['Driving Date'], '%A, %B %d, %Y').date()
# Start at 7:00 AM local time
start_time = datetime.datetime(
year=date.year,
month=date.month,
day=date.day,
hour=7,
minute=0,
tzinfo=tz
)
# Compute end time
duration = parse_driving_time(row['Driving Time'])
end_time = start_time + duration
event.add('dtstart', start_time)
event.add('dtend', end_time)
# Summary, description, location
event.add('summary', f"🚗 {row['Trip Start']} to {row['Trip End']}")
event.add('description', get_description(row), parameters={'FMTTYPE': 'text/html'})
event.add('location', f"{row['Start Lat']},{row['Start Long']} to {row['Dest Lat']},{row['Dest Long']}")
return event
def has_at_least_4_hours(time_str):
match = re.search(r'(\d+)\s*hours?', time_str)
if match:
hours = int(match.group(1))
return int(hours >= 4) # Returns 1 if True, 0 if False
return 0 # If no "hours" found, treat as 0
def create_lodging_event(row, mode: str):
assert mode in ("Check In", "Check Out")
# Determine event-local timezone
tz = get_state_timezone(row['State']) # should return a zoneinfo.ZoneInfo or pytz tz
# Create event
event = Event()
event.add('uid', uuid.uuid4().hex + '@example.com')
# DTSTAMP in local tz
event.add('dtstamp', datetime.datetime.now(tz))
# Parse base date
date = datetime.datetime.strptime(row['Driving Date'], '%A, %B %d, %Y').date()
if mode == "Check Out":
# include extra stay days + possible 4h extension
stay_days = int(row["Days to Stay"]) + has_at_least_4_hours(row["Driving Time"])
date += datetime.timedelta(days=stay_days)
# Build the naïve start time
window_duration = datetime.timedelta(hours=2)
start_tm = datetime.datetime.strptime(row[mode], "%I:%M %p").time()
naive_start = datetime.datetime.combine(date, start_tm)
# If tz is a zoneinfo.ZoneInfo
start = naive_start.replace(tzinfo=tz)
if mode == "Check Out":
start -= window_duration
end = start + window_duration
# Add localized start/end with explicit TZID
event.add('dtstart', start)
event['dtstart'].params['TZID'] = tz.key if hasattr(tz, 'key') else tz.zone
event.add('dtend', end)
event['dtend'].params['TZID'] = tz.key if hasattr(tz, 'key') else tz.zone
# Summary, description, location
emoji = "✅" if mode == "Check In" else "🏃🏨"
event.add('summary', f"{emoji} {mode} ({row[mode]})")
cal_description = "<br>".join(f"{k}: {row[k]}" for k in ("State", "Check In", "Check Out"))
event.add(
'description',
cal_description + ("<br>" * 3) + get_description(row),
parameters={'FMTTYPE': 'text/html'}
)
event.add('location', f"{row['Dest Lat']},{row['Dest Long']}")
return event
def csv_to_ical(input_csv, output_ical):
# Create calendar and basic properties
cal = Calendar()
cal.add('prodid', '-//Driving Calendar//example.com//')
cal.add('version', '2.0')
# Set up datetime.timezone (Eastern Time)
with open(input_csv, 'r', encoding='utf-8') as csvfile:
reader = csv.DictReader(csvfile)
for row in reader:
try:
driving = create_driving_time(row)
check_in = create_lodging_event(row, "Check In")
check_out = create_lodging_event(row, "Check Out")
cal.add_component(driving)
cal.add_component(check_in)
cal.add_component(check_out)
except (ValueError, KeyError) as e:
print(f"Skipping invalid row: {e}")
continue
# Write to file
with open(output_ical, 'wb') as f:
f.write(cal.to_ical())
print(f"Created calendar with {len(cal.subcomponents)} events")
# Example usage
if __name__ == "__main__":
import sys
n = int(sys.argv[1])
assert n > 0
csv_to_ical(f'input{n}.csv', f'driving_schedule_{n}.ics')
Raw
csv_to_kml.py
"""
Converts trip data from a CSV file to KML line features with descriptions.
Processes a CSV file containing trip data, creating KML line features where:
- Each line connects start/destination coordinates from a trip record
- Lines are styled with blue coloring and 3-pixel width
- Descriptions contain trip details in HTML format
- Invalid coordinate rows are skipped with error notifications
Expected CSV format (with headers):
'Start Lat', 'Start Long', 'Dest Lat', 'Dest Long', 'Trip Start',
'Trip End', 'Driving Date', 'Days to Stay', 'Driving Time',
'Miles', 'Est. Cost', 'Google Maps Link', 'Directions URL'
See "create_calendar.py" for more info about CSV format.
"""
import csv
import simplekml
def csv_to_kml(input_csv, output_kml):
kml = simplekml.Kml()
with open(input_csv, 'r', encoding='utf-8') as csvfile:
reader = csv.DictReader(csvfile)
for row in reader:
try:
start_lat = float(row['Start Lat'])
start_lon = float(row['Start Long'])
end_lat = float(row['Dest Lat'])
end_lon = float(row['Dest Long'])
line = kml.newlinestring(name=f"{row['Trip Start']} to {row['Trip End']}")
# Set coordinates (KML uses lon,lat order!)
line.coords = [(start_lon, start_lat), (end_lon, end_lat)]
line.style.linestyle.color = simplekml.Color.blue
line.style.linestyle.width = 3
description = f"""
<![CDATA[
<b>Trip Details:</b><br/>
Driving Date: {row['Driving Date']}<br/>
Days to Stay: {row['Days to Stay']}<br/>
Driving Time: {row['Driving Time']}<br/>
Distance: {row['Miles']} miles<br/>
Estimated Cost: {row['Est. Cost']}<br/>
<a href="{row['Google Maps Link']}">Google Maps Link</a><br/>
<a href="{row['Directions URL']}">Directions URL</a>
]]>
"""
line.description = description
except ValueError as e:
print(f"Skipping row due to invalid coordinates: {e}")
continue
kml.save(output_kml)
print(f"Successfully created KML file: {output_kml}")
# Example usage
# $ python3 csv_to_kml.py 1
# takes input1.csv and produces trips_with_lines1.kml
import sys
n = int(sys.argv[1])
csv_to_kml(f'input{n}.csv', f'trips_with_lines{n}.kml')
Raw
Google Apps Script for Spreadsheet.js
/**
* The main function to use in your spreadsheet is:
* MAPS_GET_DETAILS(destination, starting_location)
*
* This is not "good code", but it worked for me so I thought I'd share.
*
* If you use this, configure:
* - API_KEY
* - MPG & AVG_PRICE_PER_GAL
*/
// API_KEY must have entitlements for:
// - Directions API
// - Places API
// - Places API (new)
const API_KEY = "...YOUR_API_KEY_GOES_HERE...";
// The cache key for "New York" and "new york " should be same
const md5 = (key = '') => {
const code = key.toLowerCase().replace(/\s/g, '');
return Utilities.computeDigest(Utilities.DigestAlgorithm.MD5, key)
.map((char) => (char + 256).toString(16).slice(-2))
.join('');
};
const getCache = (key) => {
return CacheService.getDocumentCache().get(md5(key));
};
// Store the results for 6 hours
const setCache = (key, value) => {
const expirationInSeconds = 6 * 60 * 60;
CacheService.getDocumentCache().put(md5(key), value, expirationInSeconds);
};
const getJSONCache = (key) => {
return JSON.parse(CacheService.getDocumentCache().get(md5(key)));
};
// Store the results for 6 hours
const setJSONCache = (key, value) => {
const expirationInSeconds = 6 * 60 * 60;
CacheService.getDocumentCache().put(md5(key), JSON.stringify(value), expirationInSeconds);
};
// Get the Place ID using Text Search
function getPlaceFromAddress(query) {
var fields = 'places.id,places.displayName,places.formattedAddress';
var searchUrl = 'https://places.googleapis.com/v1/places:searchText';
var payload = JSON.stringify({
textQuery: query
});
var options = {
method: 'post',
contentType: 'application/json',
payload: payload,
headers: {
'X-Goog-Api-Key': API_KEY,
'X-Goog-FieldMask': fields
}
};
var response = UrlFetchApp.fetch(searchUrl, options);
var json = JSON.parse(response.getContentText());
if (!json.places || json.places.length === 0) {
return null;
}
// Return the first Place ID
return json.places[0].id;
}
function getLatLngFromAddress(address) {
const url = "https://maps.googleapis.com/maps/api/place/findplacefromtext/json"
+ "?input=" + encodeURIComponent(address)
+ "&inputtype=textquery"
+ "&fields=geometry"
+ "&key=" + API_KEY;
const response = UrlFetchApp.fetch(url);
const json = JSON.parse(response.getContentText());
if (!json.candidates || json.candidates.length === 0) {
return { lat: null, lng: null };
}
const location = json.candidates[0].geometry.location;
return { lat: location.lat, lng: location.lng };
}
// better getPlaceId
function getPlaceId(query) {
var findPlaceUrl = "https://maps.googleapis.com/maps/api/place/findplacefromtext/json"
+ "?input=" + encodeURIComponent(query)
+ "&inputtype=textquery"
+ "&fields=place_id"
+ "&key=" + API_KEY;
var response = UrlFetchApp.fetch(findPlaceUrl);
var json = JSON.parse(response.getContentText());
if (!json.candidates || json.candidates.length === 0) {
console.log("No candidates found: ", json);
return null;
}
return json.candidates[0].place_id;
}
function getPlaceDetails(placeId, home_location) {
// you may not want all of these fields
var fields = 'name,price_level,formatted_address,address_components,editorial_summary/overview,opening_hours/weekday_text,rating,url,website,reservable,dine_in,serves_beer,serves_breakfast,serves_brunch,serves_lunch,serves_dinner,delivery,curbside_pickup,serves_vegetarian_food,takeout,geometry/location';
var detailsUrl = `https://maps.googleapis.com/maps/api/place/details/json?placeid=${placeId}&fields=${fields}&key=${API_KEY}`;
var response = UrlFetchApp.fetch(detailsUrl);
var json = JSON.parse(response.getContentText());
var place = json.result;
const miles = GOOGLEMAPS_DISTANCE(home_location, place.formatted_address);
const directionsUrl = `https://www.google.com/maps/dir/?api=1&origin=${encodeURIComponent(home_location)}&destination=${encodeURIComponent(place.formatted_address)}`;
// Get lat/lng of home location
const homeLatLng = getLatLngFromAddress(home_location);
// Extract state from address_components
let state = "Unknown";
if (place.address_components && place.address_components.length > 0) {
const stateComponent = place.address_components.find(comp =>
comp.types.includes("administrative_area_level_1")
);
state = stateComponent ? stateComponent.short_name : "Unknown";
}
return [[
GOOGLEMAPS_DURATION(home_location, place.formatted_address),
parseFloat(miles),
getGasMileage(miles),
place.url || "No Google Maps URL",
directionsUrl,
homeLatLng.lat, homeLatLng.lng, // Origin lat/lng
place.geometry.location.lat, place.geometry.location.lng, // Destination lat/lng
state, // Destination state
]];
}
function getGasMileage(numMiles) {
const MPG = 25; // adjust for your car
const AVG_PRICE_PER_GAL = 4; // adjust for your economy
const gallonsUsed = (numMiles / MPG);
return gallonsUsed * AVG_PRICE_PER_GAL;
}
// Main Function: Uses query to fetch Place ID and then details
function MAPS_GET_DETAILS(query, home_location) {
const key = ['map_details', query, home_location].join(',');
// Is result in the internal cache?
const value = getJSONCache(key);
if (value !== null) return value;
if (query === '') {
return 'Please provide a search query...';
}
var placeId = encodeURIComponent(getPlaceId(query));
if (!placeId) {
return 'No place found for this query.';
}
const data = getPlaceDetails(placeId, home_location);
setJSONCache(key, data);
return data;
}
/**
* Calculate the travel time between two locations
* on Google Maps.
*
* =GOOGLEMAPS_DURATION("NY 10005", "Hoboken NJ", "walking")
*
* @param {String} origin The address of starting point
* @param {String} destination The address of destination
* @param {String} mode The mode of travel (driving, walking, bicycling or transit)
* @return {String} The time in minutes
* @customFunction
*/
const GOOGLEMAPS_DURATION = (origin, destination, mode = 'driving') => {
const key = ['duration', origin, destination, mode].join(',');
// Is result in the internal cache?
const value = getCache(key);
// If yes, serve the cached result
if (value !== null) return value;
const { routes: [data] = [] } = Maps.newDirectionFinder()
.setOrigin(origin)
.setDestination(destination)
.setMode(mode)
.getDirections();
if (!data) {
throw new Error('No route found!');
}
const { legs: [{ duration: { text: time } } = {}] = [] } = data;
// Store the result in internal cache for future
setCache(key, time);
return time;
};
/**
* Calculate the travel distance between two locations
* on Google Maps.
*
* =GOOGLEMAPS_DISTANCE("NY 10005", "Hoboken NJ", "walking")
*
* @param {String} origin The address of starting point
* @param {String} destination The address of destination
* @param {String} mode The mode of travel (driving, walking, bicycling or transit)
* @return {Number} The distance in miles
* @customFunction
*/
const GOOGLEMAPS_DISTANCE = (origin, destination, mode = 'driving') => {
// Create a unique key for caching purposes
const key = ['distance', origin, destination, mode].join(',');
const value = getCache(key);
if (value !== null) return value;
const { routes: [data] = [] } = Maps.newDirectionFinder()
.setOrigin(origin)
.setDestination(destination)
.setMode(mode)
.getDirections();
if (!data) {
throw new Error('No route found!');
}
// Extract the distance in meters from the route data
const { legs: [{ distance: { value: meters } = {} } = {}] = [] } = data;
// Convert meters to miles (1 mile ≈ 1609.344 meters)
const miles = meters / 1609.344;
// Cache and return the result
setCache(key, miles);
return miles;
};
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment