Search Console結果をGoogle スプレッドシートにエクスポート

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

{
  "nodes": [
    {
      "id": "8e3f167d-cbeb-4f7f-a867-c356d2dca9d0",
      "name": "分割",
      "type": "n8n-nodes-base.splitOut",
      "position": [
        1580,
        240
      ],
      "parameters": {
        "options": {},
        "fieldToSplitOut": "rows"
      },
      "typeVersion": 1
    },
    {
      "id": "19370d12-f6de-44a1-91a6-da097abdf7de",
      "name": "フィールド編集",
      "type": "n8n-nodes-base.set",
      "position": [
        1780,
        240
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "7343c80f-37f3-4bb5-84d8-9f21f8a350cd",
              "name": "Keyword",
              "type": "string",
              "value": "={{ $json.keys[0] }}"
            },
            {
              "id": "436e7c8b-2df2-40a9-97af-597dc00cf143",
              "name": "clicks",
              "type": "number",
              "value": "={{ $json.clicks }}"
            },
            {
              "id": "5b4aaffe-391a-4c9d-8249-f447397a3f5d",
              "name": "impressions",
              "type": "number",
              "value": "={{ $json.impressions }}"
            },
            {
              "id": "33677237-57fe-48f4-aff8-72ae81b5f5a2",
              "name": "ctr",
              "type": "number",
              "value": "={{ $json.ctr }}"
            },
            {
              "id": "f961deee-d222-4df7-a7ff-b7286405e4a7",
              "name": "position",
              "type": "number",
              "value": "={{ $json.position }}"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "9eae4908-5266-439c-a66b-5679036234de",
      "name": "分割1",
      "type": "n8n-nodes-base.splitOut",
      "position": [
        1580,
        440
      ],
      "parameters": {
        "options": {},
        "fieldToSplitOut": "rows"
      },
      "typeVersion": 1
    },
    {
      "id": "b05926b1-507f-4531-a05c-a15e835ee82e",
      "name": "フィールド編集1",
      "type": "n8n-nodes-base.set",
      "position": [
        1780,
        440
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "7343c80f-37f3-4bb5-84d8-9f21f8a350cd",
              "name": "page",
              "type": "string",
              "value": "={{ $json.keys[0] }}"
            },
            {
              "id": "436e7c8b-2df2-40a9-97af-597dc00cf143",
              "name": "clicks",
              "type": "number",
              "value": "={{ $json.clicks }}"
            },
            {
              "id": "5b4aaffe-391a-4c9d-8249-f447397a3f5d",
              "name": "impressions",
              "type": "number",
              "value": "={{ $json.impressions }}"
            },
            {
              "id": "33677237-57fe-48f4-aff8-72ae81b5f5a2",
              "name": "ctr",
              "type": "number",
              "value": "={{ $json.ctr }}"
            },
            {
              "id": "f961deee-d222-4df7-a7ff-b7286405e4a7",
              "name": "position",
              "type": "number",
              "value": "={{ $json.position }}"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "42321587-2565-4a0a-9d9d-25cbfdeb9f49",
      "name": "分割2",
      "type": "n8n-nodes-base.splitOut",
      "position": [
        1580,
        620
      ],
      "parameters": {
        "options": {},
        "fieldToSplitOut": "rows"
      },
      "typeVersion": 1
    },
    {
      "id": "9e25eef9-daa4-47dd-b2cf-03cfebadb5c6",
      "name": "フィールド編集2",
      "type": "n8n-nodes-base.set",
      "position": [
        1780,
        620
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "7343c80f-37f3-4bb5-84d8-9f21f8a350cd",
              "name": "date",
              "type": "string",
              "value": "={{ $json.keys[0] }}"
            },
            {
              "id": "436e7c8b-2df2-40a9-97af-597dc00cf143",
              "name": "clicks",
              "type": "number",
              "value": "={{ $json.clicks }}"
            },
            {
              "id": "5b4aaffe-391a-4c9d-8249-f447397a3f5d",
              "name": "impressions",
              "type": "number",
              "value": "={{ $json.impressions }}"
            },
            {
              "id": "33677237-57fe-48f4-aff8-72ae81b5f5a2",
              "name": "ctr",
              "type": "number",
              "value": "={{ $json.ctr }}"
            },
            {
              "id": "f961deee-d222-4df7-a7ff-b7286405e4a7",
              "name": "position",
              "type": "number",
              "value": "={{ $json.position }}"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "e8f1ab65-9594-45e7-ba9e-7873bd53a107",
      "name": "日付",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        1360,
        620
      ],
      "parameters": {
        "url": "=https://www.googleapis.com/webmasters/v3/sites/sc-domain:{{$json.domain}}/searchAnalytics/query",
        "method": "POST",
        "options": {},
        "jsonBody": "={\n  \"startDate\": \"{{ $now.format('yyyy-MM-dd') }}\",\n  \"endDate\": \"{{ $now.minus($json.days, 'days').format('yyyy-MM-dd') }}\",\n  \"dimensions\": [\"date\"]\n}",
        "sendBody": true,
        "specifyBody": "json",
        "authentication": "predefinedCredentialType",
        "nodeCredentialType": "googleOAuth2Api"
      },
      "credentials": {
        "googleApi": {
          "id": "9vSHyulYjxYMr8MK",
          "name": "Service Account✅"
        },
        "httpHeaderAuth": {
          "id": "Ng5SZdTqwe74l2KO",
          "name": "Header Auth account ⚠️"
        },
        "googleOAuth2Api": {
          "id": "wuKNLprxCMuetOYN",
          "name": "Google account✅3"
        }
      },
      "typeVersion": 4.2
    },
    {
      "id": "d3bbf719-9524-4269-8c26-0eb7599add55",
      "name": "スケジュールトリガー",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        700,
        460
      ],
      "parameters": {
        "rule": {
          "interval": [
            {}
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "69cf781d-7ff5-4e2d-ad7d-505a5143710a",
      "name": "付箋",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1220,
        160
      ],
      "parameters": {
        "color": 4,
        "width": 1033,
        "height": 660,
        "content": ""
      },
      "typeVersion": 1
    },
    {
      "id": "b701bc62-07e7-4494-a674-560846783a29",
      "name": "付箋1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        0,
        100
      ],
      "parameters": {
        "color": 4,
        "width": 645,
        "height": 828,
        "content": "\n## Usage\n\n1. Make a copy of this google sheet https://docs.google.com/spreadsheets/d/10hSuGOOf14YvVY2Bw8WXUIpsyXO614l7qNEjkyVY_Qg/edit?usp=sharing\n\n2. Set your google service credentials and add these scopes `https://www.googleapis.com/auth/webmasters, https://www.googleapis.com/auth/webmasters.readonly, https://www.googleapis.com/auth/adwords`\n\n3. Replace the domains with your desired domains\n\n\n1. **Understanding the Workflow:**\n- **Nodes Overview:**\nThis workflow contains several nodes:\n- **Set your domain:** Sets the domain to be used in the queries.\n- **Schedule Trigger:** Starts the workflow based on a defined schedule.\n- **HttpRequest (query, page, date):** Fetches data from Google's Search Console API using specified dimensions and dates.\n- **Split Out (x3):** Splits the incoming JSON array into individual items for further processing.\n- **Edit Fields (x3):** Maps the outgoing data to specified fields, preparing it for insertion into Google Sheets.\n- **Google Sheets (x3):** Adds or updates entries in specified Google Sheets documents with the fetched data.\n\n- **Inputs and Outputs:**\n- Input: API response from Google Search Console regarding keywords, page data, and date data.\n- Output: Entries written to Google Sheets containing keyword data, clicks, impressions, CTR, and positions.\n\n2. **Setup Instructions:**\n- **Prerequisites:**\n- An n8n instance set up and running.\n- Active Google Account with access to Google Search Console and Google Sheets.\n- Google OAuth 2.0 credentials for API access.\n\n- **Step-by-Step Setup:**\n1. Open n8n and create a new workflow.\n2. Add the nodes as described in the JSON.\n3. Configure the **Google OAuth2** credentials in n8n to enable API access.\n4. Set your domain in the **Set your domain** node.\n5. Customize the Google Sheets document URLs to your personal sheets.\n6. Adjust the schedule in the **Schedule Trigger** node as per your requirements.\n7. Save the workflow.\n\n- **Configuration Options:**\n- You can customize the date ranges in the body of the **HttpRequest** nodes.\n- Adjust any fields in the **Edit Fields** nodes based on different data requirements.\n\n3. **Use Case Examples:**\n- Useful in tracking website performance over time using Search Console metrics.\n- Ideal for digital marketers, SEO specialists, and web analytics professionals.\n- Offers value in compiling performance reports for stakeholders or team reviews.\n\n4. **Running and Troubleshooting:**\n- **Running the Workflow:**\n- Trigger the workflow manually or wait for the schedule to run it automatically.\n\n- **Monitoring Execution:**\n- Check the execution logs in n8n's dashboard to ensure all nodes complete successfully.\n\n- **Common Issues:**\n- Invalid OAuth credentials – ensure credentials are set up correctly.\n- Incorrect Google Sheets URLs – double-check document links and permissions.\n- Scheduling conflicts – make sure the schedule set does not overlap with other workflows.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "07432897-f068-4371-9f88-d70340e2082a",
      "name": "付箋17",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1440,
        100
      ],
      "parameters": {
        "color": 4,
        "width": 503.60808870324274,
        "height": 80,
        "content": "# Search console REPORTS"
      },
      "typeVersion": 1
    },
    {
      "id": "092645b2-9e75-4ff0-8d33-4a3acadac789",
      "name": "ドメインを設定",
      "type": "n8n-nodes-base.set",
      "position": [
        980,
        460
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "6f74dee0-3789-433e-b60e-ed2a05202675",
              "name": "domain",
              "type": "string",
              "value": "funautomations.io"
            },
            {
              "id": "8c73135e-9d39-4f66-821d-7decb3c64085",
              "name": "days",
              "type": "number",
              "value": 30
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "0b04b552-e484-417b-9a7e-a90d477dd45a",
      "name": "クエリレポートを取得",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        1360,
        240
      ],
      "parameters": {
        "url": "=https://www.googleapis.com/webmasters/v3/sites/sc-domain:{{$json.domain}}/searchAnalytics/query",
        "method": "POST",
        "options": {},
        "jsonBody": "={\n  \"startDate\": \"{{ $now.format('yyyy-MM-dd') }}\",\n  \"endDate\": \"{{ $now.minus($json.days, 'days').format('yyyy-MM-dd') }}\",\n  \"dimensions\": [\"query\"]\n}",
        "sendBody": true,
        "specifyBody": "json",
        "authentication": "predefinedCredentialType",
        "nodeCredentialType": "googleOAuth2Api"
      },
      "credentials": {
        "httpHeaderAuth": {
          "id": "Ng5SZdTqwe74l2KO",
          "name": "Header Auth account ⚠️"
        },
        "googleOAuth2Api": {
          "id": "SlPOQ6j86r5XbnxV",
          "name": "Oath account ✅5"
        }
      },
      "typeVersion": 4.2
    },
    {
      "id": "9f9f2be7-1301-4c91-8da1-86eab5725683",
      "name": "ページレポートを取得",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        1360,
        440
      ],
      "parameters": {
        "url": "=https://www.googleapis.com/webmasters/v3/sites/sc-domain:{{$json.domain}}/searchAnalytics/query",
        "method": "POST",
        "options": {},
        "jsonBody": "={\n  \"startDate\": \"{{ $now.format('yyyy-MM-dd') }}\",\n  \"endDate\": \"{{ $now.minus($json.days, 'days').format('yyyy-MM-dd') }}\",\n  \"dimensions\": [\"page\"]\n}",
        "sendBody": true,
        "specifyBody": "json",
        "authentication": "predefinedCredentialType",
        "nodeCredentialType": "googleOAuth2Api"
      },
      "credentials": {
        "httpHeaderAuth": {
          "id": "Ng5SZdTqwe74l2KO",
          "name": "Header Auth account ⚠️"
        },
        "googleOAuth2Api": {
          "id": "wuKNLprxCMuetOYN",
          "name": "Google account✅3"
        }
      },
      "typeVersion": 4.2
    },
    {
      "id": "737f802f-4629-41f2-9b21-4a98e92d6433",
      "name": "付箋2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        880,
        380
      ],
      "parameters": {
        "color": 4,
        "width": 300,
        "height": 300,
        "content": "## Set Domain and the days frequency"
      },
      "typeVersion": 1
    },
    {
      "id": "f8f62dde-1529-4d3a-a030-aa952496652d",
      "name": "クエリをSheetsに更新",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        1980,
        240
      ],
      "parameters": {
        "columns": {
          "value": {},
          "schema": [
            {
              "id": "Keyword",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Keyword",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "clicks",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "clicks",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "impressions",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "impressions",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "ctr",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "ctr",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "position",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "position",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "autoMapInputData",
          "matchingColumns": [
            "Keyword"
          ]
        },
        "options": {},
        "operation": "appendOrUpdate",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 996986484,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/10hSuGOOf14YvVY2Bw8WXUIpsyXO614l7qNEjkyVY_Qg/edit#gid=996986484",
          "cachedResultName": "Query"
        },
        "documentId": {
          "__rl": true,
          "mode": "url",
          "value": "https://docs.google.com/spreadsheets/d/10hSuGOOf14YvVY2Bw8WXUIpsyXO614l7qNEjkyVY_Qg/edit?usp=sharing"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "ZAI2a6Qt80kX5a9s",
          "name": "Google Sheets account✅ "
        }
      },
      "typeVersion": 4.5
    },
    {
      "id": "299c4fa9-fb7e-4c85-a8a5-3cea53ba7136",
      "name": "ページをSheetsに更新",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        2000,
        440
      ],
      "parameters": {
        "columns": {
          "value": {},
          "schema": [
            {
              "id": "page",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "page",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "clicks",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "clicks",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "impressions",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "impressions",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "ctr",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "ctr",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "position",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "position",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "autoMapInputData",
          "matchingColumns": [
            "page"
          ]
        },
        "options": {},
        "operation": "appendOrUpdate",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/10hSuGOOf14YvVY2Bw8WXUIpsyXO614l7qNEjkyVY_Qg/edit#gid=0",
          "cachedResultName": "PAGES"
        },
        "documentId": {
          "__rl": true,
          "mode": "url",
          "value": "https://docs.google.com/spreadsheets/d/10hSuGOOf14YvVY2Bw8WXUIpsyXO614l7qNEjkyVY_Qg/edit?usp=sharing"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "ZAI2a6Qt80kX5a9s",
          "name": "Google Sheets account✅ "
        }
      },
      "typeVersion": 4.5
    },
    {
      "id": "4cc4197a-7ee5-4cd8-ade7-80bca911a3cf",
      "name": "日付レポートをSheetsに更新",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        2000,
        620
      ],
      "parameters": {
        "columns": {
          "value": {},
          "schema": [
            {
              "id": "date",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "date",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "clicks",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "clicks",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "impressions",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "impressions",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "ctr",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "ctr",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "position",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "position",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "autoMapInputData",
          "matchingColumns": [
            "date"
          ]
        },
        "options": {},
        "operation": "appendOrUpdate",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 1823079319,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/10hSuGOOf14YvVY2Bw8WXUIpsyXO614l7qNEjkyVY_Qg/edit#gid=1823079319",
          "cachedResultName": "Dates"
        },
        "documentId": {
          "__rl": true,
          "mode": "url",
          "value": "https://docs.google.com/spreadsheets/d/10hSuGOOf14YvVY2Bw8WXUIpsyXO614l7qNEjkyVY_Qg/edit?usp=sharing"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "ZAI2a6Qt80kX5a9s",
          "name": "Google Sheets account✅ "
        }
      },
      "retryOnFail": true,
      "typeVersion": 4.5
    }
  ],
  "pinData": {},
  "connections": {
    "e8f1ab65-9594-45e7-ba9e-7873bd53a107": {
      "main": [
        [
          {
            "node": "42321587-2565-4a0a-9d9d-25cbfdeb9f49",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "8e3f167d-cbeb-4f7f-a867-c356d2dca9d0": {
      "main": [
        [
          {
            "node": "19370d12-f6de-44a1-91a6-da097abdf7de",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "9eae4908-5266-439c-a66b-5679036234de": {
      "main": [
        [
          {
            "node": "b05926b1-507f-4531-a05c-a15e835ee82e",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "42321587-2565-4a0a-9d9d-25cbfdeb9f49": {
      "main": [
        [
          {
            "node": "9e25eef9-daa4-47dd-b2cf-03cfebadb5c6",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "19370d12-f6de-44a1-91a6-da097abdf7de": {
      "main": [
        [
          {
            "node": "f8f62dde-1529-4d3a-a030-aa952496652d",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "b05926b1-507f-4531-a05c-a15e835ee82e": {
      "main": [
        [
          {
            "node": "299c4fa9-fb7e-4c85-a8a5-3cea53ba7136",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "9e25eef9-daa4-47dd-b2cf-03cfebadb5c6": {
      "main": [
        [
          {
            "node": "4cc4197a-7ee5-4cd8-ade7-80bca911a3cf",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "9f9f2be7-1301-4c91-8da1-86eab5725683": {
      "main": [
        [
          {
            "node": "9eae4908-5266-439c-a66b-5679036234de",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "092645b2-9e75-4ff0-8d33-4a3acadac789": {
      "main": [
        [
          {
            "node": "0b04b552-e484-417b-9a7e-a90d477dd45a",
            "type": "main",
            "index": 0
          },
          {
            "node": "9f9f2be7-1301-4c91-8da1-86eab5725683",
            "type": "main",
            "index": 0
          },
          {
            "node": "e8f1ab65-9594-45e7-ba9e-7873bd53a107",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "0b04b552-e484-417b-9a7e-a90d477dd45a": {
      "main": [
        [
          {
            "node": "8e3f167d-cbeb-4f7f-a867-c356d2dca9d0",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "d3bbf719-9524-4269-8c26-0eb7599add55": {
      "main": [
        [
          {
            "node": "092645b2-9e75-4ff0-8d33-4a3acadac789",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}