Skip to Content
IntegrationsGoogle Sheets

Google Sheets (Apps Script)

Use Google Sheets as a prompt queue with Apps Script — no external tools needed. Submit prompts directly from your spreadsheet and receive AI results back in the same row.

Testing tip — Build and verify this integration end-to-end with convoy-mock first. It’s a free, synthetic model that returns a callback in ~60 seconds and is never billed. Set DEFAULT_MODEL: 'convoy-mock' in the Apps Script CONFIG while testing rows, then switch to claude-3-haiku once everything works.

Use Case: Spreadsheet-Powered AI Pipeline

Turn any Google Sheet into a batch AI processing tool:

  • Add prompts to a column → click a button → AI results appear in the next column
  • Marketing teams generate dozens of content variations at once
  • Data teams enrich rows with AI-generated summaries or classifications
  • Operations teams process form responses with AI analysis
┌─────────────────────────────────────────────────────────────┐
│  Google Sheet                                               │
│  ┌─────────────────┬──────────┬────────────────────────┐   │
│  │ Prompt          │ Status   │ AI Response             │   │
│  ├─────────────────┼──────────┼────────────────────────┤   │
│  │ Write a tagline │ ✅ Done  │ "Ship smarter, not..." │   │
│  │ Summarize Q4... │ ⏳ Sent  │                        │   │
│  │ Draft an email  │          │                        │   │
│  └─────────────────┴──────────┴────────────────────────┘   │
│                                                             │
│  [▶ Run Convoy]  ← Custom menu button                      │
└─────────────────────────────────────────────────────────────┘
         │                              ▲
         │ POST /cargo/load             │ Apps Script polls
         ▼                              │ /cargo/{id}/tracking
    ┌─────────┐                         │
    │  Convoy │─── batch processing ────┘
    │  API    │
    └─────────┘

This integration uses polling (checking for results periodically) rather than callbacks, since Google Apps Script can’t receive incoming webhooks. A time-based trigger checks for completed results every few minutes.

Prerequisites

  • A Convoy account with a project API key (convoy_sk_...)
  • A Google account with access to Google Sheets
  • Basic familiarity with Google Sheets (no coding experience required — just copy/paste the scripts)

Step 1: Set Up Your Spreadsheet

Create a new Google Sheet with these columns:

A: PromptB: System PromptC: ModelD: StatusE: Cargo IDF: AI ResponseG: Tokens
Write a tagline for our AI productYou are a copywriterclaude-3-haiku
Summarize this for investorsYou are a business analystclaude-3-sonnet

Columns B (System Prompt) and C (Model) are optional. If left blank, the script will use sensible defaults.

Step 2: Add the Apps Script

  1. In your Google Sheet, go to Extensions → Apps Script
  2. Delete any existing code in Code.gs
  3. Paste the following script:
// ============================================================
// CONFIGURATION — Update these values
// ============================================================
const CONFIG = {
  CONVOY_API_KEY: 'convoy_sk_your_key_here',  // Your Convoy API key
  CONVOY_BASE_URL: 'https://api.cnvy.ai',     // API base URL
  DEFAULT_MODEL: 'claude-3-haiku',            // Default model if column C is empty
  DEFAULT_MAX_TOKENS: 1024,                   // Default max tokens
  SHEET_NAME: 'Sheet1',                       // Name of your sheet tab
  // Column indices (1-based)
  COL_PROMPT: 1,        // A
  COL_SYSTEM: 2,        // B
  COL_MODEL: 3,         // C
  COL_STATUS: 4,        // D
  COL_CARGO_ID: 5,      // E
  COL_RESPONSE: 6,      // F
  COL_TOKENS: 7,        // G
  HEADER_ROW: 1,        // Row number of headers (data starts at HEADER_ROW + 1)
};
 
// ============================================================
// CUSTOM MENU
// ============================================================
 
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('🚛 Convoy')
    .addItem('Submit All Pending Prompts', 'submitPendingPrompts')
    .addItem('Check Results', 'checkResults')
    .addSeparator()
    .addItem('Submit Selected Rows', 'submitSelectedRows')
    .addItem('Set Up Auto-Check (every 5 min)', 'createTimeTrigger')
    .addItem('Remove Auto-Check', 'removeTimeTrigger')
    .addToUi();
}
 
