Google Sheets (OAuth2)

This guide walks you through using a Google Sheets spreadsheet as a scheduled, refreshable data source in DashboardFox via the Google Sheets API and OAuth2.

Unlike the Google Sheets (Public API) integration, OAuth2 lets you connect to private spreadsheets — including those stored in Shared Drives — without making them publicly viewable. The trade-off is more setup: you'll register an OAuth client in Google Cloud Console, configure scopes, and complete a one-time authorization flow.

OAuth2 is the right path when your spreadsheet contains data you don't want exposed via a public link, or when your Google Workspace policy prevents external sharing. For free Gmail accounts, see the note immediately below — the Public API guide is generally a better fit.

Prerequisites

Before starting, confirm you have:

• A Google Workspace subscription (Business Starter, Business Standard, Business Plus, or Enterprise). Free personal Gmail accounts can technically complete the setup but with significant practical limitations — see the note below.

• The spreadsheet stored in either a Shared Drive (recommended) or a Workspace user's My Drive. For new integrations, we strongly recommend Shared Drives — see Part 2 for why.

• Google Cloud admin access to create a project and OAuth client. Typically this is your Workspace administrator.

• DashboardFox admin access for the integration setup.

Note for Personal Gmail Users

This guide is built around Google Workspace because the OAuth2 user-type setting called Internal is Workspace-only. Free personal Gmail accounts cannot use Internal — they must use External + Testing publishing status, which causes refresh tokens to expire every 7 days. That breaks scheduled fetches: the integration runs fine for about a week, then halts until an admin re-clicks Authenticate.

If you're on a personal Gmail account, you have two practical paths:

(a) Recommended — use the Google Sheets (Public API) guide instead. The API-key + public-link pattern works without OAuth2 and supports scheduled fetches without the 7-day expiry issue. The trade-off is that the spreadsheet must be set to "Anyone with the link can view."

(b) Manual OAuth2 workflow. Complete the steps in this guide but do not enable Schedule in Part 5. When you need fresh data:

1. Go to Settings → Integrations → Manage API.

2. Click Authenticate on the API to refresh tokens if the 7-day window has elapsed.

3. From the endpoint's Actions menu, click Connect to pull the latest values.

This makes the integration manual and on-demand — fine for occasional refreshes, not for hands-off automation. If you find yourself re-authenticating frequently, it's worth migrating to Google Workspace (Business Starter is around $7/user/month) to gain the Internal user type and stable refresh tokens.

Part 1. Set Up the Google Cloud Project and OAuth Client

Google Cloud is the identity service that issues OAuth2 access tokens for Sheets API access. You'll register an OAuth client identity for DashboardFox, then DashboardFox uses that identity to call the Sheets API.

1. Open Google Cloud Console

• Navigate to https://console.cloud.google.com.

• Sign in with your Google Workspace administrator account (not a personal Gmail). The User Type = Internal option only appears when signed in with a Workspace account whose org will own the project.

2. Create a New Project

• Click the project picker dropdown in the top bar.

• Click New Project.

• Fill in:

  Field	Value
  Project name	DashboardFox Integration (or similar)
  Organization	Select your Workspace organization. It should display your domain (e.g., yourcompany.com). If it shows "No organization," you're signed in with a personal account — sign out and back in with your Workspace admin.
  Location	Same as Organization, or your org's preferred folder

• Click Create and wait for the project to be created. Select it from the project picker.

3. Enable the Google Sheets API

• In the left navigation, go to APIs & Services → Library.

• Search for Google Sheets API.

• Click the result, then click Enable.

4. Configure the OAuth Consent Screen

• In the left nav, go to APIs & Services → OAuth consent screen (in newer Google Cloud UI, this may appear under a Branding or Audience subsection — the link from the APIs & Services dashboard works either way).

• For User Type, select Internal, then click Create.

