Google テーブルを Dun & Bradstreet データブロックで充実させる

• •ターゲットAPIの認証情報が必要な場合あり
• •Google Sheets API認証情報

{
  "meta": {
    "instanceId": "ad0113c344ee237399e44e9f11798b05baeb83a6196d514a9ae9d2ad71c3b5c9",
    "templateCredsSetupCompleted": true
  },
  "nodes": [
    {
      "id": "1c18aaa3-e056-4739-9fe1-90c8842eebc3",
      "name": "「ワークフロー実行」クリック時",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        -1296,
        6848
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "d2723780-444a-454b-9619-14f3e4d97976",
      "name": "付箋55",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1360,
        6432
      ],
      "parameters": {
        "color": 7,
        "width": 1824,
        "height": 1808,
        "content": "### Enrich DUNS rows in Google Sheets with Dun & Bradstreet Data Blocks (Bearer Token → Fetch → Upsert)\n\nAutomate company enrichment in **Google Sheets** using **Dun & Bradstreet**. This workflow reads DUNS from a sheet, gets a D&B **Bearer token** (Basic Auth → `/v3/token`), calls **Data Blocks** (`/v1/data/duns/...`), pulls **Paydex** from the response, and **append-or-updates** the sheet. A **Filter** node skips rows already marked `Complete`.\n\n"
      },
      "typeVersion": 1
    },
    {
      "id": "d68788c4-a0aa-49f7-b4ad-8f01d9dc64c0",
      "name": "付箋9",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1808,
        6432
      ],
      "parameters": {
        "width": 400,
        "height": 1792,
        "content": "## Setup Instructions\n\n### 1️⃣ Connect Google Sheets (OAuth2)\n1. In **n8n → Credentials → New → Google Sheets (OAuth2)**  \n2. Sign in with your Google account and grant access  \n3. In your workflow’s **Google Sheets** nodes, select this credential  \n4. Use/prepare a sheet with at least: **duns**, **paydex**, **Complete**\n\n---\n\n### 2️⃣ Get a D&B Bearer Token (Basic Auth → `/v3/token`)\n1. Add (or enable) an **HTTP Request** node named `Get Bearer Token1`  \n2. Configure:  \n   - **Authentication:** Basic Auth (your **D&B username** & **password**)  \n   - **Method:** `POST`  \n   - **URL:** `https://plus.dnb.com/v3/token`  \n   - **Body Parameters:** `grant_type = client_credentials`  \n   - **Headers:** `Accept = application/json`  \n3. **Execute** — the response includes `access_token` you will reference later\n\n> ⚠️ **Security:** Don’t hardcode tokens. Always fetch the token and reference it dynamically.\n\n---\n\n### 3️⃣ Call D&B Data Blocks (use dynamic Bearer token)\n1. Add an **HTTP Request** node named `Data Blocks`  \n2. Configure:  \n   - **Authentication:** None  \n   - **Method:** `GET`  \n   - **URL:**  \n     ```\n     https://plus.dnb.com/v1/data/duns/{{ $json.duns }}?blockIDs=paymentinsight_L4_v1&tradeUp=hq&customerReference=customer%20reference%20text&orderReason=6332\n     ```\n     > `{{ $json.duns }}` resolves per row from the Google Sheets node.  \n   - **Headers:**  \n     - `Accept = application/json`  \n     - `Authorization = Bearer {{$node[\"Get Bearer Token1\"].json[\"access_token\"]}}`\n       > This pulls the token from the token node’s output.\n\n---\n\n### 4️⃣ Map Paydex and Upsert to Google Sheets\n1. **Set** node (`Edit Fields1`) → create a field:  \n   - **Name:** `Paydex`  \n   - **Value (Number):**  \n     ```\n     {{$json.organization.businessTrading[0].summary[0].paydexScoreHistory[0].paydexScore}}\n     ```\n2. **Google Sheets → Append or Update**  \n   - **Operation:** `Append or Update`  \n   - **Matching column:** `duns`  \n   - **Columns mapping:**  \n     - `duns = {{ $('Get row(s) in sheet2').item.json.duns }}`  \n     - `paydex = {{ $json.Paydex }}`  \n     - `Complete = Yes`\n\n---\n\n\n## 📬 Contact  \nNeed help customizing this (e.g., enriching more fields, normalizing responses, or bulk-processing large sheets)?\n\n- 📧 **robert@ynteractive.com**  \n- 🔗 **https://www.linkedin.com/in/robert-breen-29429625/**  \n- 🌐 **https://ynteractive.com**\n"
      },
      "typeVersion": 1
    },
    {
      "id": "281b764f-8c47-4593-b1de-c71a9f32bd39",
      "name": "付箋61",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -944,
        7296
      ],
      "parameters": {
        "color": 3,
        "width": 352,
        "height": 704,
        "content": "### 📑 Fetch Company Report from D&B (PDF/JSON)\n\n1. Add a new **HTTP Request** node and name it `D&B Report`  \n2. Configure it as follows:  \n   - **Authentication:** Header Auth (use your D&B credential in n8n)  \n   - **Method:** `GET`  \n   - **URL:**  \n     ```\n     https://plus.dnb.com/v1/reports/duns/804735132?productId=birstd&inLanguage=en-US&reportFormat=PDF&orderReason=6332&tradeUp=hq&customerReference=customer%20reference%20text\n     ```  \n     > Replace the sample DUNS (`804735132`) with a dynamic reference like `{{ $json.duns }}` if pulling from input data.  \n3. Under **Headers**, add:  \n   - `Accept = application/json`  \n4. Execute the node — you’ll receive a **company report** from D&B (JSON metadata, or PDF depending on `reportFormat`).  \n\n✅ You can route the output to **Google Drive**, **email**, or save it to a database for compliance and reporting.  \n"
      },
      "typeVersion": 1
    },
    {
      "id": "5a5a82d6-a4d3-4719-80b0-c6897f7bef60",
      "name": "付箋63",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -464,
        6640
      ],
      "parameters": {
        "color": 3,
        "width": 368,
        "height": 560,
        "content": "### 🔑 Set up D&B Auth HTTP Request node\n\n1. Add a new **HTTP Request** node in your workflow  \n2. Configure it as follows:  \n   - **Authentication:** Basic Auth (use your D&B **username** and **password**)  \n   - **Method:** `POST`  \n   - **URL:** `https://plus.dnb.com/v3/token`  \n3. Under **Body Parameters**, add:  \n   - `grant_type = client_credentials`  \n4. Under **Headers**, add:  \n   - `Accept = application/json`  \n5. Execute the node — the response will include an **access_token**  \n6. Use this token in downstream requests with:  \n   - `Authorization: Bearer {{$json[\"access_token\"]}}`  \n"
      },
      "typeVersion": 1
    },
    {
      "id": "6ba9acfa-d68f-462d-aa09-03ca25195997",
      "name": "企業情報取得",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -1248,
        7200
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1h0D5C1oJElBUsz9Dv4AllEUU5eIAR4ae4VTXH1XcwBM/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1h0D5C1oJElBUsz9Dv4AllEUU5eIAR4ae4VTXH1XcwBM",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1h0D5C1oJElBUsz9Dv4AllEUU5eIAR4ae4VTXH1XcwBM/edit?usp=drivesdk",
          "cachedResultName": "Temp White Oak Companies"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "voFr7tCFLpy1XFnc",
          "name": "Google Sheets account 4"
        }
      },
      "typeVersion": 4.7
    },
    {
      "id": "85fec4ba-5945-4271-a521-6dadfb41c850",
      "name": "新規行のみ",
      "type": "n8n-nodes-base.filter",
      "position": [
        -1232,
        7776
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "96912772-2442-4ca0-a546-653a014cabed",
              "operator": {
                "type": "string",
                "operation": "empty",
                "singleValue": true
              },
              "leftValue": "={{ $json.Complete }}",
              "rightValue": ""
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "2071d117-207e-44db-9156-5dd0459ea372",
      "name": "D&B情報",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        -816,
        7856
      ],
      "parameters": {
        "url": "=https://plus.dnb.com/v1/data/duns/{{ $json.duns }}?blockIDs=paymentinsight_L4_v1&tradeUp=hq&customerReference=customer%20reference%20text&orderReason=6332 ",
        "options": {},
        "authentication": "headerAuth",
        "headerParametersUi": {
          "parameter": [
            {
              "name": "Accept",
              "value": "application/json"
            }
          ]
        }
      },
      "credentials": {},
      "typeVersion": 1
    },
    {
      "id": "894623ee-e36b-49ce-8ee4-f73d94f55ac0",
      "name": "スコア保持",
      "type": "n8n-nodes-base.set",
      "position": [
        -352,
        7696
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "1f789f32-f98e-47a0-9402-8a61ccf5fd78",
              "name": "Paydex",
              "type": "number",
              "value": "={{ $json.organization.businessTrading[0].summary[0].paydexScoreHistory[0].paydexScore }}"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "8300bc14-9714-4b3d-af51-b416e4e8c7bc",
      "name": "g-sheetsに追加",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        176,
        7840
      ],
      "parameters": {
        "columns": {
          "value": {
            "duns": "={{ $('Get Companies').item.json.duns }}",
            "paydex": "={{ $json.Paydex }}",
            "Complete": "Yes"
          },
          "schema": [
            {
              "id": "Company",
              "type": "string",
              "display": true,
              "removed": true,
              "required": false,
              "displayName": "Company",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "duns",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "duns",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "paydex",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "paydex",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Complete",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Complete",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [
            "duns"
          ],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "appendOrUpdate",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1h0D5C1oJElBUsz9Dv4AllEUU5eIAR4ae4VTXH1XcwBM/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1wlzNuN16KfD72owWceXmTvVulGBplMBv3c1DlsSayHI",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1wlzNuN16KfD72owWceXmTvVulGBplMBv3c1DlsSayHI/edit?usp=drivesdk",
          "cachedResultName": "d&b Companies"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "HlBW2puZbuCCq8jJ",
          "name": "Google Sheets account 3"
        }
      },
      "typeVersion": 4.7
    },
    {
      "id": "8576657b-7488-425a-a68a-65b68b2b2d7d",
      "name": "付箋64",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        128,
        7216
      ],
      "parameters": {
        "color": 3,
        "width": 224,
        "height": 752,
        "content": "### 3) Connect Google Sheets (OAuth2)\n\n1. In **n8n → Credentials → New → Google Sheets (OAuth2)**  \n2. Sign in with your Google account and **grant access**.  \n3. In your workflow’s **Google Sheets** node (e.g., *Append or Update*), select this spreadsheet and tab:  \n   - **Spreadsheet URL:** https://docs.google.com/spreadsheets/d/1wlzNuN16KfD72owWceXmTvVulGBplMBv3c1DlsSayHI/edit?gid=0#gid=0  \n   - **Spreadsheet ID:** `1wlzNuN16KfD72owWceXmTvVulGBplMBv3c1DlsSayHI`  \n   - **Tab (gid):** `0` *(usually “Sheet1”)*  \n4. (Optional) Set **Operation** to **Append or Update** and choose a **Matching Column** (e.g., `duns`) to prevent duplicates.  \n"
      },
      "typeVersion": 1
    },
    {
      "id": "c1670672-3ce0-4f73-b4db-58beeb988e18",
      "name": "トークン取得1",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        -320,
        7088
      ],
      "parameters": {
        "url": "https://plus.dnb.com/v3/token",
        "options": {},
        "requestMethod": "POST",
        "authentication": "basicAuth",
        "bodyParametersUi": {
          "parameter": [
            {
              "name": "grant_type",
              "value": "client_credentials"
            }
          ]
        },
        "headerParametersUi": {
          "parameter": [
            {
              "name": "Content-Type",
              "value": "application/x-www-form-urlencoded"
            }
          ]
        }
      },
      "credentials": {},
      "typeVersion": 1
    }
  ],
  "pinData": {},
  "connections": {
    "2071d117-207e-44db-9156-5dd0459ea372": {
      "main": [
        [
          {
            "node": "894623ee-e36b-49ce-8ee4-f73d94f55ac0",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "894623ee-e36b-49ce-8ee4-f73d94f55ac0": {
      "main": [
        [
          {
            "node": "8300bc14-9714-4b3d-af51-b416e4e8c7bc",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "6ba9acfa-d68f-462d-aa09-03ca25195997": {
      "main": [
        [
          {
            "node": "85fec4ba-5945-4271-a521-6dadfb41c850",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "85fec4ba-5945-4271-a521-6dadfb41c850": {
      "main": [
        [
          {
            "node": "2071d117-207e-44db-9156-5dd0459ea372",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "1c18aaa3-e056-4739-9fe1-90c8842eebc3": {
      "main": [
        [
          {
            "node": "6ba9acfa-d68f-462d-aa09-03ca25195997",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}