Book Cover Image Processor

Analyses book cover images using the OpenAI Vision API and writes the results into a Google Sheet — one row per ISBN — with an inline thumbnail and a column for each data field returned by the model.

---

How it works

1. The script scans a folder for .jpg files named ISBN_cov.jpg (e.g. 9780008663322_cov.jpg).
2. Each image is sent to the OpenAI Vision API together with the prompt defined in prompt.md.
3. The model returns a JSON object. Each key becomes a column in the sheet.
4. A 100 px-wide thumbnail is created and uploaded to a Google Drive folder.
5. A row is written to the Google Sheet: ISBN → Thumbnail → JSON fields.
6. Re-processing the same image overwrites the existing row for that ISBN rather than creating a duplicate.

---

Prerequisites

Requirement	Notes
Python 3.11+	Earlier 3.x versions may work but are untested
OpenAI account	Vision access required (GPT-4o or similar)
Google Cloud project	Free tier is sufficient
Google Sheet	Shared with the service account (see below)
Google Drive folder	For thumbnail storage; shared with the service account

---

Installation

1. Clone or download the project

git clone <repo-url>
cd boldwoodtest

2. Create a virtual environment (recommended)

python -m venv .venv
source .venv/bin/activate      # macOS / Linux
.venv\Scripts\activate         # Windows

3. Install Python dependencies

pip install openai Pillow google-api-python-client google-auth

---

Google Cloud setup

4. Create a Google Cloud project

1. Go to https://console.cloud.google.com.
2. Click Select a project → New Project, give it a name, and click Create.

5. Enable the required APIs

In your new project, go to APIs & Services → Library and enable:

• Google Sheets API
• Google Drive API

6. Create a service account

1. Go to APIs & Services → Credentials → Create Credentials → Service account.
2. Give it a name (e.g. image-processor) and click Done.
3. Click on the service account you just created, then go to the Keys tab.
4. Click Add Key → Create new key → JSON and download the file.
5. Save the downloaded file as credentials.json in the project folder (alongside image_processor.py).

Security note: credentials.json grants access to your Google account resources. Never commit it to version control. Add it to .gitignore.

7. Create the Google Drive thumbnail folder

1. Open Google Drive and create a new folder (e.g. Book Cover Thumbnails).
2. Right-click the folder → Share, and share it with the service account's email address (visible in the Credentials page, looks like name@project-id.iam.gserviceaccount.com). Give it Editor access.
3. Copy the folder ID from its URL: https://drive.google.com/drive/folders/THIS_PART_IS_THE_ID

8. Create and share the Google Sheet

1. Create a new Google Sheet.
2. Share it with the service account email and give it Editor access.
3. Copy the Sheet ID from its URL: https://docs.google.com/spreadsheets/d/THIS_PART_IS_THE_ID/edit

---

Configuration

9. Edit config.json

Open config.json and fill in your values:

{
    "image_folder": "./images",
    "prompt_file": "./prompt.md",
    "openai_model": "gpt-4o",
    "json_fields": ["description", "objects", "colors", "tags", "notes"],
    "google_credentials_file": "./credentials.json",
    "google_sheet_id": "YOUR_GOOGLE_SHEET_ID",
    "google_drive_folder_id": "YOUR_GOOGLE_DRIVE_FOLDER_ID",
    "thumbnail_row_height_px": 130
}

Key	Description
image_folder	Path to the folder containing ISBN_cov.jpg files
prompt_file	Path to the Markdown prompt file
openai_model	OpenAI model name (default: gpt-4o)
json_fields	Ordered list of JSON keys the model is expected to return; must match what prompt.md asks for
google_credentials_file	Path to the service account JSON key
google_sheet_id	ID copied from your Google Sheet URL
google_drive_folder_id	ID copied from your Google Drive folder URL
thumbnail_row_height_px	Row height in pixels to display the thumbnail (default: 130)

10. Add your OpenAI API key

Open image_processor.py and replace the placeholder near the top of the file:

OPENAI_API_KEY = "sk-..."   # Replace this with your actual key

Your key can be found at https://platform.openai.com/api-keys.

---

Customising the prompt

Edit prompt.md to change what the model analyses and what fields it returns.

Important: the field names in prompt.md must match the values in the json_fields list inside config.json. If you add or rename fields you must update both files.

Example: to add a publisher field, add it to your prompt instructions and update config.json:

"json_fields": ["description", "objects", "colors", "tags", "notes", "publisher"]

---

Preparing your images

Place your .jpg files in the folder specified by image_folder. Filenames must follow the pattern:

9780008663322_cov.jpg

• Starts with the 13-digit ISBN.
• Followed by _cov (or any other suffix — only the ISBN prefix matters).
• Extension is .jpg (case-insensitive).

---

Running the script

python image_processor.py

To use a different config file:

python image_processor.py /path/to/my_config.json

Progress is printed to the terminal:

Found 3 image(s) to process.

  9780008663322_cov.jpg ... OK (added, row appended)
  9780241999999_cov.jpg ... OK (updated, row 3)
  9780000000000_cov.jpg ... SKIPPED — Expected a 13-digit ISBN ...

All images processed.

---

Google Sheet layout

Column	Content
A	ISBN (13-digit, extracted from filename)
B	Thumbnail (=IMAGE(...) formula, 100 px wide)
C onwards	One column per field in json_fields

Row 1 is the header row (written automatically on first run). Each subsequent row corresponds to one unique ISBN.

---

Re-processing images

Simply run the script again after updating an image or the prompt. The script looks up each ISBN in column A and overwrites the existing row if found, or appends a new row if not.

---

Troubleshooting

Symptom	Likely cause
Error: Please replace OPENAI_API_KEY	The API key placeholder was not replaced in image_processor.py
No .jpg files found	Check the image_folder path in config.json
HttpError 403 from Google APIs	The service account was not shared with the Sheet or Drive folder
Thumbnail shows broken image in Sheet	The Drive file permission was not set to public; try re-running the script
JSON fields missing in sheet	The model returned unexpected JSON; check the raw output by temporarily adding a print(raw_response) call after analyse_image()
ValueError: Expected a 13-digit ISBN	Filename does not start with a 13-digit number followed by _

---

Project files

image_processor.py   Main script
config.json          User-configurable settings
prompt.md            Editable analysis prompt (sent to OpenAI)
credentials.json     Google service account key (you provide this — do not commit)
README.md            This file
images/              Place your ISBN_cov.jpg files here