Google Script - clear range except one certain cell value

Question

i have a simple function for cleaning the content of all cells in two certain ranges in one google-sheet.

function clearRange() {
      var activeSheet = SpreadsheetApp.openById("idofmytable").getSheetByName("name");
      activeSheet.getRange('B9:H35').clearContent();
      activeSheet.getRange('K9:Q35').clearContent();
    }

i would like to update the function and let the script clean all cells except if a cell has a value "10". if the cell has a value of 10, it should not be cleared.

how do i do this?

Answer 1

• You want to clear the content from all cells which are not the value of 10 in the sheet.
• You want to achieve this using Google Apps Script.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

In this answer, I used the following flow.

1. Retrieve all values from the data range in the sheet.
2. Retrieve the cell range which have no value of 10 as the a1Notation.
3. Clear the content using getRangeList.

Modified script:

function clearRange() {
  var activeSheet = SpreadsheetApp.openById("idofmytable").getSheetByName("name");

  var columnToLetter = function(column) { // This is from https://stackoverflow.com/a/21231012/7108653
    var temp, letter = '';
    while (column > 0) {
      temp = (column - 1) % 26;
      letter = String.fromCharCode(temp + 65) + letter;
      column = (column - temp - 1) / 26;
    }
    return letter;
  };

  // Retrieve all values.
  var values = activeSheet.getDataRange().getValues();

  // Retrieve the cell range as a1Notation, when the cell value is not 10.
  var ranges = values.reduce(function(ar, row, i) {
    row.forEach(function(col, j) {if (col != 10) ar.push(columnToLetter(j + 1) + (i + 1))});
    return ar;
  }, []);

  // Clear the content of the retrieved ranges.
  activeSheet.getRangeList(ranges).clearContent();
}

References:

• Class RangeList
• clearContent() of Class RangeList

If I misunderstood your question and this was not the direction you want, I apologize.

Added:

• You want to clear the content from the cells in the ranges of "B9:H35 & K9:Q35" which are not the value of 10 in the sheet.

Sample script:

function clearRange() {
  var activeSheet = SpreadsheetApp.openById("idofmytable").getSheetByName("name");

  var columnToLetter = function(column) { // This is from https://stackoverflow.com/a/21231012/7108653
    var temp, letter = '';
    while (column > 0) {
      temp = (column - 1) % 26;
      letter = String.fromCharCode(temp + 65) + letter;
      column = (column - temp - 1) / 26;
    }
    return letter;
  };

  // Retrieve values from B9:H35 and K9:Q35.
  var value1 = activeSheet.getRange('B9:H35').getValues();
  var value2 = activeSheet.getRange('K9:Q35').getValues();

  // Retrieve the cell range as a1Notation, when the cell value is not 10.
  var colOffset1 = 2;
  var colOffset2 = 11;
  var rowOffset = 9;
  var ranges1 = value1.reduce(function(ar, row, i) {
    row.forEach(function(col, j) {
    if (col != 10) ar.push(columnToLetter(j + colOffset1) + (i + rowOffset))});
    return ar;
  }, []);
  var ranges2 = value2.reduce(function(ar, row, i) {
    row.forEach(function(col, j) {
    if (col != 10) ar.push(columnToLetter(j + colOffset2) + (i + rowOffset))});
    return ar;
  }, []);

  // Clear the content of the retrieved ranges.
  activeSheet.getRangeList(ranges1).clearContent();
  activeSheet.getRangeList(ranges2).clearContent();
}