error_demo_realistic_etl.py

Starting script entry point

114 steps Google Auth Google Sheets HTTP Client Can be run directly cli: __main__ guard

This automation script gathers invoice information from PandaDoc, payment details from Stripe, and status updates from a Google Sheet. It then matches these records, updates the relevant invoices in PandaDoc and the Google Sheet with the reconciliation results, and sends a summary notification to Slack.

How It Works

Each block is a phase of the automation. Click a phase below for details.

Data Journey

Reads financial transaction data from Google Sheets → classifies transactions into `auto_pay`, `follow_up`, and `review` categories using external services via HTTP Client → and writes these categorized items back to Google Sheets.

Repetitive error handling

8 try/except blocks. Consider extracting a retry/error-handling helper function.

Heavy data manipulation

"Processes data" appears 18 times. Complex transform chains may benefit from a data pipeline library (pandas, etc.).

Step by Step

Setup & Data Gathering 6 steps

This phase focuses on establishing secure connections to necessary online services and retrieving the initial set of data. It also includes preliminary checks on the gathered information to categorize it for further action.

External Service Fetches data from HTTP Client 2 locations

Batch data gathering — collects data from HTTP Client in 2 requests

External Service Authenticates with Google Sheets 2 locations

Multi-service authentication — connects to 2 different services

Risk: The primary risk in this phase is the script's inability to successfully connect to external web services due to network issues, service unavailability, or rate limits, or a failure to authenticate with Google Sheets if access permissions are incorrect or expired.

Data Processing 55 steps (48%)

Data Processing Processes data 18 locations

Data pipeline — transforms data through 18 processing steps

Data Processing Counts items 12 locations

Data pipeline — transforms data through 12 processing steps

Data Processing Converts data type 6 locations

Data pipeline — transforms data through 6 processing steps

Data Processing Converts data format 3 locations

Data pipeline — transforms data through 3 processing steps

Data Processing Builds a collection of items 2 locations

Data pipeline — transforms data through 2 processing steps

Check Processes each item 3 locations

Iteration pattern — 3 loops process data through multiple stages

Storage & Delivery 3 steps

This phase handles the final steps of sending updated information and new data to various external systems, while also creating a local record for internal use.

Risk: The main risk is that the external services might be unavailable, experience network issues, or reject the data being sent, which would prevent successful delivery or updates.

Error Handling 8 steps

Check Handles potential errors 8 locations

Repetitive error handling — 8 identical try/except blocks suggest extracting a shared helper

Reporting 42 steps

Log/Notify Records activity 29 locations

Status logging — tracks workflow progress across 29 checkpoints

Log/Notify Records an error 12 locations

Status logging — tracks workflow progress across 12 checkpoints

Step Detail

Click any step to see its details.

Services Used

Google Auth
Google Sheets
HTTP Client

Credentials Needed

Google credentials
PandaDoc credentials
Slack credentials
Stripe credentials

View technical flow diagram

Flow

Click any step to see details

