1

I am new at this so still trying to figure how everything works.

I have a sheet that collects responses from a Google Form. Based on the answer to one of those questions I would like the row from that sheet to move to a different sheet document all together (not a sheet in the same document).

I have it set on a time based trigger every minute so as new responses come in it would kick them over to the correct document and then delete the row from the original spreadsheet.

I have been able to get part of the way there. I would like for it to take the row, columns A through E, move those to the correct document, find where the next open row is and place the data in columns A through E on the new document.

Where my issue is coming in at the moment is when the row is moved to the new document. I have formulas saved in columns G - Z on the destination page. It is finding the last row with a formula and placing the row after that (which is at the very bottom of the page). I am pretty sure this has to do with using an array? But I may be wrong. Is there a way to just have that look at the destination page column A-E, find the next blank row, and copy A-E from the original file to the new page? 

 arr = [], 
values = sheetOrg.getDataRange().getValues(),
i = values.length;
while (--i) {
  if (value1ToWatch.indexOf(values[i][1]) > -1) {
    arr.unshift(values[i])
   sheetOrg.deleteRow(i + 1) 
   sheet1in.getRange(sheet1in.getLastRow()+1, 1, arr.length, arr[0].length).setValues(arr); 
   };

I have multiple If statements each with some changes to the "valueToWatch" and the "Sheet1in" for different values and destination pages. If that information helps at all.

Jonathan Anderson
  • 11
  • 1
  • 5
  • You can also use [`OnFormSubmit`](https://developers.google.com/apps-script/reference/script/form-trigger-builder#onFormSubmit()) to get the new responses and not depend on a time based trigger. As this related [SO post](https://stackoverflow.com/a/41927335/5995040) stated, you can always set the target range as specified to make sure that you are placing the new data on the correct cell. Hope thi helps. – Mr.Rebot Dec 02 '17 at 21:40

1 Answers1

0

You can find the last cell in a column with data in it like this:

function findLastValueInColumn() {
    var column = "A";  // change to whatever column you want to check
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
    var lastDataRow = sheet.getDataRange().getLastRow();

    for (var currentRow = lastDataRow; currentRow > 0; currentRow--) {
        var cellName = column + currentRow;
        var cellval = sheet.getRange( cellName ).getValue();
        if (cellval != "") {
            Logger.log("Last value in Column " + column + " is in cell " + currentRow);
            break;
        }
    }
}

You can then use this function to figure out where to start your new data.

NSchorr
  • 875
  • 10
  • 13