How can I make a 'google sheet script' or formula to change values in different cells based on a checkbox in the sheet

Question

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.

1. Pay count has to be increased by 1.
2. Renewal date has to be reset to the new date.
3. The Checkbox should go dimmed(unclickable) after the previous operations are done and remain there until there are only about 20 days left when it should become active(clickable) again.

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

Answer 1

you can get checkboxes column values first using :

// This function gets the full column Range like doing 'A1:A9999' in excel
// @param {String} column The column name to get ('A', 'G', etc)
// @param {Number} startIndex The row number to start from (1, 5, 15)
// @return {Range} The "Range" object containing the full column: https://developers.google.com/apps-script/class_range
function getFullColumn(column, startIndex){
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
 // sheet.setActiveSheet(sheet.getSheetByName('sheet_name')); //use this if you have different sheets
  sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  return sheet.getRange(column+startIndex+':'+column+lastRow);
}

//make a call to the function to get all cells' values.
var checkboxesValues = getFullColumn('O', 1).getValues();

Now you have all the values, create a simple array and make for-loop on checkboxesValues length , where you check inside it if the value of the checkbox is TRUE or FALSE (checked - unchecked), and add it's index tp the newly created array.

example

var arr =[]
for (var i = 0; i < checkboxesValues.length; i++) {
    if(checkboxesValues[i][0] === true){
      
      arr.push(i +1); //adding row index (+1 because index start from zero in loop)
}

After that, you can make a new loop on the new array "arr" where it contains only true checkboxes values, and change values as you want.

So you go like:

for (var i = 0; i < arr.length; i++) {
    //Step A (Pay count)
    var payCount = SpreadsheetApp.getActiveSheet().getRange('N'+arr[i]).getValue()
    payCount ++
    SpreadsheetApp.getActiveSheet().getRange('N'+arr[i]).setValue(payCount);
    //step B should go here
    //Finally Step C
    SpreadsheetApp.getActiveSheet().getRange('O'+arr[i]).setValue(false); // this will uncheck it
}

I think checkboxes can't be dimmed or disabled, they are only allowed to be checked or unchecked.

Step B, I don't really get it.

I hope this helps you by any means, and that I understood your question correctly.

Answer 2

Issue:

Every time a checkbox is checked, you want to do the following:

• Update the Pay Count (+1).
• Update the Date Paid with current date.
• Update Renewal Due on date based on the currently existing formula.
• If the difference between the current date and the renewal date is more than 20 days, disable the corresponding checkbox.

Also, you want to re-enable the checkbox when the renewable date is less than 20 days from now.

Solution:

• There is no option for disabling checkboxes, but you just can remove them with removeCheckboxes().
• In order to track when a checkbox is checked, I'd suggest you to use an onEdit trigger. This should (1) check if a checkbox was checked and, if that's the case (2) update the dates and (3) remove the corresponding checkbox if there're more than 20 days remaining. Check the code sample below for an example of how this could be done.
• In order to enable the checkboxes again when the renewal date approaches (or to insert them again, which you can do with insertCheckboxes()), I'd suggest you to create a time-driven trigger which will periodically check the dates, and create the corresponding checkboxes.
• I think, in this case, checking this once a day could be an appropriate periodicity. So you could use everyDays(n). This trigger can either be installed manually, or programmatically via executing the 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).

Code sample:

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();
}

Note:

• I saw there is another onEdit function in your current script. Please integrate this on the same function. There can only be one onEdit.