Workflow n8n
Automatisation Google Sheets avec n8n : mise à jour des rapports
Ce workflow n8n a pour objectif d'automatiser la mise à jour de rapports dans Google Sheets à partir de données récupérées via des requêtes HTTP. Il est particulièrement utile pour les équipes marketing et les analystes de données qui souhaitent gagner du temps en évitant les mises à jour manuelles. Grâce à cette automatisation n8n, les utilisateurs peuvent facilement intégrer des données provenant de diverses sources et les organiser dans des feuilles de calcul, ce qui facilite l'analyse et le reporting. Le workflow commence par un déclencheur programmé qui active le processus à intervalles réguliers. Ensuite, plusieurs nœuds de type 'Split Out' sont utilisés pour diviser les données en segments gérables, permettant une manipulation plus précise des informations. Chaque segment est ensuite traité par des nœuds 'Edit Fields' pour ajuster les données avant de les envoyer vers Google Sheets. Les nœuds 'HTTP Request' sont utilisés pour récupérer des rapports de pages et des requêtes, tandis que les nœuds 'Google Sheets' mettent à jour les informations dans les feuilles de calcul. Ce processus garantit que les données sont toujours à jour et accessibles, réduisant ainsi le risque d'erreurs humaines et améliorant l'efficacité opérationnelle. En intégrant ce workflow, les entreprises peuvent optimiser leur gestion des données et améliorer la prise de décision basée sur des informations précises et à jour.
Tags clés :automatisationGoogle Sheetsworkflown8nreporting
Catégorie: Scheduled · Tags: automatisation, Google Sheets, workflow, n8n, reporting❤ 0
Workflow n8n Google Sheets, reporting : vue d'ensemble
Schéma des nœuds et connexions de ce workflow n8n, généré à partir du JSON n8n.
Workflow n8n Google Sheets, reporting : détail des nœuds
Inscris-toi pour voir l'intégralité du workflow
Inscription gratuite
S'inscrire gratuitementBesoin d'aide ?{
"nodes": [
{
"id": "8e3f167d-cbeb-4f7f-a867-c356d2dca9d0",
"name": "Split Out",
"type": "n8n-nodes-base.splitOut",
"position": [
1580,
240
],
"parameters": {
"options": {},
"fieldToSplitOut": "rows"
},
"typeVersion": 1
},
{
"id": "19370d12-f6de-44a1-91a6-da097abdf7de",
"name": "Edit Fields",
"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": "Split Out1",
"type": "n8n-nodes-base.splitOut",
"position": [
1580,
440
],
"parameters": {
"options": {},
"fieldToSplitOut": "rows"
},
"typeVersion": 1
},
{
"id": "b05926b1-507f-4531-a05c-a15e835ee82e",
"name": "Edit Fields1",
"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": "Split Out2",
"type": "n8n-nodes-base.splitOut",
"position": [
1580,
620
],
"parameters": {
"options": {},
"fieldToSplitOut": "rows"
},
"typeVersion": 1
},
{
"id": "9e25eef9-daa4-47dd-b2cf-03cfebadb5c6",
"name": "Edit Fields2",
"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": "date",
"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": "Schedule Trigger",
"type": "n8n-nodes-base.scheduleTrigger",
"position": [
700,
460
],
"parameters": {
"rule": {
"interval": [
{}
]
}
},
"typeVersion": 1.2
},
{
"id": "69cf781d-7ff5-4e2d-ad7d-505a5143710a",
"name": "Sticky Note",
"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": "Sticky Note1",
"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": "Sticky Note17",
"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": "Set your domain",
"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": "Get query Report",
"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": "Get Page Report",
"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": "Sticky Note2",
"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": "Update queries to 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": "Update Pages to 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": "Update date report to 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": {
"date": {
"main": [
[
{
"node": "Split Out2",
"type": "main",
"index": 0
}
]
]
},
"Split Out": {
"main": [
[
{
"node": "Edit Fields",
"type": "main",
"index": 0
}
]
]
},
"Split Out1": {
"main": [
[
{
"node": "Edit Fields1",
"type": "main",
"index": 0
}
]
]
},
"Split Out2": {
"main": [
[
{
"node": "Edit Fields2",
"type": "main",
"index": 0
}
]
]
},
"Edit Fields": {
"main": [
[
{
"node": "Update queries to Sheets",
"type": "main",
"index": 0
}
]
]
},
"Edit Fields1": {
"main": [
[
{
"node": "Update Pages to Sheets ",
"type": "main",
"index": 0
}
]
]
},
"Edit Fields2": {
"main": [
[
{
"node": "Update date report to sheets",
"type": "main",
"index": 0
}
]
]
},
"Get Page Report": {
"main": [
[
{
"node": "Split Out1",
"type": "main",
"index": 0
}
]
]
},
"Set your domain": {
"main": [
[
{
"node": "Get query Report",
"type": "main",
"index": 0
},
{
"node": "Get Page Report",
"type": "main",
"index": 0
},
{
"node": "date",
"type": "main",
"index": 0
}
]
]
},
"Get query Report": {
"main": [
[
{
"node": "Split Out",
"type": "main",
"index": 0
}
]
]
},
"Schedule Trigger": {
"main": [
[
{
"node": "Set your domain",
"type": "main",
"index": 0
}
]
]
}
}
}Workflow n8n Google Sheets, reporting : pour qui est ce workflow ?
Ce workflow s'adresse aux équipes marketing, aux analystes de données et aux PME qui souhaitent automatiser la mise à jour de rapports dans Google Sheets. Un niveau technique intermédiaire est recommandé pour la personnalisation et l'intégration des différents nœuds.
Workflow n8n Google Sheets, reporting : problème résolu
Ce workflow résout le problème de la mise à jour manuelle des rapports dans Google Sheets, qui peut être chronophage et sujet à des erreurs. En automatisant ce processus, les utilisateurs éliminent les frustrations liées à la gestion des données, réduisent le risque d'erreurs humaines et obtiennent des rapports toujours à jour. Cela permet également de libérer du temps pour se concentrer sur des analyses plus stratégiques.
Workflow n8n Google Sheets, reporting : étapes du workflow
Étape 1 : Le workflow est déclenché selon un calendrier défini par le nœud 'Schedule Trigger'.
- Étape 1 : Les données sont récupérées via le nœud 'HTTP Request' pour obtenir les rapports nécessaires.
- Étape 2 : Les données sont ensuite divisées en segments avec les nœuds 'Split Out', permettant une gestion plus efficace.
- Étape 3 : Chaque segment est traité par les nœuds 'Edit Fields' pour ajuster les informations selon les besoins.
- Étape 4 : Les données finales sont mises à jour dans Google Sheets à l'aide des nœuds 'Google Sheets', garantissant que toutes les informations sont correctement organisées et accessibles.
Workflow n8n Google Sheets, reporting : guide de personnalisation
Pour personnaliser ce workflow, commencez par ajuster le nœud 'Schedule Trigger' pour définir la fréquence de mise à jour souhaitée. Modifiez les URL dans les nœuds 'HTTP Request' pour pointer vers les sources de données pertinentes. Vous pouvez également adapter les nœuds 'Edit Fields' pour modifier la structure des données avant leur envoi. Assurez-vous que les identifiants de document et les noms de feuille dans les nœuds 'Google Sheets' correspondent à vos fichiers. Enfin, pour une sécurité accrue, envisagez d'ajouter des mécanismes d'authentification dans les nœuds HTTP.