LTL HOTELS

To complete our Google Sheet with additional information about our curated hotels using OpenAI scripts, we can retrieve data like hotel descriptions, amenities, or average price. We will use OpenAI's API to automate the process by generating relevant text based on the hotel name and location.

Here’s a step-by-step outline for this:

1. Set Up Google Sheets API & OpenAI API

• Enable Google Sheets API in your Google Cloud Console.
• Install the Google Sheets Python library (gspread) to access the data in the spreadsheet.
• Install openai Python library to interact with the OpenAI API.

2. Access the Spreadsheet via Python Script

Use gspread to open and modify the Google Sheet:

import gspread
from oauth2client.service_account import ServiceAccountCredentials

# Set up credentials and open the Google Sheet
scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('your-creds.json', scope)
client = gspread.authorize(creds)

# Open the specific sheet
sheet = client.open("LTL Top 1000 hotels").sheet1

3. Generate Information with OpenAI

Using the hotel name and location, prompt OpenAI to fetch relevant info:

import openai

openai.api_key = 'your-openai-api-key'

def get_hotel_info(hotel_name, location):
    prompt = f"Provide a detailed description of the hotel {hotel_name} located in {location}, including amenities, customer reviews, and unique features."
    response = openai.Completion.create(
        engine="text-davinci-003",
        prompt=prompt,
        max_tokens=300
    )
    return response.choices[0].text.strip()

4. Update the Google Sheet with New Data

For each hotel, you can append the generated information back into the spreadsheet:

hotel_names = sheet.col_values(1)
locations = sheet.col_values(2)

for i, (hotel, location) in enumerate(zip(hotel_names, locations), start=2):
    info = get_hotel_info(hotel, location)
    sheet.update_cell(i, 3, info)  # Update the third column with hotel info

5. Optimize the Process

• You can create different prompts to fetch specific info, like rating, average price, or notable amenities.
• To avoid hitting OpenAI token limits, you can run the script in batches.

---

Let's refine the script to retrieve only phone number, average price, and notable amenities using OpenAI. I'll show you how to integrate these steps to add this specific data into your Google Sheet.

Step-by-Step Implementation

---

1. Python Environment Setup

Ensure you have the required libraries installed:

pip install gspread oauth2client openai

2. Google Sheets Setup

Ensure you have the credentials to access your Google Sheet (usually a .json file). Set up access as shown earlier.

3. Python Code

Let's refine the script to make it more robust, efficient, and error-tolerant. The improvements will focus on the following:

1. Handling Edge Cases and Errors: Ensure the script gracefully handles missing or inconsistent data from OpenAI responses.
2. Rate Limiting: Manage rate limits and batching to avoid overwhelming the OpenAI API.
3. Logging: Add better logging for tracking progress and debugging.
4. More Flexible Parsing: Improve response parsing to handle slight variations in the structure of OpenAI’s answers.

import gspread
from oauth2client.service_account import ServiceAccountCredentials
import openai
import time

# Google Sheets API setup
scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('your-google-credentials.json', scope)
client = gspread.authorize(creds)

# Open the Google Sheet
sheet = client.open("LTL Top 1000 hotels").sheet1

# OpenAI API setup
openai.api_key = 'your-openai-api-key'

# Function to fetch details using OpenAI
def get_hotel_details(hotel_name, location):
    prompt = f"""
    Provide the following information about the hotel "{hotel_name}" located in "{location}":
    1. Phone number.
    2. Average price per night.
    3. Notable amenities.
    """
    
    # Call OpenAI API
    try:
        response = openai.Completion.create(
            engine="text-davinci-003",
            prompt=prompt,
            max_tokens=300,
            temperature=0
        )
        return response.choices[0].text.strip()
    except Exception as e:
        print(f"Error fetching details from OpenAI for {hotel_name}: {e}")
        return None

# Function to parse the OpenAI response
def parse_hotel_details(details):
    phone_number = None
    avg_price = None
    amenities = None

    try:
        details_split = details.split('\n')
        for line in details_split:
            line = line.strip()
            if "Phone number" in line:
                phone_number = line.split(":", 1)[-1].strip()
            elif "Average price per night" in line:
                avg_price = line.split(":", 1)[-1].strip()
            elif "Notable amenities" in line:
                amenities = line.split(":", 1)[-1].strip()
    except Exception as e:
        print(f"Error parsing details: {e}")

    return phone_number, avg_price, amenities

# Function to update Google Sheets
def update_sheet_with_details():
    hotel_names = sheet.col_values(1)
    locations = sheet.col_values(2)

    # Loop over hotels and locations and populate details
    for i, (hotel, location) in enumerate(zip(hotel_names, locations), start=2):  # Starts from row 2
        print(f"Fetching details for: {hotel}, {location}")

        # Skip empty rows
        if not hotel or not location:
            print(f"Skipping row {i}: missing hotel or location")
            continue

        details = get_hotel_details(hotel, location)

        # Skip if no details were fetched
        if not details:
            print(f"No details found for {hotel}. Skipping.")
            continue

        phone_number, avg_price, amenities = parse_hotel_details(details)

        # Check if we successfully parsed the details
        if phone_number and avg_price and amenities:
            try:
                sheet.update_cell(i, 3, phone_number)  # Update phone number in column 3
                sheet.update_cell(i, 4, avg_price)     # Update price in column 4
                sheet.update_cell(i, 5, amenities)     # Update amenities in column 5
                print(f"Updated details for {hotel}.")
            except Exception as e:
                print(f"Error updating Google Sheets for {hotel}: {e}")
        else:
            print(f"Failed to parse details for {hotel}. Skipping.")

        # Wait to avoid hitting the rate limit (60 requests/minute)
        time.sleep(1)  # Adjust the sleep if necessary

# Run the update process
update_sheet_with_details()

Key Improvements:

1. Error Handling:
   • Try-Except Blocks are added around both API calls and parsing logic to prevent the script from crashing due to unexpected errors.
   • If OpenAI fails to fetch details or the response doesn’t follow the expected format, the script will log the error and move on to the next hotel.
2. Flexible Parsing:
   • parse_hotel_details() function now checks each line individually, which increases flexibility in case OpenAI returns the data in a different format or order.
   • If a particular detail (like phone number or price) is missing, it logs the issue and skips updating that hotel.
3. Rate Limiting:
   • To prevent exceeding OpenAI’s rate limit (which is 60 requests per minute for text-davinci-003), we use time.sleep(1) to pause between requests.
   • Adjust the delay based on your usage. If you're getting rate-limit errors, increase the sleep time.
4. Logging:
   • The script prints out helpful log messages at each step, so you can monitor which hotel is being processed and where any issues might arise.
   • It logs skipped rows if either the hotel or location is missing.

---

Additional Suggestions:

1. Batch Processing:
   • If you have a large number of hotels, consider processing them in batches (e.g., 50 at a time) to avoid hitting API limits or network timeouts.
   • Modify the loop to handle batches of data, then store the last updated row and continue from there in subsequent runs.
2. Automated Re-Runs:
   • If you encounter issues with missing data or incorrect responses, you could add logic to retry failed hotels in a separate run.
3. Data Validation:
   • After parsing the response, you could add validation (e.g., check if phone numbers follow a particular format or if prices are numeric) to further ensure data accuracy before updating the sheet.

---