Google Sheets#

Google Sheets is registered for strict TeamBoost-template CSV import and OAuth-backed snapshots of one explicitly selected private spreadsheet. Both methods create the same reviewable import-session shape; they do not modify the workbook or update/delete TeamBoost tasks created by an earlier import.

The generic endpoints below live under /api/v1/providers/google-sheets/... and require an organization-scoped user token, except the signed OAuth redirect callback.

Template v1#

The first worksheet must be a grid named Tasks. Row 1 starts with these exact columns, in this order:

Template Version,Task ID,Title,Type,Status,Priority,Description,Labels,Creator Email,Assignee Email,Requester Email,Project,Task Group,Planned Start,Planned End,Duration,Parent Task ID,Previous Dependency IDs,Next Dependency IDs,Created At,Updated At

Up to 43 custom columns may follow the 21 canonical columns. Canonical columns cannot be renamed, reordered, or interleaved with custom columns. Custom values are preserved in the lossless source record and in the projected draft's descriptionJson.customColumns; they are not mapped to TeamBoost fields.

Each nonblank row is one task:

• Template Version must be 1.
• Task ID, Title, Type, Created At, and Updated At are required. Task IDs must be unique in the import.
• Type, Status, and Priority use the exact uppercase TeamBoost values.
• timestamps are plain-text ISO-8601 with an explicit offset; numeric Sheets date serials and locale-formatted dates are rejected;
• actor columns contain one valid e-mail address each;
• labels and dependency IDs are semicolon-delimited;
• duration is a positive TeamBoost days.hours value with at most one decimal digit; and
• dependency IDs must reference a different valid Task ID in the same import.

Malformed task rows are returned as row errors while valid sibling rows remain reviewable. Files are bounded to 5,000 data rows, 64 columns, and 50,000 characters per cell.

CSV import#

POST /api/v1/providers/google-sheets/imports/csv?organization_id=<id>

curl -X POST "https://<host>/api/v1/providers/google-sheets/imports/csv?organization_id=590789" \
  -H "Authorization: Bearer <user-token>" \
  -F "file=@teamboost-tasks.csv"

The upload accepts UTF-8 CSV, including a UTF-8 BOM, quoted commas, and multiline cells. The generic upload ceiling is 4 MB.

Google Cloud and OAuth setup#

1. Enable the Google Sheets API, Google Drive API, and Google Picker API in one non-production Google Cloud project.
2. Configure the Google Auth consent screen and test users or publishing status.
3. Create an OAuth 2.0 web client and register the callback URI exactly.
4. Configure the service; never commit the client secret.

GOOGLE_SHEETS_CLIENT_ID=<google-oauth-client-id>
GOOGLE_SHEETS_CLIENT_SECRET=<google-oauth-client-secret>
GOOGLE_SHEETS_REDIRECT_URI=https://<host>/api/v1/providers/google-sheets/oauth/callback
GOOGLE_SHEETS_SCOPES=https://www.googleapis.com/auth/drive.file

Only the non-sensitive, per-file drive.file scope is accepted. Broader Drive or Sheets scopes fail configuration.

Start the connection:

GET /api/v1/providers/google-sheets/oauth/authorize?organization_id=<id>

The authorization URL launches Google OnePick with the Google Sheets MIME filter and no multi-select option. The callback must return exactly one unique, opaque file ID and the exact drive.file scope:

GET /api/v1/providers/google-sheets/oauth/callback
    ?state=<signed-state>
    &code=<authorization-code>
    &scope=https://www.googleapis.com/auth/drive.file
    &picked_file_ids=<one-spreadsheet-id>

The callback verifies the selected file's Google Sheets MIME type, connected Google account identity, first Tasks grid, unmerged header, and exact template columns. It stores the OAuth tokens, spreadsheet ID, stable sheet ID, and safe display metadata as one encrypted, organization-scoped credential. Tokens and The OAuth callback never returns tokens or selected resource IDs, and resource IDs are not logged. Authorized import-session provenance retains the selected spreadsheet and worksheet IDs so an operator can audit the source. Reconnect to choose another spreadsheet.

Credential operations and sync#

GET /api/v1/providers/google-sheets/credentials?organization_id=<id>
POST /api/v1/providers/google-sheets/credentials/verify?organization_id=<id>
DELETE /api/v1/providers/google-sheets/credentials?organization_id=<id>
POST /api/v1/providers/google-sheets/imports/sync?organization_id=<id>

Verification checks the live account, selected file, spreadsheet, stable Tasks sheet ID, and header. Expiring access tokens refresh automatically without changing the account or selected-resource boundary.

Sync reads a fresh bounded snapshot:

1. minimal spreadsheet and first-sheet metadata;
2. 'Tasks'!A1:BL1 with UNFORMATTED_VALUE to validate the header; then
3. only the discovered template columns through row 5,001.

Each Google response is capped at 2 MB. Calls retry four times at most for 429, 500, and 503 responses; authentication, permission, missing-resource, template, and malformed-data failures are not retried. Numeric values in canonical plain-text fields are rejected instead of being locale-converted.

Projection and users#

USER_IMPORT is sync/import-coupled. Only explicit creator, assignee, and requester e-mails become mapping candidates; the adapter does not inspect spreadsheet collaborators or query a Google Workspace directory.

Not built#

• Arbitrary spreadsheets or configurable column mapping.
• Public-spreadsheet API-key credentials.
• Service accounts or domain-wide delegation.
• Multiple spreadsheets or tabs per organization credential.
• Native row webhooks, Drive watch channels, or preview Workspace Events/Pub/Sub.
• Scheduled/background, incremental, or bidirectional sync.
• Workbook write-back, reconciliation, or propagation of edits/deletions.
• Execution of confirmed import sessions into TeamBoost; the current flow ends at review and confirmation.