I've managed to automate a lot of my companies sheets document, but this one has been nagging me for a LONG time.

I'm trying to automate a script that scans this given range F11:F20 and if value == "" then I clear the next row to the left. This below works only if all of the fields are empty(and it takes a while, I found this online so the loop conditions are probably pretty messed up), I've tried to make a for loop, while loops, I've tried probably 15 things outside of hard coding it with if and if else statements (I want to do it proper) but I just don't know how to navigate certain cell downwards to scan and delete in general.

Image of the range F11:F20

   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var numCol = SpreadsheetApp.getActiveSheet().getLastColumn();
  
   var range, substring1;

   //loop
   for (var i = 1; i <= numCol; i  ) {
      //Here is the magic        
      range = ss.getRange("F11:F20");
      substring1 = range.getValue(); 
      if ( substring1 === '') {
        range.offset(0, -1).clearContent(); 

      }
   }
}

CodePudding user response：

Modification points:

• When getRange and getValue are used in a loop, the process cost will be high. Ref
• In your situation, I would like to propose the following flow.
  1. Retrieve values from "F11:F20".
  2. Create the range list for clearing cells.
  3. Clear contents using the range list.

When this flow is reflected to a script, it becomes as follows.

Modified script:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = sheet.getRange("F11:F20").getValues();
  var ranges = values.reduce((ar, [f], i) => {
    if (f == "") ar.push("E"   (i   11));
    return ar;
  }, []);
  sheet.getRangeList(ranges).clearContent();
}

• In this script, the active sheet is used. When you want to use the specific sheet, please modify var sheet = SpreadsheetApp.getActiveSheet(); to var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("###sheetname###");.

References:

• reduce()
• getRangeList(a1Notations)

CodePudding user response：

I don't see any iteration here... where are you iterating on i? Looks like it's looking at the same exact cells each time through the loop.

This would only return the value of the first cell in your range, which if you're doing it right would be the value in F11.

I think you want something like this (not sure if range is zero based or 1-based tho)

for (var i = 1; i <= numCol; i  ) {
  range = ss.getRange(10   i,6);
  substring1 = range.getValue(); 
  if ( substring1 === '') {
    range.offset(1, -1).clearContent(); 
  }
}

if range is zero based then get range should be (9 i, 5)