// ============================================================
// SUBMIT PROMPTS
// ============================================================
 
/**
 * Submit all rows that have a prompt but no status.
 */
function submitPendingPrompts() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONFIG.SHEET_NAME);
  const lastRow = sheet.getLastRow();
  let submitted = 0;
 
  for (let row = CONFIG.HEADER_ROW + 1; row <= lastRow; row++) {
    const prompt = sheet.getRange(row, CONFIG.COL_PROMPT).getValue();
    const status = sheet.getRange(row, CONFIG.COL_STATUS).getValue();
 
    // Skip rows without prompts or already submitted
    if (!prompt || status) continue;
 
    const success = submitRow(sheet, row);
    if (success) submitted++;
 
    // Rate limit: small delay between requests
    Utilities.sleep(200);
  }
 
  if (submitted > 0) {
    SpreadsheetApp.getUi().alert(`✅ Submitted ${submitted} prompt(s) to Convoy!`);
  } else {
    SpreadsheetApp.getUi().alert('No pending prompts found. Add prompts to column A and leave column D (Status) empty.');
  }
}
 
/**
 * Submit only the currently selected rows.
 */
function submitSelectedRows() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONFIG.SHEET_NAME);
  const selection = SpreadsheetApp.getActiveRange();
  const startRow = selection.getRow();
  const numRows = selection.getNumRows();
  let submitted = 0;
 
  for (let row = startRow; row < startRow + numRows; row++) {
    if (row <= CONFIG.HEADER_ROW) continue; // Skip header
 
    const prompt = sheet.getRange(row, CONFIG.COL_PROMPT).getValue();
    if (!prompt) continue;
 
    const success = submitRow(sheet, row);
    if (success) submitted++;
    Utilities.sleep(200);
  }
 
  SpreadsheetApp.getUi().alert(`✅ Submitted ${submitted} prompt(s)!`);
}
 
/**
 * Submit a single row to Convoy.
 */
function submitRow(sheet, row) {
  const prompt = sheet.getRange(row, CONFIG.COL_PROMPT).getValue();
  const systemPrompt = sheet.getRange(row, CONFIG.COL_SYSTEM).getValue() || '';
  const model = sheet.getRange(row, CONFIG.COL_MODEL).getValue() || CONFIG.DEFAULT_MODEL;
 
  const params = {
    model: model,
    max_tokens: CONFIG.DEFAULT_MAX_TOKENS,
    messages: [{ role: 'user', content: prompt }],
  };
 
  if (systemPrompt) {
    params.system = systemPrompt;
  }
 
  const payload = { params: params };
 
  try {
    const response = UrlFetchApp.fetch(`${CONFIG.CONVOY_BASE_URL}/cargo/load`, {
      method: 'post',
      contentType: 'application/json',
      headers: { 'X-API-Key': CONFIG.CONVOY_API_KEY },
      payload: JSON.stringify(payload),
      muteHttpExceptions: true,
    });
 
    const code = response.getResponseCode();
    const data = JSON.parse(response.getContentText());
 
    if (code === 200 || code === 201) {
      sheet.getRange(row, CONFIG.COL_STATUS).setValue('⏳ Sent');
      sheet.getRange(row, CONFIG.COL_CARGO_ID).setValue(data.cargo_id);
      return true;
    } else {
      sheet.getRange(row, CONFIG.COL_STATUS).setValue(`❌ Error: ${data.detail || code}`);
      return false;
    }
  } catch (error) {
    sheet.getRange(row, CONFIG.COL_STATUS).setValue(`❌ ${error.message}`);
    return false;
  }
}
 
// ============================================================
// CHECK RESULTS (POLLING)
// ============================================================
 
/**
 * Check all "Sent" rows for completed results.
 */