graph TB
  subgraph n_error_demo_realistic_etl_run["run()"]
    n_error_demo_realistic_etl_57(["Output: logger.info()"]):::output
    n_error_demo_realistic_etl_58(["Output: logger.info()"]):::output
    n_error_demo_realistic_etl_65{"if not invoices"}:::decision
    n_error_demo_realistic_etl_66(["Output: logger.warning()"]):::output
    n_error_demo_realistic_etl_57 --> n_error_demo_realistic_etl_58
    n_error_demo_realistic_etl_58 --> n_error_demo_realistic_etl_65
    n_error_demo_realistic_etl_65 --> n_error_demo_realistic_etl_66
  end
  n_error_demo_realistic_etl__fetch_pandadoc_invoices_compact[["_fetch_pandadoc_invoices() — 12 steps (5 Output, 5 Transform, 1 Decision, 1 API)"]]:::compact
  n_error_demo_realistic_etl__fetch_stripe_payments_compact[["_fetch_stripe_payments() — 12 steps (5 Output, 5 Transform, 1 Decision, 1 API)"]]:::compact
  subgraph n_error_demo_realistic_etl__fetch_sheet_status["_fetch_sheet_status()"]
    n_error_demo_realistic_etl_151(["Output: logger.info()"]):::output
    n_error_demo_realistic_etl_153{"try/except block"}:::decision
    n_error_demo_realistic_etl_155[["Transform: .loads()"]]:::transform
    n_error_demo_realistic_etl_158["API: gspread.authorize()"]:::api
    n_error_demo_realistic_etl_162(["Output: logger.error()"]):::output
    n_error_demo_realistic_etl_163[["Transform: .append()"]]:::transform
    n_error_demo_realistic_etl_166(["Output: logger.info()"]):::output
    n_error_demo_realistic_etl_166[["Transform: len()"]]:::transform
    n_error_demo_realistic_etl_151 --> n_error_demo_realistic_etl_153
    n_error_demo_realistic_etl_153 --> n_error_demo_realistic_etl_155
    n_error_demo_realistic_etl_155 --> n_error_demo_realistic_etl_158
    n_error_demo_realistic_etl_158 --> n_error_demo_realistic_etl_162
    n_error_demo_realistic_etl_162 --> n_error_demo_realistic_etl_163
    n_error_demo_realistic_etl_163 --> n_error_demo_realistic_etl_166
    n_error_demo_realistic_etl_166 --> n_error_demo_realistic_etl_166
  end
  n_error_demo_realistic_etl__match_invoices_to_payments_compact[["_match_invoices_to_payments() — 16 steps (11 Transform, 3 Decision, 2 Output)"]]:::compact
  subgraph n_error_demo_realistic_etl__validate_matches["_validate_matches()"]
    n_error_demo_realistic_etl_216(["Output: logger.info()"]):::output
    n_error_demo_realistic_etl_218[["Transform: set comprehension: #lbrace;r.get('invoice_id') for ...#rbrace;"]]:::transform
    n_error_demo_realistic_etl_222{"for m in matched"}:::decision
    n_error_demo_realistic_etl_223{"if m['invoice_id'] in already_processed"}:::decision
    n_error_demo_realistic_etl_226{"if m['matched'] and m['match_confidence'] == 'low'"}:::decision
    n_error_demo_realistic_etl_230[["Transform: .append()"]]:::transform
    n_error_demo_realistic_etl_232(["Output: logger.info()"]):::output
    n_error_demo_realistic_etl_232[["Transform: len()"]]:::transform
    n_error_demo_realistic_etl_216 --> n_error_demo_realistic_etl_218
    n_error_demo_realistic_etl_218 --> n_error_demo_realistic_etl_222
    n_error_demo_realistic_etl_222 --> n_error_demo_realistic_etl_223
    n_error_demo_realistic_etl_223 --> n_error_demo_realistic_etl_226
    n_error_demo_realistic_etl_226 --> n_error_demo_realistic_etl_230
    n_error_demo_realistic_etl_230 --> n_error_demo_realistic_etl_232
    n_error_demo_realistic_etl_232 --> n_error_demo_realistic_etl_232
  end
  n_error_demo_realistic_etl__enrich_with_metadata_compact[["_enrich_with_metadata() — 9 steps (4 Transform, 3 Decision, 2 Output)"]]:::compact
  subgraph n_error_demo_realistic_etl__update_pandadoc_statuses["_update_pandadoc_statuses()"]
    n_error_demo_realistic_etl_266(["Output: logger.info()"]):::output
    n_error_demo_realistic_etl_268[["Transform: list comprehension: [e for ...]"]]:::transform
    n_error_demo_realistic_etl_270{"for item in auto_pay"}:::decision
    n_error_demo_realistic_etl_271{"try/except block"}:::decision
    n_error_demo_realistic_etl_272["API: requests.patch()"]:::api
    n_error_demo_realistic_etl_281(["Output: logger.error()"]):::output
    n_error_demo_realistic_etl_284(["Output: logger.info()"]):::output
    n_error_demo_realistic_etl_284[["Transform: len()"]]:::transform
    n_error_demo_realistic_etl_266 --> n_error_demo_realistic_etl_268
    n_error_demo_realistic_etl_268 --> n_error_demo_realistic_etl_270
    n_error_demo_realistic_etl_270 --> n_error_demo_realistic_etl_271
    n_error_demo_realistic_etl_271 --> n_error_demo_realistic_etl_272
    n_error_demo_realistic_etl_272 --> n_error_demo_realistic_etl_281
    n_error_demo_realistic_etl_281 --> n_error_demo_realistic_etl_284
    n_error_demo_realistic_etl_284 --> n_error_demo_realistic_etl_284
  end
  n_error_demo_realistic_etl__update_google_sheet_compact[["_update_google_sheet() — 16 steps (7 Transform, 4 Output, 4 Decision, 1 API)"]]:::compact
  subgraph n_error_demo_realistic_etl__archive_processed["_archive_processed()"]
    n_error_demo_realistic_etl_324(["Output: logger.info()"]):::output
    n_error_demo_realistic_etl_332{"try/except block"}:::decision
    n_error_demo_realistic_etl_333[/"File: .write_text()"/]:::fileio
    n_error_demo_realistic_etl_333[["Transform: .dumps()"]]:::transform
    n_error_demo_realistic_etl_335(["Output: logger.error()"]):::output
    n_error_demo_realistic_etl_336[["Transform: .append()"]]:::transform
    n_error_demo_realistic_etl_338(["Output: logger.info()"]):::output
    n_error_demo_realistic_etl_324 --> n_error_demo_realistic_etl_332
    n_error_demo_realistic_etl_332 --> n_error_demo_realistic_etl_333
    n_error_demo_realistic_etl_333 --> n_error_demo_realistic_etl_333
    n_error_demo_realistic_etl_333 --> n_error_demo_realistic_etl_335
    n_error_demo_realistic_etl_335 --> n_error_demo_realistic_etl_336
    n_error_demo_realistic_etl_336 --> n_error_demo_realistic_etl_338
  end
  subgraph n_error_demo_realistic_etl__notify_slack_summary["_notify_slack_summary()"]
    n_error_demo_realistic_etl_344(["Output: logger.info()"]):::output
    n_error_demo_realistic_etl_354{"if self.errors"}:::decision
    n_error_demo_realistic_etl_355[["Transform: .join()"]]:::transform
    n_error_demo_realistic_etl_357{"try/except block"}:::decision
    n_error_demo_realistic_etl_358["API: requests.post()"]:::api
    n_error_demo_realistic_etl_364(["Output: logger.error()"]):::output
    n_error_demo_realistic_etl_366(["Output: logger.info()"]):::output
    n_error_demo_realistic_etl_344 --> n_error_demo_realistic_etl_354
    n_error_demo_realistic_etl_354 --> n_error_demo_realistic_etl_355
    n_error_demo_realistic_etl_355 --> n_error_demo_realistic_etl_357
    n_error_demo_realistic_etl_357 --> n_error_demo_realistic_etl_358
    n_error_demo_realistic_etl_358 --> n_error_demo_realistic_etl_364
    n_error_demo_realistic_etl_364 --> n_error_demo_realistic_etl_366
  end
  subgraph n_error_demo_realistic_etl__log_final_stats["_log_final_stats()"]
    n_error_demo_realistic_etl_370(["Output: logger.info()"]):::output
    n_error_demo_realistic_etl_371(["Output: logger.info()"]):::output
    n_error_demo_realistic_etl_372(["Output: logger.info()"]):::output
    n_error_demo_realistic_etl_373(["Output: logger.info()"]):::output
    n_error_demo_realistic_etl_374(["Output: logger.info()"]):::output
    n_error_demo_realistic_etl_375(["Output: logger.info()"]):::output
    n_error_demo_realistic_etl_376(["Output: logger.info()"]):::output
    n_error_demo_realistic_etl_370 --> n_error_demo_realistic_etl_371
    n_error_demo_realistic_etl_371 --> n_error_demo_realistic_etl_372
    n_error_demo_realistic_etl_372 --> n_error_demo_realistic_etl_373
    n_error_demo_realistic_etl_373 --> n_error_demo_realistic_etl_374
    n_error_demo_realistic_etl_374 --> n_error_demo_realistic_etl_375
    n_error_demo_realistic_etl_375 --> n_error_demo_realistic_etl_376
  end
  click n_error_demo_realistic_etl_57 call showStepDetail("error_demo_realistic_etl.py", 57)
  click n_error_demo_realistic_etl_58 call showStepDetail("error_demo_realistic_etl.py", 58)
  click n_error_demo_realistic_etl_65 call showStepDetail("error_demo_realistic_etl.py", 65)
  click n_error_demo_realistic_etl_66 call showStepDetail("error_demo_realistic_etl.py", 66)
  click n_error_demo_realistic_etl_151 call showStepDetail("error_demo_realistic_etl.py", 151)
  click n_error_demo_realistic_etl_153 call showStepDetail("error_demo_realistic_etl.py", 153)
  click n_error_demo_realistic_etl_155 call showStepDetail("error_demo_realistic_etl.py", 155)
  click n_error_demo_realistic_etl_158 call showStepDetail("error_demo_realistic_etl.py", 158)
  click n_error_demo_realistic_etl_162 call showStepDetail("error_demo_realistic_etl.py", 162)
  click n_error_demo_realistic_etl_163 call showStepDetail("error_demo_realistic_etl.py", 163)
  click n_error_demo_realistic_etl_166 call showStepDetail("error_demo_realistic_etl.py", 166)
  click n_error_demo_realistic_etl_166 call showStepDetail("error_demo_realistic_etl.py", 166)
  click n_error_demo_realistic_etl_216 call showStepDetail("error_demo_realistic_etl.py", 216)
  click n_error_demo_realistic_etl_218 call showStepDetail("error_demo_realistic_etl.py", 218)
  click n_error_demo_realistic_etl_222 call showStepDetail("error_demo_realistic_etl.py", 222)
  click n_error_demo_realistic_etl_223 call showStepDetail("error_demo_realistic_etl.py", 223)
  click n_error_demo_realistic_etl_226 call showStepDetail("error_demo_realistic_etl.py", 226)
  click n_error_demo_realistic_etl_230 call showStepDetail("error_demo_realistic_etl.py", 230)
  click n_error_demo_realistic_etl_232 call showStepDetail("error_demo_realistic_etl.py", 232)
  click n_error_demo_realistic_etl_232 call showStepDetail("error_demo_realistic_etl.py", 232)
  click n_error_demo_realistic_etl_266 call showStepDetail("error_demo_realistic_etl.py", 266)
  click n_error_demo_realistic_etl_268 call showStepDetail("error_demo_realistic_etl.py", 268)
  click n_error_demo_realistic_etl_270 call showStepDetail("error_demo_realistic_etl.py", 270)
  click n_error_demo_realistic_etl_271 call showStepDetail("error_demo_realistic_etl.py", 271)
  click n_error_demo_realistic_etl_272 call showStepDetail("error_demo_realistic_etl.py", 272)
  click n_error_demo_realistic_etl_281 call showStepDetail("error_demo_realistic_etl.py", 281)
  click n_error_demo_realistic_etl_284 call showStepDetail("error_demo_realistic_etl.py", 284)
  click n_error_demo_realistic_etl_284 call showStepDetail("error_demo_realistic_etl.py", 284)
  click n_error_demo_realistic_etl_324 call showStepDetail("error_demo_realistic_etl.py", 324)
  click n_error_demo_realistic_etl_332 call showStepDetail("error_demo_realistic_etl.py", 332)
  click n_error_demo_realistic_etl_333 call showStepDetail("error_demo_realistic_etl.py", 333)
  click n_error_demo_realistic_etl_333 call showStepDetail("error_demo_realistic_etl.py", 333)
  click n_error_demo_realistic_etl_335 call showStepDetail("error_demo_realistic_etl.py", 335)
  click n_error_demo_realistic_etl_336 call showStepDetail("error_demo_realistic_etl.py", 336)
  click n_error_demo_realistic_etl_338 call showStepDetail("error_demo_realistic_etl.py", 338)
  click n_error_demo_realistic_etl_344 call showStepDetail("error_demo_realistic_etl.py", 344)
  click n_error_demo_realistic_etl_354 call showStepDetail("error_demo_realistic_etl.py", 354)
  click n_error_demo_realistic_etl_355 call showStepDetail("error_demo_realistic_etl.py", 355)
  click n_error_demo_realistic_etl_357 call showStepDetail("error_demo_realistic_etl.py", 357)
  click n_error_demo_realistic_etl_358 call showStepDetail("error_demo_realistic_etl.py", 358)
  click n_error_demo_realistic_etl_364 call showStepDetail("error_demo_realistic_etl.py", 364)
  click n_error_demo_realistic_etl_366 call showStepDetail("error_demo_realistic_etl.py", 366)
  click n_error_demo_realistic_etl_370 call showStepDetail("error_demo_realistic_etl.py", 370)
  click n_error_demo_realistic_etl_371 call showStepDetail("error_demo_realistic_etl.py", 371)
  click n_error_demo_realistic_etl_372 call showStepDetail("error_demo_realistic_etl.py", 372)
  click n_error_demo_realistic_etl_373 call showStepDetail("error_demo_realistic_etl.py", 373)
  click n_error_demo_realistic_etl_374 call showStepDetail("error_demo_realistic_etl.py", 374)
  click n_error_demo_realistic_etl_375 call showStepDetail("error_demo_realistic_etl.py", 375)
  click n_error_demo_realistic_etl_376 call showStepDetail("error_demo_realistic_etl.py", 376)
classDef api fill:#dbeafe,stroke:#2563eb,color:#1e3a5f
classDef fileio fill:#dcfce7,stroke:#16a34a,color:#14532d
classDef dbop fill:#f3e8ff,stroke:#9333ea,color:#3b0764
classDef decision fill:#ffedd5,stroke:#ea580c,color:#7c2d12
classDef output fill:#f3f4f6,stroke:#6b7280,color:#1f2937
classDef transform fill:#ccfbf1,stroke:#0d9488,color:#134e4a
classDef entry fill:#dcfce7,stroke:#16a34a,stroke-width:3px,color:#14532d
classDef compact fill:#f0f4ff,stroke:#6366f1,color:#312e81,stroke-width:2px

API Call File I/O Database Decision Output Transform

All Steps

Step Detail

Select a step from the diagram or list to see its details.

Services

Google Auth (google.oauth2)
Google Sheets (gspread)
HTTP Client (requests)

Secrets

GOOGLE_TOKEN_JSON
PANDADOC_API_KEY
SLACK_WEBHOOK_URL
STRIPE_API_KEY

Imports

External

datetime
google.oauth2.service_account
google.oauth2.service_account.Credentials
gspread
json
logging
os
pandas
pathlib
re
requests