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

[Prem Patel]

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]

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.