function checkResults() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONFIG.SHEET_NAME);
  const lastRow = sheet.getLastRow();
  let completed = 0;
 
  for (let row = CONFIG.HEADER_ROW + 1; row <= lastRow; row++) {
    const status = sheet.getRange(row, CONFIG.COL_STATUS).getValue();
    const cargoId = sheet.getRange(row, CONFIG.COL_CARGO_ID).getValue();
 
    // Only check rows that are "Sent" and have a cargo ID
    if (!status.toString().includes('Sent') || !cargoId) continue;
 
    const result = checkCargoStatus(cargoId);
 
    if (result.status === 'COMPLETED') {
      const text = result.response.content[0].text;
      const tokens = result.response.usage.output_tokens;
 
      sheet.getRange(row, CONFIG.COL_STATUS).setValue('✅ Done');
      sheet.getRange(row, CONFIG.COL_RESPONSE).setValue(text);
      sheet.getRange(row, CONFIG.COL_TOKENS).setValue(tokens);
      completed++;
    } else if (result.status === 'FAILED') {
      sheet.getRange(row, CONFIG.COL_STATUS).setValue(`❌ Failed: ${result.error || 'Unknown'}`);
    }
    // If still processing, leave as "Sent"
 
    Utilities.sleep(100); // Small delay between API calls
  }
 
  // Only show alert if called manually (not from trigger)
  if (completed > 0) {
    Logger.log(`Completed ${completed} result(s)`);
  }
}
 
/**
 * Check the status of a single cargo request.
 */
function checkCargoStatus(cargoId) {
  try {
    const response = UrlFetchApp.fetch(
      `${CONFIG.CONVOY_BASE_URL}/cargo/${cargoId}/tracking`,
      {
        method: 'get',
        headers: { 'X-API-Key': CONFIG.CONVOY_API_KEY },
        muteHttpExceptions: true,
      }
    );
 
    const code = response.getResponseCode();
    if (code === 200) {
      return JSON.parse(response.getContentText());
    }
    return { status: 'UNKNOWN', error: `HTTP ${code}` };
  } catch (error) {
    return { status: 'UNKNOWN', error: error.message };
  }
}
 
// ============================================================
// AUTO-CHECK TRIGGER
// ============================================================
 
/**
 * Create a time-based trigger to check results every 5 minutes.
 */
function createTimeTrigger() {
  // Remove existing triggers first
  removeTimeTrigger();
 
  ScriptApp.newTrigger('checkResults')
    .timeBased()
    .everyMinutes(5)
    .create();
 
  SpreadsheetApp.getUi().alert(
    '✅ Auto-check enabled! Results will be checked every 5 minutes.\n\n' +
    'Use "Convoy → Remove Auto-Check" to disable.'
  );
}
 
/**
 * Remove the auto-check trigger.
 */
function removeTimeTrigger() {
  const triggers = ScriptApp.getProjectTriggers();
  for (const trigger of triggers) {
    if (trigger.getHandlerFunction() === 'checkResults') {
      ScriptApp.deleteTrigger(trigger);
    }
  }
}
  1. Click Save (💾) and name the project “Convoy Integration”

Replace convoy_sk_your_key_here in the CONFIG section with your actual API key. For better security in shared sheets, see the “Securing Your API Key” section below.

Step 3: Authorize and Run

  1. Close the Apps Script editor and reload your spreadsheet
  2. You’ll see a new 🚛 Convoy menu in the menu bar
  3. Click Convoy → Submit All Pending Prompts
  4. Google will ask you to authorize the script — click through the permissions:
    • “This app isn’t verified” → Click Advanced → Go to Convoy Integration (unsafe)
    • Grant access to Google Sheets and external URLs

The “unsafe” warning appears because this is a custom script, not a published add-on. It’s safe — the script only connects to your Convoy API.

Step 4: Using the Integration

Submit Prompts

  1. Add prompts to column A (one per row)
  2. Optionally fill in columns B (system prompt) and C (model)
  3. Click Convoy → Submit All Pending Prompts
  4. Column D updates to ”⏳ Sent” and column E gets the cargo ID

Check for Results

Manual: Click Convoy → Check Results

Automatic: Click Convoy → Set Up Auto-Check (every 5 min) — the script will poll for results in the background and fill them in automatically.

Submit Specific Rows

  1. Select the rows you want to submit
  2. Click Convoy → Submit Selected Rows

How It Works

