Nightly Google Drive to Google Photos backup with a Sheets log

Every night at 2:00am in my timezone, sync new photos and videos from a designated Google Drive folder into a Google Photos backup album, and keep a running log in Google Sheets. This should be a deterministic code workflow.

Trigger: cron, daily at 2:00am.

Config (set at workflow config time, not per run): the Google Drive folder ID I want to back up, the Google Photos album ID that should receive the uploads, and the Google Sheets spreadsheet ID with a Log tab and an Errors tab.

Step 1. Compute the cutoff timestamp. Read the Log tab in my Google Sheets and find the most recent row's timestamp column. Use that as the "createdTime greater than X" cutoff for the Drive listing. If the Log tab is empty, treat the cutoff as the epoch so the first run picks up everything in the folder.

Step 2. List new files in Drive. Call Google Drive 'List Files' scoped to the configured folder, filtered to mime types that start with image/ or video/, and where createdTime is greater than the cutoff from step 1. Paginate through all results. Sort ascending by createdTime so the log stays in order.

Step 3. For each new file, in order, do the following:

3a. Call Google Drive 'Download File Content' to grab the raw bytes.

3b. Call Google Photos 'Upload Bytes' with those bytes and the file's mime type to get an upload token. Upload tokens expire after 24 hours, so use them immediately.

3c. Call Google Photos 'Batch Create Media Items' with the upload token AND the configured album ID, so the new media item is created and placed in the album in a single call. Important: call batch create serially per user (Google requires this), even though step 3b uploads can be parallelized if I ever want to speed it up.

3d. On success: call Google Sheets 'Append Values' to add a row to the Log tab with the columns filename, drive_file_id, google_photos_media_item_id, google_photos_product_url, and timestamp (RFC 3339, the time this run processed the file).

3e. On failure at any sub-step (download, upload bytes, or batch create): call Google Sheets 'Append Values' to add a row to the Errors tab with the columns filename, drive_file_id, error_message, failed_step, and timestamp. Do not stop the run; continue to the next file.

Step 4. Retry behavior for next run. At the start of each run, also read the Errors tab for rows whose drive_file_id is not yet present in the Log tab, and include those files in the processing list for this run so transient failures get a second chance.

Rate limiting and error handling: on HTTP 429 from Google Photos, wait at least 30 seconds and retry with exponential backoff (up to 3 attempts). On HTTP 500, same backoff pattern. Skip files larger than 200 MB for photos or 20 GB for videos (Google Photos' limits) and log them to the Errors tab with a clear message.

The album is app-created-data scope, so we only see and modify media that this workflow itself uploaded. That's fine, it's by design. The Sheets log is the source of truth for what has and hasn't been backed up.