How to Integrate Retool with Square

Navigate to the Square Developer Portal at developer.squareup.com and sign in with your Square account. Click the New Application button (or go to My Applications). Give your app a name like 'Retool Operations Dashboard'. Accept the developer terms and click Create Application. You will be taken to your application's credential page. On the Credentials tab, you will see two environments: Sandbox (for testing) and Production. Start with Sandbox. Copy the Sandbox Access Token — this is a long string starting with 'EAAAl' or similar. This token provides full API access to Square's sandbox environment where all data is simulated. When you are ready for production use, you will switch to the Production access token. Square's sandbox environment is highly feature-complete — you can create test catalog items, simulate transactions, and test all API endpoints without affecting real payment data. This makes it ideal for building and testing your Retool integration before going live. In Retool, navigate to Settings → Configuration Variables. Create two new variables: SQUARE_ACCESS_TOKEN (paste your Sandbox token first, then update to Production when ready) and SQUARE_LOCATION_ID (available on the Locations tab in the Square Developer Portal — copy the ID for your primary or test location). Toggle both as Secret. Click Save.

Navigate to the Retool home page and open the Resources tab. Click Add Resource. In the resource type selector, search for 'REST API' and click it to open the configuration form. In the Name field, enter 'Square API'. In the Base URL field, enter Square's API base URL: https://connect.squareup.com/v2 — this is the base for Square's Connect API v2, which covers all modern Square endpoints including Payments, Orders, Catalog, Customers, Locations, and Employees. Note: for the sandbox environment, the base URL is the same — the environment is determined by which access token you use, not the URL. In the Headers section, click Add header. Set Key to Authorization and Value to Bearer {{ retoolContext.configVars.SQUARE_ACCESS_TOKEN }}. This automatically includes your access token as a Bearer token in every request. Add a second header: Key = Content-Type, Value = application/json. Add a third header: Key = Square-Version, Value = 2024-05-15 (or the latest Square API version from Square's documentation — specifying the version ensures your Retool queries work consistently even as Square releases new API versions). Click Save Changes. The Square API resource is now available for use in Retool queries.

Open or create a Retool app. In the Code panel, click + New to create your first query. Name it getLocations. Resource = Square API, method = GET, path = /locations. This retrieves all Square locations associated with your account. Set Trigger to run automatically on page load. Click Run to verify you receive location data with IDs and names. Create a second query named getPayments. Resource = Square API, method = GET, path = /payments. Add URL parameters: location_id = {{ locationSelect.value }}, begin_time = {{ startDatePicker.value | ISO 8601 }}, end_time = {{ endDatePicker.value | ISO 8601 }}, limit = 200. Square's Payments API requires date ranges in RFC 3339 format. Set Trigger to run automatically when locationSelect or date picker values change. Create a third query named getOrders. Resource = Square API, method = POST, path = /orders/search. Square's order search uses a POST request with a JSON body containing filter criteria. Set Body Type to JSON with filters for location IDs and date range, and include field_filters for order state (COMPLETED, CANCELED). Set this query to Manual trigger. Create a fourth query named getCatalogItems. Resource = Square API, method = GET, path = /catalog/list. Add URL parameter: types = ITEM,CATEGORY. This returns all catalog items and categories across all connected locations.

1// JavaScript transformer to format payment data for Table display
2// Attach to getPayments query (Advanced → Transform data)
3const response = data;
4if (!response || !response.payments) return [];
5
6return response.payments.map(payment => {
7  const amountMoney = payment.amount_money || {};
8  const amount = amountMoney.amount ? (amountMoney.amount / 100).toFixed(2) : '0.00';
9  const currency = amountMoney.currency || 'USD';
10
11  return {
12    id: payment.id,
13    status: payment.status || 'UNKNOWN',
14    amount: `${currency} $${amount}`,
15    amountRaw: amountMoney.amount || 0,
16    source: payment.source_type || 'UNKNOWN',
17    locationId: payment.location_id,
18    createdAt: payment.created_at
19      ? new Date(payment.created_at).toLocaleString()
20      : 'N/A',
21    receiptUrl: payment.receipt_url || '',
22    refunded: payment.refunded_money ? true : false
23  };
24});

Assemble the dashboard UI. At the top of the canvas, drag a Select component labeled 'Location'. Set Data source to {{ getLocations.data.locations }}, Label to {{ self.name }}, Value to {{ self.id }}. Add two Date Picker components: Start Date (default to 7 days ago) and End Date (default to today). Add a Button labeled 'Load Data' with event handlers triggering getPayments and getOrders. Below the filters, drag four Stat components. Use a JavaScript transformer on getPayments.data to calculate: Total Revenue (sum of all payment amounts divided by 100), Transaction Count (count of payments), Average Transaction Value (total / count), and Refund Count (count of refunded payments). These display at the top for immediate KPI visibility. Drag a Table component below the stats. Set Data source to {{ getPayments.data }} with the formatPayments transformer applied. Configure columns: createdAt, amount, source (card type), status (with green/red badge renderer), and a Refunded indicator. Enable row selection and add a 'View Receipt' link column using the receiptUrl field. Drag a Chart component to the right of the Stat cards. Set type to Bar. Create a standalone transformer that groups payments by day and sums the amounts to create a daily revenue time series. Set X-axis to date and Y-axis to totalRevenue. This visualizes the sales trend over the selected period. For retail chains managing 10+ locations requiring advanced analytics and inventory reconciliation, RapidDev's team can help architect comprehensive Square + Retool solutions.

1// Standalone transformer: daily revenue for Chart
2// Groups payments by date and calculates daily total
3const payments = getPayments.data;
4if (!payments || !payments.payments) return [];
5
6const dailyRevenue = payments.payments.reduce((acc, payment) => {
7  if (payment.status !== 'COMPLETED') return acc;
8  const date = new Date(payment.created_at).toLocaleDateString('en-US');
9  const amount = (payment.amount_money?.amount || 0) / 100;
10  acc[date] = (acc[date] || 0) + amount;
11  return acc;
12}, {});
13
14return Object.entries(dailyRevenue)
15  .map(([date, totalRevenue]) => ({
16    date,
17    totalRevenue: parseFloat(totalRevenue.toFixed(2))
18  }))
19  .sort((a, b) => new Date(a.date) - new Date(b.date));

Extend the dashboard with catalog management. Create a new Tab or Container on the canvas labeled 'Catalog Management'. In this section, drag a Table component and set Data source to {{ getCatalogItems.data.objects }}. Configure columns: item name, category, price (format from smallest unit), available locations (as comma-separated list), and status (ACTIVE/INACTIVE). Add column sorting and a keyword filter input. Create a query named updateCatalogItem. Resource = Square API, method = PUT, path = /catalog/object. Set Body Type to JSON with a body that constructs a Square CatalogObject with the updated price or availability from form inputs. Set this to Manual trigger. Wire it to an Update button in the catalog Table with a confirmation dialog showing what will change. For customer lookup, create a query named searchCustomers. Resource = Square API, method = POST, path = /customers/search. Body: { query: { filter: { email_address: { exact: '{{ customerEmailInput.value }}' } } } }. Set Trigger to run when the search form is submitted. Create a follow-up query named getCustomerOrders. Resource = Square API, method = GET, path = /orders/{{ customersTable.selectedRow.id }}. Actually use the Orders search endpoint filtered by customer ID to get their purchase history. Display in a Table showing order date, items, total amount, and payment method. Add Stat components for total lifetime spend and visit count calculated by a transformer.

1// POST body for Square Orders search filtered by customer
2{
3  "location_ids": ["{{ locationSelect.value }}"],
4  "query": {
5    "filter": {
6      "customer_filter": {
7        "customer_ids": ["{{ customersTable.selectedRow.id }}"]
8      },
9      "state_filter": {
10        "states": ["COMPLETED"]
11      },
12      "date_time_filter": {
13        "closed_at": {
14          "start_at": "{{ new Date(Date.now() - 365 * 24 * 60 * 60 * 1000).toISOString() }}",
15          "end_at": "{{ new Date().toISOString() }}"
16        }
17      }
18    },
19    "sort": {
20      "sort_field": "CLOSED_AT",
21      "sort_order": "DESC"
22    }
23  },
24  "limit": 50
25}