Important: Internal restricts the app to users in your Workspace domain only. This is what you want for a single-tenant BI integration: no Google verification process, no 7-day refresh-token expiry, no public exposure. If you don't see Internal as an option, you're signed in with a non-Workspace account — back out and sign in correctly.

• Fill in the App information:

  Field	Value
  App name	DashboardFox Sheets Connector (appears on the consent screen during Authenticate)
  User support email	Your admin email
  App logo	Optional
  App domain — Application home page	Your DashboardFox URL (optional but recommended)
  Developer contact information	Your admin email

• Click Save and Continue.

5. Add the Scope

• On the Scopes step, click Add or Remove Scopes.

• In the filter, type spreadsheets.

• Check the box next to https://www.googleapis.com/auth/spreadsheets.readonly (See and download all your Google Sheets spreadsheets).

• Click Update, then Save and Continue.

Why only one scope? This integration needs to read spreadsheet contents, and your admins will paste the spreadsheet ID directly into DashboardFox endpoints — there's no file-picker UI that needs to browse Drive. Adding drive.metadata.readonly would be useful only if you wanted to enumerate files for troubleshooting in Google's APIs Explorer. For Internal apps the extra scope is harmless (no verification process applies to Internal); for External apps it would just be extra scrutiny. We recommend keeping it minimal.

• If a Test users step appears, leave it empty and continue.

• Review the summary and click Back to Dashboard.

6. Create the OAuth Client ID

• In the left nav, go to APIs & Services → Credentials.

• Click + Create Credentials → OAuth client ID.

• Fill in:

  Field	Value
  Application type	Web application
  Name	DashboardFox OAuth Client (for your reference; not user-visible)
  Authorized JavaScript origins	(leave empty)
  Authorized redirect URIs	Click + Add URI and paste your DashboardFox OAuth callback URL, in the form https://yourcompany.dashboardfox.app/apiconnection.html

• Click Create.

A modal appears with your Client ID and Client Secret. Copy both immediately and store them securely. (Unlike Microsoft Entra, Google does let you re-view the secret later from the Credentials page — but treat it as a password regardless.)

You now have three values to save: Client ID, Client Secret, and Redirect URI.

Part 2. Prepare Your Spreadsheet

1. Choose Where to Store the Spreadsheet

You have two options:

Recommendation: Create a dedicated Shared Drive (e.g., "BI Data" or "Reporting") and store your spreadsheet there.

To create a Shared Drive: Open Google Drive → Shared drives in the left sidebar → + New → name the drive → Create. Add as members the user account(s) you'll use to authorize DashboardFox. Members need at minimum Content Manager role for read access to work reliably through the API.

2. Prepare the Spreadsheet for API Consumption

The Google Sheets API, when called with default value rendering (FORMATTED_VALUE), returns cell contents as their formatted display strings. To produce clean, easy-to-cast data downstream, follow these formatting rules:

• Dates: Format date columns via Format → Number → Custom date and time and use the pattern yyyy-mm-dd. The API will return "2024-01-01" — the cell exactly as displayed — which casts cleanly downstream.

• Numbers / Currency / Revenue: Format as plain Number (Format → Number → Number) with no thousands separator and no currency symbol. If you want a $ symbol in your reports, add it via formatting in DashboardFox, not in Sheets.

• Percentages: Format as plain Number. Store the underlying decimal (e.g., 0.05) rather than 5%.

• Negatives: Use plain minus-sign format, not accounting parentheses.

• Worksheet (tab) names: Use friendly names like Sales_Orders instead of Sheet1. Avoid spaces; use underscores. The API is case-sensitive on worksheet names.

• Column headers: Row 1 should contain headers; row 2+ should contain data. No title rows, summary rows, or merged cells.

• No empty columns or rows within your data block.

These rules match the prep philosophy of our Excel/CSV Import lesson — set types correctly at the source, save downstream pain.

3. Get the Spreadsheet ID

