Skip to main content

​
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.
  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: 
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:
SourceTable
Tab named Headcount Q1gs_2026_finance_headcount_q1
Tab named Budgetgs_2026_finance_budget
Tab named notesgs_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.