Streamline your communication team’s workflow by automating Gmail alerts to update Google Sheets using the Google Sheets API. This integration allows you to capture important emails, log data, and send real-time notifications via Pushover. Requires 3 accounts: Google Sheets OAuth, Gmail O Auth2, and Pushover API. Save hours weekly on manual updates and ensure no critical messages slip through the cracks!
Categories
APIs
Credentials
Copy or download to import into your n8n instance
{
"meta": {
"instanceId": "d01584c137acc6debff205dab2295feffaded6fa97a8e6a16d6a41090f7caf2c",
"templateCredsSetupCompleted": true
},
"nodes": [
{
"id": "376b4f9c-7bf1-40a1-88a3-11bbdd1824b8",
"name": "Process Email and Extract",
"type": "n8n-nodes-base.code",
"position": [
464,
176
],
"parameters": {
"jsCode": "// ... (Code from \"Check Device and Notify\" node)\n\n// Group rows by MAC\nconst latestByMac = {};\n\nfor (const item of items) { // 'items' here represents all rows fetched from your Google Sheet\n const mac = item.json.mac;\n const time = new Date(item.json.timestampISO); // Converts the timestamp ISO string to a Date object\n\n if (!mac || isNaN(time)) continue; // Skips if MAC is missing or time is invalid\n\n // This is the crucial part for finding the LATEST status\n if (!latestByMac[mac] || new Date(latestByMac[mac].json.timestampISO) < time) {\n latestByMac[mac] = item; // If this MAC is new, or the current 'item' is newer than what's stored, update it\n }\n}\n\n// Filter the latest per MAC that are still disconnected and have passed their 30-minute threshold\nconst overdueAlerts = Object.values(latestByMac).filter(item => {\n const status = item.json.status; // Get the status of the LATEST entry for this MAC\n const timestampISO = new Date(item.json.timestampISO); // Original event time of the latest entry\n const checkAfter = new Date(item.json.checkAfter); // 30 mins after original event time of the latest entry\n const alertSent = item.json.alertSent;\n\n // This check assumes 'checkAfter' means 30 minutes from timestampISO has passed,\n // and we want to trigger if that condition is met internally, irrespective of 'now'.\n const isOverdueFinal = !isNaN(checkAfter.getTime());\n\n const notAlerted = !(alertSent === true || alertSent === \"true\");\n\nreturn (status === 'disconnected' || status === 'isolated') && isOverdueFinal && notAlerted;\n});\n\nreturn overdueAlerts;"
},
"typeVersion": 2
},
{
"id": "6808c535-be1a-4bce-8a55-17e4878b2c3b",
"name": "Append Row in Sheet",
"type": "n8n-nodes-base.googleSheets",
"position": [
896,
176
],
"parameters": {
"columns": {
"value": {
"mac": "={{ $json.mac }}",
"name": "={{ $json.name }}",
"type": "={{ $json.type }}",
"rowId": "={{ $json.rowId }}",
"status": "={{ $json.status }}",
"category": "={{ $json.category }}",
"severity": "={{ $json.severity }}",
"alertSent": "={{ $json.alertSent }}",
"timestamp": "={{ $json.timestamp }}",
"checkAfter": "={{ $json.checkAfter }}",
"timestampISO": "={{ $json.timestampISO }}",
"timeStampFormated": "={{ $json.timestampFormatted }}"
},
"schema": [
{
"id": "rowId",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "rowId",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "timestamp",
"type": "string",
"display": true,
"required": false,
"displayName": "timestamp",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "timestampISO",
"type": "string",
"display": true,
"required": false,
"displayName": "timestampISO",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "category",
"type": "string",
"display": true,
"required": false,
"displayName": "category",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "severity",
"type": "string",
"display": true,
"required": false,
"displayName": "severity",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "mac",
"type": "string",
"display": true,
"required": false,
"displayName": "mac",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "name",
"type": "string",
"display": true,
"required": false,
"displayName": "name",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "type",
"type": "string",
"display": true,
"required": false,
"displayName": "type",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "status",
"type": "string",
"display": true,
"required": false,
"displayName": "status",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "checkAfter",
"type": "string",
"display": true,
"required": false,
"displayName": "checkAfter",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "alertSent",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "alertSent",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "timeStampFormated",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "timeStampFormated",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {},
"operation": "append",
"sheetName": {
"__rl": true,
"mode": "list",
"value": "YOUR_SHEET_GID_HERE",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_GOOGLE_SHEET_ID_HERE/edit#gid=YOUR_SHEET_GID_HERE",
"cachedResultName": "YOUR_SHEET_NAME_HERE"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "YOUR_GOOGLE_SHEET_ID_HERE",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_GOOGLE_SHEET_ID_HERE/edit?usp=drivesdk",
"cachedResultName": "YOUR_SHEET_NAME_HERE"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "9YLBXhozEprX4MN3",
"name": "Google Sheets account"
}
},
"typeVersion": 4.6
},
{
"id": "bc91b08b-924a-4c0f-8e8b-d1d69d218a33",
"name": "Check Every 5 minutes",
"type": "n8n-nodes-base.scheduleTrigger",
"position": [
64,
800
],
"parameters": {
"rule": {
"interval": [
{
"field": "minutes"
}
]
}
},
"typeVersion": 1.2
},
{
"id": "f35f5433-0aa4-41ba-9665-a749daae3a5a",
"name": "Get Row(s) in Sheet",
"type": "n8n-nodes-base.googleSheets",
"position": [
288,
800
],
"parameters": {
"options": {},
"sheetName": {
"__rl": true,
"mode": "list",
"value": "YOUR_SHEET_GID_HERE",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_GOOGLE_SHEET_ID_HERE/edit#gid=YOUR_SHEET_GID_HERE",
"cachedResultName": "YOUR_SHEET_NAME_HERE"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "YOUR_GOOGLE_SHEET_ID_HERE",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_GOOGLE_SHEET_ID_HERE/edit?usp=drivesdk",
"cachedResultName": "YOUR_SHEET_NAME_HERE"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "9YLBXhozEprX4MN3",
"name": "Google Sheets account"
}
},
"typeVersion": 4.6,
"alwaysOutputData": true
},
{
"id": "6db058b2-c4d5-4875-b536-ac14285cacf3",
"name": "Check Device and Notify",
"type": "n8n-nodes-base.code",
"position": [
512,
800
],
"parameters": {
"jsCode": "const latestByMac = {};\n\nfor (const item of items) {\n const mac = item.json.mac;\n const time = new Date(item.json.timestampISO);\n\n if (!mac || isNaN(time)) continue;\n\n if (!latestByMac[mac] || new Date(latestByMac[mac].json.timestampISO) < time) {\n latestByMac[mac] = item;\n }\n}\n\n// Adjust to Philippine Time (UTC+8)\nconst now = new Date();\nconst nowLocal = new Date(now.getTime() + (8 * 60 * 60 * 1000));\n\nconst overdueAlerts = Object.values(latestByMac).filter(item => {\n const status = String(item.json.status || \"\").toLowerCase();\n const checkAfter = new Date(item.json.checkAfter);\n const alertSent = String(item.json.alertSent).toLowerCase();\n\n const isOverdue = !isNaN(checkAfter.getTime()) && nowLocal >= checkAfter;\n const notAlerted = alertSent !== \"true\" && alertSent !== \"yes\" && alertSent !== \"1\";\n\n console.log(\"DEBUG\", {\n mac: item.json.mac,\n status,\n nowLocal: nowLocal.toISOString(),\n checkAfter: item.json.checkAfter,\n isOverdue,\n notAlerted,\n alertSentRaw: item.json.alertSent\n });\n\n return (status === \"disconnected\" || status === \"isolated\") && isOverdue && notAlerted;\n});\n\nreturn overdueAlerts.length > 0 ? overdueAlerts : [{ json: { debug: \"No overdue devices found\", nowLocal: nowLocal.toISOString() } }];\n"
},
"typeVersion": 2
},
{
"id": "17b9806a-7ed4-4f49-bbdf-941277fe74ae",
"name": "Update Alert",
"type": "n8n-nodes-base.googleSheets",
"position": [
944,
784
],
"parameters": {
"columns": {
"value": {
"rowId": "={{ $json.rowId }}",
"alertSent": "=true"
},
"schema": [
{
"id": "rowId",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "rowId",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "timestamp",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "timestamp",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "timestampISO",
"type": "string",
"display": true,
"required": false,
"displayName": "timestampISO",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "category",
"type": "string",
"display": true,
"required": false,
"displayName": "category",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "severity",
"type": "string",
"display": true,
"required": false,
"displayName": "severity",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "mac",
"type": "string",
"display": true,
"required": false,
"displayName": "mac",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "name",
"type": "string",
"display": true,
"required": false,
"displayName": "name",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "type",
"type": "string",
"display": true,
"required": false,
"displayName": "type",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "status",
"type": "string",
"display": true,
"required": false,
"displayName": "status",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "checkAfter",
"type": "string",
"display": true,
"required": false,
"displayName": "checkAfter",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "alertSent",
"type": "string",
"display": true,
"required": false,
"displayName": "alertSent",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "timeStampFormated",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "timeStampFormated",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "row_number",
"type": "number",
"display": true,
"removed": true,
"readOnly": true,
"required": false,
"displayName": "row_number",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [
"rowId"
],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {},
"operation": "update",
"sheetName": {
"__rl": true,
"mode": "list",
"value": "YOUR_SHEET_GID_HERE",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_GOOGLE_SHEET_ID_HERE/edit#gid=YOUR_SHEET_GID_HERE",
"cachedResultName": "YOUR_SHEET_NAME_HERE"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "YOUR_GOOGLE_SHEET_ID_HERE",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_GOOGLE_SHEET_ID_HERE/edit?usp=drivesdk",
"cachedResultName": "YOUR_SHEET_NAME_HERE"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "9YLBXhozEprX4MN3",
"name": "Google Sheets account"
}
},
"typeVersion": 4.6
},
{
"id": "64082460-3cfb-4af2-82c5-dea73f6fe80a",
"name": "Receives Alert",
"type": "n8n-nodes-base.gmailTrigger",
"position": [
16,
176
],
"parameters": {
"simple": false,
"filters": {
"q": ""
},
"options": {},
"pollTimes": {
"item": [
{
"mode": "everyMinute"
}
]
}
},
"credentials": {
"gmailOAuth2": {
"id": "vWFuUCEId6LT6VOx",
"name": "Gmail account"
}
},
"typeVersion": 1.2
},
{
"id": "1a29a2dd-f37c-45e7-b0b3-9f21f70c7738",
"name": "Clear sheet",
"type": "n8n-nodes-base.googleSheets",
"position": [
144,
1328
],
"parameters": {
"clear": "specificRows",
"operation": "clear",
"sheetName": {
"__rl": true,
"mode": "list",
"value": "YOUR_SHEET_GID_HERE",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_GOOGLE_SHEET_ID_HERE/edit#gid=YOUR_SHEET_GID_HERE",
"cachedResultName": "YOUR_SHEET_NAME_HERE"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "YOUR_GOOGLE_SHEET_ID_HERE",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_GOOGLE_SHEET_ID_HERE/edit?usp=drivesdk",
"cachedResultName": "YOUR_SHEET_NAME_HERE"
},
"startIndex": 2,
"rowsToDelete": 500
},
"typeVersion": 4.6
},
{
"id": "a1d48f39-9906-4c5d-99d6-10e953afb397",
"name": "Clear Rows Every 2 days",
"type": "n8n-nodes-base.scheduleTrigger",
"position": [
-80,
1328
],
"parameters": {
"rule": {
"interval": [
{
"daysInterval": 2,
"triggerAtHour": 2
}
]
}
},
"typeVersion": 1.2
},
{
"id": "c66fc6f3-9923-45d5-89e7-0631c22d1bf1",
"name": "Alert User",
"type": "n8n-nodes-base.pushover",
"position": [
944,
992
],
"parameters": {
"message": "={{ $json.name }} is {{ $json.status }}\n\n{{ $json.timeStampFormated }}",
"userKey": "YOUR_PUSHOVER_USER_KEY_HERE",
"priority": 0,
"additionalFields": {}
},
"credentials": {
"pushoverApi": {
"id": "Wu4e8Ac1eQCrTdnE",
"name": "Pushover account"
}
},
"typeVersion": 1
},
{
"id": "77784bb9-3cae-4150-8346-e423b720a71d",
"name": "Sticky Note",
"type": "n8n-nodes-base.stickyNote",
"position": [
-560,
16
],
"parameters": {
"width": 464,
"height": 448,
"content": "## This workflow monitors device connection status from Omada Controller alerts.\n\nHow it works:\n- Gmail Trigger receives Omada alert emails\n- Code node parses the alert into structured fields\n- Data is logged into Google Sheets\n- Every 5 minutes, workflow checks if any device has been disconnected for 30+ minutes\n- Sends a Pushover notification for overdue disconnections\n- Clears old rows from the sheet every 2 days\n"
},
"typeVersion": 1
},
{
"id": "b2e2974a-5cf2-40df-9a76-64d3f3ff3534",
"name": "Sticky Note1",
"type": "n8n-nodes-base.stickyNote",
"position": [
0,
0
],
"parameters": {
"color": 7,
"width": 224,
"height": 144,
"content": "**Receives Alert**: \n\nGmail trigger for Omada Controller emails "
},
"typeVersion": 1
},
{
"id": "7a743261-e183-4302-9a0d-90c97433c3c5",
"name": "Sticky Note2",
"type": "n8n-nodes-base.stickyNote",
"position": [
400,
0
],
"parameters": {
"color": 7,
"width": 224,
"height": 144,
"content": "**Process Email and Extract**: \n\nExtracts timestamp, device name, MAC, severity, status "
},
"typeVersion": 1
},
{
"id": "75723c9f-062e-438b-974e-9d3154b5d140",
"name": "Sticky Note3",
"type": "n8n-nodes-base.stickyNote",
"position": [
816,
0
],
"parameters": {
"color": 7,
"width": 224,
"height": 144,
"content": "**Append Row in Sheet**: \n\nLogs data to Google Sheets "
},
"typeVersion": 1
},
{
"id": "b1e05077-659a-4774-a7b5-82586944fd07",
"name": "Sticky Note4",
"type": "n8n-nodes-base.stickyNote",
"position": [
96,
576
],
"parameters": {
"color": 7,
"width": 304,
"height": 144,
"content": "**Check Every 5 minutes**: \n\nScheduled polling to detect overdue disconnections "
},
"typeVersion": 1
},
{
"id": "29b5289b-d53c-44fe-85df-db475a119ebf",
"name": "Sticky Note5",
"type": "n8n-nodes-base.stickyNote",
"position": [
464,
576
],
"parameters": {
"color": 7,
"width": 224,
"height": 144,
"content": "**Check Device and Notify**: \n\nFilters devices disconnected >30 minutes \n"
},
"typeVersion": 1
},
{
"id": "7f0bf309-1e30-43ff-8d18-2ed136e5b82f",
"name": "Sticky Note6",
"type": "n8n-nodes-base.stickyNote",
"position": [
880,
1168
],
"parameters": {
"color": 7,
"width": 224,
"height": 144,
"content": "**Alert User**: \n\nSends push notification via Pushover. You can change this to any notifying app"
},
"typeVersion": 1
},
{
"id": "6c01c483-f76b-47f1-ac07-5ab2ef149541",
"name": "Sticky Note7",
"type": "n8n-nodes-base.stickyNote",
"position": [
880,
576
],
"parameters": {
"color": 7,
"width": 224,
"height": 144,
"content": "**Update Alert**: \n\nMarks row as alerted in Google Sheets \n"
},
"typeVersion": 1
},
{
"id": "639d6524-794d-49ea-bfef-2556d4e00d1e",
"name": "Sticky Note8",
"type": "n8n-nodes-base.stickyNote",
"position": [
-32,
1136
],
"parameters": {
"color": 7,
"width": 224,
"height": 144,
"content": "**Clear Rows Every 2 days**: \n\nCleans up old entries to keep data fresh \n"
},
"typeVersion": 1
}
],
"pinData": {},
"connections": {
"Receives Alert": {
"main": [
[
{
"node": "Process Email and Extract",
"type": "main",
"index": 0
}
]
]
},
"Get Row(s) in Sheet": {
"main": [
[
{
"node": "Check Device and Notify",
"type": "main",
"index": 0
}
]
]
},
"Check Every 5 minutes": {
"main": [
[
{
"node": "Get Row(s) in Sheet",
"type": "main",
"index": 0
}
]
]
},
"Check Device and Notify": {
"main": [
[
{
"node": "Update Alert",
"type": "main",
"index": 0
},
{
"node": "Alert User",
"type": "main",
"index": 0
}
]
]
},
"Clear Rows Every 2 days": {
"main": [
[
{
"node": "Clear sheet",
"type": "main",
"index": 0
}
]
]
},
"Process Email and Extract": {
"main": [
[
{
"node": "Append Row in Sheet",
"type": "main",
"index": 0
}
]
]
}
}
}Streamline your social media content creation using Google Gemini and OpenAI GPT. This automation generates tailored posts for Facebook, Instagram, and LinkedIn, perfect for social media managers looking to enhance engagement. Requires 9 accounts, including Google Palm API and OpenAI API. Save over 10 hours each week by automating content generation and posting, ensuring a consistent online presence.
Generate engaging videos using OpenAI GPT and Blotato, then automatically upload them to TikTok, YouTube, and Instagram. This workflow is ideal for data management professionals looking to streamline video marketing efforts. Requires 3 accounts: Google Sheets OAuth, OpenAI API, and Blotato API. Save hours of manual video creation and reach your audience effortlessly with high-quality content.
Automate video creation with Google Veo3 and seamlessly upload to YouTube using the Google Drive API and Google Sheets API. This workflow is perfect for data management professionals looking to streamline content production. Requires 3 accounts: Google Sheets OAuth, Google Drive OAuth, and OpenAI API key. Save hours of manual video creation and enhance your productivity with AI-driven insights.
Automate short-form video generation using OpenAI GPT, Flux, and ElevenLabs, then seamlessly upload to social networks. Perfect for team collaboration, this workflow requires 4 accounts, including OpenAI API Key and Google Drive OAuth. Save hours on video editing and content distribution while boosting engagement across platforms.
Configure credentials and update service-specific settings before executing the workflow. Review required credentials in the Technical Details section above.