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": "Onboarding",
       "headerRow": 1,
       "parentMatchColumn": "Salesforce Account Id",
       "parentMatchField": "Id",
       "autoPopulateParent": true
     }
     

     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!

Provider configuration options

The three parent options are optional. Existing configurations containing only spreadsheetId and sheetName continue to display the complete sheet.

Scope sheet rows to a Salesforce record

Add a column to the sheet whose header exactly matches parentMatchColumn. For an Account-based grid using the example configuration above, populate that column with Salesforce Account IDs.

When AppColab Grid is placed on an Account record page, it passes the Account ID to the provider. The provider returns only rows whose Salesforce Account Id cell matches that ID. When the same Grid Definition is used on an app or home page without record context, it returns the complete sheet.

To use the Google Sheets grid beneath a Salesforce parent grid:

1. Give the Google Sheets Grid Definition a non-empty Parent Relationship Field, such as Salesforce Account Id, so it is available in the child-grid picker.
2. Open the Salesforce parent Grid Definition and add the Google Sheets Grid Definition on the Child Grids step.
3. Expand a parent row. Its Salesforce record ID is passed to the Google Sheets provider and used for the match.

If the sheet stores a business key instead of a Salesforce ID, set parentMatchField to the corresponding accessible field on the parent object. For example, use Customer_Code__c when the sheet’s match column contains customer codes.

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();

// The provider reads its JSON configuration from this Grid Definition.
ctx.gridDefinitionId = [
    SELECT Id
    FROM appcg__Grid_Definition__c
    WHERE appcg__Grid_Identifier__c = 'Google_Sheets_Grid'
    LIMIT 1
].Id;

// Optional: provide record context to test parent-aware filtering.
// ctx.parentId = '001000000000001AAA';

// 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);

ProviderContext contains gridDefinitionId and parentId. It does not contain provider configuration JSON; the provider loads appcg__Data_Provider_Config__c from the Grid Definition identified by gridDefinitionId.

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.