Google-sheets – Clear range of cells based on another

google sheetsgoogle-apps-script

I'm having trouble trying to write a script that will clear a range of cells based on another. For example I want to clear out only the cells C:H on each row if cell K on that row contains the word "Harvestable". I tried to use another example and adjust it as best to my knowledge (which isn't that good) but it's not clearing the cells when I either run it from the script editor or click the button assigned to it.

function onClick() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Gardens");

  var seeds = sheet.getRange('C8:H68'); 
  var status = sheet.getRange('K8:K68').getValue(); 

  for (var i=0; i<status.length; i++) {  
    if (status[i] == "Harvestable") {
    seeds.getCell(i+1).clearContent();
    }
  }
}

Best Answer

This line will assign the value of K8 to status

var status = sheet.getRange('K8:K68').getValue();

In order to assign the values from K8:K68 instead of getValue() use getValues()

Also replace

status[i] == "Harvestable"

status[i][0] === "Harvestable"

The above because getValues() returns an Array of Arrays (a bidimensional array or matrix) and it's better to use === to prevent confusions caused by automatic object data type coercion.

Related Solutions

Google-sheets – Clear range of cells

You can clear only the cells with orange background (color code #ff9900) by getting the background colors of all cells with .getBackgrounds() and then checking them for being orange.

function clearOrange() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Blad1');
  var range = sheet.getDataRange();
  var bgColors = range.getBackgrounds();
  for (var i=0; i<bgColors.length; i++) {
    for (var j=0; j<bgColors[i].length; j++) {
      if (bgColors[i][j] === '#ff9900') {
        range.getCell(i+1,j+1).clearContent();
      }
    }
  }  
}

I knew the color code for orange used in Google sheets because I first ran this code with the "if" statement replaced by Logger.log(bgColors[i][j]);

The reason for (i+1,j+1) is that indices in JavaScript are 0-based while row and column numbers are 1-based.

Hypothetically speaking, if you didn't have orange background as the criterion for clearing, you'd have to specify the ranges explicitly; the script has to know what to clear. You could still shorten the script by putting the ranges in an array, as below.

function clear1() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Blad1');
  var rangesToClear = ['D5:D12', 'D15:D20', 'D22:D30'];
  for (var i=0; i<rangesToClear.length; i++) { 
    sheet.getRange(rangesToClear[i]).clearContent();
  }
}

Google-sheets – See if a cell contains a string not from a list of strings in another column

You can do this by applying to range A1:A the conditional formatting with custom formula

=counta(unique(transpose(split(A1&", "&JOIN(", ",B$1:B),", "))))>counta(unique(B$1:B))

Explanation.

To the right of the inequality sign, counta(unique(B$1:B)) counts the number of distinct words in column B.
On the left, A1&", "&JOIN(", ",B$1:B) concatenates A1 with the content of column B, comma-and-space separated.
Then split(...,", ") separates all of this into a row of words
Then counta(unique(transpose(...))) counts the number of distinct words among these.
Formatting is applied if the second count is greater than the first.

Best Answer

Related Solutions

Google-sheets – Clear range of cells

Google-sheets – See if a cell contains a string not from a list of strings in another column

Related Topic