Creating a script or formulas to trigger webhook on countdown completion What is the best choice so i can achieve this in google sheets apps script

41 views
Skip to first unread message

Prem Patel

unread,
Dec 3, 2023, 7:36:20 AM12/3/23
to Google Apps Script Community
What is the best choice so i can achieve this in google sheets apps script : what i want is when i add any values in column A, B,C, I want to start 3 hour countdown in column D and another countdown in column E, when countdown of column D completed then start countdown in column E, countdown in column E will be for 24 hours. When countdown is completed in column D set value in that cell with "CD3Completed" and when column E gets completed set value in that particular cell with "CD24Completed" (column D countdown starts for 3 hours and after 3 hours value will be changed to CD3Completed. When column D has "CD3Completed" Countdown in E will start for 24 hours. and after 24 hours column E value for that particular cell in which countdown was started for 24 hour will be set to CD24Completed) Now add one more step in the script that when any of this 2 of countdown is completed and value is set to CD3Completed or CD24Completed send that row number in which this countdown is completed, and data of columns which has values in it like column A to Column J to webhook URL What i need is when i add value before . Make changes to the script accordingly as you wish to achieve this combination. Also give suggestions to achieve this using any other methods.
 
Thanks

Fabrice Faucheux

unread,
Dec 24, 2023, 4:54:11 AM12/24/23
to Google Apps Script Community
Hello,

To achieve the described functionality in Google Sheets using Google Apps Script, you can follow these steps. I'll provide you with a sample script and guide you on how to set it up. Additionally, you can use a Google Apps Script Trigger to make sure the script runs at specific intervals.

function onEdit(e) {
var sheet = e.source.getSheetByName("YourSheetName"); // Replace with your sheet name
var range = e.range;
var row = range.getRow();
var column = range.getColumn();

if (column >= 1 && column <= 3) { // Assuming columns A, B, and C

// Check if the countdown in column D has already started
var countdownDValue = sheet.getRange(row, 4).getValue();
if (countdownDValue !== "CD3Completed") {
startCountdown(sheet, row, 4, 3 * 60 * 60); // 3 hours in seconds
}

// Check if the countdown in column E has already started
var countdownEValue = sheet.getRange(row, 5).getValue();
if (countdownEValue !== "CD24Completed" && countdownDValue === "CD3Completed") {
startCountdown(sheet, row, 5, 24 * 60 * 60); // 24 hours in seconds
}
}
}

function startCountdown(sheet, row, column, seconds) {
var cell = sheet.getRange(row, column);
var endTime = new Date().getTime() + seconds * 1000;

// Set the formula to calculate countdown end time
cell.setFormula('=TEXT((TIME(' + endTime.getHours() + ',' + endTime.getMinutes() + ',' + endTime.getSeconds() + ')-NOW()),"hh:mm:ss")');

// Set up a trigger to check the countdown status periodically
ScriptApp.newTrigger('checkCountdown')
.timeBased()
.everyMinutes(1) // Check every minute
.create();
}

function checkCountdown() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("YourSheetName"); // Replace with your sheet name
var data = sheet.getDataRange().getValues();

for (var i = 0; i < data.length; i++) {
var countdownDValue = data[i][3];
var countdownEValue = data[i][4];

if (countdownDValue === "CD3Completed") {
sheet.getRange(i + 1, 4).setValue("CD3Completed");
sendToWebhook(i + 1, data[i]);
}

if (countdownEValue === "CD24Completed") {
sheet.getRange(i + 1, 5).setValue("CD24Completed");
sendToWebhook(i + 1, data[i]);
}
}
}

function sendToWebhook(row, rowData) {
var webhookUrl = "YOUR_WEBHOOK_URL"; // Replace with your webhook URL
var payload = {
row: row,
data: rowData
};

// Use UrlFetchApp to send data to webhook
UrlFetchApp.fetch(webhookUrl, {
method: "post",
contentType: "application/json",
payload: JSON.stringify(payload)
});
}


Replace "YourSheetName" with the actual name of your sheet, and "YOUR_WEBHOOK_URL" with the URL of your webhook. Save the script, and then go to the Apps Script dashboard, set up the triggers by clicking on the clock icon, and add a new trigger for the checkCountdown function to run every minute.

Note: This script assumes that the Google Apps Script trigger limitations allow for your use case. If your use case involves very frequent edits, you may need to consider other approaches such as using a time-driven trigger instead of an edit trigger or using external services for more complex scenarios.
Reply all
Reply to author
Forward
0 new messages