Google Sheets - Service Account

Google Sheets serves as an effective reverse ETL destination, enabling real-time data synchronization from data warehouses to a collaborative, user-friendly spreadsheet environment. It democratizes data access, allowing stakeholders to analyze, share, and act on insights without specialized skills. The platform supports automation and customization, enhancing decision-making and operational efficiency. Google Sheets transforms complex data into actionable intelligence, fostering a data-driven culture across organizations.

Google Sheets is equipped with specific data capacity constraints, which, when exceeded, can lead to synchronization issues. Here’s a concise overview of these limitations:

• Cell Limit: A Google Sheets document is capped at 10 million cells, which can be spread across one or multiple sheets. Once this limit is reached, no additional data can be added, either in the form of new rows or columns.
• Character Limit per Cell: Each cell in Google Sheets can contain up to 50,000 characters. It’s crucial to consider this when syncing data that includes fields with lengthy text.
• Column Limit: A single worksheet within Google Sheets is limited to 18,278 columns.
• Worksheet Limit: There is a cap of 200 worksheets within a single Google Sheets spreadsheet.

Given these restrictions, Google Sheets is recommended primarily for smaller, non-critical data engagements. It may not be the optimal choice for handling expansive data operations due to its potential for sync failures upon reaching these imposed limits.

Connector Configuration and Credential Retrieval Guide

Prerequisite Requirements

Before initiating the Google Sheet connector setup, ensure you have an created or access an Google cloud account.

Destination Setup

Set up the Service Account Key

1. Create a Service Account

   • Navigate to the Service Accounts page in your Google Cloud console.
     
   • Choose an existing project or create a new one.
     
   • Click + Create service account, enter its name and description, then click Create and Continue.
   • Assign appropriate permissions, recommending the Editor role, then click Continue.
2. Generate a Key

   • Access the API Console > Credentials page, select your service account’s email.
     
   • In the Keys tab, click + Add key and select Create new key.
   • Choose JSON as the Key type to download your authentication JSON key file. Click Continue.
     
3. Enable the Google Sheets API

   • Navigate to the API Console > Library page.
     
   • Verify that the correct project is selected at the top.
   • Find and select the Google Sheets API.
   • Click ENABLE.
     
4. Spreadsheet Access

   • If your spreadsheet is link-accessible, no extra steps are needed.
   • If not, grant your service account access to your spreadsheet.
5. Output Schema

   • Each worksheet becomes a separate source-connector stream in AI Squared.
   • Data is coerced to string format; nested structures need further processing for analysis.
   • AI Squared replicates text via Grid Sheets only; charts and images aren’t supported.