Google Sheets
How to connect Google Sheets, so people can read a sheet by pasting its link and, when an admin allows it, add rows and change cells after confirming a preview.
Status: Available. Built on Google's published Sheets API, and validated in your account during onboarding.
| Source | Kind | Reads | Changes | People sign in with |
|---|---|---|---|---|
| Google Sheets | google_sheets |
The tabs of one sheet, named by its link or id | Add a row, update cells of a row, after the person confirms (off unless an admin turns it on). No deletes. | Their own Google account |
At a glance
| Connects through | Sign-in | Network | Status | First test question | |
|---|---|---|---|---|---|
| Google Sheets | Google Sheets API | Personal sign-in | Nothing to open | Available | "Which tabs does this sheet have? <the sheet's link>" |
Add each source with the usual steps. The network answers are explained in What your network needs. If the first test question fails, the error message tells you what to fix: look it up under When something goes wrong below.
How it works
- Each person signs in with their own Google account. Google applies that person's own sharing: they read only sheets shared with them, and change only sheets they can edit.
- A separate source from Google Drive. A Google Drive source asks only for read-only Drive access (and, only where an admin lets agents save files to a folder, for adding files). A Google Sheets source asks only for
https://www.googleapis.com/auth/spreadsheets.readonly(read sheets), plusopenidandemailso SourceLace knows who signed in for the audit trail. Only where an admin turns on changes does it also ask forhttps://www.googleapis.com/auth/spreadsheets, which Google requires for adding rows and changing cells. Connecting one never grants the other. - Sheets are named by link. The Sheets permission cannot list or search files, so people paste the sheet's link (or its id). An admin can also list sheets people use often; their tabs then show in
search_schema. - Not copied. Rows are read when asked and held like any other result (up to 30 minutes), never written to a database except inside a saved chat that shows them. Every answer is read live from your systems, unless your organization has chosen Saved answers, an optional add-on your admin turns on system by system.
Set up
What your admin does in Google Workspace (Sheets)
Nothing is registered: SourceLace provides the Google app. Only if your organization restricts third-party apps does your Google Workspace admin trust it, once. About 5 minutes.
Who: a Google Workspace super admin (or an admin with the Security settings privilege).
- Check whether apps are restricted. In the Google Admin console: Security โ Access and data control โ API controls. If unconfigured third-party apps are blocked, or Sheets (Drive) is a restricted service, continue with step 2; otherwise there is nothing to do.
- Trust SourceLace. On the same page, Manage Third-Party App Access: find SourceLace (listed once anyone tried to connect; otherwise Add app and search for it), set its access to Trusted (or Specific Google data, allowing Drive and Sheets), and Save.
- Share the sheets. People read and change only sheets shared with them in Google; changes need Editor access.
Check that it worked: one person connects and pastes a sheet's link, asking what its tabs are.
Google's own guide: Google Workspace Admin Help (search for "Control which third-party apps access Google Workspace data").
Add the source (SourceLace admin)
Kind: Google Sheets (google_sheets).
| Option | Type | Default | Example | What it is |
|---|---|---|---|---|
spreadsheets |
List, separated by commas | (none) | https://docs.google.com/spreadsheets/d/1AbC.../edit |
Optional. Links or ids of sheets people use often, at most 20. Their tabs show in search_schema. People can still use any other sheet shared with them by pasting its link. |
To allow changes, turn on Allow changes and type * under Objects that can be changed (an empty list allows nothing) (every sheet people can edit), or name tabs as <sheet id>/<tab name>. Turning changes on adds the full Sheets permission to the sign-in, so each person connects the source again once and ticks every box on Google's page (people who connected before changes were possible keep working without it). Google still refuses changes to sheets the person cannot edit.
What people can do
An object is one tab, written <sheet id>/<tab name>. Anywhere a sheet is named, its full link (https://docs.google.com/spreadsheets/d/<id>/edit#gid=0) or its bare id works.
describe_objecton a sheet's link lists its tabs. On<id>/<tab name>it lists the tab's columns (from row 1) and the size of its grid. A link with#gid=...picks that tab.query(languagesheets) reads a tab. It takes one JSON object, such as{"sheet": "https://docs.google.com/spreadsheets/d/1AbC.../edit", "tab": "Pipeline", "range": "A1:H500"}. Onlysheetis required. Withouttab, SourceLace reads the tab the link points at, or the first tab; withoutrange, the whole tab (up to the row limit). Withheader(the default,true), row 1 of the range names the columns, and an empty header cell is named by its column letter.limitreturns at most that many rows. Every row also has_row, its row number in the sheet. Empty rows are skipped. Values come back as the sheet shows them (dates as dates, currency with its symbol, formula results).get_recordwith object<id>/<tab name>and a row number returns that row.- A tab can be one of the tables in a question that combines systems, for example each account in Salesforce with its quota from a tab. Name the tab as
<sheet id>/<tab name>, or paste the tab's own link. Everything in a sheet is text, so convert a number before comparing it (for exampleCAST(t."Quota" AS BIGINT)).
Changes
Through propose_change and apply_change, like every other source: the person sees a preview, and nothing is written until they confirm.
- Add a row (create): adds one row after the table on that tab. Fields are named by the column names in row 1 (any capitalization); columns not named stay empty. Names that are not in row 1 are listed as problems in the preview.
- Update cells (update): changes some cells of one row. The record id is the row number (the
_rowvalue, 2 or more). The preview shows each cell's current value, and anullvalue empties the cell. Just before writing, SourceLace reads the row and row 1 again; if either changed since the preview (for example someone inserted a row above, so row 12 is now a different row), the change is refused and must be proposed again. - Deleting rows is not offered: deleting a row changes the number of every row below it. To empty a row, update its cells to
null.
Values are always written RAW: Google stores exactly what was given and never treats it as a formula. Text such as =IMPORTXML(...) or +1 555 0100 stays plain text.
SourceLace only ever reads a sheet's title and tabs, reads cells, appends a row and sets cells. It has no call that deletes rows or tabs, clears ranges, or changes a sheet's structure or sharing.
When something goes wrong
| What you see | What to do |
|---|---|
| "Google did not grant access to Google Sheets. Connect again and tick the box on Google's consent screen." | Connect again and tick the Sheets box. |
| "Google did not let SourceLace change sheets, which your admin turned on ..." or "Google Sheets: this sign-in does not allow that. Connect the source again." | The person connected before changes were turned on, or left a box unticked. |
| "Sign-in was not completed: admin_policy_enforced. Your Google Workspace admin's policy blocks SourceLace from Google Sheets ..." | A super admin trusts SourceLace (step 2 of What your admin does). |
| "Sign-in was not completed: access_denied. You chose Cancel on Google's page ..." | Connect again and allow every permission. |
| "This link is an Excel file stored in Google Drive, not a Google Sheet ..." | Open it, choose File โ Save as Google Sheets, and use the new sheet's link. |
| "Allow changes is on for ..., but Objects that can be changed is empty ..." | An admin types * (or <sheet id>/<tab name>) under Objects that can be changed. |
| "Google Sheets: this sign-in does not allow that. Connect the source again." | Connect again and tick every box. |
| "Google Sheets: that sheet was not found, or it is not shared with you." | Check the link; ask the sheet's owner to share it with you. |
| "Google Sheets: you do not have access to this sheet. Ask its owner to share it with you." | Ask the owner to share it. |
| "Google Sheets: you do not have edit access to this sheet. Ask its owner to share it with you as an editor." | Changes need edit access in Google. |
| "This sheet has no tab named '...'. Its tabs: ..." | Use one of the tab names listed. |
| "Google Sheets: that tab or range does not exist. describe_object on the sheet lists its tabs." | Check the tab name and range. |
| "Name the tab as <spreadsheet id>/<tab name>, as describe_object on the sheet's link lists it, or paste the tab's own link (it ends in #gid=<number>)." | You named a whole sheet where a tab was needed; add the tab name after the id. Add the tab name after the id, or paste the tab's own link. |
| "Google Sheets: the tab has too many empty rows to find the rows asked for in 500 reads. Name a range that holds the data, such as "range": "A1:H5000", in a sheets query." | A tab has so many empty rows that its data could not be found. Give the range that holds the data. |
| "Row 1 has no column named '...'. Use describe_object to see the column names." | Name fields exactly as row 1 does. |
| "Google Sheets: row 1 of ... is empty. SourceLace needs column names in row 1 to change rows." | Put column names in row 1 of the tab. |
| "Row ... of ... changed after the preview (or rows moved). Propose the change again." | Someone changed the sheet; ask for a new preview. |
| "Google Sheets rows cannot be deleted through SourceLace ..." | Update the row's cells to null instead. |
| "Google Sheets is limiting how fast SourceLace can call it. Wait a minute and try again." | Wait, then try again. |
| "The spreadsheets option of ... lists at most 20 sheets." | Shorten the spreadsheets option. |
| "The google_sheets connector is not set up on SourceLace yet..." | SourceLace's Google app is not ready yet. Contact support@sourcelace.com. |
| "Google Sheets: this spreadsheet has no tabs." | The sheet has no tabs SourceLace can read. |
| "Google Sheets sent back something other than data." or "SourceLace refused to make that call to Google Sheets." | Google answered with something SourceLace cannot read as data, or the call was to an address outside Google. |