How to Integrate Retool with Google Search Console

Google Search Console API requires OAuth 2.0 authentication. For server-side Retool integration (no user browser redirect), you use a service account. In Google Cloud Console (console.cloud.google.com), navigate to APIs & Services → Library and search for 'Google Search Console API'. Click Enable. Next, go to IAM & Admin → Service Accounts → Create Service Account. Name it something like 'retool-gsc-reader'. Grant it no project-level roles (it only needs GSC property access, not Cloud project access). Complete the creation. Click the service account you just created, navigate to the Keys tab, and click Add Key → Create New Key → JSON. Download the JSON file — it contains the service account email, private key ID, and private key. Now grant this service account access to your GSC property. Open Google Search Console → Settings → Users and permissions → Add user. Enter the service account email (looks like retool-gsc-reader@your-project.iam.gserviceaccount.com). Grant it 'Restricted' access (read-only is sufficient). Store the key JSON contents as a Retool configuration variable named GOOGLE_SERVICE_ACCOUNT_JSON (mark as Secret). You'll reference specific fields from it in the token generation query.

Google service accounts use JWT-based authentication to obtain short-lived access tokens. You need a JavaScript query that constructs the JWT, signs it with the service account private key, exchanges it for an access token, and stores the token for use in GSC API queries. Create a REST API Resource named 'Google OAuth Token'. Set Base URL to https://oauth2.googleapis.com. Create a JavaScript query named getGSCToken that: 1. Parses the service account JSON from configuration variables 2. Constructs a JWT header and payload 3. Signs the JWT using the service account private key 4. POSTs to the Google OAuth token endpoint to exchange the JWT for an access token Store the resulting access_token in a Retool Variable named gscAccessToken. Google access tokens expire after 1 hour, so add a refresh mechanism. Since native JWT signing isn't trivial in Retool's JavaScript environment, the most reliable approach is to use a separate backend service or Cloud Function that takes the service account JSON and returns a fresh token. Alternatively, use Retool's built-in Google OAuth resource type if available for your Retool plan.

1// JavaScript query: getGSCToken
2// POST to Google's token endpoint to exchange service account for access token
3// Note: This requires the google-auth-library or similar JWT signing
4// For Retool, the simplest approach is a lightweight proxy endpoint
5
6// Option 1: If you have a simple token proxy endpoint:
7const response = await fetch('https://your-token-proxy.yourcompany.com/gsc-token', {
8  method: 'POST',
9  headers: { 'Content-Type': 'application/json' },
10  body: JSON.stringify({
11    // Pass identifying info — the proxy holds the service account credentials
12    property: 'https://www.yoursite.com'
13  })
14});
15const tokenData = await response.json();
16return tokenData.access_token;
17
18// Option 2: Use Retool's native Google Sheets resource (which handles OAuth)
19// then reuse the access token for GSC API calls

Create a REST API Resource named 'Google Search Console API'. Set Base URL to https://searchconsole.googleapis.com. Add a default header: Authorization = Bearer {{ gscAccessToken.value }}. This attaches the OAuth token to all GSC queries. The token comes from the Variable set by getGSCToken. Now create the main analytics query. Name it getSearchAnalytics. Set Method to POST, Path to /v1/sites/{{ encodeURIComponent(textInput_siteUrl.value) }}/searchAnalytics/query. The siteUrl must exactly match your GSC property — use the full URL like https://www.yoursite.com or sc-domain:yoursite.com for domain properties. URL-encoding is required because the URL contains slashes and colons. The query body specifies your analytics parameters: - startDate and endDate (YYYY-MM-DD) - dimensions: array of grouping dimensions (query, page, country, device, searchAppearance) - rowLimit: max rows (up to 25,000) - startRow: for pagination - dimensionFilterGroups: for filtering by dimension values Bind the results to a Retool Table. The response contains a rows array where each row has keys (dimension values) and metrics (clicks, impressions, ctr, position).

1// POST body for Search Analytics API query
2{
3  "startDate": "{{ moment(dateRange.value[0]).format('YYYY-MM-DD') }}",
4  "endDate": "{{ moment(dateRange.value[1]).format('YYYY-MM-DD') }}",
5  "dimensions": ["{{ dropdown_dimension.value || 'query' }}"],
6  "rowLimit": 100,
7  "startRow": {{ table_results.paginationOffset || 0 }},
8  "dimensionFilterGroups": [
9    {
10      "filters": [
11        {
12          "dimension": "page",
13          "operator": "contains",
14          "expression": "{{ textInput_urlFilter.value || '' }}"
15        }
16      ]
17    }
18  ]
19}

