Copying and Paste Values into next empty Row in Google Sheets

Question

I have recently started doing some work in Google Sheets, where previously I have been able to code functions in Excel, I am unable to get this working in Google Sheets Scripting.

I have two sheets

Sheet 1. Cells A1 - E5 contain values based on some selection criteria Sheet 2. Register Sheet

What I need is when executing the script (via drawing and linked macro)

I need it to copy the range A1:E1

Go into Sheet 2, Go to the first Blank Cell in Column A, and then Paste Values.

I am using the following script, and I'm getting

coordinates of the target range are outside of the dimensions of the sheet.

function moveValuesOnly () {
  var ss = SpreadsheetApp.getActiveSpreadsheet ();
  var source = ss.getRange ("PNG Sheet!A1:E1");
  var destSheet = ss.getSheetByName("Project Codes");
  var destRange = destSheet.getRange(destSheet.getLastRow()+1,1);
  source.copyTo (destRange, {contentsOnly: true});
  source.clear ();
}

Any assistance would be greatly appreciated.

Cheers Graeme

Hmm. When I test it on my sheet it works fine, UNLESS I try it when there are no more rows to paste into. Then I get the error you get. Have you tried adding more rows to your destination sheet? Google sheets don't expand automatically. — Ron Kloberdanz
@RonKloberdanz they do, but only in certain situations - such as using setValues with multiple row output. In general, you are correct - the sheet will not add rows. — tehhowch

Diego Diego · Accepted Answer · 2018-07-13T09:25:19

You're receiving this error because you're attempting to select a range in the "Project Codes" sheet that does not exist. Use instead the appendRow() method, which will

paste the passed values into the first blank row, and

automatically resize your sheet.

function moveValuesOnly() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var source = ss.getRange("PNG Sheet!A1:E1");
  var destSheet = ss.getSheetByName("Project Codes");
  destSheet.appendRow(source.getValues()[0]);
  source.clear();
}

Copying and Paste Values into next empty Row in Google Sheets

1 Answers