- 2 Minutes to read
Google Sheets as a Destination
- 2 Minutes to read
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.
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 account from the drop-down menu.
- Name your destination and click on Save to create your destination.
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 sources.
- 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.
Number of cells
When sending your data to Google Sheets, be aware that Google Sheets have a limit of approximately 10 million cells.
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.
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: This action would increase the number of cells in the workbook above the limit of 10000000 cells., badRequest
This issue may be caused by reaching Google Sheets limit for storing the data which is 10M cells. Please use one of the solutions below.
- 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 cloud datawarehouse as destination (e.g. BigQuery, Snowflake or Redshift) instead of Google Sheets.