Forum Discussion
ViApril6
Apr 24, 2023Copper Contributor
IF cell A1=A, after 15 minutes, automatically change into B
I need a formula to change value from A to B after 15 minutes of inserting A. The ChatGPT suggested me this formula but it did not work =IF(A1="A",IF(NOW()>=A1+TIME(0,15,0),"B",A1),"")
ViApril6
Apr 25, 2023Copper Contributor
Lately, the AI suggested to run appscript:
function onEdit(e) {
if (e && e.range) {
var sheet = e.range.getSheet();
var columnE = e.range.getColumn() == 5;
var valueA = e.value == "1";
if (sheet.getName() == "DIEUPHOI" && columnE && valueA) {
var range = sheet.getRange(e.range.getRow(), columnE);
var timer = Utilities.formatDate(new Date(), "GMT+7", "HH:mm:ss");
range.setValue("2");
SpreadsheetApp.flush();
ScriptApp.newTrigger("resetCell")
.timeBased()
.after(1 * 60 * 1000);
}
}
}
function resetCell() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DIEUPHOI");
var range = sheet.getRange("E:E").getValues();
range.forEach(function(row, index) {
if (row[0] == "2") {
var timeString = row[0].split("-")[1].trim();
var savedTime = new Date();
savedTime.setHours(timeString.split(":")[0], timeString.split(":")[1], timeString.split(":")[2]);
var currentTime = new Date();
if (currentTime - savedTime > 1 * 60 * 1000) {
sheet.getRange(index + 1, 5).setValue("2");
}
}
});
}
But in the test, it did not work either.
function onEdit(e) {
if (e && e.range) {
var sheet = e.range.getSheet();
var columnE = e.range.getColumn() == 5;
var valueA = e.value == "1";
if (sheet.getName() == "DIEUPHOI" && columnE && valueA) {
var range = sheet.getRange(e.range.getRow(), columnE);
var timer = Utilities.formatDate(new Date(), "GMT+7", "HH:mm:ss");
range.setValue("2");
SpreadsheetApp.flush();
ScriptApp.newTrigger("resetCell")
.timeBased()
.after(1 * 60 * 1000);
}
}
}
function resetCell() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DIEUPHOI");
var range = sheet.getRange("E:E").getValues();
range.forEach(function(row, index) {
if (row[0] == "2") {
var timeString = row[0].split("-")[1].trim();
var savedTime = new Date();
savedTime.setHours(timeString.split(":")[0], timeString.split(":")[1], timeString.split(":")[2]);
var currentTime = new Date();
if (currentTime - savedTime > 1 * 60 * 1000) {
sheet.getRange(index + 1, 5).setValue("2");
}
}
});
}
But in the test, it did not work either.