Hello guys,
I have a workflow which is triggered if there is a change in a row in the column V.
The trigger and the workflow worked perfectly until today.
The workflow or the Google Sheet Apps script have not been changed at all but for some reason since today, whenever a change is made in a row in the column V of the Google Sheet the workflow is triggered 2 times consecutively.
Does any of you have any idea why this is happening?
I am also attaching the Google Sheet Apps script below for more context
//For use with the Watch Changes module. Paste the webhook URL from your scenario here:
WATCH_CHANGE_WEBHOOK_URL = 'https://hook.eu2.make.com/tqvywlld1nkfxultc0rx4gs2diopuvgj';
//OPTIONAL (for use with Watch Changes):
SHEET = 'Leads approval 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 = '';
// Define the column index for "Review" (Column V = 22)
const REVIEW_COLUMN = 22;
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////// DO NOT TOUCH BELOW!!! ////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
function watchChanges(e) {
// Trigger only if the edited cell is in column S 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);
}
