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

Snail-Horn

I am really stuck to convert this idea to a script or a formula.

The problem scenario

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: enter image description here

Also thanks for the right formatting! I definitely need lessons on them

Iamblichus

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.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

How can I write the data in Firestore to Google Sheet with app script?

How to make a loop script for Google sheet?

Google script - Hide/Unhide google sheet based on checkbox on another sheet

How can i pass specific values from one sheet to another sheet based on specific criterias

How can I assign a Google Sheet script to only one sheet?

How I can joint 2 cell values in Google sheet with ARRAYFORMULA

How can I copy a range to a different sheet based on checkbox value, then delete on the new sheet, while keeping both check boxes up to date

Sorting google-sheet values by cells color directly in Apps Script

I want change cell values from some sheet in many sheet by google spread sheet script

How to drag a formula in google sheet?

How can I tell google script where to print in sheet?

How to change values in excel acording to different sheet

How can I make this formula automatically update sheet reference?

How can I make a Google Sheet script run reliably on Samsung tablets?

Google Sheet Script onEdit(e) if H2=Yes then make Column I (Checkbox) pressable

How can I change sheet direction from apps script

How does Google App Script to set a formula to Google sheet cell

Google script change row color based on data change, sheet sorted

How can I move a range from one Google sheet to the next blank row in a different sheet/tab

Using Google Apps Script, how can I replace text in a Google Sheets template to make a new Sheet?

How to iterate cells in a Google sheet formula?

How To set the Background Color of Values From One Sheet To the Cells with matching Values in Another Sheet with Google Apps Script? EDIT Section add

How can I rewrite the Google Sheet formula to make it autofill without hand dragging?

Sheet formula to repeat the cells

How can I make correct formula for Age diff in Google sheet?

In a Google Sheet tab created based on a sheet row, how do I reference a specific row in a formula?

Repeat a formula in google sheet within a single cell using different values

How can I write a macro to hide columns in a sheet based on a value in a different sheet in the same workbook

How to conditionally format cells in one sheet based on values in another sheet in google sheets