使用Apps脚本删除Google表格中的空白行后，是否要复制数据? [英] Copying data after deleting empty rows in Google Sheets using Apps Script?

问题描述

以下代码运行正常.它正在排序，删除重复项并删除空行.然后，我只想将数据行复制到最后一个数据行之后.但是代码正在用空行复制数据行.复制时需要避免空行

The following code is working fine. It is sorting, removing duplicates and deleting empty rows. Then I just want to copy the rows of data to after the last data row. But the code is copying the rows of data with empty rows. Need to avoid empty rows while copying

var column = 3;   
range.sort({column: column, ascending:true});   
range.removeDuplicates([column]);   

//now delete empty rows if any   
for (var i = range.getHeight(); i >= 1; i--){
    if(range.getCell(i, 1).isBlank()){
            sheet.deleteRow(range.getCell(i, 1).getRow());
    }   
}
//For Double periods in a class    
var dataToCopy = range.getValues(); //Gets the values of the source range in a 2 dimensional array    
var copyData = sheet.getRange(range.getLastRow()+1,1,dataToCopy.length,dataToCopy[0].length).setValues(dataToCopy);

推荐答案

我相信您的目标如下.

• 脚本中的for循环完成后，您要检索没有空行的值.

为此，这个答案如何?

• 在此答案中，对删除行数进行计数，并从 range.getValues(); 的 range 中减少行数.这样， dataToCopy 不包含空行.

• In this answer, the number of delete rows is counted and the number is reduced from range of range.getValues();. By this, dataToCopy doesn't include the empty rows.

当以上几点反映到您的脚本中时，它如下所示.

When above points are reflected to your script, it becomes as follows.

var column = 3;
range.sort({column: column, ascending:true});
range.removeDuplicates([column]);

//now delete empty rows if any

var deleteRows = 0;  // <--- Added

for (var i = range.getHeight(); i >= 1; i--){
    if(range.getCell(i, 1).isBlank()){
      sheet.deleteRow(range.getCell(i, 1).getRow());
      deleteRows++; // <--- Added
    }
}
//For Double periods in a class
var dataToCopy = range.offset(0, 0, range.getNumRows() - deleteRows).getValues();  // <--- Modified
var copyData = sheet.getRange(sheet.getLastRow()+1,1,dataToCopy.length,dataToCopy[0].length).setValues(dataToCopy); // <--- Modified

• 如果要将值放在 range 的最后一行，请将最后一行修改为脚本的最后一行.
• If you want to put the values to the last row of range, please modify the last line to the last line of your script.
• offset(rowOffset，columnOffset，numRows)
• 类表的getLastRow()

这篇关于使用Apps脚本删除Google表格中的空白行后，是否要复制数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！