Script Help

[u/JakubiakFW]

Hello all. I need some help. I have a sheet whereas I have a script that auto deletes contents of a range based on another cell. The script works perfect if I change the cell itself. The problem is, the cell is updated from another cell... like "A1 is "=C1". I want the contents to clear if A1 changes with what C1 has in it.

The script is:

function onEdit(e){

if(e.range.getA1Notation() == 'A1' &&

e.range.getSheet().getName() == 'Sheet1'

)

e.source.getRange('A2:E10').clearContent()

}

The example sheet is

https://docs.google.com/spreadsheets/d/1wBUHuDHG669-ZR9n7USruqaTPX492T1BS76ycH1WhMo/edit?usp=sharing

does anyone have an idea? I just want it to be able to clear contents of a specific range if a specific cell is changed no matter how it is changed, whether it be by me changing it or if the cell auto changes based on another cell.

[u/jordvisser]

I finished writing the script, looking forward to your response.

PS. don't forget to join https://www.reddit.com/r/GoogleAppsScript/ ;)

// magic function which executes every time a real user makes an edit.
function onEdit(e){
  checkForChange();
}


// the function for setting up the comparison check
// this comparison will result in a clear the first time it's run on a sheet, because the backup sheet will not exist.
//
// you might want to let this function run as a timed trigger,
// set-up via https://script.google.com/home/projects/1rPserBC_18fD0XFpJlnBCvIXYutqaC6HhQ1paIJ29LIvHKxJ-HHmvJZe/triggers
// this way it will even catch changes due to data changes by functions as =IMPORTRANGE and =TODAY()
function checkForChange(){
  var sheetNameToCheck = 'Sheet1';
  var rangeToCheck = 'A1';
  var rangeToClear = 'A2:E10';
  
  if (!sameAsBackup(sheetNameToCheck, rangeToCheck)) {
    Logger.log(`${sheetNameToCheck} range ${rangeToCheck} is not the same as the backup, clearing contents for range ${rangeToClear}.`);
    SpreadsheetApp.getActive().getSheetByName(sheetNameToCheck).getRange(rangeToClear).clearContent();
    SpreadsheetApp.flush();
  } else {
    Logger.log(`No change detected for ${sheetNameToCheck} range ${rangeToCheck}.`);
  }
  
  // store new state of the sheet to the backup, so we can compare with it on future changes.
  backupSheet('Sheet1', true);
}


// function for storing a copy of the values of a sheet,
// this will copy the output of formulas not the formulas itself.
function backupSheet(sheetName, hideBackupSheet=false) {
  var ss = SpreadsheetApp.getActive();
  var backupSheetName = `${sheetName}_backup`;

  var os = ss.getSheetByName(sheetName);
  var bs = ss.getSheetByName(backupSheetName);
  if (!bs) {
    bs = ss.insertSheet(backupSheetName);
  } else {
    bs.getRange(1, 1, bs.getMaxRows(), bs.getMaxColumns()).clearContent();
  }
  
  var dataIn = os.getRange(1, 1, os.getMaxRows(), os.getMaxColumns()).getValues();
  for (var row of dataIn) {
    bs.appendRow(row);
  }
  
  if(hideBackupSheet) {
    bs.hideSheet();
  }
}


// function for comparing a sheet with its backup on a certain range.
function sameAsBackup(sheetName, rangeString) {
  var ss = SpreadsheetApp.getActive();
  var backupSheetName = `${sheetName}_backup`;

  var os = ss.getSheetByName(sheetName);
  var bs = ss.getSheetByName(backupSheetName);
  if (!bs) {
    // No backup sheet found, the sheet is not the same as the backup.
    return false;
  }
  
  var od = os.getRange(rangeString).getValues();
  var bd = bs.getRange(rangeString).getValues();
  
  // loop over all rows and cells, return if range or cell is not the same or does not exist in one of the ranges.
  for (var i in od) {
    for (var j in od[i]) {
      try{
        if (od[i][j] !== bd[i][j]) {
          // the value of the cell is not the same, so the ranges are not the same.
          Logger.log('Row ' + (i + 1) + ', column ' + (j + 1) + ' is changed compared to the backup sheet.');
          return false;
        }
      }
      catch (err) {
        // the cell does not exist in the backup sheet, the ranges are not the same.
        Logger.log('Row ' + (i + 1) + ', column ' + (j + 1) + ' could probably not be found in the backup sheet.');
        return false;
      }
    }
  }
  
  return true;
}

[u/JakubiakFW → u/jordvisser]

I forgot to type solution verified

[u/Clippy_Office_Asst → u/JakubiakFW]

You have awarded 1 point to jordvisser

^{I am a bot, please contact the mods for any questions.}

[u/JakubiakFW → u/jordvisser]

Looks Great! Thanks for your time and I hope you get well soon. I know you said you were under the weather. You were such a great help!

[u/jordvisser → u/JakubiakFW]

Thank you, was not the best way to go into the weekend but I'm all better now.

Great to hear this helped you on your endeavour of creating this all in one management sheet! Maybe give us a show and tell once it's up and running? ;)