Javascript 删除 Google 电子表格中的所有空行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26386492/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Deleting ALL empty rows in a Google Spreadsheet
提问by user2136368
I've just started using Google Apps script to manage some sheets for a project i'm working on, I am new to Javascript so please go easy if there are any howlers in my code!.
我刚刚开始使用 Google Apps 脚本来管理我正在处理的项目的一些工作表，我是 Javascript 的新手，所以如果我的代码中有任何咆哮，请放轻松！
We have and app called forms2mobile that captures data and drops it into a Google spreadsheet. It actually drops different data into different sheets depending on which part of the app you use.
我们有一个名为 forms2mobile 的应用程序，它可以捕获数据并将其放入 Google 电子表格中。它实际上将不同的数据放入不同的工作表中，具体取决于您使用的应用程序的哪个部分。
I've hacked together a script that pulls all data from one sheet (source), and drops only certain columns into a second sheet (destination). It then deletes all rows from the source, and any blank rows from the destination.
我已经编写了一个脚本，该脚本从一个工作表（源）中提取所有数据，并仅将某些列放入第二个工作表（目标）中。然后删除源中的所有行，以及目标中的所有空白行。
The problem I have is with deleting blank rows from the destination. Typically the destination will have empty rows at the bottom, and the code I have will only delete empty rows within the range that contains data. So i'm always left with empty rows at the bottom.
我遇到的问题是从目标中删除空白行。通常，目标底部会有空行，我的代码只会删除包含数据的范围内的空行。所以我总是在底部留下空行。
The destination sheet will then be used as a data source for forms2mobile, which of course isn't happy with empty rows.
然后，目标工作表将用作 forms2mobile 的数据源，这当然对空行不满意。
I've found the class getMaxRows() but i'm not sure how to implement it. If anyone could make any suggestions that would be great.
我找到了 getMaxRows() 类，但我不确定如何实现它。如果有人可以提出任何建议，那就太好了。
Cheers Paul
干杯保罗
function NEW_copyColumnNumbers( ) {
var spreadsheet_source = SpreadsheetApp.openById('1a89ZIUcy-8168D1damCV3Q9Ix0arQn9jGS6pgp');
var spreadsheet_target = SpreadsheetApp.openById('1GQiLt9utSH_6CV__oJwmcLOkI4E9iNIRPWU7Xr');
var range_input = spreadsheet_source.getRange("A2:CC407");
var range_output = spreadsheet_target.getRange("A"+(spreadsheet_target.getLastRow()+1));
var keep_columns = [66,66,10,11,12,13,14,23,26,31,69,71,74,75,80];
copyColumnNumbers(range_input, range_output, keep_columns);
clearEmptyRows();
clearSourceData();
}
function copyColumnNumbers( range_input, range_output, columns_keep_num ) {
// Create an array of arrays containing the values in the input range.
var range_values = range_input.getValues();
// Loop through each inner array.
for ( var i = 0, row_count = range_values.length; i < row_count; i++ ) {
// Loop through the indices to keep and use these indices to
// select values from the inner array.
for ( j = 0, col_keep_count = columns_keep_num.length; j < col_keep_count; j++ ) {
// Capture the value to keep
var keep_val = range_values[i][columns_keep_num[j]];
// Write the value to the output using the offset method of the output range argument.
range_output.offset(i,j).setValue(keep_val);
}
}
}
function clearEmptyRows() {
var ss = SpreadsheetApp.openById('1GQiLt9utSH_6CV__oJwmcLOkI4E9iNIRPWU7Xr');
var s = ss.getActiveSheet();
var values = s.getDataRange().getValues();
nextLine: for( var i = values.length-1; i >=0; i-- ) {
for( var j = 0; j < values[i].length; j++ )
if( values[i][j] != "" )
continue nextLine;
s.deleteRow(i+1);
}
//I iterate it backwards on purpose, so I do not have to calculate the indexes after a removal
}
function clearSourceData() {
var ss = SpreadsheetApp.openById('1a89ZIUcy-8168D1damCV3Q9Ix0arQn9jGS6pgp');
var s = ss.getActiveSheet();
var data = s.getDataRange().getValues();
for(var n =data.length+1 ; n<0 ; n--){
if(data[n][0]!=''){n++;break}
}
s.deleteRows(2, (s.getLastRow()-1));
}
回答by Serge insas
This is how it works :
这是它的工作原理：
function removeEmptyRows(){
var sh = SpreadsheetApp.getActiveSheet();
var maxRows = sh.getMaxRows();
var lastRow = sh.getLastRow();
sh.deleteRows(lastRow+1, maxRows-lastRow);
}
Note : you can handle columns the same way if necessary using getMaxColumn(), getLastColumn()and deleteColumns(number, howMany)
注意：如有必要getMaxColumn()，您可以使用, getLastColumn()和deleteColumns(number, howMany)
EDIT
编辑
by the way, here is also another way to delete empty rows in a spreadsheet... if you combine both it will "clean" your sheet entirely !
顺便说一句，这里还有另一种删除电子表格中空行的方法......如果你将两者结合起来，它将完全“清理”你的工作表！
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])};
Logger.log(data[n].join().replace(/,/g,''))
}
sh.getDataRange().clear();
sh.getRange(1,1,targetData.length,targetData[0].length).setValues(targetData);
}
回答by apptailor
Just a quick note, I added this "if" statement to keep Serge insas's code from throwing an error if there is no empty bottom row when you are trying to remove empty rows.
只是一个简短的说明，我添加了这个“if”语句，以防止当您尝试删除空行时没有空的底行时 Serge insas 的代码抛出错误。
Place this if around the last line function removeEmptyRows() and it will not throw an error:
将此 if 放在最后一行函数 removeEmptyRows() 周围，它不会抛出错误：
if (maxRows-lastRow != 0){
sh.deleteRows(lastRow+1, maxRows-lastRow);
}
回答by Ryan Praskievicz
Script to removeEmptyRows and removeEmptyColumns in Google Sheets. It puts together everything Serge and apptailor mentioned previously. Here is a sample sheet with the script includedFile > Make a copy... to edit a copy of the sheet. Also a video that shows you how to use this sheet.
用于在 Google 表格中 removeEmptyRows 和 removeEmptyColumns 的脚本。它将前面提到的 Serge 和 apptailor 的所有内容放在一起。这是一个示例工作表，其中包含脚本文件 > 制作副本...以编辑工作表的副本。还有一个视频，向您展示如何使用此表。
//Remove All Empty Columns in the Entire Workbook
function removeEmptyColumns() {
var ss = SpreadsheetApp.getActive();
var allsheets = ss.getSheets();
for (var s in allsheets){
var sheet=allsheets[s]
var maxColumns = sheet.getMaxColumns();
var lastColumn = sheet.getLastColumn();
if (maxColumns-lastColumn != 0){
sheet.deleteColumns(lastColumn+1, maxColumns-lastColumn);
}
}
}
//Remove All Empty Rows in the Entire Workbook
function removeEmptyRows() {
var ss = SpreadsheetApp.getActive();
var allsheets = ss.getSheets();
for (var s in allsheets){
var sheet=allsheets[s]
var maxRows = sheet.getMaxRows();
var lastRow = sheet.getLastRow();
if (maxRows-lastRow != 0){
sheet.deleteRows(lastRow+1, maxRows-lastRow);
}
}
}
回答by Jossef Harush
Removing all empty lines (bottom-up)
删除所有空行（自下而上）
before
前
after
后
function isEmptyRow(row){
for (var columnIndex = 0; columnIndex < row.length; columnIndex++){
var cell = row[columnIndex];
if (cell){
return false;
}
}
return true;
}
function removeEmptyLines(sheet){
var lastRowIndex = sheet.getLastRow();
var lastColumnIndex = sheet.getLastColumn();
var maxRowIndex = sheet.getMaxRows();
var range = sheet.getRange(1, 1, lastRowIndex, lastColumnIndex);
var data = range.getValues();
sheet.deleteRows(lastRowIndex+1, maxRowIndex-lastRowIndex);
for (var rowIndex = data.length - 1; rowIndex >= 0; rowIndex--){
var row = data[rowIndex];
if (isEmptyRow(row)){
sheet.deleteRow(rowIndex + 1);
}
}
}
function removeEmptyLinesFromAllSheets(){
SpreadsheetApp.getActive().getSheets().forEach(removeEmptyLines);
}
Removing only empty lines from below and above the data
仅从数据下方和上方删除空行
before
前
after
后
function isEmptyRow(row){
for (var columnIndex = 0; columnIndex < row.length; columnIndex++){
var cell = row[columnIndex];
if (cell){
return false;
}
}
return true;
}
function getFirstNonBlankRowIndex(data){
for (var rowIndex = 0; rowIndex < data.length; rowIndex++){
var row = data[rowIndex];
if (!isEmptyRow(row)){
return rowIndex;
}
}
return 0;
}
function removePaddedEmptyLines(sheet){
var lastRowIndex = sheet.getLastRow();
var lastColumnIndex = sheet.getLastColumn();
var maxRowIndex = sheet.getMaxRows();
var range = sheet.getRange(1, 1, lastRowIndex, lastColumnIndex);
var data = range.getValues();
var firstRowIndex = getFirstNonBlankRowIndex(data);
sheet.deleteRows(lastRowIndex+1, maxRowIndex-lastRowIndex);
sheet.deleteRows(1, firstRowIndex);
}
function removePaddedEmptyLinesFromAllSheets(){
SpreadsheetApp.getActive().getSheets().forEach(removePaddedEmptyLines);
}
回答by Khazragy
I have tried this piece of code and it works good, you may take a look and try it:
我试过这段代码，效果很好，你可以看看并尝试一下：
function DeleteBlankRows(){
var sh = SpreadsheetApp.getActiveSheet();
var maxRows = sh.getMaxRows();
var lastRow = sh.getLastRow();
for (var Raw = 1; Raw < sh.getLastRow() ; Raw++)
{
if( sh.getRange('A'+Raw).getValue() == '')
{
sh.deleteRow(Raw) //deleteRows(lastRow+1, maxRows-lastRow);
}
}
回答by Wizardeesh
This works perfectly for me.
这对我来说非常有效。
function removeEmptyRows(){
var spreadsheet = SpreadsheetApp.openById("IDOFYOURSPREADSHEETFOUNDINURL");
var sh = SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[0]);
var maxRows = sh.getMaxRows();
var lastRow = sh.getLastRow();
sh.deleteRows(lastRow+1, maxRows-lastRow);
}
