Scribd
Upload
15 views10 pages

Google Apps Script Webhook Integration

Uploaded by

msdesigns.studio2023
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
15 views10 pages

Google Apps Script Webhook Integration

Uploaded by

msdesigns.studio2023
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Form Script

function onFormSubmit(e) {
var currentSubmissionId = [Link]();
// Retrieve the last submission ID we saw
var scriptProperties = [Link]();
var lastSubmissionId = [Link]('lastSubmissionId');
// If this submission ID is the same as the last, do nothing
if (currentSubmissionId === lastSubmissionId) {
return;
}
// Otherwise, store this new ID and proceed
[Link]('lastSubmissionId', currentSubmissionId);
// === Continue with your code to build payload and send it ===
try {
var responses = [Link]();
var payload = {};
for (var i = 0; i < [Link]; i++) {
var question = responses[i].getItem().getTitle();
var answer = responses[i].getResponse();
payload[question] = answer;
}

var options = {
'method': 'post',
'contentType': 'application/json',
'payload': [Link](payload)
};

var webhookUrl =
'[Link]
[Link](webhookUrl, options);

} catch (error) {
[Link]('Error sending form data to webhook: ' + error);
}
}
Sheets Script
//For use with the Watch Changes module. Paste the webhook URL from your
scenario here:
WATCH_CHANGE_WEBHOOK_URL = '[Link]
//OPTIONAL (for use with Watch Changes):
SHEET = ''; //SHEET allows you to trigger updates
only for the specified sheet (by name)
// e.g. SHEET = 'Sheet1'
RANGE = ''; //RANGE allows you to trigger updates
only for values within this range (by A1 notation)
// e.g. RANGE = 'A1:C9'

//For use with the Perform a Function module. Paste the webhook URL from your
scenario here:
PERFORM_FUNCTION_WEBHOOK_URL = '[Link]

//////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////
//////////////////////////////////////////////// DO NOT TOUCH BELOW!!!
////////////////////////////////////////////////////////////

//////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////

