Remove all blank rows in google spreadsheet
I just started using Google Apps script to manage some sheets for a project I am working on, I am new to Javascript, so please walk through if there are any warriors in my code !.
We have an application called forms2mobile that grabs data and transfers it to a Google spreadsheet. It actually transfers different data to different sheets depending on which part of the application you are using.
I hacked into a script that fetches all data from one sheet (source) and only removes certain columns in the second sheet (destination). It then removes all lines from the source and any blank lines from the destination.
The problem is removing blank lines from the target. Usually the receiver will have blank lines at the bottom, and the code I have will only remove blank lines in the range that contains the data. So I always leave blank lines at the bottom.
The destination sheet will then be used as a data source for forms2mobile, which is of course not happy with empty lines.
I found the getMaxRows () class, but I'm not sure how to implement it. If anyone could make any suggestions that would be great.
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));
}
source to share
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 treat columns the same if needed using getMaxColumn()
, getLastColumn()
anddeleteColumns(number, howMany)
EDIT
By the way, here's another way to remove blank rows in a spreadsheet ... if you combine both, it clears your sheet completely!
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);
}
source to share
In a nutshell, I added this "if" statement to prevent Serge insas from throwing an error if there is no empty bottom line when you try to delete empty lines.
Place this if around the last line function removeEmptyRows () and it won't throw an error:
if (maxRows-lastRow != 0){
sh.deleteRows(lastRow+1, maxRows-lastRow);
}
source to share
Script removeEmptyRows and removeEmptyColumns in Google Sheets. It combines everything that Serge and Apotyle mentioned earlier. Here is a sample with the attached script File> Make a Copy ... to edit a copy of the sheet. Also a video showing how to use this sheet .
//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);
}
}
}
source to share
I tried this piece of code and it works well, you can take a look and try:
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);
}
}
source to share
This works great 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);
}
source to share