0

I have the following script (copied from the forum) to copy the last row from a form response sheet (Event) to destination sheet (sheet11). The trigger is: on form submit.

function CopyLastrowForm() {
  var ss = SpreadsheetApp.getActiveSpreadsheet ();
  var sourceSheet = ss.getSheetByName("Event");
  var source = sourceSheet.getRange("A:AC" + sourceSheet.getLastRow());
  var destSheet = ss.getSheetByName("Sheet11");
  var lastRow = destSheet.getLastRow();
  if (lastRow) destSheet.insertRowAfter(lastRow);
  source.copyTo(destSheet.getRange(lastRow + 1,1), {contentsOnly: true});
}

Which works fine when the destination sheet rows are blank but I have (in all the rows of the destination sheet AD onwards) some arrayformulas!

So the script runs but result does not appear (I assume because it can not find lastrow on sheet11).

How should it be amended so it will copy A:AC (just values) from last row of Event! to Sheet11 in which A:AC is blank but Cols AD onwards already have formulas

Rubén
  • 34,714
  • 9
  • 70
  • 166
Roger Pollard
  • 1
  • "copied from the forum" There are innumerable forums. You should do yourself a much bigger favor and include attribution links for yourself, and anyone who follows you. Plus, attributing the true author is protection against plagiarism -- are you a plagiarist? Review the function `Sheet#appendRow` in the official documentation - probably it achieves your goal. – tehhowch Jan 11 '19 at 21:38
  • 1
    Possible duplicate of [ArrayFormula is breaking the getLastRow() funtion. Possible workarounds?](https://stackoverflow.com/questions/46883862/arrayformula-is-breaking-the-getlastrow-funtion-possible-workarounds) – TheMaster Jan 11 '19 at 21:40

1 Answers1

0

You could use something like this to get a single column height for whatever column you wish.

function getColumnHeight(col){
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var rg=sh.getRange(1,col,sh.getLastRow(),1);
  var vA=rg.getValues();
  while(vA[vA.length-1][0].length==0){
    vA.splice(vA.length-1,1);
  }
  return vA.length;
}
Cooper
  • 59,616
  • 6
  • 23
  • 54