From Text to a Perfect Visual Excel Grid! The Ultimate AI Travel Agent
Title
From Text to a Perfect Visual Excel Grid! The Ultimate AI Travel Agent 🐥📊
Introduction
Hello, creators! Welcome to a2set's AI Tutorial.
When planning a trip with AI, have you ever been frustrated by vague text responses? Wouldn't it be amazing if the AI could not only plan the perfect route but also generate a highly structured, professional Excel Timetable, complete with exact costs, transport methods, recommended menus, and even Google Maps links for every location?
Today, we are combining the power of Python's data processing with the latest AI 'Structured Output' technology to make this a reality. In just one minute, we will transform a complex prompt into a stunning .xlsx schedule patterned after the beautiful vertical timelines used by professional travel agencies.
Our main project today is: "A Flawless 3-Night 4-Day Trip to Sapporo, Japan (April 2026)". We will ask the AI to calculate the exact travel time, pick the best local restaurants (with addresses and URLs), and even analyze whether buying a 'JR Pass' is cheaper than buying individual train tickets for our specific route!
🚨 Mandatory Check Before We Start (OpenAI API Billing)
Today's workflow runs entirely in your web browser for free. However, to borrow the brain of our genius AI (GPT-4o), you must have at least $5 pre-loaded in your OpenAI account balance. Generating this hyper-detailed Excel sheet costs only about $0.01 ~ $0.02 per run. Think of it as spending a cup of coffee to hire a lifelong, personal AI travel planner!
If you're ready, keep your browser open and follow right along!
Step 1: Set Up Your Free Digital Workspace (Google Colab)
First, we need a workspace to run our Python code. Instead of installing heavy software on your PC, we will use Google's free cloud environment.
Open your web browser, log in to your Google account, and go to Google Colab.
Click the blue [New Notebook] button.
When the blank notebook opens, click the file name at the top left (e.g., Untitled0.ipynb) and elegantly rename it to Sapporo_Premium_Planner.ipynb.
Step 2: Connect the AI's Brain & Load $5 (OpenAI API)
Now it's time to invite our genius planner. As mentioned, a $5 pre-paid balance is required.
Go to the OpenAI Billing Page and log in.
Click the [Add payment details] button to register a card and charge the minimum amount ($5).
Once charged, click the lock icon (API keys) on the left menu and hit [Create new secret key].
A long password starting with sk- will appear. You MUST copy and save this right now! (You won't be able to see it again).
Step 3: Open the Toolbox (Install Free Libraries)
Let's head back to our 'Google Colab' screen.
Copy the code below, paste it into the input box (cell), and click the ▶️ (Play button) on the left to install our tools.
To follow the script exactly, please download the notepad file through the link below. You can simply copy and paste it to follow the tutorial right away.
# Use the ! symbol to command the computer to install the tools.
!pip install openai pandas openpyxl pydantic
Once installed, click the [+ Code] button right below to open a new cell and set up your API key.
import os
from openai import OpenAI
# Enter your personal API key received earlier.
os.environ["OPENAI_API_KEY"] = "sk-paste_your_api_key_here"
client = OpenAI()
Step 4: Build the Blueprint for the Premium Timeline (Data Schema)
To create a beautiful timetable grid just like the premium formats (Date, Time, Itinerary, Cost, Category, Memo), we need to instruct the AI exactly how to structure its output. We will force the AI to include exact addresses and Google Maps links in the memo section.
from pydantic import BaseModel
# Tell the AI exactly how to structure a single row of the itinerary.
class Event(BaseModel):
date: str # e.g., "Apr 9"
time: str # e.g., "10:35 - 13:25"
itinerary: str # Name of the place or activity (e.g., "Incheon -> New Chitose")
cost: str # Estimated cost (e.g., "¥4,500" or empty)
category: str # MUST be one of: "Transport", "Food", "Sightseeing", "Accommodation", "Shopping"
memo: str # Exact Address, Google Maps Link, or specific menus/tips
# Group all events and include the crucial transport cost analysis.
class TravelPlan(BaseModel):
jr_pass_analysis: str # Explanation of whether buying a JR Hokkaido Pass is worth it based on the itinerary.
events: list[Event]
Step 5: Directing the AI like a Movie Set (Execute Prompt)
Here comes the magic! We will give the AI an extremely detailed prompt, demanding exact addresses, Google Maps URLs, and optimal transport routes.
Open a new code cell, paste the script below, and hit play! The AI will calculate the optimal routes, search for restaurant menus/addresses, and build the timeline (takes about 15~20 seconds).
# Highly detailed scenario instructions from the director (user)
system_prompt = """
You are a top-tier travel planner specializing in Japan trips.
Create a flawless, premium vertical timetable for a 3-Night 4-Day trip to Sapporo.
(Ensure ALL output text is written in English for the final Excel file).
1. Target: 3 Nights 4 Days travel to Sapporo, Japan.
2. Flight Schedule (Strictly adhere to this time):
- Departure: April 9, 2026, 10:35 AM (Incheon) -> 1:25 PM (New Chitose)
- Return: April 12, 2026, 2:50 PM (New Chitose) -> 5:55 PM (Incheon)
3. Main Routing Missions:
- Day 1 (Apr 9): Arrival, check-in at Susukino, Sapporo City Tour. Dinner at a highly-rated Genghis Khan restaurant.
- Day 2 (Apr 10): Noboribetsu Tour (Jigokudani, Private Hot springs). Provide exact train details.
- Day 3 (Apr 11): Sapporo Beer Museum (Morning), Otaru Tour in the afternoon/evening.
- Day 4 (Apr 12): Morning Shopping, early Lunch/Snack, move to the airport.
4. Crucial Guidelines for Output:
- Calculate optimal transport routes. Incorporate meals (Breakfast, Lunch, Dinner, Snack) smoothly into the travel path.
- For every Food and Sightseeing event, you MUST provide the exact Address and a Google Maps Link in the 'memo' field. Also include recommended menus.
- Format 'time' as "HH:MM - HH:MM".
- Carefully analyze the total train costs (Sapporo-Noboribetsu-Otaru-Chitose) and write a definitive 'jr_pass_analysis' comparing individual tickets vs. a Regional JR Pass.
5. Category Classification: Strictly use "Transport", "Food", "Sightseeing", "Accommodation", or "Shopping".
"""
print("🎬 AI Agent is analyzing routes, finding exact addresses/links, and generating the Grid...")
# Force the response to strictly follow the 'TravelPlan' structure.
response = client.beta.chat.completions.parse(
model="gpt-4o-2024-08-06",
messages=[
{"role": "system", "content": system_prompt},
{"role": "user", "content": "Generate the perfect Sapporo vertical timetable in English with addresses and links."}
],
response_format=TravelPlan
)
# Extract the completed data.
travel_data = response.choices[0].message.parsed
print("✅ Detailed routing, addresses, and cost analysis complete!")
Step 6: Render the Color-Coded Excel Timetable! (The Grand Finale)
This is the ultimate step. Instead of dumping raw data, we will use Python's openpyxl library to paint an actual premium vertical timetable, merging date columns and alternating pastel colors for different categories, exactly like the ones sold by professional planners!
Run this final block of code in a new cell:
from openpyxl import Workbook
from openpyxl.styles import PatternFill, Alignment, Font, Border, Side
from google.colab import files
# Create a new Excel Workbook
wb = Workbook()
ws = wb.active
ws.title = "Sapporo Premium Itinerary"
# Define border style
thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin'))
# 1. Main Title
ws.merge_cells('A1:F1')
title_cell = ws['A1']
title_cell.value = "SAPPORO ❄️"
title_cell.font = Font(size=16, bold=True)
title_cell.alignment = Alignment(horizontal='center', vertical='center')
title_cell.fill = PatternFill(start_color="D9E1F2", end_color="D9E1F2", fill_type="solid")
ws.row_dimensions[1].height = 30
# 2. Headers
headers = ["Date", "Time", "Itinerary", "Cost", "Category", "Memo (Address/Links & Tips)"]
ws.append(headers)
for col in range(1, 7):
cell = ws.cell(row=2, column=col)
cell.font = Font(bold=True)
cell.alignment = Alignment(horizontal='center', vertical='center')
cell.border = thin_border
cell.fill = PatternFill(start_color="EDEDED", end_color="EDEDED", fill_type="solid")
# 3. Define our visual color palette for rows
colors = {
"Transport": "FFFFFF", # White/Clean
"Food": "FFF2CC", # Light Yellow
"Sightseeing": "E2EFDA", # Light Green
"Accommodation": "D9E1F2", # Light Blue
"Shopping": "FCE4D6" # Light Orange
}
# 4. Paint the grid with the AI's data
current_row = 3
start_merge_row = 3
current_date = travel_data.events[0].date if travel_data.events else None
for i, event in enumerate(travel_data.events):
ws.cell(row=current_row, column=1, value=event.date)
ws.cell(row=current_row, column=2, value=event.time)
ws.cell(row=current_row, column=3, value=event.itinerary)
ws.cell(row=current_row, column=4, value=event.cost)
ws.cell(row=current_row, column=5, value=event.category)
ws.cell(row=current_row, column=6, value=event.memo)
# Apply borders, alignment, and row colors
fill_color = colors.get(event.category, "FFFFFF")
for col in range(1, 7):
cell = ws.cell(row=current_row, column=col)
cell.border = thin_border
cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
if col > 1: # The Date column will be handled during merge
cell.fill = PatternFill(start_color=fill_color, end_color=fill_color, fill_type="solid")
# Check if we need to merge the Date column
is_last_item = (i == len(travel_data.events) - 1)
next_date = travel_data.events[i+1].date if not is_last_item else None
if is_last_item or next_date != current_date:
# Merge the Date cells for the same day
ws.merge_cells(start_row=start_merge_row, start_column=1, end_row=current_row, end_column=1)
merged_date_cell = ws.cell(row=start_merge_row, column=1)
merged_date_cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
merged_date_cell.fill = PatternFill(start_color="F2F2F2", end_color="F2F2F2", fill_type="solid")
start_merge_row = current_row + 1
current_date = next_date
current_row += 1
# 5. Adjust Column Widths for better readability
ws.column_dimensions['A'].width = 12
ws.column_dimensions['B'].width = 18
ws.column_dimensions['C'].width = 35
ws.column_dimensions['D'].width = 15
ws.column_dimensions['E'].width = 15
ws.column_dimensions['F'].width = 50
# 6. Write the JR Pass Analysis at the bottom
ws.merge_cells(start_row=current_row, start_column=1, end_row=current_row, end_column=3)
analysis_title = ws.cell(row=current_row, column=1, value="JR Pass Tip / Cost Analysis:")
analysis_title.font = Font(bold=True)
analysis_title.alignment = Alignment(horizontal='center', vertical='center')
analysis_title.fill = PatternFill(start_color="D9E1F2", end_color="D9E1F2", fill_type="solid")
ws.merge_cells(start_row=current_row, start_column=4, end_row=current_row, end_column=6)
analysis_body = ws.cell(row=current_row, column=4, value=travel_data.jr_pass_analysis)
analysis_body.alignment = Alignment(wrap_text=True, vertical='center')
analysis_body.fill = PatternFill(start_color="F2F2F2", end_color="F2F2F2", fill_type="solid")
for col in range(1, 7):
ws.cell(row=current_row, column=col).border = thin_border
ws.row_dimensions[current_row].height = 80
# Save and Download!
file_name = "Sapporo_Premium_Itinerary_a2set.xlsx"
wb.save(file_name)
print(f"🎉 Rendering successful! Downloading '{file_name}' to your computer.")
files.download(file_name)
The moment you run this code, a file named 'Sapporo_Premium_Itinerary_a2set.xlsx' will be downloaded to your PC.
Open it up, and you'll be amazed! Instead of a boring list of text, you will see a fully colored, beautifully merged timeline. The AI groups your days together, colors food events in pastel yellow, sightseeing in soft green, and most importantly, it flawlessly fills the 'Memo' column with exact addresses and clickable Google Maps links for every single restaurant and tourist spot. It even prints out the final verdict on whether you should buy a JR Pass at the very bottom of the sheet!
With just a $5 API balance and this Python script, you are now equipped with the ultimate superpower to generate unlimited, highly professional travel itineraries. Swap out the destination to "Paris" or "New York," and watch your personal AI travel agency go to work.
Stay tuned to a2set.com for more cutting-edge AI workflows that make your life infinitely easier!