GSC's response format requires transformation before it's ready for Retool Tables. Each row has a keys array (dimension values) and separate metric fields. The transformer converts this into named-key objects. Create a JavaScript transformer for getSearchAnalytics. The transformer maps dimension names to the keys array positions and merges them with metric values into flat objects. Add CTR formatting (multiply by 100 for percentage display), round average position to one decimal place, and add conditional fields for performance classification (e.g., flagging queries with high impressions but low CTR as 'Opportunity'). Bind the transformed data to a Table component. Configure column types: clicks and impressions as number columns with comma formatting, CTR as percentage, position as number. Sort by impressions descending by default to surface the highest-volume queries first. Add a Chart component showing CTR by top queries using a bar chart. The chart data comes from the top 20 rows of the transformer output, showing which queries have the best and worst CTR for optimization prioritization. Add a date comparison mode by creating a second identical query (getSearchAnalyticsPrev) with dates shifted back one period, then creating a comparison transformer that calculates click and impression changes between the two periods.

1// JavaScript transformer — convert GSC Search Analytics rows to table format
2const response = data || {};
3const rows = response.rows || [];
4const dimensions = ['query'];  // match your query's dimensions array
5
6return rows.map(row => {
7  const keyMap = {};
8  (row.keys || []).forEach((val, idx) => {
9    keyMap[dimensions[idx] || `dim_${idx}`] = val;
10  });
11
12  const ctr = row.ctr || 0;
13  const position = row.position || 0;
14
15  return {
16    ...keyMap,
17    clicks: row.clicks || 0,
18    impressions: row.impressions || 0,
19    ctr: `${(ctr * 100).toFixed(1)}%`,
20    ctr_raw: ctr,
21    position: position.toFixed(1),
22    position_raw: position,
23    // Opportunity flag: high impressions, low CTR, not on page 1
24    opportunity: row.impressions > 500 && ctr < 0.02 && position > 10
25      ? 'High Opportunity'
26      : row.impressions > 200 && ctr < 0.03
27      ? 'Potential'
28      : ''
29  };
30});

Assemble all queries into a complete SEO dashboard layout that gives your team a self-service analytics view. Top controls bar: Site URL input (textInput_siteUrl), Date Range Picker (last 28 days default), Dimension Dropdown (Query / Page / Country / Device), URL filter text input for page-specific analysis. A 'Load Data' button triggers getSearchAnalytics and getGSCToken (if token needs refresh). Summary stats row: Total Clicks (sum of clicks column), Total Impressions (sum of impressions), Average CTR (average of ctr_raw values), Average Position (average of position_raw values). Use Retool's Statistic components for these. Main table: Sortable table with all dimension values and metrics. Add conditional row coloring: green for position < 3, yellow for position 4-10, red for position > 20. Add a 'Copy Query' button column that copies the query string to clipboard for use in content tools. Opportunities section: A filtered view of the main table showing only rows where the 'opportunity' flag is set, giving SEO specialists a prioritized list of queries to optimize for. For agencies or teams managing multiple GSC properties, add a property selector dropdown that changes the site URL and re-runs all queries. Store the list of managed properties in a Retool Database table. For complex multi-property SEO dashboards that combine GSC with Google Analytics, Ahrefs, and internal content databases, RapidDev's team can help build the full data architecture.

1// JavaScript query — calculate summary statistics from GSC data
2const rows = getSearchAnalytics.data || [];
3if (rows.length === 0) return { total_clicks: 0, total_impressions: 0, avg_ctr: '0%', avg_position: 'N/A' };
4
5const totalClicks = rows.reduce((sum, r) => sum + (r.clicks || 0), 0);
6const totalImpressions = rows.reduce((sum, r) => sum + (r.impressions || 0), 0);
7const avgCtr = totalImpressions > 0 ? totalClicks / totalImpressions : 0;
8const avgPos = rows.reduce((sum, r) => sum + (r.position_raw || 0), 0) / rows.length;
9
10return {
11  total_clicks: totalClicks.toLocaleString(),
12  total_impressions: totalImpressions.toLocaleString(),
13  avg_ctr: `${(avgCtr * 100).toFixed(1)}%`,
14  avg_position: avgPos.toFixed(1),
15  opportunities_count: rows.filter(r => r.opportunity !== '').length
16};