Workflow n8n
Automatisation Google Sheets avec n8n : création et rapport de données
Ce workflow n8n a pour objectif d'automatiser la gestion des documents Google Sheets en créant de nouvelles feuilles et en envoyant des rapports par email. Il est particulièrement utile pour les équipes qui partagent régulièrement des fichiers avec des utilisateurs externes, permettant ainsi de gagner du temps et d'éviter les erreurs manuelles. Le processus commence par un déclencheur programmé qui active le workflow à intervalles réguliers. Ensuite, le workflow vérifie si des documents ont été partagés avec des utilisateurs externes grâce à un nœud de filtrage. Si des documents sont trouvés, il crée une nouvelle feuille dans Google Sheets pour y rassembler les données pertinentes. Les fichiers sont ensuite normalisés et traités en lot, ce qui permet de gérer efficacement les informations. Les données sont agrégées et formatées avant d'être envoyées par email à l'adresse spécifiée. Ce processus assure une communication fluide et rapide des informations essentielles. En utilisant ce workflow, les entreprises peuvent réduire le temps consacré à la gestion des documents et améliorer la collaboration, tout en s'assurant que les informations sont toujours à jour et accessibles.
Tags clés :Google Sheetsautomatisationworkflowemailgestion de documents
Catégorie: Scheduled · Tags: Google Sheets, automatisation, workflow, email, gestion de documents❤ 0
Workflow n8n Google Sheets, email, gestion de documents : 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, email, gestion de documents : détail des nœuds
Inscris-toi pour voir l'intégralité du workflow
Inscription gratuite
S'inscrire gratuitementBesoin d'aide ?{
"meta": {
"instanceId": "408f9fb9940c3cb18ffdef0e0150fe342d6e655c3a9fac21f0f644e8bedabcd9",
"templateCredsSetupCompleted": true
},
"nodes": [
{
"id": "f8f5a571-c4de-469e-a182-faa60060d06b",
"name": "Has Shared with External Users",
"type": "n8n-nodes-base.filter",
"position": [
40,
-220
],
"parameters": {
"options": {},
"conditions": {
"options": {
"version": 2,
"leftValue": "",
"caseSensitive": true,
"typeValidation": "strict"
},
"combinator": "or",
"conditions": [
{
"id": "c72e9718-b50a-4c5f-8a26-7b3fda89e202",
"operator": {
"type": "boolean",
"operation": "true",
"singleValue": true
},
"leftValue": "={{ $json.shared && $json.permissions.some(item => item.emailAddress ? !item.emailAddress.endsWith('example.com') : false) }}",
"rightValue": ""
},
{
"id": "0479b4ae-fc0c-49c4-8813-6978ea55265a",
"operator": {
"type": "object",
"operation": "exists",
"singleValue": true
},
"leftValue": "={{ $json.permissions.find(item => item.type === 'anyone') }}",
"rightValue": ""
}
]
}
},
"typeVersion": 2.2
},
{
"id": "14b6d453-0403-476a-8537-cdeeace70115",
"name": "Create New Sheet",
"type": "n8n-nodes-base.googleSheets",
"position": [
-620,
-220
],
"parameters": {
"title": "=audit-{{ $now.format('yyyyMMdd') }}",
"options": {},
"operation": "create",
"documentId": {
"__rl": true,
"mode": "list",
"value": "1V2aiLhp3_nH7EBniMn7D0kFHg7-A5NjpDZXMhb4F5UI",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1V2aiLhp3_nH7EBniMn7D0kFHg7-A5NjpDZXMhb4F5UI/edit?usp=drivesdk",
"cachedResultName": "94. Gdrive Permissions Audit - Personal"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "XHvC7jIRR8A2TlUl",
"name": "Google Sheets account"
}
},
"typeVersion": 4.5,
"alwaysOutputData": true
},
{
"id": "394b91b3-0c70-40d5-8d48-4df6109780e7",
"name": "Normalise Fields",
"type": "n8n-nodes-base.set",
"position": [
1140,
-140
],
"parameters": {
"options": {},
"assignments": {
"assignments": [
{
"id": "1d2f091f-7740-47d1-9bf4-91cb620ffb1f",
"name": "file_id",
"type": "string",
"value": "={{ $('File Ref').item.json.id }}"
},
{
"id": "b7836ed5-7b14-436f-aa5b-be8a6c7f2957",
"name": "file_name",
"type": "string",
"value": "={{ $('File Ref').item.json.name }}"
},
{
"id": "b1d59c01-17d9-4d0b-b0f4-1593e47f968f",
"name": "type",
"type": "string",
"value": "={{ $json.type }}"
},
{
"id": "37f50a02-c780-49b3-ad8a-0d934566c770",
"name": "user_id",
"type": "string",
"value": "={{ $json.id }}"
},
{
"id": "e16c385f-2ad2-484b-99a4-9021f77b6875",
"name": "user",
"type": "string",
"value": "={{ $json.emailAddress || 'n/a' }}"
},
{
"id": "3c825d9e-494c-4500-b04d-d9577c0d5f44",
"name": "role",
"type": "string",
"value": "={{ $json.role }}"
}
]
}
},
"typeVersion": 3.4
},
{
"id": "74a7ca8b-3ad4-470e-8c4d-b2e3cb721c27",
"name": "For Each File",
"type": "n8n-nodes-base.splitInBatches",
"position": [
440,
-140
],
"parameters": {
"options": {}
},
"typeVersion": 3
},
{
"id": "da0e4e55-9ffa-4939-acf3-a743ade6b3eb",
"name": "File Ref",
"type": "n8n-nodes-base.noOp",
"position": [
620,
-140
],
"parameters": {},
"typeVersion": 1
},
{
"id": "26e0f66a-88d7-46df-94e5-127158c47191",
"name": "Permissions To Items",
"type": "n8n-nodes-base.splitOut",
"position": [
780,
-140
],
"parameters": {
"options": {},
"fieldToSplitOut": "permissions"
},
"typeVersion": 1
},
{
"id": "5ed23aa6-1d9f-486c-ab56-4cb1144cdba9",
"name": "Aggregate",
"type": "n8n-nodes-base.aggregate",
"position": [
1320,
-60
],
"parameters": {
"options": {},
"aggregate": "aggregateAllItemData"
},
"typeVersion": 1
},
{
"id": "b7308c98-b50a-42ee-80ae-5a4beea0a654",
"name": "Flatten Rows",
"type": "n8n-nodes-base.set",
"position": [
1600,
-280
],
"parameters": {
"options": {},
"assignments": {
"assignments": [
{
"id": "c23193c9-b348-493a-9a7b-fd737cfb656f",
"name": "=rows",
"type": "array",
"value": "={{\n$input.all().flatMap(item => item.json.data)\n}}"
}
]
}
},
"executeOnce": true,
"typeVersion": 3.4
},
{
"id": "d18606d0-501e-4f2b-9456-a60497dd5574",
"name": "Rows to Items",
"type": "n8n-nodes-base.splitOut",
"position": [
1800,
-280
],
"parameters": {
"options": {},
"fieldToSplitOut": "rows"
},
"typeVersion": 1
},
{
"id": "66daa856-b047-4396-8b64-29346bdb08a0",
"name": "Send Email Report (Execute Once)",
"type": "n8n-nodes-base.gmail",
"position": [
2200,
-280
],
"webhookId": "39eabb13-1a20-412f-bf61-d3c40d875f76",
"parameters": {
"sendTo": "jim@example.com",
"message": "=Hello,\nHere is the current Google Drive Permissions Audit for {{ $now.format('yyyy-MM-dd') }}.\n\nSee the full report here - [Audit Gsheet](https://docs.google.com/spreadsheets/d/{{ $('Create New Sheet').first().json.spreadsheetId}}/edit?gid={{ $('Create New Sheet').first().json.sheetId}})\n\n## Shared with Anyone (Public Link)\n{{\n$input.all().map(item => item.json)\n .filter(row => row.type === 'anyone')\n .map(row => `* ${row.file_name} ([link](https://docs.google.com/spreadsheets/d/${row.file_id}/edit?usp=sharing))`)\n .join('\\n')\n}}\n\n## Shared with External Users (By Invite)\n{{\n$input.all().map(item => item.json)\n .filter(row => row.type == 'user')\n .map(row => `* ${row.file_name} ([link](https://docs.google.com/spreadsheets/d/${row.file_id}/edit?usp=sharing))`)\n .join('\\n')\n}}\n\nPlease review if permissions for these documents need to be updated.\n\nBest regards,\nN8N Gdrive Permissions Audit Workflow",
"options": {
"appendAttribution": true
},
"subject": "=GDrive Audit for {{ $now.format('yyyy-MM-dd') }}",
"emailType": "text"
},
"credentials": {
"gmailOAuth2": {
"id": "Sf5Gfl9NiFTNXFWb",
"name": "Gmail account"
}
},
"executeOnce": true,
"typeVersion": 2.1
},
{
"id": "41c2e73e-17cf-4d31-99fe-9c8c3b3d1a97",
"name": "Get Recently Active Documents",
"type": "n8n-nodes-base.googleDrive",
"position": [
-200,
-220
],
"parameters": {
"filter": {
"driveId": {
"mode": "list",
"value": "My Drive"
},
"fileTypes": [
"application/vnd.google-apps.document",
"application/vnd.google-apps.spreadsheet",
"application/vnd.google-apps.presentation"
],
"whatToSearch": "all"
},
"options": {
"fields": [
"permissions",
"shared",
"name",
"id",
"kind",
"mimeType"
]
},
"resource": "fileFolder",
"queryString": "=modifiedTime > '{{ $now.minus({ 'days': 1 })}}' and trashed = false",
"searchMethod": "query"
},
"credentials": {
"googleDriveOAuth2Api": {
"id": "yOwz41gMQclOadgu",
"name": "Google Drive account"
}
},
"typeVersion": 3
},
{
"id": "68d83b74-be18-4b2e-8422-2fc9ec6a4b90",
"name": "Sticky Note",
"type": "n8n-nodes-base.stickyNote",
"position": [
-980,
-500
],
"parameters": {
"color": 7,
"width": 600,
"height": 520,
"content": "## 1. Scheduled Trigger to Audit Everyday\n[Read more about the Scheduled Trigger node](https://docs.n8n.io/integrations/builtin/core-nodes/n8n-nodes-base.scheduletrigger)\n\nThe Scheduled Trigger is used to automate this workflow at a frequency which meets your data access auditing requirements. Here we've set it to run everyday and for each run a new Google Sheet is created to capture the results of the audit.\n\nCheck out the example Sheet here: https://docs.google.com/spreadsheets/d/1V2aiLhp3_nH7EBniMn7D0kFHg7-A5NjpDZXMhb4F5UI/edit?gid=503992967"
},
"typeVersion": 1
},
{
"id": "c5416a4f-4fae-405d-ac41-35193349d16f",
"name": "Schedule Trigger",
"type": "n8n-nodes-base.scheduleTrigger",
"position": [
-860,
-220
],
"parameters": {
"rule": {
"interval": [
{
"triggerAtHour": 6
}
]
}
},
"typeVersion": 1.2
},
{
"id": "d3009d45-9a5d-445f-ad99-745f28b9f705",
"name": "Sticky Note1",
"type": "n8n-nodes-base.stickyNote",
"position": [
-360,
-500
],
"parameters": {
"color": 7,
"width": 680,
"height": 520,
"content": "## 2. Identify Documents with Possible Access Control Risks\n[Learn more about Gdrive node](https://docs.n8n.io/integrations/builtin/app-nodes/n8n-nodes-base.googledrive)\n\nFile Sharing is powerful in Google Drive but we may grant excess permissions or visibility out of habit or to overcome access challenges. Though sometimes justified, often we forget to go back and reduce the permission scopes once the access has served its purpose.\n\nThis workflow fetches recently modified documents and takes note of the current permissions assigned to them. Those which are set to allow for anyone with a link or shared with external users can be flagged for review."
},
"typeVersion": 1
},
{
"id": "dff3abeb-7ae1-4038-8a05-75bf7630b63e",
"name": "Sticky Note2",
"type": "n8n-nodes-base.stickyNote",
"position": [
340,
-320
],
"parameters": {
"color": 7,
"width": 1160,
"height": 500,
"content": "## 3. Aggregate Results into Rows\n[Read more about the Split Out node](https://docs.n8n.io/integrations/builtin/core-nodes/n8n-nodes-base.splitout)\n\nWith our list, we just need to convert them to rows which we can add to our audit sheet.\nWe can use a few of n8n's data transformation nodes to complete this task."
},
"typeVersion": 1
},
{
"id": "c88f5d67-9712-4f08-bd2f-7ea9056b8640",
"name": "Sticky Note3",
"type": "n8n-nodes-base.stickyNote",
"position": [
1520,
-480
],
"parameters": {
"color": 7,
"width": 880,
"height": 460,
"content": "## 4. Logs Results and Send Audit Report via Email\n[Read more about the Gmail node](https://docs.n8n.io/integrations/builtin/app-nodes/n8n-nodes-base.gmail/)\n\nFinally, we'll log the identified documents to our google sheet and send a report via email.\nAlternatively, you may send to other security observability software or your security team."
},
"typeVersion": 1
},
{
"id": "c9ef29d8-d126-4aff-96a9-26c79483bc16",
"name": "Filter Out Owner of Document",
"type": "n8n-nodes-base.filter",
"position": [
960,
-140
],
"parameters": {
"options": {},
"conditions": {
"options": {
"version": 2,
"leftValue": "",
"caseSensitive": true,
"typeValidation": "strict"
},
"combinator": "and",
"conditions": [
{
"id": "310d287a-cab3-4a94-8aa5-615a1fcb970a",
"operator": {
"type": "string",
"operation": "notEquals"
},
"leftValue": "={{ $json.role }}",
"rightValue": "owner"
}
]
}
},
"typeVersion": 2.2,
"alwaysOutputData": false
},
{
"id": "1185fbd0-7632-4ea9-8648-7fcba63d1565",
"name": "Append to New Sheet",
"type": "n8n-nodes-base.googleSheets",
"position": [
2000,
-280
],
"parameters": {
"columns": {
"value": {},
"schema": [
{
"id": "file_id",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "file_id",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "file_name",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "file_name",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "type",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "type",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "user_id",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "user_id",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "user",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "user",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "role",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "role",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "autoMapInputData",
"matchingColumns": [],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {},
"operation": "append",
"sheetName": {
"__rl": true,
"mode": "id",
"value": "={{ $('Create New Sheet').first().json.sheetId }}"
},
"documentId": {
"__rl": true,
"mode": "id",
"value": "={{ $('Create New Sheet').first().json.spreadsheetId }}"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "XHvC7jIRR8A2TlUl",
"name": "Google Sheets account"
}
},
"typeVersion": 4.5
},
{
"id": "5755749e-16c1-43b0-ba14-76e593cd3404",
"name": "Sticky Note4",
"type": "n8n-nodes-base.stickyNote",
"position": [
-1480,
-1060
],
"parameters": {
"width": 440,
"height": 1260,
"content": "## Try It Out!\n### This n8n template reviews and audits recently active Google Drive files and reports on files with excessively open permissions. This shows how you can automate simple SecOp tasks for access control management.\n\nFile Sharing Permissions are routinely abused when access needs and scopes expand to many colleagues, clients and users. Often, granting excessively open permissions means you can get back to work rather than deal with numerous access request notifications. Whilst sometimes justified, the problem is that the permissions are rarely reverted to a safer setting at a later date when it is no longer needed.\n\nThis template serves to improve your security posture by giving frequent reminders of these open files so that they can be actioned and not forgotten about.\n\nSee example Audit Report here: [https://docs.google.com/spreadsheets/d/1V2aiLhp3_nH7EBniMn7D0kFHg7-A5NjpDZXMhb4F5UI/edit?gid=503992967](https://docs.google.com/spreadsheets/d/1V2aiLhp3_nH7EBniMn7D0kFHg7-A5NjpDZXMhb4F5UI/edit?gid=503992967)\n\n### How it works\n* A scheduled trigger runs everyday to generate a new audit report. A new sheet is created in a designated Google Sheets document to store the day's results.\n* The Google Drive node is used with Advanced Search params to fetch recently modified files for the user with each file result containing the current permission settings.\n* The results are filtered for those with publicly accessible \"anyone with link\" and sharing with external users via domain.\n* The results are then manipulated into rows so that we can append them to the Sheet we created earlier.\n* The audit Google Sheet is updated with the results and an audit report is sent to the user to action.\n\n### How to use\n* Set the scheduled trigger to a more appropriate interval which works for you or your organisation.\n* Consider using allowlists for organisations you frequently share with to reduce the number of false positives.\n* The results can be forwarded to other security or analytical products as required.\n\n### Requirements\n* Google Drive for Document Management\n* Google Sheet for Reports and Data Collection\n* Gmail to Email Reports\n\n### Customising the workflow\n* Not using Google? Apply the same approach using Microsoft Sharepoint or Dropbox.\n\n\n### Need Help?\nJoin the [Discord](https://discord.com/invite/XPKeKXeB7d) or ask in the [Forum](https://community.n8n.io/)!\n\nHappy Hacking!"
},
"typeVersion": 1
}
],
"pinData": {},
"connections": {
"File Ref": {
"main": [
[
{
"node": "Permissions To Items",
"type": "main",
"index": 0
}
]
]
},
"Aggregate": {
"main": [
[
{
"node": "For Each File",
"type": "main",
"index": 0
}
]
]
},
"Flatten Rows": {
"main": [
[
{
"node": "Rows to Items",
"type": "main",
"index": 0
}
]
]
},
"For Each File": {
"main": [
[
{
"node": "Flatten Rows",
"type": "main",
"index": 0
}
],
[
{
"node": "File Ref",
"type": "main",
"index": 0
}
]
]
},
"Rows to Items": {
"main": [
[
{
"node": "Append to New Sheet",
"type": "main",
"index": 0
}
]
]
},
"Create New Sheet": {
"main": [
[
{
"node": "Get Recently Active Documents",
"type": "main",
"index": 0
}
]
]
},
"Normalise Fields": {
"main": [
[
{
"node": "Aggregate",
"type": "main",
"index": 0
}
]
]
},
"Schedule Trigger": {
"main": [
[
{
"node": "Create New Sheet",
"type": "main",
"index": 0
}
]
]
},
"Append to New Sheet": {
"main": [
[
{
"node": "Send Email Report (Execute Once)",
"type": "main",
"index": 0
}
]
]
},
"Permissions To Items": {
"main": [
[
{
"node": "Filter Out Owner of Document",
"type": "main",
"index": 0
}
]
]
},
"Filter Out Owner of Document": {
"main": [
[
{
"node": "Normalise Fields",
"type": "main",
"index": 0
}
]
]
},
"Get Recently Active Documents": {
"main": [
[
{
"node": "Has Shared with External Users",
"type": "main",
"index": 0
}
]
]
},
"Has Shared with External Users": {
"main": [
[
{
"node": "For Each File",
"type": "main",
"index": 0
}
]
]
}
}
}Workflow n8n Google Sheets, email, gestion de documents : pour qui est ce workflow ?
Ce workflow s'adresse aux équipes de gestion de projet, aux responsables de la communication et aux professionnels qui utilisent Google Sheets pour collaborer. Il est idéal pour les entreprises de taille moyenne à grande qui cherchent à automatiser leurs processus de reporting et de partage de documents.
Workflow n8n Google Sheets, email, gestion de documents : problème résolu
Ce workflow résout le problème de la gestion manuelle des documents partagés. Il élimine les frustrations liées à la recherche de fichiers, à la création de rapports et à l'envoi d'emails manuels. Grâce à cette automatisation, les utilisateurs gagnent du temps et réduisent les risques d'erreurs, tout en s'assurant que les informations sont toujours à jour et correctement partagées avec les parties prenantes.
Workflow n8n Google Sheets, email, gestion de documents : étapes du workflow
Étape 1 : Le workflow est déclenché selon un calendrier défini.
- Étape 1 : Il vérifie les documents récemment actifs sur Google Drive pour identifier ceux partagés avec des utilisateurs externes.
- Étape 2 : Si des documents sont trouvés, une nouvelle feuille est créée dans Google Sheets.
- Étape 3 : Les fichiers sont normalisés et traités en lot pour extraire les informations pertinentes.
- Étape 4 : Les données sont agrégées et formatées avant d'être envoyées par email à l'adresse spécifiée.
- Étape 5 : Le workflow se termine en s'assurant que toutes les informations sont correctement partagées.
Workflow n8n Google Sheets, email, gestion de documents : guide de personnalisation
Pour personnaliser ce workflow, commencez par ajuster le déclencheur programmé selon vos besoins. Modifiez l'ID du document Google Sheets dans le nœud 'Create New Sheet' pour l'adapter à votre projet. Vous pouvez également personnaliser les conditions dans les nœuds de filtrage pour cibler des documents spécifiques. Enfin, n'oubliez pas de changer l'adresse email dans le nœud 'Send Email Report' pour vous assurer que les bonnes personnes reçoivent les rapports. Assurez-vous également de vérifier les autorisations d'accès aux documents pour éviter tout problème de partage.