PostgresからAIエージェントのディalog履歴をGoogle Sheetsにエクスポート

• •PostgreSQLデータベース接続情報
• •ターゲットAPIの認証情報が必要な場合あり
• •Google Sheets API認証情報

{
  "meta": {
    "instanceId": "f4f5d195bb2162a0972f737368404b18be694648d365d6c6771d7b4909d28167",
    "templateCredsSetupCompleted": true
  },
  "nodes": [
    {
      "id": "0a03e403-4a72-4382-b648-602f4bd6ef87",
      "name": "「Test workflow」クリック時",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        -1720,
        380
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "a421a477-bcee-460c-bc6a-8150a924fdd3",
      "name": "create_atカラムを追加",
      "type": "n8n-nodes-base.postgres",
      "position": [
        -1640,
        -100
      ],
      "parameters": {
        "query": "ALTER TABLE ONLY \"n8n_chat_histories\" ADD COLUMN \"created_at\" TIMESTAMP DEFAULT NOW();",
        "options": {},
        "operation": "executeQuery"
      },
      "credentials": {
        "postgres": {
          "id": "4E1voKcpNaIKnNJY",
          "name": "PG - Chat Memory POC"
        }
      },
      "typeVersion": 2.5
    },
    {
      "id": "0cb5189e-857d-49a1-a8e9-923d4f955383",
      "name": "sessionIdで会話を取得",
      "type": "n8n-nodes-base.postgres",
      "position": [
        380,
        480
      ],
      "parameters": {
        "table": {
          "__rl": true,
          "mode": "list",
          "value": "n8n_chat_histories",
          "cachedResultName": "n8n_chat_histories"
        },
        "where": {
          "values": [
            {
              "value": "={{ $('Loop Over Session IDs').item.json.session_id }}",
              "column": "session_id"
            }
          ]
        },
        "schema": {
          "__rl": true,
          "mode": "name",
          "value": "=public"
        },
        "options": {},
        "operation": "select",
        "returnAll": true
      },
      "credentials": {
        "postgres": {
          "id": "4E1voKcpNaIKnNJY",
          "name": "PG - Chat Memory POC"
        }
      },
      "executeOnce": false,
      "typeVersion": 2.5
    },
    {
      "id": "72d8c711-a3f9-4f95-b79a-a9190d2b4964",
      "name": "テンプレートシートを複製",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        -400,
        600
      ],
      "parameters": {
        "url": "=https://sheets.googleapis.com/v4/spreadsheets/{{ $('Clear Sheet Content').params.documentId.value }}/sheets/0:copyTo",
        "method": "POST",
        "options": {},
        "sendBody": true,
        "authentication": "predefinedCredentialType",
        "bodyParameters": {
          "parameters": [
            {
              "name": "destinationSpreadsheetId",
              "value": "={{ $('Clear Sheet Content').params.documentId.value }}"
            }
          ]
        },
        "nodeCredentialType": "googleSheetsOAuth2Api"
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "ufBkeygvc1l17m5N",
          "name": "Baptiste AS - Google Sheets account"
        }
      },
      "typeVersion": 4.1
    },
    {
      "id": "940fae1e-1ab7-4211-8d9f-fe5b9274e5e5",
      "name": "シートの内容をクリア",
      "type": "n8n-nodes-base.googleSheets",
      "onError": "continueErrorOutput",
      "position": [
        -760,
        460
      ],
      "parameters": {
        "clear": "specificRange",
        "range": "A2:C10000",
        "operation": "clear",
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": "={{ $json.session_id }}"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "14bKI5J0h18Nv48jbe1IXpZWma6EtqYLFWnpKoCB5Bgc",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/14bKI5J0h18Nv48jbe1IXpZWma6EtqYLFWnpKoCB5Bgc/edit?usp=drivesdk",
          "cachedResultName": "Conversation logs"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "ufBkeygvc1l17m5N",
          "name": "Baptiste AS - Google Sheets account"
        }
      },
      "typeVersion": 4.5
    },
    {
      "id": "b16e85bc-a84b-4da0-85da-568a1c981e02",
      "name": "シート名を変更",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        -200,
        600
      ],
      "parameters": {
        "url": "=https://sheets.googleapis.com/v4/spreadsheets/{{ $('Clear Sheet Content').params.documentId.value }}:batchUpdate",
        "method": "POST",
        "options": {},
        "jsonBody": "={\n  \"requests\": [{\n    \"updateSheetProperties\": {\n      \"properties\": {\n          \"sheetId\": {{ $json.sheetId }},\n          \"title\": \"{{ $('Clear Sheet Content').item.json.session_id }}\",\n          \"hidden\": false\n      },\n      \"fields\": \"title, hidden\"\n    }\n  }]\n}",
        "sendBody": true,
        "specifyBody": "json",
        "authentication": "predefinedCredentialType",
        "nodeCredentialType": "googleSheetsOAuth2Api"
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "ufBkeygvc1l17m5N",
          "name": "Baptiste AS - Google Sheets account"
        }
      },
      "typeVersion": 4.1
    },
    {
      "id": "63ac37f4-1f80-4e5a-9af4-ec2e05cbaccb",
      "name": "スケジュールトリガー",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        -1720,
        560
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "triggerAtHour": 12
            }
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "81cfed36-ee79-408f-8bad-0147a8acb0b3",
      "name": "付箋",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1740,
        -480
      ],
      "parameters": {
        "color": 4,
        "width": 320,
        "height": 540,
        "content": "## Add a datetime column\nIf not already done, add a `created_at` column to store the datetime of the message\n\n\n👉 Ideally, do it before your first interaction, otherwise all your previous conversations will be set as the time of the execution of this node.\n\n💡 **Replace** the name of your chat memory before executing the request\n\nThe table schema will at least contain the following fields: `session_id`, `message.content`, `message.type`, and `created_at`"
      },
      "typeVersion": 1
    },
    {
      "id": "cecbaa6a-5d8b-4704-b249-bcd336875773",
      "name": "session_idを設定",
      "type": "n8n-nodes-base.set",
      "position": [
        -20,
        600
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "3cc4ae1d-1693-4b30-9cbf-83fbb220813d",
              "name": "session_id",
              "type": "string",
              "value": "={{ $('Clear Sheet Content').first().json.session_id }}"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "1f8b387b-52d0-4023-9d4b-4dd61272fe82",
      "name": "Postgres - セッションIDを取得",
      "type": "n8n-nodes-base.postgres",
      "position": [
        -1380,
        420
      ],
      "parameters": {
        "query": "select distinct(session_id) from n8n_chat_histories",
        "options": {},
        "operation": "executeQuery"
      },
      "credentials": {
        "postgres": {
          "id": "4E1voKcpNaIKnNJY",
          "name": "PG - Chat Memory POC"
        }
      },
      "typeVersion": 2.5
    },
    {
      "id": "2ab2fc24-1c19-4c08-b20f-121bf22c6f9c",
      "name": "セッションIDでループ処理",
      "type": "n8n-nodes-base.splitInBatches",
      "position": [
        -1060,
        420
      ],
      "parameters": {
        "options": {}
      },
      "typeVersion": 3
    },
    {
      "id": "46763fb1-bf18-44a4-82b8-26e09325f159",
      "name": "会話を追加",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        560,
        640
      ],
      "parameters": {
        "columns": {
          "value": {
            "Who": "={{ $json.message.type }}",
            "Date": "={{ $json.created_at.toDateTime().format('yyyy-MM-dd hh:mm:ss') }}",
            "Message": "={{ $json.message.content }}"
          },
          "schema": [
            {
              "id": "Who",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Who",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Message",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Message",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Date",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Date",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": []
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": "={{ $('Loop Over Session IDs').item.json.session_id }}"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "14bKI5J0h18Nv48jbe1IXpZWma6EtqYLFWnpKoCB5Bgc",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/14bKI5J0h18Nv48jbe1IXpZWma6EtqYLFWnpKoCB5Bgc/edit?usp=drivesdk",
          "cachedResultName": "Conversation logs"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "ufBkeygvc1l17m5N",
          "name": "Baptiste AS - Google Sheets account"
        }
      },
      "typeVersion": 4.5
    },
    {
      "id": "4b711779-938d-4a51-96dc-a3a629cfdcb3",
      "name": "付箋2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1400,
        -480
      ],
      "parameters": {
        "color": 4,
        "width": 360,
        "height": 540,
        "content": "## For Supabase users\nAlthough in theory you could use Supabase native nodes, I found it easier to use PG. It's also more sustainable in the long term.\n\n### Get your Supabase credentials\n1. Go to your Supabase project\n2. Click on Connect\n3. Go to the \"Transaction pooler\" section and click on \"View parameters\"\n4. Copy/paste the parameters as new PG credentials in your n8n instance.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "2389393d-3e62-4349-a1cd-819d2b010f29",
      "name": "付箋3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1460,
        140
      ],
      "parameters": {
        "color": 7,
        "width": 280,
        "height": 720,
        "content": "## Get all sessions \nIn this node, we execute a SQL query that collects all sessionIDs.\n\n**Replace** the name of your chat memory before executing the request"
      },
      "typeVersion": 1
    },
    {
      "id": "eb323ee1-7afb-421d-86f4-02846782fb3e",
      "name": "付箋4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -500,
        -620
      ],
      "parameters": {
        "color": 6,
        "width": 440,
        "height": 700,
        "content": "## Important - How it works\n- Each time this workflow is executed, all conversations are cleared and replaced in order to always have up to date data.\n- The reason is that the sessionID can be overridden in the AI Agent, with a value more permanent (e.g a userID). This way, the conversation can continue over several sessions."
      },
      "typeVersion": 1
    },
    {
      "id": "3ca21e1a-1c1c-4064-9e06-540aef692291",
      "name": "付箋5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1160,
        140
      ],
      "parameters": {
        "color": 7,
        "width": 280,
        "height": 720,
        "content": "## Loop over each session\nThis node iterates over each session and add it in a separate sheet"
      },
      "typeVersion": 1
    },
    {
      "id": "135eb659-31d5-4760-af09-938c3913bb6c",
      "name": "付箋6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1000,
        -480
      ],
      "parameters": {
        "color": 4,
        "width": 360,
        "height": 540,
        "content": "## Google Sheets template\nhttps://docs.google.com/spreadsheets/d/14bKI5J0h18Nv48jbe1IXpZWma6EtqYLFWnpKoCB5Bgc/edit?usp=sharing"
      },
      "typeVersion": 1
    },
    {
      "id": "6c329217-3f99-40bd-8ff5-57b2266f4012",
      "name": "付箋7",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1780,
        -620
      ],
      "parameters": {
        "color": 4,
        "width": 1240,
        "height": 700,
        "content": "# Setup\n### 👉 Make sure to double check these steps before launching this workflow for the first time"
      },
      "typeVersion": 1
    },
    {
      "id": "b06b11b7-e0c7-491c-b7ce-4e321187663c",
      "name": "付箋8",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -860,
        140
      ],
      "parameters": {
        "color": 7,
        "width": 340,
        "height": 720,
        "content": "## Clear Google Sheet content\nThis will clear each Google Sheets data in order to have up to date data.\n\nError path: the error path is actually normal, it happens if the `session_id` doesn't already exist.\n\n**TODO**\nReplace the Google Sheets (document) with your own Google Sheets (see Setup if needed)"
      },
      "typeVersion": 1
    },
    {
      "id": "495026a8-4b3a-4ad8-8bf4-120cfa039a63",
      "name": "付箋9",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -460,
        140
      ],
      "parameters": {
        "color": 7,
        "width": 640,
        "height": 720,
        "content": "## Create a new Google Sheet based on a template\nThis part of the workflow prepares a fresh sheet for each session by copying a predefined template.\n\n**What it does:**\n**Clear Sheet Content**\nClears the sheet content named after the current `session_id`. This ensures any old data is removed before new data is written.\nNote: If the sheet doesn't exist yet, this step will fail silently: that's expected.\n\n**Duplicate Template Sheet**\n   Duplicates the first sheet (index 0) from your Google Sheets document. This acts as a blank template for each new session.\n\n**Rename Sheet**\n   Renames the newly duplicated sheet with the current `session_id` to clearly identify the session it belongs to.\n\n\n**TODO**\nUpdate the document ID with your own Google Sheets file before running the workflow."
      },
      "typeVersion": 1
    },
    {
      "id": "8eaba5f3-3455-43c0-bffa-6a0bbde39de7",
      "name": "付箋10",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        220,
        140
      ],
      "parameters": {
        "color": 7,
        "width": 620,
        "height": 720,
        "content": "## Store transcripts in Google Sheets\nThis section writes each session's messages into its corresponding sheet.\n\n**What it does:**\n* Appends new rows to the sheet named after the `session_id`.\n* Each row contains:\n  * **Who**: The speaker type (e.g. user, assistant), taken from `message.type`\n  * **Message**: The message content, from `message.content`\n  * **Date**: Timestamp of the message, formatted as `yyyy-MM-dd hh:mm:ss` using `created_at`\n\n\n**TODO**\nMake sure your sheet includes the following column headers in the first row: `Who`, `Message`, `Date`."
      },
      "typeVersion": 1
    },
    {
      "id": "6f802f26-e4b2-4787-bc0c-f3f800d79f74",
      "name": "付箋11",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1800,
        140
      ],
      "parameters": {
        "color": 7,
        "width": 280,
        "height": 720,
        "content": "## Triggers\nTest it manually, then, once validated, create a scheduler for it to run hourly, daily, weekly... or even create an external trigger. \nYour choice!"
      },
      "typeVersion": 1
    },
    {
      "id": "145ee4b6-d492-4533-83a0-2096aff97cca",
      "name": "付箋1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -2740,
        -620
      ],
      "parameters": {
        "color": 7,
        "width": 860,
        "height": 1480,
        "content": "# Store n8n AI Agent Memory Logs in Google Sheets\n\n## Overview\nThis n8n workflow retrieves AI agent chat memory logs stored in Postgres and pushes them to Google Sheets, creating one sheet per session. It’s useful for teams building chat-based products or agents and needing to review or analyze session logs in a collaborative format.\n\n## Who is it for\n* Anyone with an AI Agent in Production storing the conversation logs in Postgres (or Supabase) who wants to see transcript and have control\n* Product teams building AI agents or assistants.\n* Teams that want to centralize conversation history for analysis or support.\n* Anyone managing AI chat memory and needing to explore it in a spreadsheet.\n\n## Prerequisites\n* A Postgres database with a `n8n_chat_histories` table with an AI Agent connected to it. If you need an example, you can follow [this tutorial](https://www.youtube.com/watch?v=JjBofKJnYIU)\n* Once done, you need to run the Postgresql query to add the `created_at` column (see Setup > Add a datetime column)\n* Google Sheets access and OAuth credentials connected to n8n.\n* A Google Sheets document set up as a template (see below).\n\n## Google Sheets Template\nThis workflow expects a Google Sheets file where each session will be stored in its own tab. \nA basic tab layout is duplicated and renamed with the session ID.\n👉 [Use this template as a starting point](https://docs.google.com/spreadsheets/d/14bKI5J0h18Nv48jbe1IXpZWma6EtqYLFWnpKoCB5Bgc/edit?usp=sharing)\nNote: You can hide the template after the first tabs have been created\n\n## How it works\n1. **Trigger**\n   The workflow can be launched manually or on a schedule (e.g. daily at noon).\n\n2. **Retrieve sessions**\n   Runs a SQL query to get distinct `session_id` values from the `n8n_chat_histories` table.\n\n3. **Loop over sessions**\nFor each session:\n   * Clears the corresponding sheet (if it exists).\n   * Duplicates the template tab.\n   * Renames it with the current `session_id`.\n\n4. **Fetch messages**\n   Selects all messages linked to the session from Postgres.\n\n5. **Append to sheet**\n   Adds each message to the Google Sheet with columns:\n\n   * **Who**: speaker role (`user`, `assistant`, etc.)\n   * **Message**: text content\n   * **Date**: timestamp from `created_at`, formatted `yyyy-MM-dd hh:mm:ss`\n\n## Notes\n* The sheet is **cleared and rebuilt** each run to ensure logs are up-to-date.\n* If a sheet for a session doesn’t exist, it will be created by duplicating the first tab (template)\n* You can group sessions under a persistent ID (like `user_id`) by overriding `session_id` in your memory config.\n* Works perfectly with Supabase by using PG credentials from the connection pooler.\n"
      },
      "typeVersion": 1
    }
  ],
  "pinData": {},
  "connections": {
    "b16e85bc-a84b-4da0-85da-568a1c981e02": {
      "main": [
        [
          {
            "node": "cecbaa6a-5d8b-4704-b249-bcd336875773",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "cecbaa6a-5d8b-4704-b249-bcd336875773": {
      "main": [
        [
          {
            "node": "940fae1e-1ab7-4211-8d9f-fe5b9274e5e5",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "63ac37f4-1f80-4e5a-9af4-ec2e05cbaccb": {
      "main": [
        [
          {
            "node": "1f8b387b-52d0-4023-9d4b-4dd61272fe82",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "46763fb1-bf18-44a4-82b8-26e09325f159": {
      "main": [
        [
          {
            "node": "2ab2fc24-1c19-4c08-b20f-121bf22c6f9c",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "940fae1e-1ab7-4211-8d9f-fe5b9274e5e5": {
      "main": [
        [
          {
            "node": "0cb5189e-857d-49a1-a8e9-923d4f955383",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "72d8c711-a3f9-4f95-b79a-a9190d2b4964",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "2ab2fc24-1c19-4c08-b20f-121bf22c6f9c": {
      "main": [
        [],
        [
          {
            "node": "940fae1e-1ab7-4211-8d9f-fe5b9274e5e5",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "72d8c711-a3f9-4f95-b79a-a9190d2b4964": {
      "main": [
        [
          {
            "node": "b16e85bc-a84b-4da0-85da-568a1c981e02",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "1f8b387b-52d0-4023-9d4b-4dd61272fe82": {
      "main": [
        [
          {
            "node": "2ab2fc24-1c19-4c08-b20f-121bf22c6f9c",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "0cb5189e-857d-49a1-a8e9-923d4f955383": {
      "main": [
        [
          {
            "node": "46763fb1-bf18-44a4-82b8-26e09325f159",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "0a03e403-4a72-4382-b648-602f4bd6ef87": {
      "main": [
        [
          {
            "node": "1f8b387b-52d0-4023-9d4b-4dd61272fe82",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}