-1

I have a sheet that have formulas on column B at every other Row starting from B3, so it's B3, B5 and up to B101, I have the following script to clear the rest of the sheet except for column B, How can I add to the function to clear every other row from B3 to B101?

function ClearCells() {
 var sheet = SpreadsheetApp.getActive().getSheetByName('sheetName');

 sheet.getRange('C3:T102').clearContent();

 sheet.getRange('A3:A102').clearContent();
 }
New contributor
Frontline medical is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
4
  • Welcome to Web Applications. Please show what you tried to adapt the script to your goal.
    – Rubén
    Jan 12 at 23:58
  • I have a button connected to the script to clear the sheet but keep the formulas in the odd rows of column B starting with B3 to B101 Jan 13 at 15:48
  • Hi and welcome to WebApps. Just to be clear, is there any date/formula on the even rows in Column B? You said that you had a script that keep[s] the formulas in the odd rows of column B starting with B3 to B101. Why not just delete that part of the script that keeps those formulas?
    – Tedinoz
    Jan 14 at 9:57
  • Yes, Even cells has formulas,from B4 to B104, I don't have a script to keep those formulas from being cleared, what I need to do is clear only the odd rows and keep the formulas on the even cells, So I need to Clear B3, don't clear B4, clear B5, don't clear B6 and so on, every other row till B104 Jan 14 at 15:49
0

Try something like this:

function clearoddrows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()

  var sheetname = "Sheet1"
  var sheet = ss.getSheetByName(sheetname)

  // get the range B3 to B101
  var startrow = 3
  var endrow = 101
  var column = 2

  // get the range
  var range = sheet.getRange(startrow, 2, (endrow-startrow+1))
  // Logger.log("DEBUG: range = "+range.getA1Notation())

  // get the values
  var values = range.getValues()

  // create a loop, increment by 2 for every other row
  for (var i=0;i<endrow-startrow+1;i=i+2){
    //Logger.log("DEBUG: i:"+i+" value "+values[i][0])

    // clear content
    sheet.getRange(startrow+i,2).clearContent()

  }

}

The key is that the loop value increments by 2, so it will affect every other row.

Note: I think that this is an inefficient piece of code. It goes against the grain to perform getRange() and ClearContent() inside the loop. But it runs quickly and will do until someone dreams up something better ;)

FWIW: I did consider capturing the column data in an array, and then updating the value inside the loop, then setValues() after the loop. However, this has the effect of converting formula to values which is unacceptable.

Your Answer

Frontline medical is a new contributor. Be nice, and check out our Code of Conduct.

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.