在谷歌电子表格中，行数为1 [英] Row count off by one in google spreadsheet

问题描述

我想删除空行。行885是一个非空行，应该保留。但是，我的代码说（并在msgbox中显示）885是空的，应该删除。当它到达i = 884时，它会打印出真正在第885行的内容，并表示它不会被删除。所以，我很困惑。它似乎正确地处理了这些数据，但它报告的行号是错误的。我怀疑你会告诉我补偿是基于零的，行从1开始，所以如果lastrow = 888（基于1），那么我需要减去1以匹配补偿。所以我在电子表格中第885行看到的实际上是第884行作为偏移量。

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.

但是...当i =行885有数据时，偏移量为884。而当我= 884时，偏移量是883 ......所以它为什么要打印第885行？在这里严重困惑。我错了什么？

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)  

推荐答案

下面是一些应该完成你想要的代码。你是正确的，问题的根源在于基于0的索引。棘手的部分是JavaScript / Apps脚本数组使用基于0的索引，但当您调用类似于 getLastRow（）时，返回的范围是基于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);
    }
  }
}

这篇关于在谷歌电子表格中，行数为1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！