Skip to content

Instantly share code, notes, and snippets.

@VanDalkvist

VanDalkvist/google-sheets-bot-integration.json

Created April 25, 2025 17:38
Show Gist options
  • Save VanDalkvist/56c76f597851ed15791aee8e2899cd3b to your computer and use it in GitHub Desktop.
Save VanDalkvist/56c76f597851ed15791aee8e2899cd3b to your computer and use it in GitHub Desktop.
Download ZIP
Raw
google-sheets-bot-integration.json
{
"nodes": [
{
"parameters": {
"modelId": {
"__rl": true,
"value": "gpt-3.5-turbo-0125",
"mode": "list",
"cachedResultName": "GPT-3.5-TURBO-0125"
},
"messages": {
"values": [
{
"content": "=Ты — ассистент, который придумывает заголовки для постов.\n\nВот текст поста:\n____\n{{$json.message.text}}\n____\n\nЕсли он:\n— пустой,\n— состоит только из ссылок, хештегов или бессмысленных фраз,\n— связан с «эстафетой» или призывом подхватить,\n\nверни `null`.\n\nЕсли в тексте есть смысл — сгенерируй короткий заголовок по сути.\n\nОтвет должен содержать только заголовок или `null`.\n"
}
]
},
"options": {
"maxTokens": 35,
"temperature": 0.4
}
},
"type": "@n8n/n8n-nodes-langchain.openAi",
"typeVersion": 1.8,
"position": [
440,
500
],
"id": "50d36027-9464-42e4-afa9-29a6b1e2167f",
"name": "OpenAI",
"credentials": {
"openAiApi": {
"id": "",
"name": "OpenAI account"
}
}
},
{
"parameters": {
"chatId": "={{ $('All').item.json.message.chat.id }}",
"text": "={{JSON.stringify($('Map to Sheets').item.json, null, 2)}}",
"additionalFields": {
"parse_mode": "HTML"
}
},
"type": "n8n-nodes-base.telegram",
"typeVersion": 1.2,
"position": [
1920,
280
],
"id": "387a0a0a-69cb-4ef6-9506-12304ee4b990",
"name": "Telegram",
"webhookId": "",
"credentials": {
"telegramApi": {
"id": "",
"name": "Gconf sheets workshop"
}
}
},
{
"parameters": {
"authentication": "serviceAccount",
"operation": "appendOrUpdate",
"documentId": {
"__rl": true,
"value": "1NWh6oSojigRRh4NKgBP5wCZnwcG6Yv4eG2LK4_H3D-c",
"mode": "list",
"cachedResultName": "Копия Дашборд сообщников",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1NWh6oSojigRRh4NKgBP5wCZnwcG6Yv4eG2LK4_H3D-c/edit?usp=drivesdk"
},
"sheetName": {
"__rl": true,
"value": "=week{{ $('All').item.json.weekName }}",
"mode": "name"
},
"columns": {
"mappingMode": "autoMapInputData",
"value": {},
"matchingColumns": [
"Ссылка"
],
"schema": [
{
"id": "Имя",
"displayName": "Имя",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "Пост",
"displayName": "Пост",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "Тип",
"displayName": "Тип",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "Сообщение",
"displayName": "Сообщение",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
},
{
"id": "Ссылка",
"displayName": "Ссылка",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true,
"removed": false
},
{
"id": "Дата",
"displayName": "Дата",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true
}
],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {}
},
"type": "n8n-nodes-base.googleSheets",
"typeVersion": 4.5,
"position": [
1700,
280
],
"id": "072a73ac-d49b-42f2-ba35-6c6881b06b45",
"name": "Google Sheets",
"credentials": {
"googleApi": {
"id": "",
"name": ""
}
}
},
{
"parameters": {
"updates": [
"message"
],
"additionalFields": {}
},
"name": "Prod",
"type": "n8n-nodes-base.telegramTrigger",
"typeVersion": 1,
"position": [
-660,
280
],
"id": "bc37b121-6444-4ade-8085-5ecb14872d82",
"webhookId": "",
"credentials": {
"telegramApi": {
"id": "",
"name": "Gconf sheets workshop"
}
}
},
{
"parameters": {
"assignments": {
"assignments": [
{
"id": "bbd3163c-4f32-4671-a9e5-880fe579538b",
"name": "debugChat",
"value": "=",
"type": "string"
}
]
},
"includeOtherFields": true,
"options": {}
},
"type": "n8n-nodes-base.set",
"typeVersion": 3.4,
"position": [
-440,
280
],
"id": "3c67e26a-ba14-44f6-97f2-a45805f676ab",
"name": "Vars"
},
{
"parameters": {
"rules": {
"values": [
{
"conditions": {
"options": {
"caseSensitive": true,
"leftValue": "",
"typeValidation": "strict",
"version": 2
},
"conditions": [
{
"leftValue": "={{$json.message.text}}",
"rightValue": "#пост",
"operator": {
"type": "string",
"operation": "contains"
},
"id": "a8b5c52e-2fb8-46e0-8d9e-4b6af820fca3"
}
],
"combinator": "and"
},
"renameOutput": true,
"outputKey": "post"
},
{
"conditions": {
"options": {
"caseSensitive": true,
"leftValue": "",
"typeValidation": "strict",
"version": 2
},
"conditions": [
{
"id": "6ade14e8-8015-4c1a-93bd-c00ddfa2ac2e",
"leftValue": "={{$json.message.text}}",
"rightValue": "#вопросы",
"operator": {
"type": "string",
"operation": "contains"
}
}
],
"combinator": "and"
},
"renameOutput": true,
"outputKey": "questions"
},
{
"conditions": {
"options": {
"caseSensitive": true,
"leftValue": "",
"typeValidation": "strict",
"version": 2
},
"conditions": [
{
"id": "88a40343-8432-4ea2-9099-38c955a12758",
"leftValue": "={{true}}",
"rightValue": "={{true}}",
"operator": {
"type": "boolean",
"operation": "true",
"singleValue": true
}
}
],
"combinator": "and"
},
"renameOutput": true,
"outputKey": "fallback"
}
]
},
"options": {}
},
"type": "n8n-nodes-base.switch",
"typeVersion": 3.2,
"position": [
-220,
480
],
"id": "e4b15333-f8a1-40d8-9a34-049b34cd4106",
"name": "Switch",
"alwaysOutputData": false
},
{
"parameters": {},
"type": "n8n-nodes-base.noOp",
"typeVersion": 1,
"position": [
0,
700
],
"id": "40e3e39a-cee3-477e-adae-9ca1c2b07185",
"name": "No Operation, do nothing"
},
{
"parameters": {
"authentication": "serviceAccount",
"documentId": {
"__rl": true,
"value": "1NWh6oSojigRRh4NKgBP5wCZnwcG6Yv4eG2LK4_H3D-c",
"mode": "list",
"cachedResultName": "Копия Дашборд сообщников",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1NWh6oSojigRRh4NKgBP5wCZnwcG6Yv4eG2LK4_H3D-c/edit?usp=drivesdk"
},
"sheetName": {
"__rl": true,
"value": 347214941,
"mode": "list",
"cachedResultName": "Настройки листов",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1NWh6oSojigRRh4NKgBP5wCZnwcG6Yv4eG2LK4_H3D-c/edit#gid=347214941"
},
"options": {}
},
"name": "Fetch Week Settings",
"type": "n8n-nodes-base.googleSheets",
"typeVersion": 4,
"position": [
-160,
-180
],
"id": "7eae8d6a-95c5-4afd-a46b-d5a25f7f897a",
"credentials": {
"googleApi": {
"id": "",
"name": ""
}
}
},
{
"parameters": {
"jsCode": "const now = new Date();\n\n// DEBUG — посмотреть что за дата\nconsole.log('NOW ISO:', now.toISOString());\n\nlet detectedWeek = 'Неизвестно';\n\nfor (const item of $input.all()) {\n const start = new Date(item.json.start);\n const end = new Date(item.json.end);\n\n console.log('start:', start.toISOString());\n console.log('end:', end.toISOString());\n\n if (now.getTime() >= start.getTime() && now.getTime() <= end.getTime()) {\n detectedWeek = item.json.week;\n break;\n }\n}\n\nreturn [\n {\n json: {\n weekName: detectedWeek\n }\n }\n];\n"
},
"name": "Detect Current Week",
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [
100,
-160
],
"id": "e645a294-1a85-459f-a44b-aab8b086ed74",
"executeOnce": false
},
{
"parameters": {
"assignments": {
"assignments": [
{
"id": "2c652186-8ba9-45d7-8e7e-816d45a4afb7",
"name": "title",
"value": "={{ $json.message.content }}",
"type": "string"
}
]
},
"options": {}
},
"type": "n8n-nodes-base.set",
"typeVersion": 3.4,
"position": [
820,
500
],
"id": "1c98a547-2b0e-45d8-9384-1bf2d38e81d7",
"name": "Title"
},
{
"parameters": {
"jsCode": "const results = [];\n\nfor (const item of $input.all()) {\n const message = item.json.message || {};\n const base = {\n ...item.json,\n postText: message.text || '',\n messageLink: `https://t.me/c/${message.chat.id.toString().replace('-100', '')}/${message.message_id}`,\n author: message.from?.username || '',\n username: [message.from?.first_name || '', message.from?.last_name || ''].join(' ').trim(),\n messageId: message.message_id,\n chatId: message.chat.id,\n postType: item.json.postType\n };\n\n const links = extractLinks(message);\n\n if (links.length === 0) {\n results.push({ json: { ...base, postLink: '' } });\n } else {\n for (const link of links) {\n results.push({ json: { ...base, postLink: link } });\n }\n }\n}\n\nreturn results;\n\nfunction extractLinks(message) {\n const links = [];\n\n // 1. Инлайн-ссылки: type === 'text_link'\n if (Array.isArray(message.entities)) {\n for (const entity of message.entities) {\n if (entity.type === 'text_link' && entity.url) {\n links.push(entity.url);\n }\n\n // 2. Явные ссылки (type === 'url'): нужно извлечь текст по offset и length\n if (entity.type === 'url' && message.text) {\n const url = message.text.substr(entity.offset, entity.length);\n links.push(url);\n }\n }\n }\n\n // 3. Резервное извлечение всех ссылок из текста\n const rawTextLinks = (message.text || '').match(/https?:\\/\\/[^\\s]+/g) || [];\n for (const l of rawTextLinks) {\n if (!links.includes(l)) links.push(l);\n }\n\n return links;\n}"
},
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [
240,
200
],
"id": "9510a143-be66-4143-91ee-b8e9c9e5d328",
"name": "Get fields"
},
{
"parameters": {
"mode": "combine",
"combineBy": "combineByPosition",
"options": {}
},
"name": "All",
"type": "n8n-nodes-base.merge",
"typeVersion": 3,
"position": [
1260,
280
],
"id": "18021e6c-9c6b-4d5e-9d59-dde36c6ad485"
},
{
"parameters": {
"assignments": {
"assignments": [
{
"id": "7768f381-9aa3-4421-9613-7a4510679308",
"name": "title",
"value": "={{ $json.title && $json.title !== 'null' ? $json.title : 'Нет названия' }}",
"type": "string"
}
]
},
"options": {}
},
"type": "n8n-nodes-base.set",
"typeVersion": 3.4,
"position": [
1040,
500
],
"id": "fb558a3f-8316-41be-b32e-0b62fbd7ffbd",
"name": "Clean title"
},
{
"parameters": {
"assignments": {
"assignments": [
{
"id": "2a86c876-07fa-49a4-9ac3-1f77510e9c52",
"name": "Имя",
"value": "={{ $json.username }}",
"type": "string"
},
{
"id": "fbee084a-3d5f-40f3-9bae-aed2482afcc9",
"name": "Пост",
"value": "={{ $json.title }}",
"type": "string"
},
{
"id": "9b4b1c5e-adf8-4f97-a842-98462171f461",
"name": "Тип",
"value": "={{ $json.postType }}",
"type": "string"
},
{
"id": "b6968639-0c7b-4d55-90db-f348c385dda1",
"name": "Сообщение",
"value": "={{ $json.messageLink }}",
"type": "string"
},
{
"id": "c21b4ec0-868e-476a-aced-344a188902ed",
"name": "Ссылка",
"value": "={{ $json.postLink }}",
"type": "string"
},
{
"id": "dc819f89-53d9-4bd0-b30f-124006e3921a",
"name": "Дата",
"value": "={{ DateTime.utc() }}",
"type": "string"
}
]
},
"options": {}
},
"type": "n8n-nodes-base.set",
"typeVersion": 3.4,
"position": [
1480,
280
],
"id": "3b407134-aec4-4ad3-b69c-3311a9896dc5",
"name": "Map to Sheets"
},
{
"parameters": {
"assignments": {
"assignments": [
{
"id": "bbd3163c-4f32-4671-a9e5-880fe579538b",
"name": "postType",
"value": "=Пост",
"type": "string"
}
]
},
"includeOtherFields": true,
"options": {}
},
"type": "n8n-nodes-base.set",
"typeVersion": 3.4,
"position": [
20,
200
],
"id": "abd75c8f-5e32-45b8-9cce-4165fa31b8c7",
"name": "type post"
},
{
"parameters": {
"assignments": {
"assignments": [
{
"id": "bbd3163c-4f32-4671-a9e5-880fe579538b",
"name": "postType",
"value": "=Вопросы",
"type": "string"
}
]
},
"includeOtherFields": true,
"options": {}
},
"type": "n8n-nodes-base.set",
"typeVersion": 3.4,
"position": [
20,
340
],
"id": "12d1c1c3-d0d5-4a34-90f5-dee6c0d18868",
"name": "type questions"
},
{
"parameters": {
"mode": "combine",
"combineBy": "combineAll",
"options": {}
},
"name": "Merge",
"type": "n8n-nodes-base.merge",
"typeVersion": 3,
"position": [
600,
20
],
"id": "5b7e36ee-7fe2-47b8-bdd3-86e8bcc61d4e"
},
{
"parameters": {
"content": "## Вычисляем вкладку недели\n",
"height": 320,
"width": 580
},
"type": "n8n-nodes-base.stickyNote",
"position": [
-260,
-320
],
"typeVersion": 1,
"id": "e1f229b4-8497-4524-8103-d004e8e06e56",
"name": "Sticky Note"
},
{
"parameters": {
"content": "## Подключение к google sheets\n\nКоворкинг по автоматизации | 27.03 \nhttps://t.me/c/2427462969/4/2273\n",
"width": 360
},
"type": "n8n-nodes-base.stickyNote",
"position": [
1160,
20
],
"typeVersion": 1,
"id": "ce355acd-73e0-46b2-ad2d-7e5d6c1fef6f",
"name": "Sticky Note1"
},
{
"parameters": {
"content": "### ошибка данных - идти в жпт + вспомнить какие данные хотим получить\n\nhttps://docs.n8n.io/code/builtin/current-node-input/",
"width": 510
},
"type": "n8n-nodes-base.stickyNote",
"position": [
-160,
20
],
"typeVersion": 1,
"id": "affa0790-8cdd-4778-ab22-7f7820370f5c",
"name": "Sticky Note3"
},
{
"parameters": {
"content": "### объединение всех переменных + надо дождаться всех веток",
"height": 80,
"width": 210
},
"type": "n8n-nodes-base.stickyNote",
"position": [
960,
320
],
"typeVersion": 1,
"id": "694f4767-22ba-463f-ad0d-772b4c986310",
"name": "Sticky Note2"
},
{
"parameters": {
"content": "### Можно пойти в жпт и попросить его создать промпт для создания заголовка. Спросить рекомендации, обработать крайние кейсы",
"width": 360
},
"type": "n8n-nodes-base.stickyNote",
"position": [
360,
640
],
"typeVersion": 1,
"id": "63b76fa5-b4fa-4886-bb5c-a121615a143b",
"name": "Sticky Note4"
},
{
"parameters": {
"content": "### готовый промпт и помощник для n8n и переменных\n\nhttps://chatgpt.com/g/g-Uiz5ukAUR-n8n-workflow-automation-optimize-workflows",
"height": 220,
"width": 430
},
"type": "n8n-nodes-base.stickyNote",
"position": [
-680,
660
],
"typeVersion": 1,
"id": "f2d7157c-c594-4050-bd0e-88a9ba9a0637",
"name": "Sticky Note5"
},
{
"parameters": {
"content": "### ааа! бот не работает\n\n## должен быть админом",
"height": 140,
"width": 310
},
"type": "n8n-nodes-base.stickyNote",
"position": [
-900,
460
],
"typeVersion": 1,
"id": "68a521f3-3d1e-44ac-9492-142a401430a7",
"name": "Sticky Note6"
}
],
"connections": {
"OpenAI": {
"main": [
[
{
"node": "Title",
"type": "main",
"index": 0
}
]
]
},
"Google Sheets": {
"main": [
[
{
"node": "Telegram",
"type": "main",
"index": 0
}
]
]
},
"Prod": {
"main": [
[
{
"node": "Vars",
"type": "main",
"index": 0
}
]
]
},
"Vars": {
"main": [
[
{
"node": "Switch",
"type": "main",
"index": 0
},
{
"node": "Fetch Week Settings",
"type": "main",
"index": 0
}
]
]
},
"Switch": {
"main": [
[
{
"node": "type post",
"type": "main",
"index": 0
}
],
[
{
"node": "type questions",
"type": "main",
"index": 0
},
{
"node": "OpenAI",
"type": "main",
"index": 0
}
],
[
{
"node": "No Operation, do nothing",
"type": "main",
"index": 0
}
]
]
},
"Fetch Week Settings": {
"main": [
[
{
"node": "Detect Current Week",
"type": "main",
"index": 0
}
]
]
},
"Detect Current Week": {
"main": [
[
{
"node": "Merge",
"type": "main",
"index": 0
}
]
]
},
"Title": {
"main": [
[
{
"node": "Clean title",
"type": "main",
"index": 0
}
]
]
},
"Get fields": {
"main": [
[
{
"node": "OpenAI",
"type": "main",
"index": 0
},
{
"node": "Merge",
"type": "main",
"index": 1
}
]
]
},
"All": {
"main": [
[
{
"node": "Map to Sheets",
"type": "main",
"index": 0
}
]
]
},
"Clean title": {
"main": [
[
{
"node": "All",
"type": "main",
"index": 1
}
]
]
},
"Map to Sheets": {
"main": [
[
{
"node": "Google Sheets",
"type": "main",
"index": 0
}
]
]
},
"type post": {
"main": [
[
{
"node": "Get fields",
"type": "main",
"index": 0
}
]
]
},
"type questions": {
"main": [
[
{
"node": "Get fields",
"type": "main",
"index": 0
}
]
]
},
"Merge": {
"main": [
[
{
"node": "All",
"type": "main",
"index": 0
}
]
]
}
},
"pinData": {},
"meta": {
"templateCredsSetupCompleted": true,
"instanceId": ""
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment