# How to Integrate Retool with SharpSpring

- Tool: Retool
- Difficulty: Intermediate
- Time required: 30 minutes
- Last updated: April 2026

## TL;DR

Connect Retool to SharpSpring by creating a REST API Resource with SharpSpring's API endpoint and account ID plus secret key authentication. Use SharpSpring's REST API to build agency marketing automation dashboards that manage leads, track campaign performance, monitor automation workflows, and generate client-facing reports — giving agency teams direct access to SharpSpring data across all client accounts.

## Build a SharpSpring Agency Marketing Automation Dashboard in Retool

SharpSpring is a marketing automation platform specifically designed with agencies in mind, offering white-label options and multi-client management capabilities. Agencies using SharpSpring often manage dozens of client accounts and need a centralized operations view: which client's lead pipeline is most active, which automation workflows are triggering at the right rates, which email campaigns are performing well, and how pipeline value compares to last month — none of which is easily visible from SharpSpring's standard interface across multiple accounts.

Retool provides a solution by enabling agencies to build custom operations dashboards that pull SharpSpring data through its REST API. A Retool app can show lead counts and pipeline values for each client, flag automation workflows that have stopped triggering, identify leads that have been stuck in the same stage for too long, and generate weekly performance reports — all in a single panel that your account managers can check each morning.

SharpSpring's API is distinctive in using an RPC (Remote Procedure Call) style rather than a traditional REST approach. Every API request is a POST to the same endpoint URL, with the specific method name and parameters sent in the JSON body. This differs from REST APIs where the endpoint path indicates the operation. Retool handles this pattern cleanly by letting you configure the full JSON body per query, but it requires understanding SharpSpring's body structure before you begin.

## Before you start

- A SharpSpring account with API access (API credentials are available to all SharpSpring customers)
- SharpSpring API Account ID and Secret Key (SharpSpring Settings → API Settings → Generate Credentials)
- A Retool account with permission to create Resources
- Understanding of SharpSpring's data model (leads, opportunities, campaigns, workflows, lists)
- Familiarity with Retool's query editor, JavaScript queries, and Table component configuration

## Step-by-step guide

### 1. Create a SharpSpring REST API Resource and understand the RPC pattern

Navigate to the Resources tab in your Retool instance and click Add Resource. Select REST API from the list of resource types. Name the resource 'SharpSpring API'.

In the Base URL field, enter https://api.sharpspring.com/pubapi/v1.2. This is SharpSpring's current API endpoint. Unlike standard REST APIs, SharpSpring uses the same URL for every operation — the specific API method is specified in the request body.

SharpSpring authenticates all requests using account ID and secret key parameters sent in the request body as JSON. There is no API key header. Leave the authentication section of the resource at 'None' — authentication is handled per-query in the body.

To keep credentials secure, store them as configuration variables in Retool. Go to Settings → Configuration Variables. Create a variable named SHARPSPRING_ACCOUNT_ID (not marked as Secret since it appears in request bodies, but stored as a variable for easy updates) and SHARPSPRING_SECRET_KEY (marked as Secret). In each query body, you'll reference these variables.

Add a default header: Content-Type with value application/json. SharpSpring's API requires JSON body format for all POST requests. Click Save Changes.

SharpSpring's RPC body format is always: { 'method': 'getLeads', 'params': { 'where': {}, 'limit': 100, 'offset': 0 }, 'id': '1', 'accountID': 'YOUR_ID', 'secretKey': 'YOUR_KEY' }. The 'method' field specifies what operation to perform, and 'params' contains the method-specific parameters.

```
// SharpSpring API request body structure — ALL requests use this pattern
// Method: POST, Path: empty (base URL is the full endpoint)
{
  "method": "getLeads",
  "params": {
    "where": {},
    "limit": 100,
    "offset": 0
  },
  "id": "1",
  "accountID": "{{ retoolContext.configVars.SHARPSPRING_ACCOUNT_ID }}",
  "secretKey": "{{ retoolContext.configVars.SHARPSPRING_SECRET_KEY }}"
}
```

**Expected result:** The SharpSpring API resource is saved with the correct base URL and Content-Type header. Configuration variables for account ID and secret key are set up and ready to reference in query bodies.

### 2. Query leads and contact data

Create your first SharpSpring query to retrieve leads. In your Retool app, open the Code panel and click + to add a query. Name it getLeads and select the SharpSpring API resource.

Set Method to POST. Leave the Path field empty — SharpSpring uses only the base URL for all requests. In the Body section, select JSON and enter the SharpSpring RPC request body for the getLeads method:

The 'where' parameter in params lets you filter leads. An empty where object {} returns all leads (subject to the limit). To filter by lead stage, use: 'where': { 'leadStatus': 'Open' }. To filter by date, use: 'where': { 'updateTimestamp': { 'gt': '2026-01-01 00:00:00' } }.

SharpSpring's getLeads response has a specific structure: result.lead array containing the actual lead objects. Each lead includes: id, firstName, lastName, emailAddress, leadStatus (stage name), leadScore, ownerEmail, companyName, and all standard CRM fields.

Add a JavaScript transformer to flatten the response and format for display:

For individual lead detail, create getLeadById:
- Same POST body structure with method: 'getLead' and params: { 'id': '{{ table_leads.selectedRow.id }}' }

Bind the getLeads results to a Table named table_leads. Add filter components: a Select for leadStatus (lead stage), a Text Input for name/company search, and a Slider for minimum lead score. Connect each component's onChange event to trigger getLeads with the updated filter params.

```
// POST body for getLeads query
{
  "method": "getLeads",
  "params": {
    "where": {
      "leadStatus": "{{ select_leadStatus.value || undefined }}"
    },
    "limit": 100,
    "offset": {{ (pagination.page - 1) * 100 }},
    "order": { "updateTimestamp": "DESC" }
  },
  "id": "retool_leads_query",
  "accountID": "{{ retoolContext.configVars.SHARPSPRING_ACCOUNT_ID }}",
  "secretKey": "{{ retoolContext.configVars.SHARPSPRING_SECRET_KEY }}"
}
```

**Expected result:** The getLeads query returns up to 100 leads from SharpSpring, formatted by the transformer into a clean table with lead name, company, status, score, and last update date. Filter dropdowns allow narrowing results by stage and score.

### 3. Fetch opportunities and pipeline data

Create queries to retrieve SharpSpring opportunity (deal) data for pipeline reporting. SharpSpring's getOpportunities method returns open deals with their stage, value, and associated contacts.

Create a query named getOpportunities:
- Method: POST
- Body: RPC body with method 'getOpportunities', params with where: {} and limit: 100

Each opportunity object includes: id, opportunityName, accountName (company), amount (deal value), status, closeDate, probability, ownerEmail, and primary contact information.

Create a JavaScript transformer to calculate pipeline metrics:

For a pipeline stage funnel, create a JavaScript query named buildPipelineSummary that groups opportunities by status and calculates total value per stage:

Bind buildPipelineSummary results to both a Table (showing stages with count and total value) and a Bar Chart (stages on x-axis, total value on y-axis). This gives account managers an instant pipeline view without navigating SharpSpring's CRM interface.

For leads-to-opportunities conversion, combine getLeads and getOpportunities data in a JavaScript query that identifies leads with high lead scores who don't yet have an associated opportunity — these are the best candidates for the sales team to qualify and convert.

Add Stat components above the pipeline chart showing: total pipeline value (sum of all opportunity amounts), number of deals closing this month, and weighted pipeline value (amount × probability for each deal, summed).

```
// JavaScript query — build pipeline stage summary from opportunities
const opportunities = (getOpportunities.data?.result?.opportunity || []);

const pipelineByStage = opportunities.reduce((acc, opp) => {
  const stage = opp.status || 'Unknown Stage';
  const amount = parseFloat(opp.amount) || 0;

  if (!acc[stage]) {
    acc[stage] = { stage, count: 0, total_value: 0, weighted_value: 0 };
  }

  acc[stage].count++;
  acc[stage].total_value += amount;
  acc[stage].weighted_value += amount * (parseFloat(opp.probability) / 100 || 0);

  return acc;
}, {});

const stageOrder = ['Prospecting', 'Qualification', 'Proposal', 'Negotiation', 'Closed Won'];

return Object.values(pipelineByStage)
  .map(stage => ({
    ...stage,
    total_value_formatted: `$${stage.total_value.toLocaleString()}`,
    weighted_value_formatted: `$${stage.weighted_value.toFixed(0).toLocaleString()}`
  }))
  .sort((a, b) => {
    const aIdx = stageOrder.indexOf(a.stage);
    const bIdx = stageOrder.indexOf(b.stage);
    return (aIdx === -1 ? 99 : aIdx) - (bIdx === -1 ? 99 : bIdx);
  });
```

**Expected result:** The pipeline summary table shows deal counts and values grouped by stage. The funnel chart visualizes pipeline progression from Prospecting through Closed Won. Stat components display total pipeline value and weighted value.

### 4. Query email campaigns and automation workflows

Create queries to retrieve campaign performance and automation health data. SharpSpring's API provides access to email campaign statistics and workflow configurations.

Create a query named getCampaigns:
- Method: POST
- Body: RPC body with method 'getCampaigns', params: { where: {}, limit: 50, offset: 0 }

SharpSpring campaign objects include: id, name, status, sends (total sent count), opens, clicks, unsubscribes, bounces, and createTimestamp.

Create a JavaScript transformer to calculate performance rates:

