See llms.txt for all machine-readable content.

Back to Templates

Reconcile and approve CSV imports with Google Sheets, Drive, and Gmail

Created by

Created by: Hiroto Tsuya || hiroto
Hiroto Tsuya

Last update

Last update a day ago

Categories

Quick overview

Compare CSV exports with a Google Sheets master before importing new records. This workflow validates each batch, collects a reviewer decision in Sheets, checks for changes before export, and saves approved new records to Google Drive. Gmail drafts keep review requests and outcomes ready to send.

How it works

  1. Triggers manually (demo) or via an n8n form submission that provides a batch ID and Google Drive file ID.
  2. Reads the “Imports” register in Google Sheets, downloads the source CSV from Google Drive, parses it, and reads the “Master” sheet from Google Sheets.
  3. Validates the CSV rows (required columns and formats), rejects duplicate record IDs in the source, and compares each record to Master to classify it as new, unchanged, conflicting, or invalid.
  4. Writes or updates a case row in the “Imports” sheet with hashes and a detailed proposal summary, and creates a Gmail draft instructing reviewers how to approve, reject, or request changes.
  5. Polls the “Imports” sheet on an interval until a reviewer records a valid decision (including matching proposal hash and reviewer email) or the review window times out.
  6. If approved, re-reads Master and marks the case stale if Master has changed since the proposal snapshot.
  7. If still approved, exports a CSV containing only the new records to a private Google Drive folder (reusing an existing export if found) and records the final status and output file ID in the “Imports” sheet.
  8. Creates a final Gmail draft summarizing the outcome and linking to the review register and the exported file (if generated).

Setup

  1. Create a Google Sheets spreadsheet with two sheets named “Master” (record_id, email, due_date, amount) and “Imports” (case_key, payload_hash, proposal_hash, status, created_at, expires_at, summary, decision, reviewed_hash, reviewer, comment, output_file_id, draft_id).
  2. Add Google Sheets, Google Drive, and Gmail credentials with permission to read/write the spreadsheet, download and upload files in Drive, and create Gmail drafts.
  3. Update the configuration values in the workflow (spreadsheetId, outputFolderId, reportTo, reviewerEmail, and optionally review/poll settings) and disable demoMode for live runs.
  4. Upload a UTF-8 CSV to Google Drive with headers record_id, email, due_date (YYYY-MM-DD), and amount, then provide its Drive file ID when submitting a case.
  5. In the “Imports” sheet, have the reviewer enter decision (approve/reject/revise), copy proposal_hash into reviewed_hash, and set reviewer to the configured reviewerEmail so the workflow can proceed.

Requirements

  • A private Google Sheets review register, Google Drive access to the source CSV and output folder, and a Gmail account for drafts. Restrict editing of the review register to trusted reviewers.

Additional info

The workflow creates Gmail drafts only; it does not send email or update the Master sheet. The reviewer email is an acknowledgement field, not identity verification. Run one active execution per batch: Google Sheets does not provide transactional locking. Invalid or conflicting batches are held in full. If a run fails after creating an export or draft, inspect the saved case and resume the failed execution before starting another run.