Private & API-Key Google Sheets

TableCrafter reads Google Sheets through Google's own published CSV export endpoint, not the Google Sheets API. This page documents exactly which sharing modes and URL formats work, what "API key" means in practice, and why truly private sheets cannot be reached by the free engine.

How TableCrafter actually reads a sheet

TableCrafter does not call sheets.googleapis.com and it does not perform OAuth. Instead, when the source URL matches a Google Sheets pattern, the data fetcher rewrites it to Google's public CSV export URL and downloads the resulting comma-separated text over HTTPS. The detection is a single regular expression in both includes/class-tc-data-fetcher.php and includes/sources/class-tc-csv-source.php:

// Matches any docs.google.com spreadsheet link
preg_match('/docs\.google\.com\/spreadsheets\/d\/([a-zA-Z0-9-_]+)/', $url, $matches);

Any matching URL is normalized to the export form before the request is made:

https://docs.google.com/spreadsheets/d/{SHEET_ID}/export?format=csv

If your original link contains a gid query parameter (the numeric tab identifier), it is preserved so the correct worksheet tab is exported:

.../export?format=csv&gid=0

What "private" and "API key" really mean here

The free TableCrafter engine has no concept of a stored Google credential, service account, or per-request API key. There is no api_key shortcode attribute and no OAuth flow. Authentication is determined entirely by the sheet's own Google sharing setting and by what is embedded in the URL:

• Link-shared sheets (set to "Anyone with the link can view") are readable. This is the only supported mode for Google Sheets.
• Truly private sheets (restricted to named accounts) return an HTTP error or a Google sign-in HTML page instead of CSV, which fails the fetch. The free engine cannot authenticate to reach them.
• "API key" sources in TableCrafter means generic REST/JSON or CSV endpoints where the key is part of the URL itself (a query string or path token). Those are passed through verbatim by the remote fetcher. Google Sheets does not use this model.

Preparing your sheet in Google

1. Open the sheet in Google Sheets.
2. Click Share, then under General access choose Anyone with the link and set the role to Viewer.
3. Copy the browser URL. Either the editor URL or a published URL will work because TableCrafter only needs the spreadsheet ID.
4. If you want a specific tab, note its gid value from the URL fragment (for example #gid=123456).

Supported URL formats

Any of the following are accepted. They all funnel through the same CSV normalization once the /spreadsheets/d/{ID} pattern is recognized.

Connect from the admin builder

The dashboard offers a one-click helper so you do not have to construct the URL by hand:

1. Go to wp-admin → TableCrafter.
2. In the Shortcode Builder, click the Google Sheets button (rendered with the spreadsheet icon).
3. Paste your link into the prompt. The label is explicit: "Paste your Google Sheet URL (Must be 'Anyone with the link can view')".
4. The URL is dropped into the source field and a preview is triggered automatically so you can confirm the data parses before copying the shortcode.

The handler is wired to the #tc-google-sheet-btn element in assets/js/admin.js; it does not store any token, it simply fills the source field.

Shortcode usage

The only required attribute is source. Everything else mirrors a standard CSV/JSON table.

Note that root has no effect on a Google Sheet: it is a JSON-only path selector, and CSV data has no nested root.

Basic public sheet

[tablecrafter source="https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit"]

Specific tab with search and pagination

[tablecrafter
  source="https://docs.google.com/spreadsheets/d/1BxiMVs0.../edit#gid=123456"
  search="true"
  per_page="15"]

Curated columns, sorted, with export

[tablecrafter
  source="https://docs.google.com/spreadsheets/d/1BxiMVs0.../gviz/tq?tqx=out:csv"
  include="name,region,revenue:Revenue (USD)"
  sort="revenue:desc"
  export="true"]

How rows and headers are parsed

The CSV parser in TC_CSV_Source::parse() applies a few rules worth knowing when designing your sheet:

• Row 1 is the header row. Each column name becomes a data key, and those keys are what you reference in include, exclude, and sort.
• A byte-order mark (BOM) on the first header cell is stripped automatically, so the first column name is not silently corrupted.
• Rows whose cell count does not match the header count are skipped. Ragged rows, stray trailing columns, or merged cells that break alignment will drop those rows from the table.
• Quoted fields and escaped quotes are handled with explicit CSV parameters for PHP 8.4 compatibility.

Security and SSRF protection

Every remote source, including Google Sheets, passes through TC_Security::is_safe_url(), which delegates to WordPress core's wp_http_validate_url() to block private and loopback addresses before any request is made. The fetch itself uses cURL with full SSL verification enabled (CURLOPT_SSL_VERIFYPEER and CURLOPT_SSL_VERIFYHOST) and the WordPress-bundled CA certificates, so the connection to Google is authenticated and cannot be silently downgraded.

Caching and refresh behavior

Fetched sheet data is cached using a stale-while-revalidate (SWR) strategy. The first render fetches synchronously and stores the result; subsequent loads serve the cached copy instantly. When the cached HTML is older than five minutes, a background refresh is scheduled, and the underlying data cache uses a one-hour TTL. For tables that change often, add auto_refresh="true" with a suitable refresh_interval to re-pull the export on a timer.

Troubleshooting

Next, see csv-files.html for the shared CSV parsing rules that govern these tables, and auto-refresh.html for tuning live updates on a Google Sheets source.