For automation workflows, create getWorkflows:
- Method: POST
- Body: RPC body with method 'getActiveVisitorActivity' or the appropriate workflow method (check current API documentation as SharpSpring's workflow API methods have evolved)

Bind getCampaigns to a Table with conditional row formatting: red background for campaigns where the unsubscribe rate exceeds 0.5%, yellow for open rates below 15%, green for campaigns performing above 25% open rate.

For the workflow health panel, create a JavaScript query that processes workflow data and flags workflows that appear stalled (last trigger date more than 48 hours ago for workflows configured as real-time triggers). Display these stalled workflows in a separate alert Table at the top of the page.

For agencies managing multiple SharpSpring client accounts, you'll need separate API resources per client account (each with their own account ID and secret key). Create naming conventions like 'SharpSpring - Client A' and 'SharpSpring - Client B' in your Resources tab, then use a Select component to switch the active resource in your dashboard using Retool's dynamic resource selection.

```
// JavaScript transformer — calculate campaign performance metrics
const campaigns = (data.result?.campaign || data.result || []);

return campaigns.map(campaign => {
  const sends = parseInt(campaign.sends || 0);
  const opens = parseInt(campaign.opens || 0);
  const clicks = parseInt(campaign.clicks || 0);
  const unsubs = parseInt(campaign.unsubscribes || 0);
  const bounces = parseInt(campaign.bounces || 0);

  const openRate = sends > 0 ? ((opens / sends) * 100).toFixed(1) : '0';
  const clickRate = sends > 0 ? ((clicks / sends) * 100).toFixed(1) : '0';
  const unsubRate = sends > 0 ? ((unsubs / sends) * 100).toFixed(2) : '0';

  return {
    id: campaign.id,
    name: campaign.name || 'Unnamed Campaign',
    status: campaign.status || 'Unknown',
    sends,
    opens,
    clicks,
    open_rate: `${openRate}%`,
    click_rate: `${clickRate}%`,
    unsub_rate: `${unsubRate}%`,
    performance_flag: parseFloat(unsubRate) > 0.5 ? 'HIGH_UNSUB'
      : parseFloat(openRate) < 15 ? 'LOW_OPEN' : 'OK',
    created: campaign.createTimestamp
      ? new Date(campaign.createTimestamp * 1000).toLocaleDateString()
      : 'N/A'
  };
}).sort((a, b) => b.sends - a.sends);
```

**Expected result:** The campaigns table shows all email campaigns with calculated open and click rates. Performance flags highlight campaigns needing attention. Conditional row formatting makes issues immediately visible without reading every number.

### 5. Build an agency client reporting panel

Create a client-facing reporting panel that generates a structured marketing performance summary from SharpSpring data. This replaces manual monthly report compilation for agency account managers.

The report needs data from three sources: getLeads (new leads this month), getOpportunities (pipeline value), and getCampaigns (email performance). Create a JavaScript query named buildClientReport that aggregates all three datasets:

In your Retool app layout, add:
- A client selector (Text Input or Dropdown if you have multiple client accounts)
- A month/year period selector
- A 'Generate Report' button that triggers all three data queries
- A Report Preview section with:
  - New leads this month count and lead score distribution
  - Pipeline summary: open deals, total value, deals closed this month
  - Email performance: campaigns sent, average open rate, best performing campaign
  - Month-over-month comparison if previous month data is stored in your database

For Retool's share feature, create a read-only version of this report view that client contacts can access via a shared URL. Go to your app's Share settings and create a Public or Limited Access link. Clients can view their performance report without needing a Retool account.

For agencies managing 10+ SharpSpring client accounts with automated monthly reporting requirements, RapidDev's team can help build a scalable multi-client reporting infrastructure that automates data collection and report generation across all client accounts from a single Retool workspace.

```
// JavaScript query — build agency client report from SharpSpring data
const leads = (getLeads.data?.result?.lead || []);
const opportunities = (getOpportunities.data?.result?.opportunity || []);
const campaigns = (getCampaigns.data?.result?.campaign || []);

// Filter to current month
const startOfMonth = new Date();
startOfMonth.setDate(1);
startOfMonth.setHours(0, 0, 0, 0);
const startTs = Math.floor(startOfMonth.getTime() / 1000);

const newLeadsThisMonth = leads.filter(
  l => parseInt(l.createTimestamp) >= startTs
);

const openDeals = opportunities.filter(o => o.status !== 'Closed Won' && o.status !== 'Closed Lost');
const closedWon = opportunities.filter(o => o.status === 'Closed Won');
const totalPipelineValue = openDeals.reduce((sum, o) => sum + (parseFloat(o.amount) || 0), 0);

const emailStats = campaigns.reduce((acc, c) => {
  const sends = parseInt(c.sends || 0);
  const opens = parseInt(c.opens || 0);
  acc.totalSends += sends;
  acc.totalOpens += opens;
  acc.count++;
  return acc;
}, { totalSends: 0, totalOpens: 0, count: 0 });

return {
  report_month: new Date().toLocaleDateString('en-US', { month: 'long', year: 'numeric' }),
  leads: {
    new_this_month: newLeadsThisMonth.length,
    total_active: leads.filter(l => l.leadStatus !== 'Closed').length
  },
  pipeline: {
    open_deals: openDeals.length,
    total_value: `$${totalPipelineValue.toLocaleString()}`,
    closed_won_count: closedWon.length
  },
  email: {
    campaigns_sent: emailStats.count,
    avg_open_rate: emailStats.totalSends > 0
      ? `${((emailStats.totalOpens / emailStats.totalSends) * 100).toFixed(1)}%`
      : 'N/A'
  }
};
```

**Expected result:** The client report panel generates a structured performance summary with new leads, pipeline value, and email metrics for the selected period. The data is ready for client presentation either as a shared Retool view or exported as a PDF.

## Best practices

- Store SharpSpring account ID and secret key as configuration variables in Retool — the secret key should be marked as Secret to restrict it to resource configurations only.
- Always include the 'method' and 'id' fields at the top level of every SharpSpring request body — the id field can be any string (e.g., 'retool_query_1') but must be present for the API to process the request.
- Convert SharpSpring Unix epoch timestamps by multiplying by 1000 (not dividing) when constructing JavaScript Date objects — SharpSpring uses seconds while JavaScript expects milliseconds.
- Cache lead and opportunity data in Retool Database via a nightly Workflow rather than querying SharpSpring's API live for every dashboard load — this improves performance and reduces API load.
- For agencies with multiple SharpSpring client accounts, create separate named Resources (e.g., 'SharpSpring - Client A') for each account rather than building dynamic authentication switching — it's simpler and more maintainable.
- Filter campaign queries to 'Sent' or 'Completed' status to ensure performance statistics are available — draft campaigns have no send data and will show misleading zeros in your metrics.
- Add confirmation modals before any mutation operations (updating lead stages, changing opportunity values) — SharpSpring doesn't have built-in undo functionality, so accidental changes must be manually corrected.

## Use cases

### Build a lead pipeline and CRM management dashboard

Create a Retool app where agency account managers can view all leads in a client's SharpSpring pipeline, filter by pipeline stage, lead score, and last activity date, and identify leads that have been stuck in the same stage for more than 14 days. Add a Form panel to update lead information or move leads between pipeline stages directly from Retool without opening SharpSpring.

Prompt example:

```
Build a lead pipeline dashboard that fetches all active leads from SharpSpring, groups them by pipeline stage with counts and total deal value, highlights leads with no activity in 14+ days in a separate warning table, and includes an edit form that updates lead properties and stage via the SharpSpring API.
```

### Campaign performance and email analytics tracker

Build a Retool dashboard for agency account managers that shows all SharpSpring email campaigns for a selected client with their sent count, open rate, click rate, and conversion data. Add a date filter to compare current month performance against previous month, and a Chart showing weekly email engagement trends. Flag campaigns where the unsubscribe rate exceeds 0.5%.

Prompt example:

```
Create a campaign analytics panel that queries SharpSpring for email campaigns with their statistics, displays open rate and click rate in a sortable Table, shows a line chart of weekly engagement trends over the past 90 days, and highlights campaigns with high unsubscribe rates in red so account managers can alert clients.
```

### Automation workflow health monitoring panel

Build an operational dashboard that shows all active SharpSpring automation workflows for a client, with each workflow's trigger count this month, current enrolled contacts count, and last trigger date. Alert the team when a workflow that normally triggers daily hasn't triggered in the past 48 hours — which often indicates a configuration problem that needs investigation.

Prompt example:

```
Build an automation health panel that fetches all active workflows from SharpSpring, shows each workflow's name, trigger type, last triggered date, and enrolled contacts count, highlights workflows that haven't triggered in 48+ hours as 'stalled', and provides a link to open each workflow in SharpSpring for investigation.
```

## Troubleshooting

### All API requests return an authentication error or empty result

Cause: SharpSpring authenticates via the accountID and secretKey fields in the JSON body — not via headers. If these fields are missing from the body or contain incorrect values, all requests fail silently or return auth errors.

Solution: Verify that your request body includes both accountID and secretKey as top-level fields (not inside the params object). Confirm the configuration variable names match exactly. Test by temporarily replacing the config variable references with the raw account ID and secret key values to isolate whether the issue is in the credentials or the variable references.

```
// Correct SharpSpring request body structure
// accountID and secretKey are at the top level, NOT inside params
{
  "method": "getLeads",
  "params": { "where": {}, "limit": 10 },
  "id": "1",
  "accountID": "YOUR_ACCOUNT_ID_HERE",
  "secretKey": "YOUR_SECRET_KEY_HERE"
}
```

### getLeads returns results but the lead count is much lower than expected

Cause: SharpSpring's default limit is 100 results per request, and the API applies this limit without warning. If you have more than 100 leads, only the first 100 are returned.

Solution: Implement pagination using the offset parameter. The maximum limit value supported varies — check SharpSpring's documentation for your plan's max. For full lead exports, increment offset by the limit on each call until the result count is less than the limit, indicating you've reached the last page. For dashboards, consider storing leads in Retool Database via a nightly Workflow rather than paginating live.

### Campaign open and click rates appear to be 0% for all campaigns

Cause: SharpSpring returns statistical fields only for campaigns in specific statuses. Campaigns in 'Draft' status have no send statistics, and some API versions return statistics in a nested sub-object rather than flat fields.

Solution: Filter campaigns to status 'Sent' or 'Completed' in your where clause. In the transformer, check both flat fields (campaign.opens) and nested stat objects (campaign.stats?.opens) to handle different API response formats. Log the raw response in Retool's query preview to see the actual data structure for your account.

### Timestamps in SharpSpring responses display as numbers in the year 1970

Cause: SharpSpring timestamps are Unix epoch values in seconds, not milliseconds. JavaScript's Date constructor expects milliseconds, so dividing by 1000 (incorrect) gives dates near 1970.

Solution: Multiply SharpSpring timestamps by 1000 before passing to JavaScript's Date constructor: new Date(timestamp * 1000). Never divide — SharpSpring's epoch seconds must be converted to milliseconds by multiplying. Verify in Retool's query preview by checking if a raw timestamp value around 1.7 billion converts to a 2026 date when multiplied by 1000.

```
// Correct timestamp conversion for SharpSpring Unix epoch (seconds → ms)
new Date(parseInt(sharpspring_timestamp) * 1000).toLocaleDateString()
```

## Frequently asked questions

### Does SharpSpring have a native connector in Retool?

No, SharpSpring does not have a native connector in Retool. You connect it via a REST API Resource, but SharpSpring's API uses a unique RPC-style pattern where all requests are POST to the same endpoint URL with the method name and parameters in the JSON body. This is different from standard REST APIs where the endpoint path specifies the operation. Once you understand this pattern, building queries is straightforward.

### How do I handle multiple SharpSpring client accounts in Retool?

Create a separate REST API Resource in Retool for each client account, named clearly (e.g., 'SharpSpring - ACME Corp'). Each resource has its own set of configuration variables for that client's account ID and secret key. In your dashboard, use a Select component to choose the active client, and use Retool's dynamic resource selection feature or separate query sets per client to switch between accounts. For more than 5-10 clients, consider a database-driven approach where client credentials are stored securely and loaded dynamically.

### What's the difference between SharpSpring leads and opportunities in Retool queries?

In SharpSpring's data model, leads (accessed via getLeads) are contact records with lead scores, statuses, and behavioral tracking data. Opportunities (accessed via getOpportunities) are deal records with monetary values, close dates, and probability percentages — these represent potential sales in the pipeline. For CRM reporting, you typically show leads in a marketing context and opportunities in a sales pipeline context. Some queries join both to show which leads have associated opportunities.

### Can I create new leads or update existing ones in SharpSpring from Retool?

Yes. SharpSpring's API supports creating leads with the createLeads method and updating them with updateLeads. The request body follows the same RPC pattern with the method name changed and the lead data in the params.objects array. Build a Retool form with fields for firstName, lastName, emailAddress, companyName, and custom attributes, then trigger a POST query with the createLeads method body to add the new contact to SharpSpring.

### How do I paginate through large lead lists in SharpSpring's API?

SharpSpring uses offset-based pagination. Set the limit parameter in params to your page size (maximum 100 recommended) and increment the offset by the limit for each subsequent page. In Retool, store the current offset in a Variable component and build a 'Load More' button that increments the offset and appends new results to your existing dataset using a JavaScript query. Continue until the API returns fewer results than the requested limit, indicating you've reached the last page.

---

Source: https://www.rapidevelopers.com/retool-integrations/sharpspring
© RapidDev — https://www.rapidevelopers.com/retool-integrations/sharpspring