Since Google Apps Script can’t receive incoming webhooks (it’s serverless and only runs on triggers), this integration uses polling instead of callbacks:

  1. Submit — The script POSTs each prompt to /cargo/load and stores the cargo_id
  2. Poll — A time-based trigger calls checkResults() every 5 minutes
  3. Complete — When a cargo’s status is COMPLETED, the script writes the result back to the sheet
Timeline:
0:00  Submit prompt → Status: "⏳ Sent"
0:05  Auto-check → Status still PROCESSING
0:10  Auto-check → Status still PROCESSING
0:15  Auto-check → Status: COMPLETED → Write result → "✅ Done"

Advanced Configuration

Custom Column Layout

Modify the CONFIG object to match your sheet’s layout:

const CONFIG = {
  // ...
  SHEET_NAME: 'AI Prompts',  // Use a specific tab
  COL_PROMPT: 2,    // B column for prompts
  COL_SYSTEM: 3,    // C column for system prompts
  COL_MODEL: 4,     // D column for model
  COL_STATUS: 5,    // E column for status
  COL_CARGO_ID: 6,  // F column for cargo ID
  COL_RESPONSE: 7,  // G column for response
  COL_TOKENS: 8,    // H column for tokens
  HEADER_ROW: 2,    // Headers on row 2 (data starts row 3)
};

Temperature and Max Tokens per Row

Add columns for temperature and max tokens:

// Add to CONFIG:
const CONFIG = {
  // ...
  COL_TEMPERATURE: 8,  // H
  COL_MAX_TOKENS: 9,   // I
};
 
// Modify submitRow():
function submitRow(sheet, row) {
  const prompt = sheet.getRange(row, CONFIG.COL_PROMPT).getValue();
  const systemPrompt = sheet.getRange(row, CONFIG.COL_SYSTEM).getValue() || '';
  const model = sheet.getRange(row, CONFIG.COL_MODEL).getValue() || CONFIG.DEFAULT_MODEL;
  const temperature = sheet.getRange(row, CONFIG.COL_TEMPERATURE).getValue();
  const maxTokens = sheet.getRange(row, CONFIG.COL_MAX_TOKENS).getValue() || CONFIG.DEFAULT_MAX_TOKENS;
 
  const params = {
    model: model,
    max_tokens: maxTokens,
    messages: [{ role: 'user', content: prompt }],
  };
 
  if (systemPrompt) params.system = systemPrompt;
  if (temperature) params.temperature = parseFloat(temperature);
 
  // ... rest of function
}

Batch Processing with Progress Bar

Show a progress indicator for large batches:

function submitPendingPrompts() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONFIG.SHEET_NAME);
  const lastRow = sheet.getLastRow();
 
  // Count pending rows first
  const pendingRows = [];
  for (let row = CONFIG.HEADER_ROW + 1; row <= lastRow; row++) {
    const prompt = sheet.getRange(row, CONFIG.COL_PROMPT).getValue();
    const status = sheet.getRange(row, CONFIG.COL_STATUS).getValue();
    if (prompt && !status) pendingRows.push(row);
  }
 
  if (pendingRows.length === 0) {
    SpreadsheetApp.getUi().alert('No pending prompts found.');
    return;
  }
 
  // Submit with progress
  const toast = SpreadsheetApp.getActiveSpreadsheet();
  for (let i = 0; i < pendingRows.length; i++) {
    toast.toast(`Submitting ${i + 1} of ${pendingRows.length}...`, '🚛 Convoy', 3);
    submitRow(sheet, pendingRows[i]);
    Utilities.sleep(200);
  }
 
  toast.toast(`Done! Submitted ${pendingRows.length} prompts.`, '🚛 Convoy', 5);
}

Multi-Message Conversations

Support multi-turn conversations by encoding messages in a single cell:

// In your prompt cell, use this format:
// USER: First message
// ASSISTANT: First response
// USER: Follow-up question
 
