Example Table:

So, A2 corresponds with D2, B2 corresponds with E2, and C2 corresponds with F2

I'd like a script that works as follows:

If A2 is ever cleared (and becomes an empty cell), I want D2 to automatically become FALSE

If B2 is ever cleared (and becomes an empty cell), I want E2 to automatically become FALSE

If C2 is ever cleared (and becomes an empty cell), I want F2 to automatically become FALSE

...and so on

Notes:

• The actual sheet has 100's of "Exercise" cells and corresponding "Completed" cells. All of the "Exercise" cells are next to each other and all of the "Completed" cells are next to each other (like in the table above). If there are, for example, exactly 125 "Exercise" cells, there will always be exactly 125 corresponding "Completed" cells.

• The "Completed" TRUE/FALSE cells all start as FALSE (and are intentionally there to be changed by me), in the actual cell they are checkboxes (which represent TRUE/Checked and FALSE/Unchecked. If, for instance, on Monday, I add 3 exercises that I plan to do in the first 3 columns A2:C2, as I complete all three, I would then manually check (mark TRUE) their corresponding "Completed" cells. Then what I want is for when I delete the exercise names from the "Exercise" cells, I want the corresponding "Completed" cells to return to FALSE. So lets say I only delete the 3rd "Exercise" cell (C2), then only F2 should return to FALSE. If I delete all 3 "Exercise" cells, then all 3 "Completed" cells should return to FALSE.

• FYI - The Google sheet acts as a backend to a front end app. So the checking/unchecking of "Completed" cells and adding/removing of "Exercise" cells is all happening inside of an app.

• I'm aware that in the end I am going to want a trigger that executes the script when any "Exercise" cell has been cleared, etc... I do want that, but the code below is where I'm currently at, where I'm just trying to get the script/code to work so that when simply running it from within Apps Script, it would set the correct corresponding values to whatever "mock" setup that I set the cells to. In the end, including a trigger is the final goal.

Here is the code I wrote so far (which is not working, I have never really worked with Google Apps Script before, so this is all new to me):

function resetCompletedExerciseOnClear() {
  var sheetName = 'Test';
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName(sheetName);

  var row = 2;

  var exerciseNamesArray = sheet.getRange('A2:C2').getValues();

  var startingColumnExerciseCompleted = 4;
  var exerciseCompletedArray = sheet.getRange('D2:F2').getValues();

  for (var i = 0; i < exerciseNamesArray.length; i  ) {
    for (var j = 0; j < exerciseCompletedArray.length; j  ) {
      if (exerciseNamesArray[i] == "") {
        exerciseCompletedArray[j] == "FALSE";
      } else {
        exerciseCompletedArray[j] == "TRUE";
      }
    }
  }
  sheet.getRange(row, startingColumnExerciseCompleted, 1, exerciseCompletedArray.length).setValues([exerciseCompletedArray]);

As I mentioned above, the script currently does not have any triggers attached (in Apps Script) to look for any changes to the "Exercise" cells and then automatically run the script when a change occurs (But that is what I want happening in the end). At this point I'm just manually running the script (in Apps Script), which gets the sheet reference, creates arrays for both the "Exercise" range and "Completed" range. Starts a nested loop that looks for an empty cell at each index in the "Exercise" names array, and if it finds any, then it changes the "Completed" exercise values to "FALSE" in the corresponding array indices, the else statement may be redundant, then finally it pushes that new array to the sheet.

• or at least that what I was hoping the code would do :)

CodePudding user response：

Try this:

function onEdit(e) {
  const sh = e.range.getSheet();
  if(sh.getName() == "Sheet0" && e.range.columnStart < 4 && e.range.rowStart > 1 && !e.value) {
    e.range.offset(0,3).setValue("FALSE");
  }
}

Probably need to change sheet name

CodePudding user response：

A user on Reddit was able to provide a working script that when paired with an onChange trigger, did exactly what I was looking for.

Here is the code below:

function resetCompletedExerciseOnClear() {
  var sheetName = 'Test';
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName(sheetName);
  var numExercises = 3;
  var exerciseNamesArray = sheet.getRange(2,1,1,numExercises).getValues()[0];
  var exerciseCompletedArray = sheet.getRange(2,numExercises   1, 1, numExercises).getValues()[0];

  if (!exerciseNamesArray.includes("")) {
    return; // for increased efficiency, will immediately return if no blanks are found
  }

  exerciseNamesArray.forEach(function(exercise, idx) {
    if (exercise == "") {
      exerciseCompletedArray[idx] = false;
    }
  })
  
  sheet.getRange(2,numExercises   1, 1, numExercises).setValues([exerciseCompletedArray]);
}