SharePoint to Google BigQuery

Last updated 19 October 2026

One way: SharePoint stays the system of record, and SPSync keeps a read-only copy of your lists current in Google BigQuery.

Status: Shipped · product: SPSync for Warehouses & Lakes

On Google Cloud, BigQuery is often where the rest of an organisation’s data already lives. Replicating SharePoint lists there puts them in the same SQL as everything else: Looker Studio and Looker dashboards, scheduled queries, notebooks and dbt models, with no cluster to size for the replication itself.

The service account and the dataset

BigQuery has no connection string. SPSync reads the service account’s downloaded JSON key from an environment variable, so the key never appears in the configuration file. Grant that account two roles:

• BigQuery Data Editor on the project or the target dataset, to create and alter tables and write rows;
• BigQuery Job User, to run load, query and DML jobs.

The destination’s dataset name is the whole namespace. If the dataset does not exist, SPSync creates it in the service account’s own project, in the region you set, US unless you choose another such as EU or europe-west2. A dataset that already exists keeps its region. Datasets in a different project are not supported.

How rows arrive

Each list becomes a table in that dataset, for example hr_leave_requests, with child tables such as hr_leave_requests__approvers for multi-value columns and one sp_users table for people. Lookups arrive as {col}_id and {col}_value. Text of every length is STRING, whole numbers are INT64, currency is NUMERIC(38,9), timestamps are UTC TIMESTAMP and JSON-shaped values use BigQuery’s native JSON type.

After the first load, each run merges changed rows with BigQuery’s MERGE statement. Deleted items stay with sp_deleted_at set, and the command-line tool’s hard delete setting issues a plain DELETE instead. Optional reporting views are available here too. A new SharePoint column appears on the next run and a removed one stays; a retyped column is not forked into a _v2 column on BigQuery today (SPSync does that on SQL Server, PostgreSQL, MySQL, ClickHouse, Oracle, SQLite and DuckDB).

Typical uses

KPI dashboards in Looker Studio or Looker read the tables on the sync schedule, with no spreadsheet export in between. dbt models add the dataset as another source. Data preparation for models starts from typed, deduplicated rows. A set of extraction scripts run by hand becomes one replication that is reviewed in a pull request.

Limits to know

• SPSync caps generated names at 63 bytes, well inside BigQuery’s own limit.
• BigQuery does not enforce primary keys, foreign keys or uniqueness, so SPSync’s own write path keeps one row per item.
• SPSync loads with batch load jobs. A schedule of a few minutes against a brand new table can still meet BigQuery’s own consistency rules for very recent writes, which differ from a traditional database’s.

Setting it up

1. Sign in with delegated access, or have an admin register an app with Sites.Selected on the sites you need for unattended runs.
2. Create the service account, grant the two roles and download its key.
3. Put the key in an environment variable and set the dataset name and region.
4. Select the lists or a whole site, then run.
5. Connect Looker Studio, Looker or a notebook to the dataset.

BigQuery bills storage and query bytes scanned separately from SPSync.

spsync auth login --client-id 00000000-0000-0000-0000-000000000000
spsync export-config --output replication.yaml --pipeline-name bigquery --prefix bigquery --destination-kind bigquery --connection-string-env SPSYNC_DB_CONNECTION --dataset-name bigquery --client-id 00000000-0000-0000-0000-000000000000 --site-url https://contoso.sharepoint.com/sites/example --all-lists
spsync doctor replication.yaml
spsync run replication.yaml

pipeline_name: bigquery
prefix: bigquery
auth:
  mode: delegated
  client_id: 00000000-0000-0000-0000-000000000000
  tenant_id: common
destination:
  kind: bigquery
  connection_string_env: SPSYNC_DB_CONNECTION
  dataset_name: bigquery
scopes:
  - site_url: https://contoso.sharepoint.com/sites/example
    mode: entire_site
reconcile_interval_days: 7
create_reporting_views: false

# every night at 02:00
0 2 * * * cd /opt/spsync && SPSYNC_DB_CONNECTION="$(cat /etc/spsync/db.env)" uv run spsync run replication.yaml >> /var/log/spsync/run.log 2>&1

# .github/workflows/spsync-run.yml
name: SPSync
on:
  schedule:
    - cron: "0 2 * * *"
  workflow_dispatch: {}
jobs:
  run:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: astral-sh/setup-uv@v3
      - run: uv sync
      - run: uv run spsync run replication.yaml
        env:
          SPSYNC_DB_CONNECTION: ${{ secrets.SPSYNC_DB_CONNECTION }}
          SPSYNC_LICENCE_KEY: ${{ secrets.SPSYNC_LICENCE_KEY }}

# azure-pipelines.yml
schedules:
  - cron: "0 2 * * *"
    branches: { include: [main] }
    always: true
steps:
  - script: uv sync
  - script: uv run spsync run replication.yaml
    env:
      SPSYNC_DB_CONNECTION: $(SPSYNC_DB_CONNECTION)
      SPSYNC_LICENCE_KEY: $(SPSYNC_LICENCE_KEY)

# Windows Task Scheduler action (see /docs/cli/automation/)
$env:SPSYNC_DB_CONNECTION = (Get-Content C:\spsync\db.env)
uv run spsync run C:\spsync\replication.yaml
schtasks /Create /SC DAILY /ST 02:00 /TN "SPSync" /TR "powershell -File C:\spsync\run.ps1"

# ~/Library/LaunchAgents/io.spsync.run.plist, then: launchctl load ~/Library/LaunchAgents/io.spsync.run.plist