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") {

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.

| improve this answer | |
  • Ungh, I tried it and I got this error instead: Exception: The parameters (number) don't match the method signature for SpreadsheetApp.Range.getCell. (line 9, file "ClearFields") – Melanie Hoyle Jun 11 at 21:42
  • @MelanieHoyle Please see getCell – Rubén Jun 11 at 22:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.