> ## Documentation Index
> Fetch the complete documentation index at: https://support.configview.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Google Sheets setup

> Ingest data from Google Sheets into ConfigView, with each tab becoming its own table.

<div align="center">
  <img src="https://mintcdn.com/acme-6639573a/CrY3kmWcaVg9rZ-q/images/google-sheets.svg?fit=max&auto=format&n=CrY3kmWcaVg9rZ-q&q=85&s=5d679c65d29246046072decfdbc90c98" width="80" data-path="images/google-sheets.svg" />
</div>

## Part 1: Google Sheets Data Ingestion

Set up a Google service account so ConfigView can read your spreadsheets and pull each tab into its own database table.

***

### Step 1: Create a Google Cloud project

1. Go to the [Google Cloud Console](https://console.cloud.google.com/).
2. Click the **project dropdown** (top-left) > **New Project**.
3. Name it `configview-integration` (or reuse the project you created for other ConfigView integrations), pick your Organization, and click **Create**.

***

### Step 2: Enable the Sheets and Drive APIs

1. In the Cloud Console, open **APIs & Services** > **Library**.
2. Search for **Google Sheets API** and click **Enable**.
3. Go back to the Library and search for **Google Drive API**. Click **Enable**.

> Both APIs are required. Sheets API reads cell values; Drive API is used to open the spreadsheet by its file ID.

***

### Step 3: Create a service account and download the JSON key

1. Open **IAM & Admin** > **Service Accounts** > **Create Service Account**.
2. Name it `configview-sheets` and click **Create and continue**. No project roles are needed — click **Continue** then **Done**.
3. Open the service account you just created, go to the **Keys** tab, click **Add Key** > **Create new key**.
4. Choose **JSON** and click **Create**. The key file downloads automatically.
5. Open the file in a text editor. Copy the value of the `client_email` field (it looks like `configview-sheets@your-project.iam.gserviceaccount.com`) — you'll need it in the next step. Keep the whole file contents handy for Step 5.

> Treat this JSON like a password — anyone with the contents can read every sheet shared with the service account.

***

### Step 4: Share each sheet with the service account

Service accounts are separate Google identities. They cannot read any sheet by default — you must share each spreadsheet with them explicitly.

1. Open the Google Sheet you want ConfigView to ingest.
2. Click **Share** in the top-right.
3. Paste the `client_email` from Step 3 into the **Add people and groups** field.
4. Set the role to **Viewer** and uncheck **Notify people** (the service account has no inbox).
5. Click **Share**.

Repeat for every sheet you plan to ingest.

***

### Step 5: Enable the Google Sheets app in ConfigView

1. Go to `https://{companyname}.configview.com/admin/cron/`.
2. Under **Applications**, check the box next to **Google Sheets**.
3. Click **Save**.

Checking the box creates an empty `GSHEETS_SERVICE_ACCOUNT_JSON` secret for you. Unchecking it later deletes the secret and disables any scheduled runs.

***

### Step 6: Paste the JSON key into the secret

1. Go to `https://{companyname}.configview.com/admin/secret/`.
2. Find the `GSHEETS_SERVICE_ACCOUNT_JSON` entry created by Step 5.
3. Paste the entire contents of the JSON key file (everything from the opening `{` to the closing `}`) as the value and **Save**.

To rotate the key later, generate a new one in Google Cloud and update the same secret value.

***

### Step 7: Import a sheet

1. On the same `/admin/external/` page, scroll to **Auto import sheets**.
2. Paste the full Google Sheet URL (or just the spreadsheet ID — the part between `/d/` and `/edit` in the URL).
3. Click **Insert**.

ConfigView will open the spreadsheet, iterate through every tab, and create or refresh one database table per tab. Table names are namespaced by a prefix derived from the spreadsheet title so different workbooks never collide — e.g. a workbook titled `2026 Finance` with a tab named `Q1 Headcount` becomes the table `gs_2026_finance_q1_headcount`.

***

### Step 8: Verify

1. Go to `https://{companyname}.configview.com/admin/status/` and run the **Google Sheets** health check. It verifies three things: the `GSHEETS_SERVICE_ACCOUNT_JSON` secret is populated and parseable, the service account can authenticate with Google, and every registered sheet is still reachable (a `warn` here means one of your shares was revoked).
2. Open the SQL editor or any existing dashboard that reads from the new tables and confirm each tab from your spreadsheet shows up as its own table with the expected columns and row count.

If the import fails:

* **403 / permission denied** — the service account isn't shared on the sheet. Repeat Step 4.
* **404 / not found** — the spreadsheet ID is wrong, or the sheet was deleted.
* **Secret `GSHEETS_SERVICE_ACCOUNT_JSON` not found** — the Google Sheets app isn't enabled at `/admin/cron/`, or the secret was deleted.
* **JSON decode error** — the secret value is empty or not a complete JSON object. Re-paste the full file contents at `/admin/secret/`.

***

### Step 9: Schedule automatic refreshes (optional)

Manual Inserts are one-shot. To keep tables fresh:

1. Go to `https://{companyname}.configview.com/admin/cron/`.
2. Find **Sheet Data** under the **Google Sheets** app.
3. Set a schedule (hourly, daily, or whatever cadence you want) and save.

On each run the poller iterates every registered sheet and refreshes its tables. Results are recorded in the Activity log at `/admin/activity/` with `folder="Google Sheets"` and `action="cron_run"`.

***

### Sheet structure requirements

For a tab to import cleanly:

* **Row 1 must be the header row.** Column names come from row 1.
* **Headers must be non-blank and unique within a tab.** Blank or duplicate headers are dropped silently by the importer.
* **No merged cells in the header row.**
* Data starts on row 2. Empty tabs are skipped.

All columns are stored as `TEXT`. Cast to the type you need in SQL, for example:

```sql theme={null}
SELECT CAST(amount AS DECIMAL(10,2)) AS amount FROM q1_headcount;
```

***

### Limitations (current release)

* **Full refresh per run.** Each import (manual or cron) truncates the target tables and re-inserts every row. History is not preserved.
* **Removed tabs aren't cleaned up.** Deleting a tab in Google Sheets leaves the corresponding table in the database untouched.
* **No schema evolution.** Renaming or removing a column in the sheet does not drop the column from the table; renamed columns appear as a new column alongside the old one.
* **Single service account.** All registered sheets use the same `GSHEETS_SERVICE_ACCOUNT_JSON` secret. Using different service accounts per sheet is not supported today.

***

### Data Tables

Each tab in the source spreadsheet becomes one table, named `gs_<workbook>_<tab>` (both parts sanitized: lowercased, non-alphanumeric characters replaced with underscores). Example for a workbook titled `2026 Finance`:

| Source                   | Table                          |
| ------------------------ | ------------------------------ |
| Tab named `Headcount Q1` | `gs_2026_finance_headcount_q1` |
| Tab named `Budget`       | `gs_2026_finance_budget`       |
| Tab named `notes`        | `gs_2026_finance_notes`        |

Column names follow the same sanitization. Every import is also recorded in the Activity log at `/admin/activity/` with the number of tabs and rows ingested, duration, and the user who triggered it.