How to copy only specific rows inside function instead of full row

Question

I have a function which I got from online and modded slightly for my own use. Something I can not get working is how do I delete the full row and only copy over specific rows inside the function below.

I would like to be able to pass in variables to the function for each sheet and its corresponding columns to copy over.

ex. moveCompletedRows(type,whereToMoveType,[cell's to move]){}

function moveCompletedRows(type,whereToMoveType) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('SortBot');
  var targetSheet = ss.getSheetByName(whereToMoveType);
  var val = sheet.getDataRange().getValues();
  var headers = val.shift();
  var arr = [], rowsToWriteBack = [];

  rowsToWriteBack.push(headers);

  val.forEach(function (r, i) {
    r[1] == type ? arr.push(r) : rowsToWriteBack.push(r)
  });

  if (arr.length > 0) {
    targetSheet.getRange(targetSheet.getLastRow() + 1, 1, arr.length, arr[0].length)
    .setValues(arr);
    sheet.clear();
    sheet.getRange(1, 1, rowsToWriteBack.length, rowsToWriteBack[0].length)
    .clear()
    .setValues(rowsToWriteBack);
  }
}

Aside: The reason my current setup needs to change is since my form is so large (multi section form) submits alot of columns (A-CH) and each entry only submits around 8 each (just in different columns), even with hiding columns, when I view the pages on a mobile device it freezes/crashes 50% of the time(I am not sure this will fully solve it as the sheet with many rows will still be on that master sheet, note that it will be blank). Which makes me question if just having a sheet with that many cells that is never viewed on a mobile device cause the sheet to load very slow?

Thanks

Edit:

Link to the Drive folder containing the form/sheet/ script

https://drive.google.com/folderview?id=0B-7N8A7FPKw5V20yRVFQcmNZTlk&usp=sharing

Edit2:

Been trying to work on this the last few days and I have a secondary script I have semi working but part marked below is what I want to have working in the original formula

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet2Copy = ss.getSheetByName('Actions');
  var targetSheet = ss.getSheetByName('Test');

  var data = sheet2Copy.getRange(1,1, sheet2Copy.getLastRow(), sheet2Copy.getLastColumn()).getValues();

  var dest = [];

  for (var i = 0; i < data.length; i++ ) {

    Logger.log(data[i][12]);// just to check if the condition is true sometimes ;-)

    if (data[i][1] == "Action") {

      var rowsToWriteBack = []; // initialise intermediate array

      // GET THIS WORKING IN ABOVE FORMULA
      // Trying to select columns to put into new sheet.  Will eventually make the #'s variables passed into the formula to make it dynamic
      rowsToWriteBack.push(
        data[i][0],
        data[i][1],
        data[i][2],
        data[i][10],
        data[i][11],
        data[i][12],
        data[i][13]
      );// choose here the columns you want to add 

      dest.push(rowsToWriteBack);
    }
  } // here is the end of the for loop

  Logger.log(dest) ; // log the dest array instead

  if (dest.length > 0 ) { // if something has been written in your array then batch write it to the dest. sheet
    targetSheet.getRange(targetSheet.getLastRow()+1,1,dest.length,dest[0].length).setValues(dest);
  }
}

Answer 1

Check this out! This will check every row, and will only take non-empty entries, and puts the result in the sheet targetSheetName.

function minimizeRows(sheetName,targetSheetName) {
  // default sheetName is "test8" and default targetSheetName = "test-results";
  sheetName = sheetName ? sheetName : "test8";
  targetSheetName = targetSheetName ? targetSheetName : "test8-results";

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName) ? ss.getSheetByName(sheetName) : ss.insertSheet(sheetName);
  var values = sheet.getDataRange().getValues();
  var headers = values.shift();

  // this adds the column name after each entry
  for(var i=0;i<values.length;i++) {
    for(var k=0;k<values[i].length;k++) {
      if(values[i][k])
        values[i][k] = values[i][k] + " (column: " + headers[k] + ")";
    }
  }

  // this filters out only the non-empty entries
  for(var i=0;i<values.length;i++) {
    for(var k=0;k<values[i].length;k++) {
      if(!values[i][k]) {
        values[i].splice(k,1);
        k--; // important to decrease k, because the row values[i] is decreased in size by one by split(k,1)
      }
    }
  }

  // this loop makes sure that every row will become the same length (the max length of a row)
  var maxLengthOfRow = Math.max.apply(Math,values.map(function(item) { return item.length; }));
  for(var i=0;i<values.length;i++) {
    for(var k=0;k<maxLengthOfRow;k++) {
      if(!values[i][k])
        values[i].push(""); // add empty entries to create the right length of the row
    }
  }

  var targetSheet = ss.getSheetByName(targetSheetName) ? ss.getSheetByName(targetSheetName) : ss.insertSheet(targetSheetName);
  targetSheet.getRange(targetSheet.getLastRow()+1,1,values.length,values[0].length).setValues(values);
}

Answer 2

So I was unable to get it working directly off the main sheet "SortBot" and made it so SortBot writes all the cells to another sheet next to it. Then another script "moveToSheet" which read off the new sheet and transfers all the desired columns to the new sheet.

A problem I ran into was all the responses that are sent to the new form I am unable to watch for changes and run a trigger. So I have it set the cell C2 to "Not sorted" when a response comes in and if the page is open you select C2 (Dropdown) and select "Sorted" and it will then sort the sheet.

If someone wants to see how more of the system works let me know, I am planning on making a video walkthrough of my finished system for others to use

// Main page running the code
 var responseRange = "A2:CJ";
  var targetSheetSortCell = "C2";
  var targetSheetSortCellValue = "Not sorted";

  var type1 = "Action";
  var sheetName1 = "Actions";
  var targetURL1 = "SHEET_URL";
  var columnsToCopy1 = [0,1,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25];

  moveToSheet(type1, sheetName1, targetURL1, columnsToCopy1, responseRange, targetSheetSortCell, targetSheetSortCellValue)


// The function
function moveToSheet(type, sheet, targetURL, columnsToCopy, responseRange, targetSheetSortCell, targetSheetSortCellValue) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet2Copy = ss.getSheetByName(sheet);
  var target = SpreadsheetApp.openByUrl(targetURL)
  var targetSheet = target.getSheetByName(sheet);
  var columnsToCopy = columnsToCopy;
  var responseRange = responseRange;
  var targetSheetSortCell = targetSheetSortCell;
  var targetSheetSortCellValue = targetSheetSortCellValue;
  var data = sheet2Copy.getRange(1,1, sheet2Copy.getLastRow(), sheet2Copy.getLastColumn()).getValues();

  var dest = [];

  for (var i = 0; i < data.length; i++ ) {

    if (data[i][1] == type) {

      var destRow = []; // initialise intermediate array

      for (var v = 0; v < columnsToCopy.length; v++ ) {
          destRow.push(data[i][columnsToCopy[v]]);
      }

      dest.push(destRow);

      targetSheet.getRange(targetSheetSortCell).setValue(targetSheetSortCellValue);
    }
  }

  if (dest.length > 0 ) { // if something has been written in your array then batch write it to the dest
    sheet2Copy.getRange(responseRange).clear();

    targetSheet.getRange(targetSheet.getLastRow()+1,1,dest.length,dest[0].length).setValues(dest);
  }

}