function parseConversation(promptText) {
  const lines = promptText.split('\n');
  const messages = [];
  let currentRole = 'user';
  let currentContent = '';
 
  for (const line of lines) {
    if (line.startsWith('USER:')) {
      if (currentContent) messages.push({ role: currentRole, content: currentContent.trim() });
      currentRole = 'user';
      currentContent = line.replace('USER:', '').trim();
    } else if (line.startsWith('ASSISTANT:')) {
      if (currentContent) messages.push({ role: currentRole, content: currentContent.trim() });
      currentRole = 'assistant';
      currentContent = line.replace('ASSISTANT:', '').trim();
    } else {
      currentContent += '\n' + line;
    }
  }
  if (currentContent) messages.push({ role: currentRole, content: currentContent.trim() });
 
  return messages;
}

Securing Your API Key

For shared spreadsheets, store the API key in Script Properties instead of the code:

  1. In Apps Script, go to Project Settings (⚙️)
  2. Scroll to Script Properties
  3. Add a property: Key = CONVOY_API_KEY, Value = convoy_sk_your_key_here

Then update the script:

const CONFIG = {
  // Replace the hardcoded key with:
  CONVOY_API_KEY: PropertiesService.getScriptProperties().getProperty('CONVOY_API_KEY'),
  // ...
};

Script Properties are only visible to editors of the script, not viewers of the spreadsheet. This is more secure than hardcoding the key in the source.

Email Notification on Completion

Get notified when a batch finishes:

function checkResults() {
  // ... existing code ...
 
  // After the loop, send email if any completed
  if (completed > 0) {
    MailApp.sendEmail({
      to: Session.getActiveUser().getEmail(),
      subject: `🚛 Convoy: ${completed} result(s) ready`,
      body: `${completed} AI-generated result(s) have been added to your spreadsheet.\n\n` +
            `View your sheet: ${SpreadsheetApp.getActiveSpreadsheet().getUrl()}`,
    });
  }
}

Limitations

LimitationDetailsWorkaround
Execution timeApps Script has a 6-minute limit per executionProcess in batches of ~50 rows
Trigger frequencyMinimum 1 minute between triggers5-minute polling is usually sufficient
No webhooksApps Script can’t receive incoming HTTP requestsUse polling pattern (built into this script)
URL Fetch quota20,000 calls/dayMore than enough for typical use
Cell character limit50,000 characters per cellTruncate long responses or use multiple cells

Troubleshooting

”Exception: Request failed” error

  • Verify your API key is correct in the CONFIG section
  • Check that the Convoy API URL is reachable
  • Look at the error details in View → Execution log in Apps Script

Status stays ”⏳ Sent” forever

  • Click Convoy → Check Results manually to force a check
  • Verify the cargo ID is valid by checking in the Convoy dashboard
  • Batch processing typically takes minutes to hours — be patient
  • If it’s been over an hour, the request may have failed silently

”Authorization required” popup

  • This is normal on first run — click through to grant permissions
  • If you see “This app is blocked”, ask your Google Workspace admin to allow it

Results are truncated

  • Increase DEFAULT_MAX_TOKENS in the CONFIG
  • Google Sheets cells have a 50,000 character limit — very long responses may be cut off

Script runs too slowly

  • Reduce the number of rows processed per run
  • Increase Utilities.sleep() delays if hitting rate limits
  • Use submitSelectedRows() for smaller batches

”Exceeded maximum execution time”

  • Apps Script has a 6-minute limit
  • For large batches (100+ rows), submit in groups of 50
  • The auto-check trigger handles results incrementally, so it won’t time out

Tips for Production Use

  1. Use a dedicated tab — Keep your Convoy prompts on a separate sheet tab to avoid conflicts
  2. Freeze the header row — Makes it easier to scroll through large batches
  3. Add data validation — Use a dropdown in column C for valid model names
  4. Color-code status — Use conditional formatting: green for ”✅ Done”, yellow for ”⏳ Sent”, red for ”❌”
  5. Archive completed rows — Move done rows to an “Archive” tab to keep the active sheet clean
  6. Share carefully — If sharing the sheet, use Script Properties for the API key (see above)

Cost efficiency: Convoy batches your requests together, reducing costs by up to 50% compared to individual API calls. Combined with Google Sheets’ free tier, this is one of the most cost-effective ways to run batch AI workflows.

Last updated on
Slack Bot