A surprising amount of real business logic lives in Google Sheets: price lists, rosters, inventory counts, campaign trackers. The data is genuinely useful and genuinely hard to get at programmatically. The usual workarounds are App Script endpoints, scheduled CSV exports, or "publish to web" links that break the moment someone reorders a column.
Dataloom treats a spreadsheet as a data source like any other. Each sheet behaves as a table, the header row supplies the column names, and the rows beneath are your records. From that, Dataloom generates REST endpoints with proper documentation, API keys, filtering, sorting and pagination.
The sheet stays the source of truth, and the people who maintain it carry on working in the interface they already know. What changes is that everything else — applications, integrations, AI assistants — can now read it over HTTP without anybody emailing a file around.
Connecting a Google Sheet
Connecting uses a Google service account rather than your personal login, which is what keeps the integration stable when people join or leave. Create a service account in Google Cloud, download its JSON key, and give Dataloom that key along with the spreadsheet ID from the sheet URL.
Then share the spreadsheet with the service account’s email address, exactly as you would share it with a colleague. Give it Viewer access for read-only endpoints or Editor access if you intend to publish endpoints that write rows. If the share step is missed the connection test fails with a permission error, which is the usual cause of a first-attempt failure.
Dataloom reads the sheet names as tables and the first row of each as column headers. Keep those headers stable and meaningful — they become your API field names, and they are what your AI assistant sees when deciding how to filter.
What you get with Google Sheets
- Each sheet in the spreadsheet becomes a table
- The header row supplies column names
- Service-account authentication, not a personal Google login
- Filtering, sorting and pagination over sheet rows
- Read-only or read-write depending on the sharing level you grant
- The spreadsheet remains the source of truth and stays editable by hand
An example endpoint
Open leads from one region, highest value first.
GET /crm-sheet/leads
?filter={"Region":"Western Cape","Status":"open"}
&sort=EstimatedValue&order=desc
&page=1&pageSize=15
X-API-Key: your_api_key{
"data": [
{ "LeadName": "Table Bay Aquatics", "Region": "Western Cape",
"Status": "open", "EstimatedValue": 84000, "Owner": "N. Petersen" },
{ "LeadName": "Cape Reef Supply", "Region": "Western Cape",
"Status": "open", "EstimatedValue": 51500, "Owner": "A. Mbeki" }
],
"meta": { "page": 1, "pageSize": 15, "totalCount": 28,
"totalPages": 2, "hasNextPage": true }
}Filtering, sorting and pagination work the same way on every endpoint, whatever the source. See the no-code API builder for how endpoints are put together.
Connect Claude to Google Sheets
Once an endpoint is published, it is also a tool on your workspace’s hosted MCP server — which is where spreadsheets get genuinely interesting. Instead of exporting a sheet and pasting it into a chat, your assistant queries it live and gets current values every time.
You can ask for "open leads in the Western Cape above R50,000" and Claude will call the endpoint with the right filter rather than paging through the whole sheet. Because the column names come from your header row, the filters it builds line up with the sheet as it actually is.
If you granted Editor access and published write endpoints, the assistant can update rows too — still bounded by the endpoints you chose to expose, still authenticated with OAuth 2.1, and still revocable per assistant.
// mcp-config.json
{
"mcpServers": {
"dataloom": {
"url": "https://mcp.dataloom.cloud/w/your-workspace",
"transport": "http"
}
}
}Read more about the hosted MCP server, or follow the step-by-step setup guide.
Google Sheets questions, answered
Why does Dataloom need a service account instead of my Google login?
A service account keeps the connection stable and independent of any one person’s account, and it lets you grant access to just the spreadsheets you choose by sharing them with it.
My connection test fails with a permission error. Why?
Almost always because the spreadsheet has not been shared with the service account. Copy the client_email from the JSON key and share the sheet with that address.
How are columns determined?
From the first row of each sheet. Those header values become your API field names, so keep them stable — renaming a header changes the field name callers depend on.
Can the API write back to the sheet?
Yes, if the service account has Editor access and you publish write endpoints. With Viewer access the spreadsheet is read-only through the API.