Unlike Microsoft Graph (which requires a site ID + driveItem ID lookup), Google Sheets identifies workbooks by a single ID that's visible in the URL.

• Open your spreadsheet in a browser.

• Look at the URL bar:

https://docs.google.com/spreadsheets/d/1IhI1C2SKHkH-dNVglzA_pTto2ItMA0R3iS7l-id5IAc/edit#gid=798997327

• Copy the portion between /d/ and /edit — that's your Spreadsheet ID.

4. Confirm the Worksheet (Tab) Name

• Look at the tab name at the bottom of the spreadsheet (e.g., Sales_Orders).

• Note the exact spelling and casing. The API is case-sensitive.

You now have two values: Spreadsheet ID and the Worksheet name(s) of the tabs you want to import.

Part 3. Configure the OAuth2 API in DashboardFox

1. Create the API Container

• Log in to DashboardFox as an Admin.

• Navigate to Settings → Integrations → Create App → APIs.

• Fill in the form:

  Field	Value
  API Name	Google Sheets (or a name identifying this connection, e.g., Google Sheets — Sales Data)
  API Description	Optional. Useful for documenting which spreadsheet this connects to.
  API Connection	Select OAuth2 from the dropdown.

• Click Continue.

You'll land on the Register New API screen with sections for API Connection List, API Authorization List, Api Refresh Token, and Api Endpoints.

2. Configure the API Connection (Form 1 — Authorization Request)

This is the URL where Google prompts the user to sign in and consent.

• Under API Authorization List, click Add Details.

• At the top of the form, set:

  Field	Value
  API Request URL — name	request_url
  API Request URL — value	https://accounts.google.com/o/oauth2/v2/auth

• Click Add New Parameter for each of the following rows:

  Name	Value
  redirect_uri	Your DashboardFox callback URL — exactly matching what's registered in Google Cloud (e.g., https://yourcompany.dashboardfox.app/apiconnection.html)
  client_id	The Client ID from Google Cloud
  response_type	code
  scope	https://www.googleapis.com/auth/spreadsheets.readonly
  access_type	offline
  prompt	consent

Critical: access_type=offline is what tells Google to issue a refresh token. Without it, you'll get an access token that expires in ~1 hour and you'll have no way to renew it — every scheduled fetch will fail until you re-Authenticate manually. prompt=consent forces Google to show the consent screen and re-issue a refresh token if you ever re-authenticate; without it, a second Authenticate may skip refresh token re-issuance entirely.

• Click Next Step.

3. Configure the API Authorization (Form 2 — Token Exchange)

This is where DashboardFox exchanges the auth code Google returns for an access token and refresh token.

• Set the top of the form:

  Field	Value
  Method	POST
  API Authorization Request URL — name	request_url
  API Authorization Request URL — value	https://oauth2.googleapis.com/token

• Click Add New Parameter for each row:

  Name	Value
  client_id	Your Client ID
  client_secret	Your Client Secret
  grant_type	authorization_code
  code	(leave blank — DashboardFox fills this at runtime)
  redirect_uri	Same URL as Form 1

• Click Save.

Important: The redirect_uri parameter in Form 2 must be named exactly redirect_uri (not request_uri). Google rejects the token exchange if it sees an unexpected parameter name.

4. Configure the Refresh Token (Form 3)

This is the URL DashboardFox calls to get a fresh access token when the current one expires.

• Under Api Refresh Token, click Add Details.

• Set:

  Field	Value
  Method	POST
  Request URL — name	request_url
  Request URL — value	https://oauth2.googleapis.com/token (same as Form 2)

• Click Add New Parameter for each:

  Name	Value	Is Saved
  grant_type	refresh_token	unchecked
  client_id	Your Client ID	unchecked
  client_secret	Your Client Secret	unchecked
  refresh_token	(leave value blank — DashboardFox populates after first auth)	checked ✓

Difference from Microsoft: Google's refresh-token request does not require a scope parameter. The new access token inherits scopes from the original consent automatically.

