Google Sheets Grid Integration

The Google Sheets Grid integration allows users to view, search, add, inline-edit, delete, and download rows from any Google Spreadsheet directly inside the AppColab Grid UI—in real time, without copying or duplicating rows in Salesforce database storage.

What’s Deployed

One-Time Setup Guide

Connecting Google Sheets requires authorizing Salesforce to communicate with the Google Sheets REST API.

Step 1: Enable Google Sheets API & Scopes in Google Cloud

1. Go to the Google Cloud Console.
2. Select your project (or click New Project to create one, e.g. AppColab Salesforce Integration).
3. Navigate to APIs & Services → Library:
   • Search for Google Sheets API → click Enable.
4. Navigate to APIs & Services → OAuth consent screen:
   • Create an app
   • App name: AppColab Grid Integration
   • User Type: External (or Internal for Google Workspace orgs).
5. Click “Create OAuth Client” or go to “Clients” -> create
   • Application type: Web application
   • Name: AppColab Grid Integration
   • Click “Create”
   • Note down “Client ID” and “Client Secret”
6. Add Scope and Test Users
   • Go to “Data Access” tab
   • Click Add or Remove Scopes and add:
     • https://www.googleapis.com/auth/spreadsheets
   • Under Audience -> Test Users, add your Google account email.
   • Click Save and Continue.
7. Compelete OAuth Client Setup:
   • Go to Clients section again.
   • Authorized redirect URIs: Paste your Salesforce Callback URL (found in Salesforce Setup → Auth. Providers → Google_OAuth → Callback URL).

Step 2: Configure Salesforce Auth Provider & Credentials

2.1 Create the Auth. Provider

1. In Salesforce Setup, enter Auth. Providers in the Quick Find box and click New:
   • Provider Type: Google
   • Name: Google_OAuth
   • URL Suffix: Google_OAuth
   • Consumer Key: Paste your Google Cloud Client ID from Step 1.
   • Consumer Secret: Paste your Google Cloud Client Secret from Step 1.
   • Default Scopes: openid email https://www.googleapis.com/auth/spreadsheets
   • Click Save.
2. Copy the Callback URL displayed at the bottom of the page and ensure it matches the authorized redirect URI in your Google Cloud Console.

2.2 Create the External Credential

1. In Salesforce Setup, enter Named Credentials in Quick Find.
2. Click the External Credentials tab → click New:
   • Label: Google APIs
   • Name: Google_APIs
   • Authentication Protocol: OAuth 2.0
   • Authentication Provider: Select Google_OAuth
   • Click Save.
3. Under the Principals section, click New:
   • Parameter Name: Google_User
   • Sequence: 1
   • Click Save.
4. Click the dropdown arrow ▼ next to Google_User → click Authenticate.
5. Log into your Google account and grant spreadsheet permissions. (Status will change to Configured).

2.3 Create the Named Credential

1. Click the Named Credentials tab → click New:
   • Label: Google Sheets
   • Name: Google_Sheets
   • URL: https://sheets.googleapis.com
   • External Credential: Select Google APIs
   • Generate Authorization Header: Checked
   • Allow Formulas in HTTP Header: Checked
2. Click Save.

Step 3: Assign Permission Set Access

1. Setup → Permission Sets → click Integration Admin.
2. Click External Credential Principal Access and ensure Google_APIs - Google_User is enabled.
3. Click Manage Assignments and assign the permission set to your user.

Creating a Google Sheets Grid Definition

1. In Salesforce, open the AppColab Grid app → go to the Grid Definitions tab → click New Grid Definition.
2. Fill in:
   • Grid Settings Name: Google_Sheets_Grid
   • Grid Label: Google Sheets Live Grid
   • Is Active?: Checked
   • Data Provider Class: GoogleSheetsGridDataProvider
   • Data Provider Configuration:

     {
       "spreadsheetId": "YOUR_SPREADSHEET_ID",
       "sheetName": "Sheet1"
     }
     

     How to find your spreadsheetId:
     Open your spreadsheet in Google Sheets and look at the browser address bar:
     https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
     The long string between /d/ and /edit is your spreadsheetId.

3. Click Save & Next → proceed to Preview.
4. The Grid automatically discovers all column names from Row 1 of your sheet and renders all live data rows!

Supported Grid Features

Testing via Anonymous Apex

You can verify the connection without opening the UI by running this script in Developer Console (Debug → Open Execute Anonymous Window):

GoogleSheetsGridDataProvider provider = new GoogleSheetsGridDataProvider();
appcg.GridDataProviderTypesV1.ProviderContext ctx = new appcg.GridDataProviderTypesV1.ProviderContext();

// Replace with your spreadsheet ID
ctx.providerConfigJson = '{"spreadsheetId":"YOUR_SPREADSHEET_ID","sheetName":"Sheet1"}';

// 1. Test capabilities and dynamic columns
appcg.GridDataProviderTypesV1.Capabilities caps = provider.getCapabilities(ctx);
System.debug('Discovered Columns: ' + caps.columns.size());

// 2. Test live data fetch
appcg.GridDataProviderTypesV1.QueryRequest req = new appcg.GridDataProviderTypesV1.QueryRequest();
req.context = ctx;
appcg.GridDataProviderTypesV1.PageResult page = provider.getData(req);
System.debug('Total Rows Returned: ' + page.totalCount);

Troubleshooting

• 401 / 403 AuthenticationException: The Google OAuth token has expired or is missing the spreadsheets scope. Go to Setup → Named Credentials → External Credentials → Google APIs → click Authenticate on Google_User.
• GridUiException: spreadsheetId is required: Verify that your Data Provider Configuration JSON contains valid "spreadsheetId" and "sheetName" properties.
• HTTP 404: Requested entity was not found: Ensure that the spreadsheet is shared with or accessible by the Google account authenticated in Salesforce Setup, and that the spreadsheetId was copied accurately.