I am really stuck to convert this idea to a script or a formula.
The task is sequential A then B and then C. I have this google sheet that has a column with only checkboxes. I want to click the checkbox when a payment is done. And then the sequence of things need to take place.
Now there are formulae in some of the cells:
A. Pay Count column (contains how many times a payment is done) has this formula:
=IF(ISBLANK(PAIDON),,IF(RENEWON="",1,ROUNDUP(DAYS(RENEWON,PAIDON)/period)))
B. RENEWON column (calculates the next renewal date) has this formula:
=IF(ISBLANK(PAIDON),,IF(OR(SUBSCRIPTION="LifeTime",SUBSCRIPTION="OneTime"),,DATE(YEAR(L2),MONTH(L2)+IFS(SUBSCRIPTION="Yearly",12,SUBSCRIPTION="2Yearly",24,SUBSCRIPTION="3Yearly",36,SUBSCRIPTION="4Yearly",48,SUBSCRIPTION="5Yearly",60, SUBSCRIPTION="Monthly",1),DAY(L2)-1)))
You can understand that I am kind of a newbie here. So please do ask me for any information I missed here.
Need suggestions of how to convert the whole idea to a script or formula.
Any idea/guidance is helpful to me.
UPDATE: Additional info: My ranges are given in here for further help:
Also thanks for the right formatting! I definitely need lessons on them
Every time a checkbox is checked, you want to do the following:
Pay Count
(+1).Date Paid
with current date.Renewal Due on
date based on the currently existing formula.Also, you want to re-enable the checkbox when the renewable date is less than 20 days from now.
createDailyTrigger
function below. Once the trigger is installed, the function enableCheckboxes
(check code sample below) would run daily and check if the renewable date is less than 20 days from now (and insert the checkbox if that's the case).function onEdit(e) {
const range = e.range;
const column = range.getColumn();
const row = range.getRow();
const value = e.value;
if (column === 15 && row > 1 && value == "TRUE") {
const sheet = e.source.getActiveSheet();
const countCell = sheet.getRange(row, 14);
countCell.setValue(countCell.getValue() + 1);
const now = new Date();
sheet.getRange(row, 12).setValue(now);
SpreadsheetApp.flush(); // Force update: pay date and renewable date
const renewalDate = sheet.getRange(row, 13).getValue();
// Remove checkbox if renewal date is more than 20 days from now
if (!isPaymentTime(renewalDate)) {
sheet.getRange(row, 15).removeCheckboxes();
}
}
}
function isPaymentTime(date) { // Check if renewal date is less than 20 days from now
const twentyDays = 1000 * 3600 * 24 * 20; // 20 days in milliseconds
const now = new Date();
if (date instanceof Date) return date.getTime() - now.getTime() < twentyDays;
else return false;
}
function enableCheckboxes() {
const sheet = SpreadsheetApp.getActive().getSheetByName("PODexpenses");
const firstRow = 2;
const renewalColumn = 13;
const numRows = sheet.getLastRow() - firstRow + 1;
const renewalDates = sheet.getRange(firstRow, renewalColumn, numRows).getValues().flat();
renewalDates.forEach((renewalDate, i) => {
if (isPaymentTime(renewalDate)) { // Check if less than 20 days
sheet.getRange(i + firstRow, 15).insertCheckboxes(); // Insert checkbox
}
})
}
function createDailyTrigger() {
ScriptApp.newTrigger("enableCheckboxes")
.timeBased()
.everyDays(1)
.create();
}
onEdit
function in your current script. Please integrate this on the same function. There can only be one onEdit
.Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments