Note: This is an advanced method that only works with passthrough tables (an enterprise only feature).

Introduction

If you have a high volume of data that you need to enrich in Clay, you may be able to take advantage of passthrough tables. Passthrough tables automatically delete data in your Clay table, with the idea being that you:

Send data to Clay.
Enrich that data in Clay.
Send that data back to your system of record.
Delete the data from the Clay table to make more room for more data.

If you've exported your data to a Google Sheet, you can utilize Google Apps Scripts to periodically send that data to Clay to be enriched over time.

Setting up Google Apps Script

Warning: This script is specifically built to support data that has one single column (i.e. one column of domains that you're sending to Clay). If your data has more than one column, customizations may be needed.

From your Google Sheet, click on "Extensions > Apps Script".
Copy the code below and paste it into your Code Editor. The basic gist of this code is to cycle through each row in your Google Sheet and send the data in that row to your Clay table via a webhook. As it completes this, it will check off a box in the second column of your Google Sheet to indicate that the data was sent to Clay.

There are a few things you'll need to edit in this script:

Adjust the maxRows value to indicate the total number of rows you want to process from your Google Sheet.
Adjust the Utilities.sleep() value. This value represents how long to wait between each row of data that is being sent. You'll want to set this to a value that roughly matches the amount of time your data takes to enrich. It may take some trial and error to get this right, but the goal is to ensure that data is being enriched/deleted from Clay before new data is coming in. Note, this value cannot be less than 100ms as that would result in webhooks being sent at a rate faster than 10/sec which is the webhook throughput limit for Clay.
Adjust the url parameter to indicate your Clay webhook URL. You'll get this after creating a new webhook table.

function sendToWebhook() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getDataRange(); // Adjust if you want a specific range
  var data = range.getValues();
  var maxRows = 459217; // Set the maximum number of cells to process
  var count = 0;
  
  // Loop through each cell in the first column until 2000 cells are processed
  for (var i = 0; i < data.length; i++) {
    if (count >= maxRows) {
      break;
    }
    var domain = data[i][0];
    if (domain) {  // Ensure the cell is not empty
      var sent = sendToWebhook(domain);
      if (sent) {
        // Check the box in the second column to indicate success
        sheet.getRange(i + 1, 2).setValue(true);
        
      }
      Logger.log(domain)
      count++;
    }
    
    // Wait for X milli-seconds before processing the next cell
    Utilities.sleep(1000);
  }
}

function sendToWebhook(domain) {
  var url = "CLAY_WEBHOOK_URL"; // Replace with your actual webhook URL
  
  var options = {
    'method': 'post',
    'contentType': 'application/json',
    'payload': JSON.stringify({ "domain": domain })
  };
  
  try {
    var response = UrlFetchApp.fetch(url, options);
    return response.getResponseCode() === 200; // Return true if successful
  } catch (e) {
    Logger.log('Error sending to webhook: ' + e.message);
    return false; // Return false if there was an error
  }
}

Once you've finalized your script, run the sendToWebhook() function by clicking "Run". This will cycle through each piece of data that you have in your Google Sheet and send it to Clay.

Need More Help?

Chat with us below or email us at [email protected]

Lookup Row with Google Sheets

Lookup, Add, or Update Row with Google Sheets

Guide: How to send your Clay table data to Zapier with HTTP API

Guide: How to send Zapier information to Clay

Import Data to Clay Using Webhooks