New Google Sheets trigger

This trigger lets you keep track of all updates in the cells of your sheet. Trigger works by Webhooks and sends an event immediately when a cell is updated and a row is added.

You’ll need to make additional adjustments to your spreadsheets setup to use this trigger. First, connect your Google Sheets account to Albato.

Connecting Google Sheets to Albato

How to set up spreadsheet

Open your spreadsheet which you want to use in the integration

Go to the Extensions section → Apps Scripts

Remove the data and paste this code:


function generateData(e) {

let ss = SpreadsheetApp.getActive();

var spreadsheetName = ss.getName();// Spreadsheet Name

var sheetName = ss.getSheetName();// Sheet Name

var spreadsheetId = ss.getId();// id листа

var row = ss.getCurrentCell().getRow();// Row number

var column = ss.getCurrentCell().getColumn();// Column number

var values = ss.getActiveRange().getValues();// Array of new data

var user = e.user;// The user who made the changes

var date = new Date();// Date

sendWebhook(spreadsheetName, sheetName, row, column, values, user, date, spreadsheetId);

}

function sendWebhook(spreadsheetName, sheetName, row, column, values, user, date, spreadsheetId) {

var endpoint = "<https://webhook.site/ded7da72-670e-4a86-86aa-f13c9343455d>";

var jsonarray = JSON.stringify;

var payload = {

"spreadsheetName" : spreadsheetName,

"sheetName" : sheetName,

"row" : row,

"column" : column,

"values" : JSON.stringify(values),

"user" : user,

"date" : date,

"spreadsheetId": spreadsheetId

};

var options = {

"method" : "post",

"payload" : payload

};

UrlFetchApp.fetch(endpoint, options)

}

function createSpreadsheetOpenTrigger() {

const ss = SpreadsheetApp.getActive();

ScriptApp.newTrigger("generateData").forSpreadsheet(ss).onChange().create();

}

Copy the Albato webhook URL from your Google Sheets connection in the Apps section.




Paste this webhook into the endpoint variable (line 14).




Click the Save icon. Pick the “createSpreadsheetOpenTrigger” option and click the Run button.




Click the Review permissions button.




Select your account.




If you receive a warning, open the "Advanced" tab.




Next, click the “Go to Untitled Project” button.




Give access to your app by clicking the “Allow” button.




Go to the "Triggers" section and make sure you have one trigger saved. If you made mistakes during the setup process, several triggers may be saved. These triggers should be deleted.




The spreadsheet is ready!

You can create your automation scenario with the “Watch for worksheet updates” trigger.

Updated on: 30/08/2023