function watchChanges(e) {

// REQUIRED
const UPDATE_WEBHOOK_URL = WATCH_CHANGE_WEBHOOK_URL;
if(!UPDATE_WEBHOOK_URL) {
[Link]('Enter WATCH_CHANGE_WEBHOOK_URL');
throw new Error('Enter WATCH_CHANGE_WEBHOOK_URL');
}

// OPTIONAL
const sheetValue = SHEET;
const rangeValue = RANGE;

const ss = [Link]();
const sheet = [Link]();

if (sheetValue && sheetValue !== '' && sheetValue !== [Link]()) {


[Link]('No triggering');
return null;
}
if (sheetValue && sheetValue && rangeValue !== '' &&
!isWithinRange_([Link].getA1Notation(), rangeValue)) {
[Link]('No triggering');
return null;
}

var dataRange = [Link]();


var dataArr = [Link]([Link], 1, [Link] -
[Link] + 1, [Link]()).getValues();
var rowValues = [];

[Link](function (row) {
var out = {};
[Link](function (v, i) {
out[i] = v;
});
[Link](out);
});

var payload = {
spreadsheetId: [Link](),
spreadsheetName: [Link](),
sheetId: [Link](),
sheetName: [Link](),
rangeA1Notation: [Link].getA1Notation(),
range: [Link],
oldValue: [Link],
value: [Link],
user: [Link],
rowValues: rowValues
};

var options = {
method: 'post',
contentType: 'application/json',
payload: [Link](payload)
};

var response = [Link](UPDATE_WEBHOOK_URL, options);


[Link](response);

function isWithinRange_(a1Notation, rangeToCheck) {


// arguments = [a1Notation, rangeToCheck]

var input = [Link](arguments, function (e) {


return [Link]();
});
var rangeArgs = /^([A-Z]+)?(\d+)?:([A-Z]+)?(\d+)?$/.exec(input[1]);
var a1NotationArgs = /^([A-Z]+)(\d+)$/.exec(input[0]).map(function (e, i) {
return i == 1 ? (' ' + e).substr(-2) : e * 1;
});
/* If range arguments are missing(like missing end column in "A1:1"), add
arbitrary arguments(like "A1:ZZ1")*/
rangeArgs = [Link](function (e, i) {
return e === undefined ?
i % 2 === 0 ?
i > 2 ?
Infinity : -Infinity
: i > 2 ?
'ZZ' : ' A'
: i % 2 === 0 ?
e * 1 : (' ' + e).substr(-2);
});
[Link](rangeArgs, a1NotationArgs);
return (a1NotationArgs[1] >= rangeArgs[1] &&
a1NotationArgs[1] <= rangeArgs[3] &&
a1NotationArgs[2] >= rangeArgs[2] &&
a1NotationArgs[2] <= rangeArgs[4]);
}

/**
* @return The result of the Integromat scenario execution.
* @customfunction
*/
function INTEGROMAT(input) {

const FUNCTION_WEBHOOK_URL = PERFORM_FUNCTION_WEBHOOK_URL;

if (!FUNCTION_WEBHOOK_URL){
[Link]('Enter PERFORM_FUNCTION_WEBHOOK_URL');
throw new Error('Enter PERFORM_FUNCTION_WEBHOOK_URL');
}

var spreadsheet = [Link]();


var cell = [Link]();

var payload = {
spreadsheetId: [Link](),
spreadsheetName: [Link](),
sheetId: [Link](),
sheetName: [Link](),
cell: cell.getA1Notation(),
col: [Link](),
row: [Link](),
parametersArray: [],
parametersCollection: {}
};

for(var i = 0; i < [Link]; i++) {


[Link](arguments[i]);
[Link]['p' + i] = arguments[i];
}

var options = {
method: 'post',
contentType: 'application/json',
payload: [Link](payload)
};

var response = [Link](FUNCTION_WEBHOOK_URL, options);

return [Link]([Link]()).value;
}

/**
* @return The result of the Make scenario execution.
* @customfunction
*/
function MAKE_FUNCTION(input) {
return INTEGROMAT(input);
}

Common questions

Powered by AI

The 'watchChanges' function is triggered by changes in a specified sheet or range and sends data about the change (e.g., spreadsheet ID, sheet name, range) via a POST request to a webhook URL. The 'INTEGROMAT' function, on the other hand, is a custom function that sends payload information about the active cell and additional parameters to another webhook URL, following different conditional checks. The former is a listener-based trigger, whereas the latter requires direct invocation via sheet functions .

Error handling in 'onFormSubmit(e)' is implemented using a try-catch block. If an error occurs during the payload creation or the HTTP request to send form data, the catch block logs an error message indicating the issue. This helps in diagnosing failures without crashing the entire script, ensuring robustness and debugging efficiency .

Within both scripts, data types are managed through JSON serialization of JavaScript objects. During payload construction, responses and changes are encapsulated in key-value pairs within a JSON object. This structured data format is essential for compatibility with webhooks, enabling easy parsing and further processing by the receiving systems. The standardized data handling ensures that different data types (strings, numbers) are consistently formatted for transmission .

The 'watchChanges' function may struggle with scalability if monitoring entire large spreadsheets due to excessive webhook triggers and payload generation, which can lead to slow performance and quota limits being exceeded. Strategies to mitigate these issues include implementing selective range monitoring, aggregating multiple changes into batch notifications, and using event-based triggers more judiciously to target only critical areas .

The 'isWithinRange_' function checks whether a given cell's A1 notation falls within a specified range. It standardizes the range format and compares this with the cell's position, returning true if the cell is within the range. This functionality is crucial for efficiently triggering events only when specific ranges are affected, reducing unnecessary webhooks and computational load in large spreadsheets .

Webhook URLs in these scripts serve as endpoints for HTTP POST requests. The 'onFormSubmit' and 'watchChanges' functions both send JSON payloads containing relevant data to specified webhook URLs. These URLs represent external services or endpoints where form submissions or sheet changes are handled. They are crucial for integrating Google Apps Script with external automation or workflow systems, facilitating data synchronization or triggering remote processes .

In 'onFormSubmit(e)', the payload is generated by iterating over each form response, extracting the question title and its corresponding answer, and compiling these into a JSON object. In 'watchChanges', the payload is constructed by gathering detailed context about the spreadsheet change, such as range, old/new values, and user details. Each function tailors its payload to fit the information needed by the receiving webhook, ensuring the correct interpretation and processing of events based on the context—be it form submissions or sheet updates .

Hardcoding webhook URLs in script files exposes them to potential security risks as unauthorized access to the script can lead to misuse of the URLs for malicious purposes. This practice could allow hackers to intercept data or initiate unauthorized actions. It is advisable to use environment variables or encrypted vaults instead to protect sensitive endpoint information .

The PropertiesService in 'onFormSubmit(e)' enhances functionality by storing the last processed submission ID, preventing duplicate processing. This could be similarly applied to 'watchChanges' to track processed triggers or maintain state between executions. For instance, it could save recent range modifications or parameters, allowing the function to operate more intelligently and avoid redundant operations .

The function 'onFormSubmit(e)' checks whether the current submission ID matches the last processed submission ID stored in the script's properties. If they match, it returns immediately, avoiding duplicate processing. This is accomplished using properties retrieved and set via the PropertiesService in Google Apps Script .

You might also like