Configure a Google Sheets data source

Last updated 10/05/2026

The Google Sheets data source reads data from Google Sheets through Offline Sync and writes it to the platform's built-in data warehouse. This article covers authorization on the Google side, data source configuration, sync plan creation, and mounting on a Flow.

Limitations and prerequisites

Before you start, confirm the following limitations and prerequisites:

• Google Sheets is supported only as a data source, not as a Data Target, and nothing is written back to Google Sheets.
• The system generates source field names from the column letters shown at the top of Google Sheets (A, B, C, and so on) by adding the col_ prefix. For example, column A becomes col_A and column B becomes col_B. All source fields are of the string type, so convert or adjust fields as the target table requires during field mapping.
• You need a Google account that can create a Google Cloud project, a service account, and a JSON key, and that has permission to share the target Google spreadsheet with the service account.
• The platform's runtime environment must be able to access the Google Sheets API and the Google Drive API.

Environment configuration	Use cases	Requirements
not distinguish environment	The development and production environments share one service account	Upload one JSON key
Independent setting	The development and production environments use different service accounts	Upload a key for each; both accounts must be able to access a Google spreadsheet and sheet with the same names

tip

With Independent setting, configuration and preview use the development environment credentials, and production tasks use the production environment credentials. Check the spreadsheet names, sheet names, and sharing permissions in both environments in advance.

Complete authorization in Google Cloud and Google Sheets

The Google Sheets data source uses a service account for server-side authentication, so you don't need to configure an OAuth consent screen, client ID, redirect URI, or domain-wide delegation.

1. Create or select a Google Cloud project. Go to the Google Cloud Console and select an existing project or create a new one.
2. Enable the required APIs. In APIs & Services > Library, search for and enable Google Sheets API and Google Drive API. The former reads cell data; the latter discovers spreadsheets and reads file metadata.
3. Create a service account. Go to IAM & Admin > Service Accounts and create a service account dedicated to reading Google Sheets. For this integration, you don't need to grant the service account broad roles such as project Owner, Editor, or Drive Admin.
4. Create a JSON key. Open the service account's Keys tab, select Add key > Create new key > JSON, then download the key file and keep it safe.
5. Share the target spreadsheet. Open the Google spreadsheet you want to read, click Share in the upper-right corner, enter the client_email from the JSON file, select Viewer as the permission, and complete the sharing.

Grant the service account view access to the target Google spreadsheet.

Add the service account as a Viewer in Google Sheets

Every spreadsheet you want to read must be shared directly with the service account or inherit access from a parent folder. Enabling the APIs alone doesn't grant access to spreadsheets.

warning

The Service Account JSON contains sensitive credentials. Don't commit the key to code repositories or put it in logs, group chats, or tickets. If the key is leaked, disable or delete it in Google Cloud immediately and generate a new one.

If you can't create a key, ask your Google Cloud administrator to check the organization policy iam.disableServiceAccountKeyCreation. If you can't share spreadsheets with iam.gserviceaccount.com addresses, ask your Google Workspace administrator to check the Drive external sharing policy.

Create a Google Sheets data source

1. Go to DataOps Platform > Integration > Data Sources and click Add Data source.
2. Select GoogleSheets and fill in the data source name and remarks.
3. Select not distinguish environment or Independent setting. With independent settings, you need to configure the development and production environments separately.
4. In Service Account JSON, upload the original .json file downloaded from Google Cloud.
5. Click Connectivity Test. When Connectable is shown, click Complete.

Upload the Service Account JSON and confirm that the connectivity test result is Connectable.

Upload the service account JSON and complete the connectivity test

Config items	Description
Service Account JSON	Upload the original JSON key file downloaded from Google Cloud; the platform validates it automatically.
not distinguish environment	The development and production environments share the current key
Independent setting	Maintain separate keys for the development and production environments; each environment must pass the connectivity test separately

When you edit a saved data source, the page shows only the masked service account email and doesn't display the private key. To replace the key, upload a new JSON file and test the connection again.

Create an offline sync plan

1. Go to DataOps Platform > Integration > Offline Sync and click Create an integration plan.
2. In Data Source, select GoogleSheets and the data source you created.
3. Select the Spreadsheet to read, and then the Sheet in it. The lists show only content that the current service account can access.
4. Enter the Column range. The range contains only column letters, such as A:F, F:F, or AA:ZZZ; the system converts them to uppercase automatically.
5. Expand File Reading Rules and set Skip header based on the spreadsheet content. When it's on, reading starts from row 2; when it's off, reading starts from row 1.
6. Click Source Data Preview and confirm that the selected range, row count, and data content are as expected.

After selecting the spreadsheet, sheet, and column range, check the read results in the source data preview.

Configure the read range and preview source data

tip

The column range doesn't include row numbers or sheet names. A1:F, A:F50000, 工作表1!A:F, and F:A (whose start column is after its end column) are all invalid formats.

With Independent setting, production runs use the production environment service account to find the Google spreadsheet and sheet with the same names. If the names are duplicated, missing, or unauthorized, production tasks may fail.

Field mapping and sync verification

After configuring the data source, select the target table in the platform's built-in data warehouse and create field mappings. Google Sheets fields are generated from column letters; for example, column A corresponds to col_A and column B to col_B, all of the string type.

1. In Data Target, select or create the target table and set the write mode as your business requires.
2. In Field Mapping, map source fields to target fields. If the target field types differ, complete the necessary type conversion first.
3. Fill in the plan name, owner, and remarks, then save the plan.
4. Click Manual Execution, confirm in the execution records that the task succeeded, then check the row count, field order, and data content in the target table.

Symptom	What to check
Connectivity test fails	Make sure you uploaded the complete service account JSON, the key hasn't been disabled or deleted, and Google Sheets API and Google Drive API are enabled
Target Google spreadsheet not found	Make sure the spreadsheet is shared with the client_email in the JSON, with at least Viewer permission
Sheet not found	Make sure the sheet hasn't been deleted. With independent settings, check whether the production account can access the spreadsheet and sheet with the same names
Column range error	Enter only column letters and make sure the start column isn't after the end column; make sure the range doesn't exceed the readable range of the sheet
429 error or request throttling	Retry later, and check the Google API quota and task concurrency

Mount on a Flow

After manual verification succeeds, you can mount the offline sync plan on a Flow so that it runs automatically on a schedule.

1. Open the saved offline sync plan and click Mount on task flow.
2. Select the target Flow.
3. Enter the node name, select the execution mode, and set the owner and remarks as needed. The node type and integration plan are filled in automatically.
4. Click Create node and mount on it. After mounting succeeds, click Go to flow page to check the node's position and its upstream and downstream dependencies.
5. Save and release the Flow. Production scheduling uses the data source configuration of the production environment.

To change the sync plan or data source, first assess the dependencies of existing Flows, and verify again after the change.