0

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:

  1. 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 ("").

  2. Columns B:J is an =IMPORTRANGE from a different sheet with the range set to A5:H (open ended range).

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:

tehhowch
  • 9,645
  • 4
  • 24
  • 42
pminc
  • 15
  • 1
  • 7
  • 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 Oct 02 '18 at 15:22
  • 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 Oct 02 '18 at 17:16
  • Use a breakpoint (click the line number) to pause the execution and allow line-by-line evaluation and variable inspection. – tehhowch Oct 02 '18 at 17:48
  • @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 Oct 03 '18 at 13:27
  • 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 Oct 03 '18 at 13:54
  • You'll want to set the breakpoint on the final line (`database....`), so that editor has a chance to execute the line that performs the filter (so you can compare the instantiated values of `rows_new` and `nonblank_values`). You'll probably want to experiment with a few different [`Array#filter`](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/filter) predicates to see how they behave. You can also experiment directly on the MDN site, which may be faster / easier. – tehhowch Oct 03 '18 at 15:08

1 Answers1

0

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);
}
pminc
  • 15
  • 1
  • 7