• Click Save.

5. Authenticate

• Back on the Register New API screen, click the Authenticate button (next to Add Details under the Authorization List section).

• A popup opens to Google sign-in.

• Sign in with the Workspace admin or service account you want this integration to act as. This account must have access to the spreadsheet — see Part 2 Step 1.

• Review the consent screen — Google will list the permission requested (read-only Sheets access). Click Continue or Allow.

• The popup redirects back to your DashboardFox callback page. A green success message confirms the access token and refresh token were stored.

• Close the popup.

If something goes wrong, see the Troubleshooting section below.

Part 4. Add Endpoints (One per Worksheet)

For each worksheet you want to import, you'll create a separate endpoint. Each endpoint becomes its own table in DashboardFox.

1. Add the First Endpoint

• Under Api Endpoints, click Add Endpoint.

• Fill in the form:

  Field	Value
  Endpoint Name	The worksheet name, e.g., Sales_Orders
  Response By Name	values (Google Sheets API returns the spreadsheet data under a values array)
  Method	GET
  Request URL — name	request_url
  Request URL — value	The full Sheets API URL — see template below

The endpoint URL template:

https://sheets.googleapis.com/v4/spreadsheets/{SPREADSHEET_ID}/values/{WORKSHEET_NAME}

Substitute:

• {SPREADSHEET_ID} — the ID copied from the spreadsheet URL (between /d/ and /edit)

• {WORKSHEET_NAME} — the exact (case-sensitive) tab name; URL-encode spaces as %20 if present (better practice: rename the tab to use underscores)

If you want to pull only a specific range rather than the entire used area, use the format {WORKSHEET_NAME}!A1:Z style. For typical use, the bare worksheet name returns all populated cells in that tab, which is what you want.

Why no valueRenderOption query parameter? The Sheets API defaults to FORMATTED_VALUE, which returns cell contents as their formatted display strings — "2024-01-01" for dates, "1234.56" for plain numbers, etc. This is exactly what you want for clean casting in App Builder, provided you've followed the Sheets prep rules in Part 2. The alternative, UNFORMATTED_VALUE, returns raw values where dates appear as serial numbers (e.g., 45292), which requires extra casting work. Stick with the default.

2. Add the Authorization Header

This is what tells DashboardFox to attach the OAuth Bearer token to each request.

• In the endpoint form, scroll to the parameter section.

• Click Add New Parameter with these settings:

  Field	Value
  Name	Authorization
  Param Type (dropdown)	Is Auth Header
  Value	access_token
  Authentication (dropdown)	Authentication
  Is Query Param	unchecked

• Click Save.

3. Fetch the Data

• On the endpoint row, open the Actions menu (the three-dot menu on the right) and click Connect.

• A status indicator shows the fetch in progress.

• The Sheets API is generally fast — most spreadsheets under 10,000 rows finish in seconds. Very large sheets (50,000+ rows) may take a minute or two.

• A green success bar confirms completion.

4. Preview the Data

• From the Actions menu, click Preview.

