Skip to main content

​
Overview

This tutorial guides you through a Google Sheets script that integrates with Contextual AI. The script automates form filling by connecting to Contextual AI’s API, enabling dynamic, real-time population of form fields powered by a Contextual AI RAG agent. Alt text

​
Features

  • Use Contextual AI RAG Agents directly from within Google Sheets
  • Real-time API integration

​
Prerequisites

  • Access to Google Sheets
  • A Contextual AI API key

​
Tutorial Steps

To get started, make sure you have already set up your Agent. If note, you can follow these directions.

​
1. Configuring the Sheets Script

Ensure that the documents Vanguard BND Prospectus and iShares Government Bond Prospectus are uploaded to your Agent’s datastore.
  1. Open the example Google Sheet with the script installed.
  2. Go to File → Make a copy (so you have your own version).
  3. Go to Extensions → Apps Script.
  4. Modify the agent URL and API key with your Agent ID and API key.
  5. Add your form fields in Column A.
  6. Add your instructions in Column B (hidden by default).

​
2. Running the Script

  • Custom menu buttons are already included to run the scripts.
  • Select the three vertical dots on the buttons to modify executed scripts.
  • Watch Column C for processing status updates.
  • Results appear in real time as they’re fetched.

​
Code Structure

The script consists of two main functions:

​
1. fetchDataAndPopulateSheet()

Coordinates the data fetching process, manages sheet range selection, and schedules API calls. 
function fetchDataAndPopulateSheet() {
 const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 const loadingCell = sheet.getRange("D1");
 loadingCell.setValue("Bear with us... asking Contextual AI...");


//  update with your agent ID and API key
 const apiUrl = 'https://api.contextual.ai/v1/agents/99bd0fc6-75ca-4057-91b1-28dc1c261221/query?retrievals_only=false'
 const apiKey = 'key-ADD_YOURS';


 try {
   const scopeMetadata = sheet.getRange(1, 3).getValue(); // Column B, Row 1
   const formFieldsRange = sheet.getRange(5, 1, sheet.getLastRow() - 4, 2); // Columns A and B starting from Row 5
   const formFieldsValues = formFieldsRange.getValues();


   const rowsWithData = formFieldsValues
     .map(([field, instructions], idx) => ({ field, instructions, row: idx + 5 }))
     .filter(({ field, instructions }) => field && instructions);


   rowsWithData.forEach(({ row }) => {
     sheet.getRange(row, 3).setValue("Bear with us... asking Contextual AI...");
   });


   // Staggered execution of API calls
   rowsWithData.forEach((data, index) => {
     makeApiCall(data, scopeMetadata, apiUrl, apiKey, sheet);
   });
 } catch (error) {
   console.error(`Error in fetchDataAndPopulateSheet: ${error.message}`);
 } finally {
   loadingCell.clearContent();
 }
}

​
2. makeApiCall()

Handles individual API calls to Contextual AI Formats request payload Processes API responses 
function makeApiCall(data, scopeMetadata, apiUrl, apiKey, sheet) {
  const { field, instructions, row } = data;
 console.log(`Starting call: ${row}`)
 const messageContent = `Ignore the previous format. ${instructions}: ${field} (${scopeMetadata})`;


 const payload = {
   messages: [
     {
       content: messageContent,
       role: "user",
     },
   ],
 };


 const options = {
   method: 'post',
   headers: {
     'Authorization': `Bearer ${apiKey}`,
     'Content-Type': 'application/json',
     'Accept': 'application/json',
   },
   payload: JSON.stringify(payload),
 };


 try {
   sheet.getRange(row, 3).setValue("Bear with us... asking Contextual AI...");
      SpreadsheetApp.flush(); // Ensure the value is displayed before the API call
   const response = UrlFetchApp.fetch(apiUrl, options);
   const result = JSON.parse(response.getContentText());
   console.log(`Finished call: ${row}`)
   const answer = result.message.content.trim();
   const retrievalContents = JSON.stringify(result.retrieval_contents, null, 2);
   const attributions = JSON.stringify(result.attributions, null, 2);


   // Populate the sheet with the API response data
   sheet.getRange(row, 3).setValue(answer); // Column C (Answer)
   //sheet.getRange(row, 4).setValue(retrievalContents); // Column D (Retrieved Documents)
   //sheet.getRange(row, 5).setValue(attributions); // Column E (Evidence)
   SpreadsheetApp.flush();
 } catch (error) {
   console.error(`Error in processing row ${row}: ${error.message}`);
   sheet.getRange(row, 3).setValue("Error: " + error.message);
 }
}

​
Error Handling

The script includes basic error handling:
  • API connection issues
  • Data formatting problems
  • Rate limiting responses
  • Invalid sheet ranges

​
Security Considerations

  • Store API keys securely
  • Don’t share sheets containing API credentials
  • Regularly rotate API keys
  • Limit script access to necessary users only

​
Troubleshooting

This guide assumes basic familiarity with Google Sheets and Apps Script. For detailed API documentation, refer to the Contextual AI API docs.