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, Dec 17 '16 at 18:21
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](https://developers.google.com/apps-script/reference/spreadsheet/range#copyvaluestorangesheet-column-columnend-row-rowend) — Alan Wells, Dec 17 '16 at 18:35

score 0 · Accepted Answer · edited May 23 '17 at 12:09

0

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.

edited May 23 '17 at 12:09

Community

1
1

answered Dec 17 '16 at 20:40

Paul

887
6
22

I have a Query sheet that all my form responses go to. I want to be able to move only columns A:E to Pending sheet. But i have formulas in F3 to K200 and instead of moving the line to A3 it added it to line 201. The code you have collects the correct column numbers now but still moves it to row 200. Any advice? – Jesse Miller Dec 17 '16 at 21:44
As I said above, I've assumed the cell you want to check formula or no formula was in the first column. To alter that, change the line `if (targetSpace[i][0] !== ""){ `to `if (targetSpace[i][5] !== ""){`. That instructs the script to check column F. – Paul Dec 17 '16 at 23:12
even with switching that code it is still putting the row after the formula(line 200) – Jesse Miller Dec 18 '16 at 03:28
If you post a link to a publicly accessible sheet (with dummy data, but correctly representing the presence of the formulae you mentioned in "Pending") I will look at it more closely. – Paul Dec 18 '16 at 10:08
Here is a link to a public test version with formulaes. https://docs.google.com/spreadsheets/d/1JoB_ZeEZ0tl6JN3iCqgI_PKp9Ajt7hoK01yvxm2LhnQ/edit?usp=sharing – Jesse Miller Dec 18 '16 at 15:49
Code seems to be working correctly now another code i had was interfering. But now it it moving it to the correct page but instead of adding to a new line below the other its writing over the data. – Jesse Miller Dec 18 '16 at 17:29
Yes, it overwrites the data because in your first post, you ask for a modification that ignores cells with a formula in. Having just looked at your spreadsheet, I am assuming what you really want is something that gives you the last row but only for entries in A:E? If so, this has already been answered in this [question](http://stackoverflow.com/questions/17632165/determining-the-last-row-in-a-single-column). – Paul Dec 18 '16 at 17:42
I've updated the answer - please remember to accept it. – Paul Dec 18 '16 at 17:52

copy google form response rows into new sheet

1 Answers1