# BigQuery · REST API

> BigQuery can be both a source and a destination for an automation: read a table or a query's results into a spreadsheet, or load a spreadsheet's rows…

HTML version: https://developers.sheetgo.com/api/bigquery/

[Developers](/index.md) › [REST API](/api.md) › [Storage](/api.md#api-endpoints)

REST API · 5 endpoints

## BigQuery

**Complete for this group.** Each operation below lists its parameters, request body, responses and errors. Every model they use is documented under [Models used on this page](#models), so you do not need another page to build a request. Base URL `https://api.sheetgo.com`; paths are complete, version included (`/rest/beta/…`): append them to the base URL exactly as shown; send `Authorization: Bearer sg_…`. Also in the API's own docs: <https://api.sheetgo.com/rest/reference/bigquery> ([Markdown](https://api.sheetgo.com/rest/reference/bigquery.md)).

**Conventions for every endpoint.** Lists take `page` (from 1) and `per_page` (1-100, default 50), and `total` counts **pages**, not items. Collection URLs end with `/`. `POST …/run` returns `200` when the run is *queued*; poll `GET …/run`. Google Sheets use `provider: "drive"`; column fields take letters (`"E"`). Error bodies and status codes: [REST API › Errors](/api.md#api-errors).

**Python recipes.** [API client helper](/examples.md#recipe-sheetgo-api).

**BigQuery** can be both a source and a destination for an automation: read a table or a query's results into a spreadsheet, or load a spreadsheet's rows into a BigQuery table.

These endpoints are the discovery half — list the **projects**, **datasets** and **tables** the authorized Google account can see, read one table's **typed schema**, and **dry-run a query** to check it parses and see what it would cost before you create the automation. All of it is read-only: `validate-query` runs nothing.

Authorizing BigQuery is a Google consent screen in the Sheetgo web app and cannot be driven from an API key. Until it is granted, these endpoints answer `412`.

## Operations

#### GET `/rest/beta/bigquery/projects` — List BigQuery projects

Every GCP project the authorized Google account can see.

`id` is the string project id — the value an automation's `project` field
expects. `numeric_id` is the same project's numeric form, accepted too.

An empty list means the account genuinely has no BigQuery projects. A
missing authorization is a `412`, never an empty list.

##### Parameters

| Name | In | Type |  | Description |
| --- | --- | --- | --- | --- |
| `page` | query | integer | optional | 1-based page number Default: `1` · Min: `1` |
| `per_page` | query | integer | optional | Items per page, max 100 Default: `50` · Min: `1` · Max: `100` |

##### Responses

| Status | Meaning | Body |
| --- | --- | --- |
| 200 | Projects visible to the account | [`BigQueryProjectListResponse`](#model-BigQueryProjectListResponse) |
| 401 | Authentication failed or token is invalid | — |
| 403 | The account lacks BigQuery permission | — |
| 412 | BigQuery is not authorized for this account | — |
| 422 | Validation Error | [`HTTPValidationError`](#model-HTTPValidationError) |
| 502 | BigQuery could not complete the request | — |

##### Example request

```
curl -s -X GET "https://api.sheetgo.com/rest/beta/bigquery/projects" \
  -H "Authorization: Bearer $SHEETGO_API_KEY"
```

In the API's own docs: <https://api.sheetgo.com/rest/reference/bigquery#get-rest-beta-bigquery-projects>

#### GET `/rest/beta/bigquery/projects/{project}/datasets` — List a project's datasets

Datasets in one project. `id` is what an automation's `dataset` expects.

A `bigquery` destination writes into an existing dataset — Sheetgo creates
the *table* if it is missing, but never the dataset.

##### Parameters

| Name | In | Type |  | Description |
| --- | --- | --- | --- | --- |
| `project` | path | string | required | Project id, as returned by GET /rest/beta/bigquery/projects |
| `page` | query | integer | optional | 1-based page number Default: `1` · Min: `1` |
| `per_page` | query | integer | optional | Items per page, max 100 Default: `50` · Min: `1` · Max: `100` |

##### Responses

| Status | Meaning | Body |
| --- | --- | --- |
| 200 | Datasets in the project | [`BigQueryDatasetListResponse`](#model-BigQueryDatasetListResponse) |
| 401 | Authentication failed or token is invalid | — |
| 403 | The account lacks BigQuery permission | — |
| 412 | BigQuery is not authorized for this account | — |
| 422 | Validation Error | [`HTTPValidationError`](#model-HTTPValidationError) |
| 502 | BigQuery could not complete the request | — |

##### Example request

```
curl -s -X GET "https://api.sheetgo.com/rest/beta/bigquery/projects/PROJECT/datasets" \
  -H "Authorization: Bearer $SHEETGO_API_KEY"
```

In the API's own docs: <https://api.sheetgo.com/rest/reference/bigquery#get-rest-beta-bigquery-projects-project-datasets>

#### GET `/rest/beta/bigquery/projects/{project}/datasets/{dataset}/tables` — List a dataset's tables

Tables in one dataset. `id` is what an automation's `table` expects.

A destination may also name a table that does not exist yet — it is created
on the first run — so this listing is a convenience, not a constraint.

##### Parameters

| Name | In | Type |  | Description |
| --- | --- | --- | --- | --- |
| `project` | path | string | required | Project id, as returned by GET /rest/beta/bigquery/projects |
| `dataset` | path | string | required | Dataset id |
| `page` | query | integer | optional | 1-based page number Default: `1` · Min: `1` |
| `per_page` | query | integer | optional | Items per page, max 100 Default: `50` · Min: `1` · Max: `100` |

##### Responses

| Status | Meaning | Body |
| --- | --- | --- |
| 200 | Tables in the dataset | [`BigQueryTableListResponse`](#model-BigQueryTableListResponse) |
| 401 | Authentication failed or token is invalid | — |
| 403 | The account lacks BigQuery permission | — |
| 412 | BigQuery is not authorized for this account | — |
| 422 | Validation Error | [`HTTPValidationError`](#model-HTTPValidationError) |
| 502 | BigQuery could not complete the request | — |

##### Example request

```
curl -s -X GET "https://api.sheetgo.com/rest/beta/bigquery/projects/PROJECT/datasets/DATASET/tables" \
  -H "Authorization: Bearer $SHEETGO_API_KEY"
```

In the API's own docs: <https://api.sheetgo.com/rest/reference/bigquery#get-rest-beta-bigquery-projects-project-datasets-dataset-tables>

#### GET `/rest/beta/bigquery/projects/{project}/datasets/{dataset}/tables/{table}` — Read one table's schema

One table's column types, row and byte counts, and partitioning.

The types matter for a `bigquery` destination: `partition.field` must name a
column, and BigQuery infers the partition type from that column rather than
taking it from the payload.

`partitioning` describes the table as it exists today. An existing table
keeps its own partitioning no matter what a destination payload asks for —
only `write_mode: "recreate"` replaces it.

##### Parameters

| Name | In | Type |  | Description |
| --- | --- | --- | --- | --- |
| `project` | path | string | required | Project id, as returned by GET /rest/beta/bigquery/projects |
| `dataset` | path | string | required | Dataset id |
| `table` | path | string | required | Table id |

##### Responses

| Status | Meaning | Body |
| --- | --- | --- |
| 200 | The table's typed schema and stats | [`BigQueryTableDetail`](#model-BigQueryTableDetail) |
| 401 | Authentication failed or token is invalid | — |
| 403 | The account lacks BigQuery permission | — |
| 404 | No such table | — |
| 412 | BigQuery is not authorized for this account | — |
| 422 | Validation Error | [`HTTPValidationError`](#model-HTTPValidationError) |
| 502 | BigQuery could not complete the request | — |

##### Example request

```
curl -s -X GET "https://api.sheetgo.com/rest/beta/bigquery/projects/PROJECT/datasets/DATASET/tables/TABLE" \
  -H "Authorization: Bearer $SHEETGO_API_KEY"
```

In the API's own docs: <https://api.sheetgo.com/rest/reference/bigquery#get-rest-beta-bigquery-projects-project-datasets-dataset-tables-table>

#### POST `/rest/beta/bigquery/projects/{project}/validate-query` — Validate a query without running it

Dry-run a query: check it parses, see what it would return and cost.

Nothing is scanned and nothing can be mutated, so this is safe to call
freely — which is why it needs only the `bigquery.read` scope despite being
a POST.

**Invalid SQL answers `200` with `valid: false`**, not a 4xx: "is this
query valid?" has *no* as a legitimate answer. A `4xx`/`5xx` here means the
request could not be answered at all — missing authorization, no
permission, or BigQuery being unreachable.

Use `total_bytes_processed` to size an uncapped source query before creating
the automation. A `bigquery` source's generated SQL has no `LIMIT`, so this
is how you find out what a full scan will cost.

##### Parameters

| Name | In | Type |  | Description |
| --- | --- | --- | --- | --- |
| `project` | path | string | required | Project id, as returned by GET /rest/beta/bigquery/projects |

##### Request body (required)

`application/json` · [`ValidateQueryRequest`](#model-ValidateQueryRequest)

| Field | Type |  | Description |
| --- | --- | --- | --- |
| `query` | string | required | The SQL to check. Standard SQL; legacy SQL is not used. |

##### Responses

| Status | Meaning | Body |
| --- | --- | --- |
| 200 | The dry run's verdict | [`ValidateQueryResponse`](#model-ValidateQueryResponse) |
| 401 | Authentication failed or token is invalid | — |
| 403 | The account lacks BigQuery permission | — |
| 412 | BigQuery is not authorized for this account | — |
| 422 | Validation Error | [`HTTPValidationError`](#model-HTTPValidationError) |
| 502 | BigQuery could not complete the request | — |

##### Example request

```
curl -s -X POST "https://api.sheetgo.com/rest/beta/bigquery/projects/PROJECT/validate-query" \
  -H "Authorization: Bearer $SHEETGO_API_KEY" \
  -H "Content-Type: application/json" \
  -d @body.json
```

In the API's own docs: <https://api.sheetgo.com/rest/reference/bigquery#post-rest-beta-bigquery-projects-project-validate-query>

## Models used on this page

Every request and response model referenced above, including nested ones (12). The full catalogue is on the [Models](/api/models.md) page.

#### `BigQueryDataset`

| Field | Type |  | Description |
| --- | --- | --- | --- |
| `id` | string | required | Dataset id. Pass this back as `dataset`. |
| `name` | string | nullable | Display name |

#### `BigQueryDatasetListResponse`

| Field | Type |  | Description |
| --- | --- | --- | --- |
| `page` | integer | nullable | Page number Default: `1` |
| `per_page` | integer | nullable | Number of items per page Default: `50` |
| `results` | array of [`BigQueryDataset`](#model-BigQueryDataset) | optional | List of results |
| `total` | integer | nullable | Total number of pages Default: `0` |
| `truncated` | boolean | optional | True when the listing hit `BIGQUERY_DISCOVERY_MAX_ITEMS`, so items past it are not reachable by paging. Default: `false` |

Used by: [GET /rest/beta/bigquery/projects/{project}/datasets](/api/bigquery.md#get-rest-beta-bigquery-projects-project-datasets)

#### `BigQueryPartitioningInfo`

How an existing table is partitioned.

An existing table keeps its own partitioning regardless of what a
destination payload asks for, so this is what a destination will actually
write into.

| Field | Type |  | Description |
| --- | --- | --- | --- |
| `field` | string | nullable | Partition column; null for ingestion-time partitioning |
| `granularity` | string | nullable | HOUR, DAY, MONTH or YEAR |
| `is_ingestion_time` | boolean | optional | True when partitioned by ingestion time Default: `false` |
| `type` | string | nullable | The column's BigQuery type |

#### `BigQueryProject`

One GCP project visible to the authorized account.

| Field | Type |  | Description |
| --- | --- | --- | --- |
| `id` | string | required | Project id. Pass this back as `project` in an automation source or destination. |
| `name` | string | nullable | Display name |
| `numeric_id` | string | nullable | The project's numeric id. Accepted as `project` too, but the string id above is the readable form. |

#### `BigQueryProjectListResponse`

| Field | Type |  | Description |
| --- | --- | --- | --- |
| `page` | integer | nullable | Page number Default: `1` |
| `per_page` | integer | nullable | Number of items per page Default: `50` |
| `results` | array of [`BigQueryProject`](#model-BigQueryProject) | optional | List of results |
| `total` | integer | nullable | Total number of pages Default: `0` |
| `truncated` | boolean | optional | True when the listing hit `BIGQUERY_DISCOVERY_MAX_ITEMS`, so items past it are not reachable by paging. Default: `false` |

Used by: [GET /rest/beta/bigquery/projects](/api/bigquery.md#get-rest-beta-bigquery-projects)

#### `BigQueryTable`

| Field | Type |  | Description |
| --- | --- | --- | --- |
| `id` | string | required | Table id. Pass this back as `table`. |
| `name` | string | nullable | Display name |

#### `BigQueryTableDetail`

One table, with the typed schema a destination's `partition.field` needs.

| Field | Type |  | Description |
| --- | --- | --- | --- |
| `dataset` | string | required | Dataset the table belongs to. |
| `num_bytes` | integer | nullable | Size in bytes |
| `num_rows` | integer | nullable | Row count as BigQuery reports it |
| `partitioning` | [`BigQueryPartitioningInfo`](#model-BigQueryPartitioningInfo) | nullable | Null when the table is not partitioned |
| `project` | string | required | Google Cloud project the table belongs to. |
| `schema` | object (map of string → string) | optional | Column name to BigQuery type, e.g. `{"Rank": "INTEGER", "created_at": "TIMESTAMP"}`. A destination's `partition.field` must name one of these, and its type is inferred from here. |
| `table` | string | required | The table's own id, unqualified. |

Used by: [GET /rest/beta/bigquery/projects/{project}/datasets/{dataset}/tables/{table}](/api/bigquery.md#get-rest-beta-bigquery-projects-project-datasets-dataset-tables-table)

#### `BigQueryTableListResponse`

| Field | Type |  | Description |
| --- | --- | --- | --- |
| `page` | integer | nullable | Page number Default: `1` |
| `per_page` | integer | nullable | Number of items per page Default: `50` |
| `results` | array of [`BigQueryTable`](#model-BigQueryTable) | optional | List of results |
| `total` | integer | nullable | Total number of pages Default: `0` |
| `truncated` | boolean | optional | True when the listing hit `BIGQUERY_DISCOVERY_MAX_ITEMS`, so items past it are not reachable by paging. Default: `false` |

Used by: [GET /rest/beta/bigquery/projects/{project}/datasets/{dataset}/tables](/api/bigquery.md#get-rest-beta-bigquery-projects-project-datasets-dataset-tables)

#### `HTTPValidationError`

| Field | Type |  | Description |
| --- | --- | --- | --- |
| `detail` | array of [`ValidationError`](#model-ValidationError) | optional |  |

Used by: [GET /rest/beta/workflows/](/api/workflows.md#get-rest-beta-workflows), [POST /rest/beta/workflows/](/api/workflows.md#post-rest-beta-workflows), [POST /rest/beta/workflows/run/{id}](/api/workflows.md#post-rest-beta-workflows-run-id), [GET /rest/beta/workflows/{id}](/api/workflows.md#get-rest-beta-workflows-id), [PATCH /rest/beta/workflows/{id}](/api/workflows.md#patch-rest-beta-workflows-id), [DELETE /rest/beta/workflows/{id}](/api/workflows.md#delete-rest-beta-workflows-id), [GET /rest/beta/workflows/{id}/collaborators](/api/workflows.md#get-rest-beta-workflows-id-collaborators), [POST /rest/beta/workflows/{id}/collaborators](/api/workflows.md#post-rest-beta-workflows-id-collaborators), [DELETE /rest/beta/workflows/{id}/collaborators](/api/workflows.md#delete-rest-beta-workflows-id-collaborators), [PATCH /rest/beta/workflows/{id}/collaborators/{email}](/api/workflows.md#patch-rest-beta-workflows-id-collaborators-email), [DELETE /rest/beta/workflows/{id}/collaborators/{email}](/api/workflows.md#delete-rest-beta-workflows-id-collaborators-email), [GET /rest/beta/workflows/{id}/destinations](/api/workflows.md#get-rest-beta-workflows-id-destinations), [GET /rest/beta/workflows/{id}/files](/api/workflows.md#get-rest-beta-workflows-id-files), [GET /rest/beta/workflows/{id}/general-access](/api/workflows.md#get-rest-beta-workflows-id-general-access), [PUT /rest/beta/workflows/{id}/general-access](/api/workflows.md#put-rest-beta-workflows-id-general-access), [DELETE /rest/beta/workflows/{id}/general-access](/api/workflows.md#delete-rest-beta-workflows-id-general-access), [POST /rest/beta/workflows/{id}/leave](/api/workflows.md#post-rest-beta-workflows-id-leave), [POST /rest/beta/workflows/{id}/request-access](/api/workflows.md#post-rest-beta-workflows-id-request-access), [GET /rest/beta/workflows/{id}/run](/api/workflows.md#get-rest-beta-workflows-id-run), [POST /rest/beta/workflows/{id}/run](/api/workflows.md#post-rest-beta-workflows-id-run), [GET /rest/beta/workflows/{id}/sources](/api/workflows.md#get-rest-beta-workflows-id-sources), [GET /rest/beta/automations/](/api/automations.md#get-rest-beta-automations), [POST /rest/beta/automations/run/{id}](/api/automations.md#post-rest-beta-automations-run-id), [GET /rest/beta/automations/{id}](/api/automations.md#get-rest-beta-automations-id), [DELETE /rest/beta/automations/{id}](/api/automations.md#delete-rest-beta-automations-id), [GET /rest/beta/automations/{id}/destinations](/api/automations.md#get-rest-beta-automations-id-destinations), [GET /rest/beta/automations/{id}/files](/api/automations.md#get-rest-beta-automations-id-files), [GET /rest/beta/automations/{id}/run](/api/automations.md#get-rest-beta-automations-id-run), [POST /rest/beta/automations/{id}/run](/api/automations.md#post-rest-beta-automations-id-run), [GET /rest/beta/automations/{id}/sources](/api/automations.md#get-rest-beta-automations-id-sources), [GET /rest/beta/workflows/{id}/trigger](/api/triggers.md#get-rest-beta-workflows-id-trigger), [DELETE /rest/beta/workflows/{id}/trigger](/api/triggers.md#delete-rest-beta-workflows-id-trigger), [GET /rest/beta/workflows/{id}/dashboard](/api/dashboards.md#get-rest-beta-workflows-id-dashboard), [DELETE /rest/beta/workflows/{id}/dashboard/elements/{element\_id}](/api/dashboards.md#delete-rest-beta-workflows-id-dashboard-elements-element-id), [GET /rest/beta/workflows/{id}/dashboard/elements/{element\_id}/data](/api/dashboards.md#get-rest-beta-workflows-id-dashboard-elements-element-id-data), [PUT /rest/beta/workflows/{id}/dashboard/publish](/api/dashboards.md#put-rest-beta-workflows-id-dashboard-publish), [GET /rest/beta/workflows/{workflow\_id}/forms/](/api/forms.md#get-rest-beta-workflows-workflow-id-forms), [POST /rest/beta/workflows/{workflow\_id}/forms/](/api/forms.md#post-rest-beta-workflows-workflow-id-forms), [GET /rest/beta/workflows/{workflow\_id}/forms/{form\_id}](/api/forms.md#get-rest-beta-workflows-workflow-id-forms-form-id), [PATCH /rest/beta/workflows/{workflow\_id}/forms/{form\_id}](/api/forms.md#patch-rest-beta-workflows-workflow-id-forms-form-id), [DELETE /rest/beta/workflows/{workflow\_id}/forms/{form\_id}](/api/forms.md#delete-rest-beta-workflows-workflow-id-forms-form-id), [GET /rest/beta/workflows/{workflow\_id}/forms/{form\_id}/responses](/api/forms.md#get-rest-beta-workflows-workflow-id-forms-form-id-responses), [HEAD /rest/beta/workflows/{workflow\_id}/forms/{form\_id}/responses](/api/forms.md#head-rest-beta-workflows-workflow-id-forms-form-id-responses), [GET /rest/beta/automations/{automation\_id}/webhook](/api/webhook.md#get-rest-beta-automations-automation-id-webhook), [GET /rest/beta/workflows/{workflow\_id}/webhooks](/api/webhook.md#get-rest-beta-workflows-workflow-id-webhooks), [GET /rest/beta/api-destination/{public\_id}](/api/api-destination.md#get-rest-beta-api-destination-public-id), [HEAD /rest/beta/api-destination/{public\_id}](/api/api-destination.md#head-rest-beta-api-destination-public-id), [GET /rest/beta/integrations/{provider}/options](/api/integrations.md#get-rest-beta-integrations-provider-options), [GET /rest/beta/outlook/messages](/api/outlook.md#get-rest-beta-outlook-messages), [POST /rest/beta/outlook/messages/batch](/api/outlook.md#post-rest-beta-outlook-messages-batch), [GET /rest/beta/outlook/messages/{message\_id}](/api/outlook.md#get-rest-beta-outlook-messages-message-id), [GET /rest/beta/outlook/messages/{message\_id}/attachments/{attachment\_id}](/api/outlook.md#get-rest-beta-outlook-messages-message-id-attachments-attachment-id), [POST /rest/beta/outlook/send](/api/outlook.md#post-rest-beta-outlook-send), [GET /rest/beta/gmail/messages](/api/gmail.md#get-rest-beta-gmail-messages), [POST /rest/beta/gmail/messages/batch](/api/gmail.md#post-rest-beta-gmail-messages-batch), [GET /rest/beta/gmail/messages/{message\_id}](/api/gmail.md#get-rest-beta-gmail-messages-message-id), [GET /rest/beta/gmail/messages/{message\_id}/attachments/{attachment\_id}](/api/gmail.md#get-rest-beta-gmail-messages-message-id-attachments-attachment-id), [POST /rest/beta/gmail/send](/api/gmail.md#post-rest-beta-gmail-send), [GET /rest/beta/files/{provider}](/api/files.md#get-rest-beta-files-provider), [GET /rest/beta/files/{provider}/roots](/api/files.md#get-rest-beta-files-provider-roots), [GET /rest/beta/files/{provider}/search](/api/files.md#get-rest-beta-files-provider-search), [GET /rest/beta/files/{provider}/{file\_id}](/api/files.md#get-rest-beta-files-provider-file-id), [GET /rest/beta/bigquery/projects](/api/bigquery.md#get-rest-beta-bigquery-projects), [GET /rest/beta/bigquery/projects/{project}/datasets](/api/bigquery.md#get-rest-beta-bigquery-projects-project-datasets), [GET /rest/beta/bigquery/projects/{project}/datasets/{dataset}/tables](/api/bigquery.md#get-rest-beta-bigquery-projects-project-datasets-dataset-tables), [GET /rest/beta/bigquery/projects/{project}/datasets/{dataset}/tables/{table}](/api/bigquery.md#get-rest-beta-bigquery-projects-project-datasets-dataset-tables-table), [POST /rest/beta/bigquery/projects/{project}/validate-query](/api/bigquery.md#post-rest-beta-bigquery-projects-project-validate-query), [POST /rest/auth/refresh](/api/auth.md#post-rest-auth-refresh), [POST /rest/auth/token](/api/auth.md#post-rest-auth-token)

#### `ValidateQueryRequest`

| Field | Type |  | Description |
| --- | --- | --- | --- |
| `query` | string | required | The SQL to check. Standard SQL; legacy SQL is not used. |

Used by: [POST /rest/beta/bigquery/projects/{project}/validate-query](/api/bigquery.md#post-rest-beta-bigquery-projects-project-validate-query)

#### `ValidateQueryResponse`

The dry run's verdict. Nothing was scanned and nothing was mutated.

A query that will not parse is a legitimate answer here, not an error: this
endpoint returns `200` with `valid: false`.

| Field | Type |  | Description |
| --- | --- | --- | --- |
| `error` | string | nullable | BigQuery's explanation when `valid` is false |
| `schema` | object (map of string → string) | nullable | Columns the query would return, with their types |
| `statement_type` | string | nullable | `SELECT`, `INSERT`, `DELETE`, … as BigQuery classifies it. Note a valid `DELETE` reports `valid: true` — this endpoint reports what the statement *is*, it does not refuse writes. |
| `total_bytes_processed` | integer | nullable | Bytes the query would scan. Use it to size the cost of an uncapped source query before creating the automation. |
| `valid` | boolean | required | False when BigQuery rejected the SQL |

Used by: [POST /rest/beta/bigquery/projects/{project}/validate-query](/api/bigquery.md#post-rest-beta-bigquery-projects-project-validate-query)

#### `ValidationError`

| Field | Type |  | Description |
| --- | --- | --- | --- |
| `loc` | array of string or integer | required |  |
| `msg` | string | required |  |
| `type` | string | required |  |