Google-sheets – Skip empty rows while copying

google sheetsgoogle-apps-script

I have this script in Google Sheets which is able to archive range Sheet1!A5:H to Sheet2

function tc() {
    var reference = 'sheet1!A2'; 
    var rng = SpreadsheetApp.getActiveSpreadsheet().getRange(reference);
    rng.setValue(rng.getValue()+1);
    var ss = SpreadsheetApp.getActiveSpreadsheet ();
    var ss = SpreadsheetApp.getActiveSpreadsheet ();
    var source = ss.getRange ("Sheet1!A5:H");
    var destSheet = ss.getSheetByName("Sheet2");
    var lastRow = destSheet.getLastRow();
    destSheet.insertRowAfter(lastRow);
    source.copyTo(destSheet.getRange(lastRow + 1,1), {contentsOnly: true});
}

The issue is that this will also archive all empty rows from that range.

Is there a way how to skip archiving empty rows from a given range?

I tried to add something like var filtered_input = source.filter(String); but this didn't do the trick. Can someone help me out?

Sheet1 looks like:

Sheet2 looks like this after running the script 2 times:

Best Answer

How about this modification? I think that you can use getValues() and setValues() in your situation, because contentsOnly: true is used to copyTo(). So please modify as follows.

From:

var lastRow = destSheet.getLastRow();
destSheet.insertRowAfter(lastRow);
source.copyTo(destSheet.getRange(lastRow + 1,1), {contentsOnly: true});

To:

var values = source.getValues().filter(function(e) {return e.some(function(f) {return f})});
destSheet.getRange(destSheet.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);

References:

If I misunderstood your question, please tell me. I would like to modify it.

Related Solutions

Google Sheets – How to Paste Cell Range Automatically as Values Only

You could use an onEdit trigger for the script. That will update whenever changes are made. For example:

/**
 * A function named onEdit will be called whenever
 * a change is made to the spreadsheet.
 * 
 * @param  {object} e The edit event (not used in this case)
 */
function onEdit(e){
  var copyFromRange = 'Sheet1!A13:A'; // no row for second cell reference
  var copyToRangeStart = 'Sheet2!A3';
  copyValuesOnly(copyFromRange, copyToRangeStart);
}

/**
 * This function will copy the values from a given range to
 * a second range, which starts from the given cell reference
 * 
 * @param  {string} copyFromRange    Range reference eg: 
 * @param  {string} copyToRangeStart Cell reference eg:
 */
function copyValuesOnly(copyFromRange, copyToRangeStart) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var source = ss.getRange(copyFromRange);
  source.copyTo(ss.getRange(copyToRangeStart), {contentsOnly: true});
}

See https://developers.google.com/apps-script/understanding_triggers for more details on triggering scripts automatically.

I can't comment yet, so will ask here: Do you need to copy to a second spreadsheet or just a second sheet within the same spreadsheet? The second case is answered above, but I can adjust if you need to copy to a separate spreadsheet?

edit: added code to fully answer the question. Thanks for the guidance Jacob.

Google Sheets – How to Apply Formatting to Whole Row or Column Based on Condition

Try the function COUNTA, it counts the number of cells in a range that are not empty,

and indicate more than zero for conditional rule:

Was this what you were looking for?