• A dropdown appears with the table name DashboardFox created from the API response:

  api_{apiId}_{endpointId}_values_listdatatable — the data rows (the table you'll use in App Builder)

• Select the table and click Load.

• Verify the data looks correct: row count, column count, header row, data types.

• If something is wrong (wrong worksheet name, missing data, etc.), edit the endpoint, then use Purge & Connect from the Actions menu to rebuild the table from scratch.

5. Note the Table Name for App Builder

The table name pattern is:

api_{apiId}_{endpointId}_values_listdatatable

You'll reference this name in App Builder when you build your app's semantic layer.

6. Repeat for Each Worksheet

For every worksheet in your spreadsheet, repeat steps 1–5 with a new endpoint, changing only the Endpoint Name and the {WORKSHEET_NAME} segment of the URL.

Part 5. Schedule Automated Refreshes (Optional)

Skip this section if you're on a personal Gmail account — see Note for Personal Gmail Users at the top. Personal accounts in Testing mode have refresh tokens that expire every 7 days, which will break a schedule.

For ongoing automatic updates from the spreadsheet on Workspace accounts:

• From the endpoint's Actions menu, click Schedule.

• Configure the cadence — hourly, daily, weekly, etc. We recommend a minimum of 15 minutes between fetches to stay well clear of Google's API quotas.

• Save.

DashboardFox will use the stored refresh token to obtain a new access token automatically when needed, then re-fetch the spreadsheet data on schedule. No manual re-authentication required as long as the refresh token remains valid — refresh tokens for Workspace + Internal apps don't time-expire under normal active use.

Each fetch replaces the previous data — the table is purged and repopulated, so no stale rows accumulate.

Building Reports on the Imported Data

Once your endpoints have loaded successfully:

1. Register the app for users in Settings → Security → Apps — find the auto-created app, click Edit, and assign yourself the App Builder role.

2. Go to App Builder and find your new app.

3. Drag the _listdatatable tables from your endpoints into the semantic layer.

4. Apply data type casts on date/numeric columns (see Sheets Prep Best Practices below).

5. Build reports following the lessons in our Academy.

Sheets Prep Best Practices — App Builder Cast Formulas

When your Google Sheets cells are formatted per the guidance in Part 2 Step 2, columns import as text strings that cast cleanly. Use these formula patterns in App Builder:

If you chose to keep Sheets' currency or percentage formatting (with $, ,, or % symbols in the data — recall the API returns these formatted as-displayed when using default rendering), use these instead:

Limitations

• Workspace required for production-stable scheduling. Personal Gmail accounts cannot use the Internal user type, which is what avoids the 7-day Testing-mode refresh token expiry. See the personal Gmail note at the top of this article.

• Shared Drives on Business Starter have caveats. All paid Workspace tiers including Business Starter support Shared Drives, but Business Starter has limited admin controls (e.g., no per-item external-sharing restrictions) and only 30 GB pooled storage per user. Business Standard (2 TB) is more comfortable for production-grade use.

• Worksheet (tab) name changes break the endpoint. If you rename a tab in Sheets, you must update the endpoint URL to match.

• Spreadsheet deletion or trashing breaks the integration. Moving the spreadsheet within Drive (or between Drives) is generally safe — the spreadsheet ID is stable. But if the file is deleted or moved to Trash, fetches will return errors.

• Sheets API quotas. Google Sheets API has per-user and per-project read quotas (around 60 read requests per minute per user, and 300 per project per minute, as of writing). For a single scheduled integration, even a 15-minute cadence stays well under these limits.

• Spreadsheet size limits. Google Sheets allows up to 10 million cells per spreadsheet, but practical performance degrades well before that ceiling. For very large datasets, consider splitting across multiple spreadsheets or moving to a proper database.

• Refresh token revocation. Refresh tokens can become invalid if: an admin revokes app access in the Workspace admin console, the authorizing user's account is suspended or deleted, or 6 months of total inactivity elapses (not a concern for active scheduled integrations).

• No service-account flow in this UI. DashboardFox's OAuth2 API integration uses the delegated-user flow. If you require a non-user-bound service account flow (Google JWT auth), this UI doesn't support it — contact support to discuss options.

Troubleshooting

For unresolved issues, contact team@dashboardfox.com.

Related Articles

• Google Sheets (Public API) — the API-key + public-link pattern for spreadsheets that can be shared "Anyone with the link"

• Microsoft Excel (OneDrive for Business / SharePoint via OAuth2) — the equivalent OAuth2 flow for Microsoft

• Microsoft Excel (Personal OneDrive) — file-import workflow for personal Microsoft accounts

• Excel/CSV Import Lesson — data preparation best practices (same principles apply to Sheets)

• App Builder Lessons — building reports on imported data