Google Sheets as a Destination
- 3 Minutes to read
- DarkLight
Google Sheets as a Destination
- 3 Minutes to read
- DarkLight
Article summary
Did you find this summary helpful?
Thank you for your feedback!
Google Sheets is a cloud-based spreadsheet software offered as part of Google Workspace (formerly G Suite). It allows users to create, edit, and collaborate on spreadsheets in real-time, offering a wide range of features for data organization, analysis, and visualization.
| Use Case | Solution |
|---|---|
| More than 10M cells (384,615 rows with 26 columns labeled A-Z) | 10M cells is Google Sheets' cell limit. If your data volume reaches 10M cells or more, consider using a data warehouse like Google BigQuery. For more information, see Troubleshooting. |
Create a New Google Sheets Data Destination
- On the Destinations page, click on the Create Destination button and select the destination from the list.
- Select your authorizer from the drop-down menu.
- Name your destination and click on Save.
Need to authorize another connection?
Click on Add new Account in drop-down menu during authorizer selection and follow the on-screen prompts. You can also go to the Authorizers tab and click on Add New Service.
Create a Flow to Google Sheets
- Navigate to Flows and click on Create Flow.
- Click on Connect Your Data to add your source(s).
- Click on Connect Your Data Destination to add the destination.
- Choose the write mode and fill in the other required information.
- Check the Data Preview to see if your configuration is correct.
- Name your flow and click on Create Flow to finish the setup.
Limitations
Number of cells
When sending your data to Google Sheets, be aware that Google Sheets have a limit of approximately 10 million cells.
Columns Changes
If you do one of the following actions after you already saved your flow, it can break your flow:
- Change the columns order
- Change the column names
- Add columns
- Delete columns
All actions can be executed by copying the source with JSON connector, and then editting the labels using advanced settings of the source.
Troubleshooting
Can’t Find the Location of Specific Google Sheet
To navigate to your newly created Google Sheets, go to the Flows tab and click on the electric plug icon next to your flow for configuration details. Click on the blue button on the pop-up to get redirected to your Google Sheets.
Large Numbers Are Cut or Rounded
This issue arises because Google Sheets has a limitation on storing extremely large numbers. In most cases, it's improbable that you genuinely require the value to be stored as a numerical entity (unless you're calculating, for example, the number of atoms in the solar system). More often than not, the number you are trying to store serves as an identifier (e.g., a Facebook Post ID). Therefore, you can safely convert it to a string to prevent rounding.
To address this, you can adjust the schema of the connected source, changing the data type of the specific column from integer to string.
Failed to Write USER_ENTERED Data Error
Stream transfer: write data from stream: writing to sheet: Failed to write data to sheet by columns: Failed to write "USER_ENTERED" data batch to sheet: googleapi: Error 400: Invalid data[0]: This action would increase the number of cells in the workbook above the limit of 10000000 cells., badRequest
This issue is caused by reaching Google Sheets limit, which is 10M cells. Please use one of the following solutions:
- Reduce the amount of extracted data. You can do this by adjusting the shortening the timeframe of the extraction.
- If you are using SmartCache with append configuration, consider using replace configuration instead. For more information, see article on snapshot keeping policy.
- Consider using a cloud data warehouse as destination (e.g. BigQuery, Snowflake or Redshift) instead of Google Sheets.
Was this article helpful?