Copy only non-blank rows from one tab to another

Question

I have a Google Sheets script that copies a range of cells from one tab to another. The problem is that I'd like it to only copy the non-blank range of cells, but instead it copies the entire range including blank rows. There are many versions of this problem already discussed, but I can't seem to find the right solution so I'm asking it again with all the specifics below.

The range I'm copying is comprised of:

Column A contains a formula that has a text output if column B is non-blank. If column B is blank, then the formula in column A creates a blank entry ("").
Columns B:J is an =IMPORTRANGE from a different sheet with the range set to A5:H (open ended range).
- Example Source data (imported into the main sheet)
- Example Main sheet (this contains the script, which can be triggered in the menu at the top under "Copy Data")

Here's the current script:

// custom menu function
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  var menu = ui.createMenu('Copy Data');
  var item = menu.addItem('Copy Data','copyData');
  item.addToUi();
}

function copyData() { 
// START1: get current sheet and tabs
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var current = ss.getSheetByName('ImportRange'); 
  var database = ss.getSheetByName('RunningList');

// count rows to snap
  var current_rows = current.getLastRow();
  var database_rows = database.getLastRow() + 1;
  var database_rows_new = current_rows + database_rows - 3;
  var rows_new = current.getRange('A3:J' + current_rows).getValues();
  var nonblank_values = rows_new.filter(String);

// snap rows, can run this on a trigger to be timed
  database.getRange(database_rows, 1, nonblank_values.length, nonblank_values[0].length).setValues(nonblank_values);
}

Thank you for your time reviewing this problem.

EDIT 1

When I debug the script, it looks like the filter function is not actually filtering out blank rows. So how would I actually do that?

Debugging Info:

What sort of debugging have you done? I'm surprised you don't get issues with your final line. Generally you only supply the top-left cell to be written, and then use the actual data you are going to write to determine how many rows and columns you need to include in the Range (i.e. getRange(database_rows, 1, nonblank.length, nonblank[0].length).setValues(nonblank);) Further, have you tested that String is an appropriate predicate for 2D arrays like the ones obtained from getValues()? — tehhowch
Hey @tehhowch I'm a total novice to scripts, and I've patched this script together based on whatever I could find online. The only debugging I've done is to run the "debug" tool within the script page, and it doesn't return any issues. Would your function work even if there may be blank cells in the last column (although there is content in other columns of the same row?). Thank you! — pminc
Use a breakpoint (click the line number) to pause the execution and allow line-by-line evaluation and variable inspection. — tehhowch
@tehhowch I'm not sure how to implement the Range code you provided, here's what I've done but I get an error when I run it "ReferenceError: "nonblank" is not defined." Here's how I've integrated what you provided: // count rows to snap var range = current.getRange(database_rows, 1, nonblank.length, nonblank[0].length).setValues(nonblank);) var database_rows = database.getLastRow() + 1; var database_rows_new = getLastRow(range) + database_rows - 3; The final section remains unchanged under the // snap rows heading. — pminc
Got it, thanks for clarifying. Just implemented it and the result is still the same, tons of blank rows after the last non-blank row. — pminc

pminc pminc · Accepted Answer · 2018-10-04T14:46:18

I believe I've found a suitable solution that currently works for my use case. I'm sure there are ways to improve it if you'd like to share your thoughts. I found a script to count nonblank rows here, and integrated it along with some adjustments to the rest of the script:

// add custom menu function "Copy Data"
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  var menu = ui.createMenu('Copy Data');
  var item = menu.addItem('Copy Data','copyData');
  item.addToUi();

}

// function to identify last populated row of any tab (based on column A)
function getLastPopulatedRow(sheet) {
  var data = sheet.getDataRange().getValues();
  for (var i = data.length-1; i > 0; i--) {
    for (var j = 0; j < data[0].length; j++) {
      if (data[i][j]) return i+1;
    }
  }
  return 0;
}

// function to copy data from one tab to another
function copyData() { 

// step 1: get current sheet and tabs
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var current = ss.getSheetByName('ImportRange'); 
  var database = ss.getSheetByName('RunningList');

// step 2: count number of new rows needed and grab non-blank rows from first tab
  var current_lastrow = getLastPopulatedRow(current);
  var database_rows = getLastPopulatedRow(database) + 1;
  var database_rows_new = current_lastrow + database_rows - 3;
  var rows_new = current.getRange('A3:I' + current_lastrow).getValues();

// step 3: add values to second tab
  database.getRange("A" + database_rows + ":I" + database_rows_new).setValues(rows_new);
}

Copy only non-blank rows from one tab to another

1 Answers