Google Sheets (1 Minute Trigger)

Copy and Paste the entire Apps Script code in your Google Sheets App Script. No need to change anything in the code. Make sure that you copy and paste the entire code as it is.

Full Tutorial here: https://forum.pabbly.com/threads/google-sheets-1-minute-trigger-send-new-rows-in-every-1-minute.12954/

---

function onOpen(e) {
  SpreadsheetApp.getUi().createMenu('Pabbly Webhooks')
  .addItem('Send rows every minute', 'setup')
  .addItem('Help', 'help')
  .addToUi();
}

function setup() {
  var trigOwner = PropertiesService.getDocumentProperties().getProperty('trigOwner');
  var sheetId = PropertiesService.getDocumentProperties().getProperty('sheetId');
  var setup = PropertiesService.getDocumentProperties().getProperty('setup');
  var sheet = !sheetId ? false : SpreadsheetApp.getActive().getSheets().find(sh => sh.getSheetId() == sheetId);
  var n = !sheet ? sheetId : sheet.getName();
  var config = (!trigOwner ? '' : `Trigger owner: ${trigOwner}.`)
  + (!sheetId ? '' : ` Sheet: ${n}.`)
  + (!setup ? '' : `\nWebhook & last column: ${setup}.`);
  if (config.length) {
    config = `\n* Current configuration:- ${config}\n`;
  }
  var title = 'Setup Trigger for Pabbly Connect?';
  var body = `The trigger will auto-send new rows from Google Sheets every minute.

  1. Set / Update Trigger: Enter the webhook URL and click the "Yes" button to set the trigger. Optionally set the last column name which when filled will trigger and send the row.

  Input examples:-
  a. Webhook URL only: https://connect.pabbly.com/workflow/sendwebhookdata/IjU33
  b. Webhook & last column: https://connect.pabbly.com/workflow/sendwebhookdata/IjU33, D
  (Assuming D is the last column to be sent. The new row will only be sent if there is data in the Column D. Webhook URL and the last column name should be separated by a comma.)

  2. Remove Existing Trigger: Click the "No" button if you wish to remove the trigger you set earlier.
  ${config}\n`;

  var ui = SpreadsheetApp.getUi();
  var result = ui.prompt(title, body, ui.ButtonSet.YES_NO_CANCEL);

  var button = result.getSelectedButton();
  var setup = result.getResponseText();
  if (button == ui.Button.YES) {
    if (!trigOwner) {
      ScriptApp.newTrigger('onSchedule')
      .timeBased()
      .everyMinutes(1)
      .create();
      PropertiesService.getDocumentProperties().setProperty('trigOwner', Session.getEffectiveUser().getEmail());
    }
    PropertiesService.getDocumentProperties().setProperty('sheetId', SpreadsheetApp.getActiveSheet().getSheetId().toString());
    PropertiesService.getDocumentProperties().setProperty('setup', setup);
    PropertiesService.getDocumentProperties().setProperty('lastSentRow', 1);
    ui.alert('Trigger set successfully!');
  } else if (button == ui.Button.NO) {
    PropertiesService.getDocumentProperties().deleteAllProperties();
    var msg = 'Cleared configurations.';
    if (Session.getEffectiveUser().getEmail() == trigOwner) {
      var triggers = ScriptApp.getUserTriggers(SpreadsheetApp.getActive());
      for (var i = 0; i < triggers.length; i++) {
        ScriptApp.deleteTrigger(triggers[i]);
      }
      msg += '\nRemoved the trigger.';
    } else {
      msg += `Use the account ${trigOwner} to remove the trigger.`;
    }
    ui.alert(msg);
  }
}

function help() {
  var htmlOutput = HtmlService
  .createHtmlOutput('<p>Refer detailed guide <a href="https://forum.pabbly.com/threads/google-sheets.12066/" target="_blank">here</a>.</p>')
  .setWidth(300)
  .setHeight(100);
  SpreadsheetApp.getUi().showModelessDialog(htmlOutput, 'Pabbly Webhooks Help');
}

function onSchedule() {
  const start = Date.now();
  var ss = SpreadsheetApp.getActive();
  var sheetId = PropertiesService.getDocumentProperties().getProperty('sheetId');
  var sheet = ss.getSheets().find(sh => sh.getSheetId() == sheetId);
  var setup = PropertiesService.getDocumentProperties().getProperty('setup');
  if (!sheet || !setup) {return;}

  var webhook_url, end_column;
  var li = setup.lastIndexOf(',');
  if (li == -1) {
    webhook_url = setup;
    let i = sheet.getDataRange().getLastColumn();
    let l = '';
    let c;
    while (i > 0) {
      c = (i - 1) % 26;
      l = String.fromCharCode(c + 65) + l;
      i = (i - c - 1) / 26;
    }
    end_column = l;
  } else {
    webhook_url = setup.substring(0,li);
    end_column = setup.substring(li+1).trim();
  }
  var lastSentRow = PropertiesService.getDocumentProperties().getProperty('lastSentRow');

  if (!webhook_url || !end_column || !lastSentRow) {return;}
 
  lastSentRow = parseInt(lastSentRow,10);
  var rowEnd = parseInt(sheet.getLastRow(), 10);
  if (rowEnd > lastSentRow) {
    if (timeUp(start)) {return;}
    var headers = sheet.getRange(`A1:${end_column}1`).getDisplayValues()[0]; 
    var values = sheet.getRange(`A${parseInt(lastSentRow+1,10)}:${end_column+rowEnd}`).getDisplayValues();
    var sent = -1;
    for (var v = 0; v < values.length; v++) {
      if ((values[v][values[v].length-1]).length) {
        connectWebhook(
          convertToJson(values[v],headers,ss.getName(),ss.getId(),sheet.getName(),sheetId,end_column,lastSentRow+1+v),
          webhook_url
        );
        sent = v;
      }
      if (timeUp(start)) {break;}
    }

    if (sent >= 0) {
      lastSentRow += (sent + 1);
      PropertiesService.getDocumentProperties().setProperty('lastSentRow', lastSentRow);
    }
  }

}

function timeUp(start) {
  return (Date.now() - start) > 45000;
}

function connectWebhook(data,url) {
  var options = {
    'method': 'post',
    'payload': JSON.stringify(data)
  };
  UrlFetchApp.fetch(url,options);
}

function convertToJson(values,headers,spreadsheetName,spreadsheetID,sheetName,sheetID,triggerColumn,rowIndex) {
  var data = {};
  for (var i in values) {
    data[headers[i]] = values[i];
  }
  data.SpreadsheetName = spreadsheetName;
  data.SpreadsheetID = spreadsheetID;
  data.SheetName = sheetName;
  data.SheetID = sheetID;
  data.TriggerColumn = triggerColumn;
  data.RowIndex = rowIndex;
  return data;
}