Google Sheets Integration

Publish your sheet as CSV

Open your Google Sheet, go to File → Share → Publish to web. Choose Comma-separated values (.csv) and click Publish.

Copy the sheet URL

The URL will look like: https://docs.google.com/spreadsheets/d/.../edit. Copy it from your browser's address bar.

Go to CleverROI → Google Sheet Import

In your dashboard, navigate to Google Sheet Import (under Leads). Paste the URL and click Fetch & Import.

Our system automatically maps common column names (name, email, phone, budget, message, source). Rows without email or phone are skipped.

Create a Zap with Google Sheets trigger

In Zapier, create a new Zap. Choose Google Sheets as the trigger app and New Spreadsheet Row as the event.

Connect your sheet and set up the trigger

Select the spreadsheet and worksheet you want to monitor.

Add a Webhook action

Add an action step, choose Webhooks by Zapier, and select POST. Configure as follows:

• URL: https://yourdomain.com/api/webhook.php
• Payload Type: JSON
• Headers: X-API-KEY with your API key
• Data: Map sheet columns to JSON fields. For custom fields, create a JSON object:

{
  "name": "{{full_name}}",
  "email": "{{email}}",
  "phone": "{{phone}}",
  "budget": "{{budget}}",
  "message": "{{message}}",
  "source": "google_sheet",
  "custom_fields": {
    "Your role": "{{role}}",
    "When do you want to start?": "{{start_time}}",
    "Are you decision maker?": "{{decision_maker}}",
    "Company size": "{{company_size}}",
    "Primary goal": "{{primary_goal}}"
  }
}

Note: Replace {{...}} with the actual column names from your sheet.

Test and publish

Run a test with a sample row. If successful, the lead will appear in your CleverROI dashboard. Publish your Zap.

Open the Apps Script editor

In your Google Sheet, go to Extensions → Apps Script.

Copy the script below

Delete any existing code and paste the following script. Replace YOUR_API_KEY with your actual API key and yourdomain.com with your CleverROI domain.

/**
 * CLEVERROI GOOGLE SHEET → WEBHOOK INTEGRATION (ENHANCED)
 * Columns A–K: input fields (including custom fields)
 * Columns M–V: output scoring data
 */

function onFormSubmit(e) {
  var sheet = e.range.getSheet();
  var row = e.range.getRow();
  processRow(row, sheet);
}

function testLatestRow() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  processRow(lastRow, sheet);
}

function processAllRows() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  for (var row = 2; row <= lastRow; row++) {
    var existingScore = sheet.getRange(row, 13).getValue(); // Column M
    if (existingScore) continue;
    processRow(row, sheet);
    Utilities.sleep(500);
  }
  Logger.log("All rows processed.");
}

function processRow(row, sheet) {
  try {
    // Read input columns A–K (11 columns)
    var rowData = sheet.getRange(row, 1, 1, 11).getValues()[0];

    // Build payload with custom_fields object
    var payload = {
      name: rowData[0] || "",
      email: rowData[1] || "",
      phone: rowData[2] || "",
      budget: rowData[3] || "",
      message: rowData[4] || "",
      source: rowData[5] || "google_sheet",
      custom_fields: {
        "Your role": rowData[6] || "",
        "When do you want to start?": rowData[7] || "",
        "Are you decision maker?": rowData[8] || "",
        "Company size": rowData[9] || "",
        "Primary goal": rowData[10] || ""
      }
    };

    var options = {
      method: 'post',
      contentType: 'application/json',
      headers: { 'X-API-KEY': 'YOUR_API_KEY' }, // <-- REPLACE THIS
      payload: JSON.stringify(payload),
      muteHttpExceptions: true
    };

    var response = UrlFetchApp.fetch('https://yourdomain.com/api/webhook.php', options);
    var result = JSON.parse(response.getContentText());

    if (result.success && result.data) {
      var data = result.data;

      // M – Score
      sheet.getRange(row, 13).setValue(data.score || "");
      // N – Grade
      sheet.getRange(row, 14).setValue(data.grade || "");
      // O – Priority Action
      sheet.getRange(row, 15).setValue(data.priority_action || "");
      // P – Closing Probability
      sheet.getRange(row, 16).setValue(data.closing_probability || "");

      // Q – AI Impact
      var aiImpact = (data.ai && data.ai.impact) ? data.ai.impact : "";
      sheet.getRange(row, 17).setValue(aiImpact);

      // R – Contact Grade
      sheet.getRange(row, 18).setValue(data.contact_grade || "");

      // S – AI Confidence
      var aiConfidence = (data.ai && data.ai.confidence) ? data.ai.confidence : "";
      sheet.getRange(row, 19).setValue(aiConfidence);

      // T – AI Score display
      var aiScoreDisplay = (data.ai && data.ai.display) ? data.ai.display : "";
      sheet.getRange(row, 20).setValue(aiScoreDisplay);

      // U – Reasons (join array, truncate)
      var reasons = (data.reasons || []).join("; ");
      if (reasons.length > 200) reasons = reasons.substring(0,200) + "…";
      sheet.getRange(row, 21).setValue(reasons);

      // V – Structured Signals
      var signals = [];
      if (data.structured_signals) {
        for (var key in data.structured_signals) {
          if (data.structured_signals[key]) signals.push(key);
        }
      }
      var signalStr = signals.join(", ");
      if (signalStr.length > 200) signalStr = signalStr.substring(0,200) + "…";
      sheet.getRange(row, 22).setValue(signalStr);

    } else {
      sheet.getRange(row, 13).setValue("API Error");
    }
  } catch (error) {
    sheet.getRange(row, 13).setValue("Script Error");
    Logger.log(error);
  }
}

Set up a trigger

In the Apps Script editor, click on the clock icon (Triggers). Add a new trigger:

• Choose function: onFormSubmit
• Choose event source: From spreadsheet
• Choose event type: On form submit (or On change if you want every edit)

Save and authorize the script (it will ask for permissions to access your sheet and send external requests).

Test it

Add a new row to your sheet (or submit a form if you're using Google Forms). The script will run automatically, and you should see the score appear in columns M–V within seconds.

You can also manually run testLatestRow() from the Apps Script editor to test the last row.

Process existing rows

If you already have many rows that you want to score, run the processAllRows() function once from the editor. It will process each row that doesn't already have a score, with a 500ms delay to avoid hitting rate limits.