Hi there,
I have a Google Sheet which collects some data.
From that Google Sheet I am distributing that data to different place through a workflow build in Make.
In order to start the workflow in Make, I am using as a trigger the Google Sheet Watch changes which is activated by this script below added in Apps Scrips
" //For use with the Watch Changes module. Paste the webhook URL from your scenario here:
WATCH_CHANGE_WEBHOOK_URL = ‘URL’;
//OPTIONAL (for use with Watch Changes):
SHEET = ‘’; // Leave blank to avoid multiple triggers
RANGE = ‘’; // Leave blank to avoid multiple triggers
//For use with the Perform a Function module. Paste the webhook URL from your scenario here:
PERFORM_FUNCTION_WEBHOOK_URL = ‘’;
// Define the column index for “Review” (Column AB = 28)
const REVIEW_COLUMN = 28;
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////// DO NOT TOUCH BELOW!!! /////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
function watchChanges(e) {
// Trigger only if the edited cell is in column V and its new value is “Approved”
if (e.range.getColumn() !== REVIEW_COLUMN) {
console.log(“Edit not in Review column. No triggering.”);
return;
}
if (e.value !== “Approved”) {
console.log(“Review value is not ‘Approved’. No triggering.”);
return;
}
// REQUIRED
const UPDATE_WEBHOOK_URL = WATCH_CHANGE_WEBHOOK_URL;
if (!UPDATE_WEBHOOK_URL) {
console.log(‘Enter WATCH_CHANGE_WEBHOOK_URL’);
throw new Error(‘Enter WATCH_CHANGE_WEBHOOK_URL’);
}
// OPTIONAL
const sheetValue = SHEET;
const rangeValue = RANGE;
const ss = SpreadsheetApp.getActive();
const sheet = ss.getActiveSheet();
if (sheetValue && sheetValue !== ‘’ && sheetValue !== sheet.getName()) {
console.log(‘No triggering’);
return null;
}
if (sheetValue && sheetValue !== ‘’ && rangeValue !== ‘’ && !isWithinRange_(e.range.getA1Notation(), rangeValue)) {
console.log(‘No triggering’);
return null;
}
var dataRange = sheet.getDataRange();
var dataArr = sheet.getRange(e.range.rowStart, 1, e.range.rowEnd - e.range.rowStart + 1, dataRange.getLastColumn()).getValues();
var rowValues = [];
dataArr.forEach(function (row) {
var out = {};
row.forEach(function (v, i) {
out[i] = v;
});
rowValues.push(out);
});
var payload = {
spreadsheetId: e.source.getId(),
spreadsheetName: e.source.getName(),
sheetId: e.source.getSheetId(),
sheetName: e.source.getSheetName(),
rangeA1Notation: e.range.getA1Notation(),
range: e.range,
oldValue: e.oldValue,
value: e.value,
user: e.user,
rowValues: rowValues
};
var options = {
method: ‘post’,
contentType: ‘application/json’,
payload: JSON.stringify(payload)
};
var response = UrlFetchApp.fetch(UPDATE_WEBHOOK_URL, options);
console.log(response);
}
function isWithinRange_(a1Notation, rangeToCheck) {
// arguments = [a1Notation, rangeToCheck]
var input = Array.prototype.map.call(arguments, function (e) {
return e.toUpperCase();
});
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 = rangeArgs.map(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);
});
console.log(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) {
console.log(‘Enter PERFORM_FUNCTION_WEBHOOK_URL’);
throw new Error(‘Enter PERFORM_FUNCTION_WEBHOOK_URL’);
}
var spreadsheet = SpreadsheetApp.getActive();
var cell = spreadsheet.getActiveCell();
var payload = {
spreadsheetId: spreadsheet.getId(),
spreadsheetName: spreadsheet.getName(),
sheetId: spreadsheet.getSheetId(),
sheetName: spreadsheet.getSheetName(),
cell: cell.getA1Notation(),
col: cell.getColumn(),
row: cell.getRow(),
parametersArray: [],
parametersCollection: {}
};
for (var i = 0; i < arguments.length; i++) {
payload.parametersArray.push(arguments[i]);
payload.parametersCollection[‘p’ + i] = arguments[i];
}
var options = {
method: ‘post’,
contentType: ‘application/json’,
payload: JSON.stringify(payload)
};
var response = UrlFetchApp.fetch(FUNCTION_WEBHOOK_URL, options);
return JSON.parse(response.getContentText()).value;
}
/**
** @return The result of the Make scenario execution.*
** @customfunction*
*/
function MAKE_FUNCTION(input) {
return INTEGROMAT(input);
}