Copy non-empty range into new sheet, and drag down formulas

Question

I want to copy all the non-empty cells in columns A/B from sheet "Consolidation Sheet" and then paste these cells into the sheet "Inputs for Web App" into columns H:I (always starting from the top, rather than appending them as new rows, since these values will be updated constantly).
Then I basically want to drag down all of the formulas in the other columns (A through G) all the way down to the last row pasted.
Format column I as a date.

I'm not sure if I need a loop to do this or just an array. I've tried it both ways but keep getting errors.

option 1: HERE I GET THIS ERROR:

TypeError: (class)@2e3b19c is not a function, it is object.

function copynotempty(){
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CONSOLIDATION SHEET");
  var col = 0 ; // choose the column you want to check: 0 = col A, 1= col B ...
  var lastrow = ss.getLastRow();
  var range = ss.getRange(5,1,lastrow-4,2);
  var values=range.getValues();// data is a 2D array, index0 = col A
  var formulas=range.getFormulas();// data is a 2D array, index0 = col A
  var target= new Array();// this is a new array to collect data
   //for(n=0;n<range.getHeight();++n){
     if (values()!=''|| formulas()!=''){ ;  (
           var sh2=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Inputs for Web App"); //target sheet of the spreadsheet
           range.copyTo(sh2.getRange(4,8,range.height,range[0].length),{contentsOnly:true});
              }
            }

option 2: here I keep getting this error:

"TypeError: Cannot set property "0.0" of undefined to "(VALUE OF CELL)"

function copynotempty(){
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CONSOLIDATION SHEET");
  var col = 0 ; // choose the column you want to check: 0 = col A, 1= col B ...
  var lastrow = ss.getLastRow();
  var range = ss.getRange(5,1,lastrow-4,2);
  var values=range.getValues();// data is a 2D array, index0 = col A
  var formulas=range.getFormulas();// data is a 2D array, index0 = col A
  var target= new Array();// this is a new array to collect data
for(n=0;n<range.getHeight();++n){
     if (values[n][col]!=''|| formulas[n][col]!=''){ ;
        for (cc=0;cc<range.getWidth();++cc){
                if (values[n][cc]!=''){target[n][cc]=values[n][cc]} // if the cell has a value, copy it  
                }
              }
            }
            if(target.length>0){// if there is something to copy
              var sh2=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Inputs for Web App"); //second sheet of your spreadsheet
          sh2.getRange(4,8,target.height,target[0].length).setValues();// paste the selected values in the 2cond sheet
          }
        }

Robin Gertenbach Robin Gertenbach · Accepted Answer · 2016-10-07T08:01:56

In the first function your issue is that you try to call values() and formulas() which are both 2d arrays of strings.

Moving the data over (which is currently the only thing you are trying to do in the functions) can be done like this:

function copynotempty(){
  var ss = SpreadsheetApp
      .getActiveSpreadsheet()
      .getSheetByName("CONSOLIDATION SHEET");

  var lastrow = ss.getLastRow();
  var range = ss.getRange(5,1,lastrow-4,2);

  var nonEmpty = range
      .getValues()
      .filter(function(row) {           // filter matrix for rows
        return row.some(function(col) { // that have at least one column
          return col !== "";});});      // that is not empty

  if (nonEmpty) {
    var sh2=SpreadsheetApp
        .getActiveSpreadsheet()
        .getSheetByName("Inputs for Web App");

    sh2.getRange(4,8, nonEmpty.length, nonEmpty[0].length)
       .setValues(nonEmpty);
  }
}

Can this maybe be solved with array formulae?
This is probably best done by setting the column format to a dtae format of your choice or do you want a string?

Copy non-empty range into new sheet, and drag down formulas

1 Answers