Row count off by one in google spreadsheet

问题:

I want to delete empty rows. Row 885 is a non-empty row and should be retained. However, my code says (and shows in the msgbox) that 885 is empty and should be deleted. When it gets to i=884, it prints what is really in row 885 and says it will NOT be deleted. So, I'm confused. It appears to be acting upon the data properly, yet it's reporting the row number wrong. I suspect you're going to tell me offsets are zero based and rows start at 1, so if lastrow=888 (1 based), then I need to subtract 1 to match the offset. so what I see as row 885 in the spreadsheet is really row 884 as an offset.

But... when i=row 885 has data, the offset is 884. And when i=884, the offset is 883... so why is it printing what's in row 885??? Seriously confused here. What am I getting wrong?

Last but most importantly... it's deleting the wrong row! How can it be referencing the right data, yet still deleting the wrong row???

var s = SpreadsheetApp.getActiveSheet();
var range = SpreadsheetApp.getActiveSheet().getDataRange();
var i = range.getLastRow();
var msg;
var colNum;

for (; i > 1; i--) {  // I like to start at the end
  var foundAvalue=0;  // reset flag for each row

  rowRange = range.offset(i, 0, 1); 

  var valArray=rowRange.getValues();
  var foundAvalue=0;
  var msg;
  var totalColumns=rowRange.getNumColumns();

   colNum=0;   

   while (colNum < totalColumns && (foundAvalue==0)) {
     if (valArray[0][colNum] != '') {       
       foundAvalue=1; 
       msg="Row " + i + " =" + valArray[0];
       Browser.msgBox(msg);
       msg="Row " + i + " will not be deleted";
       Browser.msgBox(msg);
     }
     colNum++;
   }
   if (foundAvalue == 0) {
      msg="Row " + i + " =" + valArray[0] + "WILL be deleted";
      Browser.msgBox(msg);
           // delete empty row
      // s.deleteRow(i);
   }

 } // end for(i)  

回答1:

Below is some code which should accomplish what you want. You are correct in that the root of the problem is in the 0-based indexing. The tricky part is that JavaScript/Apps Script arrays use 0-based indexing, but when you call something like getLastRow(), the returned range is 1-based. All-in-all, your code is good - it is only that issue that is tripping you up. Hope this helps:

function DeleteFun(){
  var mySheet = SpreadsheetApp.getActiveSheet();
  var range = mySheet.getDataRange();

  // Iterate using a counter, starting at the last row and stopping at
  // the header (assumes the header is in the first row)
  for (i = mySheet.getLastRow() - 1; i > 0; i--) {
    var foundAvalue = 0;
    // Here we get the values for the current row and store in an array
    rowRange = range.getValues()[i]
    // Now we iterate through that array
    for (j = 0; j <= rowRange.length; j++) {
      // If any non-nulls are found, alert they won't be deleted and move on
      if (rowRange[j] != null && rowRange[j] != '') {
        foundAvalue = 1; 
        msg="Row " + (i+1) + " =" + rowRange[0] + " and will not be deleted";
        Browser.msgBox(msg);
        break;
       }
    }

    if (foundAvalue == 0) {
      msg="Row " + (i+1) + " =" + rowRange[0] + "WILL be deleted";
      Browser.msgBox(msg);
      // Delete empty row
      mySheet.deleteRow(i+1);
    }
  }
}

回答2:

You could do it in 'pure' array as well if you don't use formulas in your sheet.

Like this for example :

function deleteEmptyRows(){ 
 var sh = SpreadsheetApp.getActiveSheet();
 var data = sh.getDataRange().getValues();
 var targetData = new Array();
 for(n=0;n<data.length;++n){
 if(data[n].join().replace(/,/g,'')!=''){ targetData.push(data[n])};// checks the whole row
 Logger.log(data[n].join().replace(/,/g,''))
 }
 sh.getDataRange().clear(); // clear the whole sheet
 sh.getRange(1,1,targetData.length,targetData[0].length).setValues(targetData);//write back all non empty rows
 }