Enrich Google Sheets with Dun & Bradstreet Data Blocks

Description

Automate company enrichment directly in Google Sheets using Dun & Bradstreet (D&B) Data Blocks. This workflow reads DUNS numbers from a sheet, fetches a Bearer token (via Basic Auth → /v3/token), calls the Data Blocks API for each row (/v1/data/duns/...), extracts Paydex, and appends or updates the sheet. A Filter node skips rows already marked Complete for efficient, idempotent runs.

✅ What this template does
Pulls DUNS values from a Google Sheet
(Option A) Uses an HTTP Header Auth credential for D&B, or
(Option B) Dynamically fetches a Bearer token from /v3/token (Basic Auth)
Calls D&B Data Blocks per row to retrieve payment insights
Extracts Paydex and upserts results back to the sheet
Skips rows already Complete

👤 Who's it for
RevOps/Data teams enriching company lists at scale
SDR/Marketing teams validating firmographic/credit signals
BI/Automation builders who want a no-code/low-code enrichment loop

🧩 How it works (node-by-node)
Get Companies (Google Sheets) – Reads rows with at least duns, paydex, Complete.
Only New Rows (Filter) – Passes only rows where Complete is empty.
D&B Info (HTTP Request) – Calls Data Blocks for each DUNS using a header credential (Authorization: Bearer <token>).
Keep Score (Set) – Maps nested JSON to a single Paydex field:
{{$json.organization.businessTrading[0].summary[0].paydexScoreHistory[0].paydexScore}}
Append to g-sheets (Google Sheets) – Append or Update by duns, writing paydex and setting Complete = Yes.

> The workflow also includes Sticky Notes with in-canvas setup help.

🛠️ Setup instructions (from the JSON)

1) Connect Google Sheets (OAuth2)
In n8n → Credentials → New → Google Sheets (OAuth2) and sign in.
Use/prepare a sheet with columns like: duns, paydex, Complete.
In your Google Sheets nodes, select your credential and target spreadsheet/tab.
For upsert behavior, set Operation to Append or Update and Matching column to duns.

> Replace any example Sheet IDs/URLs with your own (avoid publishing private IDs).

2) Get a D&B Bearer Token (Basic Auth → /v3/token) — Optional Dynamic Token Node
Add/enable HTTP Request node named Get Bearer Token1.
Configure:
Authentication: Basic Auth (your D&B username/password)
Method: POST
URL: https://plus.dnb.com/v3/token
Body Parameters: grant_type = client_credentials
Headers: Accept = application/json
Execute to receive access_token.
Reference the token in other nodes via:
Authorization: Bearer {{$node["Get Bearer Token1"].json["access_token"]}}

> ⚠️ Security: Don't hardcode tokens. Prefer credentials or fetch dynamically.

3) Call D&B Data Blocks (use Header Auth or dynamic token)
Node: D&B Info (HTTP Request)
Authentication:** Header Auth (recommended)
URL:**
https://plus.dnb.com/v1/data/duns/{{ $json.duns }}?blockIDs=paymentinsight_L4_v1&tradeUp=hq&customerReference=customer%20reference%20text&orderReason=6332
Headers:**
Accept = application/json
If not using a stored Header Auth credential, set:
Authorization = Bearer {{$node["Get Bearer Token1"].json["access_token"]}}

> {{ $json.duns }} is resolved from the current row provided by Get Companies.

4) Map Paydex and Upsert to Google Sheets
Keep Score (Set)**
Field Paydex (Number):
{{$json.organization.businessTrading[0].summary[0].paydexScoreHistory[0].paydexScore}}
Append to g-sheets (Google Sheets)**
Operation: Append or Update
Matching column: duns
Columns mapping:
duns = {{ $('Get Companies').item.json.duns }}
paydex = {{ $json.Paydex }}
Complete = Yes

🧪 Test checklist
Add a few test DUNS rows (leave Complete blank).
Run the workflow and confirm Only New Rows passes expected items.
Check D&B Info returns payment insight data.
Confirm Paydex is set and the row is updated with Complete = Yes.

🔐 Security & best practices
Store secrets in Credentials (HTTP Header Auth/Basic Auth).
Avoid publishing real Sheet IDs or tokens in screenshots/notes.
Consider rate limits and backoff for large sheets.
Log/handle API errors (e.g., invalid DUNS or expired tokens).

🩹 Troubleshooting
401/403 from D&B:** Verify credentials/token; ensure correct environment and entitlements.
Missing Paydex path:** D&B responses vary by subscription/data availability—add guards (IF node) before mapping.
Rows not updating:* Confirm *Append or Update* is used and *Matching column** exactly matches your sheet header duns.
Filtered out rows:** Ensure Complete is truly empty (no spaces) for new items.

🧯 Customize further
Enrich additional fields (e.g., viability score, portfolio comparison, credit limits).
Add retry logic, batching, or scheduled triggers.
Push results to a CRM/DB or notify teams via Slack/Email.

📬 Contact
Need help customizing this (e.g., enriching more fields, normalizing responses, or bulk-processing large sheets)?

📧 [email protected]
🔗 https://www.linkedin.com/in/robert-breen-29429625/
🌐 https://ynteractive.com