copy google form response rows into new sheet

Question

Any help is greatly appreciated.

I am trying to get form responses to move over to another sheet if cell in row 5 is greater than 0. The code I am using below moves it over but adds it under the last cell that has a formula in it. Is there a modified version of this code that i can tell it to ignore formula's in cell. Also only copy over rows A:E. Thanks

    function moveToAppropriateSheet(e) {
  // assumes source data in sheet named Form Responses 1
  // target sheet of move to named TR Requested
  // test column with yes/no is col 11 or K
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName('Query');
  var r = s.getRange(s.getLastRow(),1,1,20);
  var data = r.getValues();

  if(data[0][4] >0) {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Pending");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    targetSheet.getRange(targetSheet.getLastRow()+1,1,1,20).setValues(data);
    s.deleteRow(row);
  }
};

First, you need to proof read your question. You stated "Also only copy over rows A:E" I'm assuming that you meant columns A:E That's easy enough to determine, but anything that you can do to make your question easier to understand, increases the odds that you'll get a good answer. — Alan Wells
Do you want cells with formulas to have only the value of the formula copied, and not the formula itself? Link to Apps Script documentation - copy values to range — Alan Wells

Paul Paul · Accepted Answer · 2016-12-17T20:40:46

Edited in light of new information:

You want to copy only the first 5 columns of the row you copy in "Query" to the sheet "Pending".
You already have entries in "Pending", in columns F, G, H, ... You wish to add your copied row under the last entry in columns A:E
I've also assumed for simplicity's sake that you always copy 5 columns so I can check only column A to find the last row (in the subset A:E).
I also note that this is essentially a duplicate of this answer by Mogsdad.

If my assumptions are correct, then:

  function moveToAppropriateSheet(e) {
  // assumes source data in sheet named Form Responses 1
  // target sheet of move to named TR Requested
  // test column with yes/no is col 11 or K
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName('Query');
  var r = s.getRange(s.getLastRow(),1,1,5);   // Change 20 to 5 to copy only cols A:E
  var data = r.getValues();


  var targetSheet = ss.getSheetByName("Pending");
  var lastRowIgnoringFormulae = 0;

  var targetSpace = targetSheet.getRange("A:A").getValues();
  var lastRow = targetSpace.filter(String).length;

  if(data[0][4] >0) {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    targetSheet.getRange(lastRow+1,1,1,5).setValues(data); // +1 to convert into R1C1 notation
    s.deleteRow(row);
  }
};

I hope I've helped.

copy google form response rows into new sheet

1 Answers