javascript - Deleting ALL empty rows in a Google Spreadsheet -

javascript - Deleting ALL empty rows in a Google Spreadsheet -

i've started using google apps script manage sheets project i'm working on, new javascript please go easy if there howlers in code!.

we have , app called forms2mobile captures info , drops google spreadsheet. drops different info different sheets depending on part of app use.

i've hacked script pulls info 1 sheet (source), , drops columns sec sheet (destination). deletes rows source, , blank rows destination.

the problem have deleting blank rows destination. typically destination have empty rows @ bottom, , code have delete empty rows within range contains data. i'm left empty rows @ bottom.

the destination sheet used info source forms2mobile, of course of study isn't happy empty rows.

i've found class getmaxrows() i'm not sure how implement it. if create suggestions 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 array of arrays containing values in input range. var range_values = range_input.getvalues(); // loop through each inner array. ( var = 0, row_count = range_values.length; < row_count; i++ ) { // loop through indices maintain , utilize these indices // select values inner array. ( j = 0, col_keep_count = columns_keep_num.length; j < col_keep_count; j++ ) { // capture value maintain var keep_val = range_values[i][columns_keep_num[j]]; // write value output using offset method of 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 = values.length-1; >=0; i-- ) { for( var j = 0; j < values[i].length; j++ ) if( values[i][j] != "" ) go on nextline; s.deleterow(i+1); } //i iterate backwards on purpose, not have calculate indexes after removal } function clearsourcedata() { var ss = spreadsheetapp.openbyid('1a89ziucy-8168d1damcv3q9ix0arqn9jgs6pgp'); var s = ss.getactivesheet(); var info = s.getdatarange().getvalues(); for(var n =data.length+1 ; n<0 ; n--){ if(data[n][0]!=''){n++;break} } s.deleterows(2, (s.getlastrow()-1)); }

this how works :

function removeemptyrows(){ var sh = spreadsheetapp.getactivesheet(); var maxrows = sh.getmaxrows(); var lastrow = sh.getlastrow(); sh.deleterows(lastrow+1, maxrows-lastrow); }

note : can handle columns same way if necessary using getmaxcolumn(), getlastcolumn() , deletecolumns(number, howmany)

edit

by way, here way delete empty rows in spreadsheet... if combine both "clean" sheet exclusively !

function deleteemptyrows(){ var sh = spreadsheetapp.getactivesheet(); var info = 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); }

demo sheet in view - create re-create use

javascript google-apps-script google-spreadsheet