/**
* Google Sheets App Script to push only newly added rows to a webhook
* This script tracks the last processed row to avoid duplicate submissions
*/
// The webhook URL to send data to
const WEBHOOK_URL = "YOUR CLAY WEBHOOK URL";
// The column number containing LinkedIn URLs (1 for column A, 2 for column B, etc.)
const LINKEDIN_COLUMN = 1; // Change this if your LinkedIn URLs are in a different column
// Property key to store the last processed row
const LAST_ROW_PROPERTY_KEY = "lastProcessedRow";
/**
* Set up a trigger to run when the spreadsheet is edited
*/
function createOnEditTrigger() {
// Delete any existing triggers to avoid duplicates
const triggers = ScriptApp.getProjectTriggers();
for (let i = 0; i < triggers.length; i++) {
if (triggers[i].getHandlerFunction() === "onEdit") {
ScriptApp.deleteTrigger(triggers[i]);
}
}
// Create a new trigger
ScriptApp.newTrigger("onEdit")
.forSpreadsheet(SpreadsheetApp.getActive())
.onEdit()
.create();
// Get the current last row with data and store it as the starting point
const currentLastRow = getLastRowWithData();
PropertiesService.getScriptProperties().setProperty(LAST_ROW_PROPERTY_KEY, currentLastRow.toString());
// Add a note to cell A1 to show the script is active
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
sheet.getRange("A1").setNote("Webhook script active. Last processed row: " + currentLastRow);
}
/**
* Handler function that runs when the spreadsheet is edited
*/
function onEdit(e) {
// Get the active sheet
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// Get the last processed row from properties
const lastProcessedRowStr = PropertiesService.getScriptProperties().getProperty(LAST_ROW_PROPERTY_KEY);
const lastProcessedRow = lastProcessedRowStr ? parseInt(lastProcessedRowStr) : 0;
// Get the current last row with data
const currentLastRow = getLastRowWithData();
// If there are new rows
if (currentLastRow > lastProcessedRow) {
// Process only the newly added rows
for (let row = lastProcessedRow + 1; row <= currentLastRow; row++) {
const linkedInUrl = sheet.getRange(row, LINKEDIN_COLUMN).getValue();
// Only send if there's a value
if (linkedInUrl) {
sendToWebhook(linkedInUrl);
// Add a note to the cell to indicate it's been sent
sheet.getRange(row, LINKEDIN_COLUMN).setNote("Sent to webhook at " + new Date().toLocaleString());
}
}
// Update the last processed row in properties
PropertiesService.getScriptProperties().setProperty(LAST_ROW_PROPERTY_KEY, currentLastRow.toString());
// Update the note on A1
sheet.getRange("A1").setNote("Webhook script active. Last processed row: " + currentLastRow);
}
}
/**
* Get the last row that contains data in the LinkedIn column
*/
function getLastRowWithData() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const values = sheet.getRange(1, LINKEDIN_COLUMN, sheet.getLastRow()).getValues();
for (let i = values.length - 1; i >= 0; i--) {
if (values[i][0] !== "") {
return i + 1;
}
}
return 0;
}
/**
* Send the LinkedIn URL to the webhook
*/
function sendToWebhook(linkedInUrl) {
try {
// Prepare the data to send
const payload = {
linkedin: linkedInUrl
};
// Set up the options for the HTTP request
const options = {
method: "post",
contentType: "application/json",
payload: JSON.stringify(payload)
};
// Send the request
const response = UrlFetchApp.fetch(WEBHOOK_URL, options);
// Log the response
Logger.log("Webhook response: " + response.getContentText());
return true;
} catch (error) {
Logger.log("Error sending to webhook: " + error.toString());
return false;
}
}
/**
* Reset the last processed row to 0
* (Run this function manually if you want to resend all rows)
*/
function resetProcessedRows() {
PropertiesService.getScriptProperties().setProperty(LAST_ROW_PROPERTY_KEY, "0");
SpreadsheetApp.getUi().alert("Reset complete. The next edit will process all rows with data.");
}
/**
* Manually send a specific row to the webhook
* (Run this function from a custom menu)
*/
function sendSpecificRow() {
const ui = SpreadsheetApp.getUi();
const response = ui.prompt("Send specific row", "Enter the row number to send:", ui.ButtonSet.OK_CANCEL);
if (response.getSelectedButton() == ui.Button.OK) {
const rowNum = parseInt(response.getResponseText());
if (!isNaN(rowNum) && rowNum > 0) {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const linkedInUrl = sheet.getRange(rowNum, LINKEDIN_COLUMN).getValue();
if (linkedInUrl) {
if (sendToWebhook(linkedInUrl)) {
sheet.getRange(rowNum, LINKEDIN_COLUMN).setNote("Manually sent to webhook at " + new Date().toLocaleString());
ui.alert("Row " + rowNum + " sent successfully!");
} else {
ui.alert("Failed to send row " + rowNum + ". Check logs for details.");
}
} else {
ui.alert("No LinkedIn URL found in row " + rowNum);
}
} else {
ui.alert("Please enter a valid row number.");
}
}
}
/**
* Create a custom menu when the spreadsheet opens
*/
function onOpen() {
const ui = SpreadsheetApp.getUi();
ui.createMenu('Clay Webhook')
.addItem('Initialize Webhook', 'initialize')
.addItem('Send Specific Row', 'sendSpecificRow')
.addItem('Reset Processed Rows', 'resetProcessedRows')
.addToUi();
}
/**
* Initialize the script
* (Run this function manually to set everything up)
*/
function initialize() {
createOnEditTrigger();
SpreadsheetApp.getUi().alert("Webhook script initialized. New LinkedIn URLs will be sent to the webhook automatically.");
} .png){kind=logo}
