Squareの売上集計レポートを自動のにGoogle スプシにインポート

• •ターゲットAPIの認証情報が必要な場合あり
• •Google Sheets API認証情報

{
  "meta": {
    "instanceId": "d6e2f2f655b1125bbcac14a4cac6d2e46c7a150e927f85fc96fdca1a6dc39e0e",
    "templateCredsSetupCompleted": true
  },
  "nodes": [
    {
      "id": "8943addf-613e-4169-a9e5-58debec9e076",
      "name": "Square店舗情報を取得",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        1040,
        540
      ],
      "parameters": {
        "url": "https://connect.squareup.com/v2/locations",
        "options": {},
        "sendHeaders": true,
        "authentication": "genericCredentialType",
        "genericAuthType": "httpHeaderAuth",
        "headerParameters": {
          "parameters": [
            {
              "name": "Content-Type",
              "value": "application/json"
            }
          ]
        }
      },
      "credentials": {
        "httpHeaderAuth": {
          "id": "n1GRrdbh899dbLYB",
          "name": "Square Header Auth"
        }
      },
      "typeVersion": 4.2
    },
    {
      "id": "7bebea87-6c2e-4a90-8ba5-d4912370bce5",
      "name": "店舗情報をリスト化",
      "type": "n8n-nodes-base.splitOut",
      "position": [
        1260,
        540
      ],
      "parameters": {
        "include": "selectedOtherFields",
        "options": {},
        "fieldToSplitOut": "locations",
        "fieldsToInclude": "id"
      },
      "typeVersion": 1
    },
    {
      "id": "ca074893-cb12-49eb-ac27-87f7468932c8",
      "name": "売上なし店舗を除外",
      "type": "n8n-nodes-base.if",
      "position": [
        1760,
        540
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "498f5fab-6930-4e89-9fbe-0d67671da8d2",
              "operator": {
                "type": "array",
                "operation": "notEmpty",
                "singleValue": true
              },
              "leftValue": "={{ $json.orders }}",
              "rightValue": ""
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "4431553a-1ddb-4789-abd6-2011a3f53efc",
      "name": "Squareから売上を取得",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        1520,
        540
      ],
      "parameters": {
        "url": "https://connect.squareup.com/v2/orders/search",
        "method": "POST",
        "options": {},
        "jsonBody": "={\n  \"location_ids\": [\"{{ $json.locations.id }}\"],\n  \"query\": {\n    \"filter\": {\n      \"state_filter\": {\n        \"states\": [\"COMPLETED\"]\n      },\n      \"date_time_filter\": {\n        \"created_at\": {\n          \"start_at\": \"{{ $('Schedule Trigger').item.json.timestamp.toDateTime().minus(1, 'days').format('yyyy-MM-dd') }}T00:00:00-05:00\",\n          \"end_at\": \"{{ $('Schedule Trigger').item.json.timestamp.toDateTime().minus(1, 'days').format('yyyy-MM-dd') }}T23:59:59-05:00\"\n        }\n      }\n    }\n  },\n  \"limit\": 1000,\n  \"return_entries\": false\n}",
        "sendBody": true,
        "sendHeaders": true,
        "specifyBody": "json",
        "authentication": "genericCredentialType",
        "genericAuthType": "httpHeaderAuth",
        "headerParameters": {
          "parameters": [
            {
              "name": "Content-Type",
              "value": "application/json"
            }
          ]
        }
      },
      "credentials": {
        "httpHeaderAuth": {
          "id": "n1GRrdbh899dbLYB",
          "name": "Square Header Auth"
        }
      },
      "typeVersion": 4.2
    },
    {
      "id": "63126724-cdf0-443f-80b9-6355cb39212c",
      "name": "販売レポートを編集",
      "type": "n8n-nodes-base.code",
      "position": [
        2040,
        540
      ],
      "parameters": {
        "mode": "runOnceForEachItem",
        "jsCode": "// Date and Location Metadata\nconst date = $('Schedule Trigger').item.json['Readable date'].split(',')[0];\nconst location_id = $json.orders[0].location_id || null;\nconst location_name = $('Get Square Locations').item.json.locations.find(locations => locations.id === location_id)?.name;\n\n// Our Result Variables\nlet total_money = 0;\nlet total_tax = 0;\nlet total_discount = 0;\nlet total_tip = 0;\nlet total_returns = 0;\nlet cash_rounding = 0;\n\nlet cash_tender = 0;\nlet card_tender = 0;\nlet gift_card_tender = 0;\nlet other_tender = 0;\nlet fees = 0;\n\n// Loop Through Each Order\nfor (const sale of $json.orders) {\n\n    // Add the sales, taxes, discounts and tips\n    total_money += sale.total_money?.amount || 0;\n    total_tax += sale.total_tax_money?.amount || 0;\n    total_discount += -(sale.total_discount_money?.amount || 0);\n    total_tip += sale.total_tip_money?.amount || 0;\n    if (sale.rounding_adjustment) {\n      cash_rounding += sale.rounding_adjustment.amount_money?.amount || 0;\n    }\n\n  \n    if (sale.return_amounts) {\n      // If there are returns, subtract from sales totals and add to return amount total\n      total_money -= sale.return_amounts?.total_money?.amount || 0;\n      total_tax -= sale.return_amounts?.tax_money?.amount || 0;\n      total_discount -= sale.return_amounts?.discount_money?.amount || 0;\n      total_tip -= sale.return_amounts?.tip_money?.amount || 0;\n  \n      total_returns += -(sale.return_amounts?.total_money?.amount || 0);\n      total_returns -= -(sale.return_amounts?.tax_money?.amount || 0);\n      total_returns -= -(sale.return_amounts?.tip_money?.amount || 0);\n      total_returns -= -(sale.return_amounts?.discount_money?.amount || 0);\n  \n      // If an array of refunds is provided\n      for (const refund of sale.refunds || []) {\n        const transaction_id = refund.transaction_id;\n      \n        // Look for the original sale this refund refers to\n        const original_sale = $json.orders.find(original =>\n          original.id && transaction_id && original.id.includes(transaction_id)\n        );\n      \n        if (original_sale) {\n          if (original_sale.rounding_adjustment) {\n            const amount = original_sale.rounding_adjustment.amount_money?.amount || 0;\n            cash_rounding -= amount;\n            total_returns += amount;\n          }\n      \n          if (original_sale.tenders) {\n            for (const tender of original_sale.tenders) {\n              if (tender.id === refund.tender_id) {\n                const amount = refund.amount_money?.amount || 0;\n                if (tender.type === 'CARD') card_tender -= amount;\n                else if (tender.type === 'CASH') cash_tender -= amount;\n                else if (tender.type === 'SQUARE_GIFT_CARD') gift_card_tender -= amount;\n                else other_tender -= amount;\n      \n                if (refund.processing_fee_money && tender.id === refund.tender_id) {\n                  fees -= refund.processing_fee_money.amount || 0;\n                }\n              }\n            }\n          }\n        }\n      }\n    }\n  \n    if (sale.tenders) {\n      for (const tender of sale.tenders) {\n        const amount = tender.amount_money?.amount || 0;\n        if (tender.type === 'CARD') card_tender += amount;\n        else if (tender.type === 'CASH') cash_tender += amount;\n        else if (tender.type === 'SQUARE_GIFT_CARD') gift_card_tender += amount;\n        else other_tender += amount;\n  \n        if (tender.processing_fee_money) {\n          fees -= tender.processing_fee_money.amount || 0;\n        }\n      }\n    }\n  \n}\n\n// Final computed values\nconst net_sales = total_money - total_tip - total_tax - cash_rounding;\nconst gross_sales = net_sales - total_discount - total_returns;\nconst net_total = cash_tender + card_tender + gift_card_tender + other_tender + fees;\n\nreturn {\n  json: {\n    date,\n    location_id,\n    location_name,\n    gross_sales: gross_sales / 100.0,\n    total_returns: total_returns / 100.0,\n    total_discount: total_discount / 100.0,\n    net_sales: net_sales / 100.0,\n    total_tax: total_tax / 100.0,\n    total_tip: total_tip / 100.0,\n    cash_rounding: cash_rounding / 100.0,\n    total_payments_collected: total_money / 100.0,\n    cash: cash_tender / 100.0,\n    card: card_tender / 100.0,\n    gift_card: gift_card_tender / 100.0,\n    other: other_tender / 100.0,\n    fees: fees / 100.0,\n    net_total: net_total / 100.0,\n  }\n};"
      },
      "typeVersion": 2
    },
    {
      "id": "deb2feb9-da4d-47bd-8bbd-bdb09f43a1b3",
      "name": "付箋",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        0,
        0
      ],
      "parameters": {
        "width": 660,
        "height": 1340,
        "content": "## Automatically Pull Square Report Data Into Google Sheets\n\n## What It Does  \nThis workflow automatically connects to the Square API and generates a daily sales summary report for all your Square locations. The report matches the figures displayed in **Square Dashboard > Reports > Sales Summary**.\n\nIt's designed to run daily and pull the previous day's sales into a Google Sheet for easy analysis and reporting.\n\n## Prerequisites  \nTo use this workflow, you'll need:\n- A Square API credential (configured as a Header Auth credential)\n- A Google Sheets credential\n\n## How to Set Up Square Credentials:  \n- Go to **Credentials > Create New**  \n- Choose **Header Auth**  \n- Set the **Name** to `Authorization`  \n- Set the **Value** to your Square Access Token (e.g., `Bearer <your-api-key>`)\n\n## How It Works  \n1. **Trigger:** The workflow runs every day at 4:00 AM  \n2. **Fetch Locations:** An HTTP request retrieves all Square locations linked to your account  \n3. **Fetch Orders:** For each location, an HTTP request pulls completed orders for the specified report_date  \n4. **Filter Empty Locations:** Locations with no sales are ignored  \n5. **Aggregate Sales Data:** A Code node processes the order data and produces a summary identical to Square’s built-in Sales Summary report  \n6. **Output:** A cleaned, consistent summary that can be consumed by parent workflows or other nodes\n\n## Example Use Cases  \n- Automatically store daily sales data in Google Sheets for analysis and historical tracking  \n- Automatically create charts or visualizations based on the imported data  \n- Build weekly/monthly reports by looping over multiple dates  \n- Quickly calculate commissions or rent payments based on sales volume\n\n## How to Use  \n- Configure both HTTP Request nodes to use your Square API credential  \n- Set the workflow to **Active** so it runs automatically  \n- Select the Google Sheet you want to import data into and map the data to your columns\n\n## Customization Options  \n- Add pagination to handle locations with more than 1,000 orders per day  \n- Expand the workflow to save or send the report output via additional integrations (email, database, webhook, etc.)\n\n## Why It's Useful  \nThis workflow saves time, reduces manual report pulling from Square, and enables smarter automation around sales data — whether for operations, finance, or performance monitoring.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "2a40012c-8487-45a8-8398-7f656679ff67",
      "name": "付箋1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        700,
        340
      ],
      "parameters": {
        "color": 5,
        "height": 420,
        "content": "## Trigger  \n- This workflow runs every day at 4:00 AM.  \n- Each day, it pulls the previous day's sales data from Square.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "1517eef0-281f-43a9-ba60-542c9f93f1ce",
      "name": "付箋2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        960,
        340
      ],
      "parameters": {
        "color": 5,
        "width": 460,
        "height": 420,
        "content": "## Get Square Locations and Process Each One Separately  \n- This HTTP node connects to the Square Locations API to fetch all your locations.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "5a6ad643-5f29-49d0-ab37-acfaf95eae45",
      "name": "付箋3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1440,
        340
      ],
      "parameters": {
        "color": 5,
        "height": 420,
        "content": "## Get Sales from Square  \n- This HTTP node retrieves all orders for the given location on the specified date."
      },
      "typeVersion": 1
    },
    {
      "id": "11e6e1e9-7ca8-4f44-98dc-1b140222d8ff",
      "name": "付箋4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1980,
        340
      ],
      "parameters": {
        "color": 5,
        "height": 420,
        "content": "## Compile a Report for Each Location  \n- This code node calculates totals for each location.  \n- Please ensure the numbers match EXACTLY with the Square Sales Summary Dashboard.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "ab30e56c-3981-41e4-a600-d43802d942e5",
      "name": "スケジュールトリガー",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        780,
        540
      ],
      "parameters": {
        "rule": {
          "interval": [
            {}
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "9a840211-a8de-481b-a262-d6728c0116e6",
      "name": "付箋5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2240,
        340
      ],
      "parameters": {
        "color": 5,
        "height": 420,
        "content": "## Upload Each Report to Google Sheets  \n- Each location's sales will be uploaded to a Google Sheet for easy analysis and record-keeping.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "b58d40a6-049e-4b0d-8397-1cf0b1b2bf9f",
      "name": "売上データを Google シートにアップロード",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        2300,
        540
      ],
      "parameters": {
        "columns": {
          "value": {
            "Card": "={{ $json.card }}",
            "Cash": "={{ $json.cash }}",
            "Date": "={{ $json.date }}",
            "Fees": "={{ $json.fees }}",
            "Tips": "={{ $json.total_tip }}",
            "Other": "={{ $json.other }}",
            "Taxes": "={{ $json.total_tax }}",
            "Returns": "={{ $json.total_returns }}",
            "Location": "={{ $json.location_name }}",
            "Gift Card": "={{ $json.gift_card }}",
            "Net Sales": "={{ $json.net_sales }}",
            "Net Total": "={{ $json.net_total }}",
            "Gross Sales": "={{ $json.gross_sales }}",
            "Location ID": "={{ $json.location_id }}",
            "Total Money": "={{ $json.total_payments_collected }}",
            "Cash Rounding": "={{ $json.cash_rounding }}",
            "Total Discount": "={{ $json.total_discount }}"
          },
          "schema": [
            {
              "id": "Date",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Date",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Location ID",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Location ID",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Location",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Location",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Gross Sales",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Gross Sales",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Returns",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Returns",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Total Discount",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Total Discount",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Net Sales",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Net Sales",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Taxes",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Taxes",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Tips",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Tips",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Cash Rounding",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Cash Rounding",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Total Money",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Total Money",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Cash",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Cash",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Card",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Card",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Gift Card",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Gift Card",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Other",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Other",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Fees",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Fees",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Net Total",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Net Total",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 1213795200,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/15_kD4zmytEy-Rcpti_etCYSE_T4dJJiY0FkDgE1AbnA/edit#gid=1213795200",
          "cachedResultName": "Data"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "15_kD4zmytEy-Rcpti_etCYSE_T4dJJiY0FkDgE1AbnA",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/15_kD4zmytEy-Rcpti_etCYSE_T4dJJiY0FkDgE1AbnA/edit?usp=drivesdk",
          "cachedResultName": "2025 Sales"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "9zTRQkN0dmrqrNaj",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 4.5
    },
    {
      "id": "68479e42-ed7c-43f9-864a-5f42381cdf02",
      "name": "付箋6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2500,
        340
      ],
      "parameters": {
        "height": 520,
        "content": "## Setting up Your Google Sheet\nHere are the columns you can create in your Google sheet:\n- Date\n- Location ID\n- Location Name\n- Gross Sales\n- Discounts\n- Returns\n- Net Sales\n- Taxes\n- Tips\n- Cash Rounding\n- Total Money Collected\n- Cash\n- Card\n- Gift Card\n- Other Payment Method\n- Fees\n- Net Total\n"
      },
      "typeVersion": 1
    }
  ],
  "pinData": {},
  "connections": {
    "ab30e56c-3981-41e4-a600-d43802d942e5": {
      "main": [
        [
          {
            "node": "8943addf-613e-4169-a9e5-58debec9e076",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "8943addf-613e-4169-a9e5-58debec9e076": {
      "main": [
        [
          {
            "node": "7bebea87-6c2e-4a90-8ba5-d4912370bce5",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "63126724-cdf0-443f-80b9-6355cb39212c": {
      "main": [
        [
          {
            "node": "b58d40a6-049e-4b0d-8397-1cf0b1b2bf9f",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "4431553a-1ddb-4789-abd6-2011a3f53efc": {
      "main": [
        [
          {
            "node": "ca074893-cb12-49eb-ac27-87f7468932c8",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "7bebea87-6c2e-4a90-8ba5-d4912370bce5": {
      "main": [
        [
          {
            "node": "4431553a-1ddb-4789-abd6-2011a3f53efc",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "ca074893-cb12-49eb-ac27-87f7468932c8": {
      "main": [
        [
          {
            "node": "63126724-cdf0-443f-80b9-6355cb39212c",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}