0

I'm new to google script. I need to get a flexible range copied from one tab in a sheet to last empty row in another sheet. Flexible Range is made up from fixed amount of columns (25) and changing number of rows (I have calculated cell that indicates number of rows).

This is what I managed to write thanks to some help of other developers. I'm getting error getRange is not defined (error relates to last row of code). Any ideas how to fix it?

function template(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName("Sprzedaż template");
  const numRows = sourceSheet.getRange("AB84").getValue();
  const sourceRange = sourceSheet.getRange("B84:Z84").offset(0,0,numRows);


  const destinationSheet = ss.getSheetByName("Sprzedaż");
  const lastRow = destinationSheet.getLastRow();
  getRange(sourceRange).copyTo(destinationSheet.getRange(lastRow+1,1),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  
  };

1 Answer 1

0

Delete:

getRange(sourceRange).copyTo(destinationSheet.getRange(lastRow+1,1),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

Substitute: sourceRange.copyTo(destinationSheet.getRange(lastRow+1,1),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)

What's the difference?

sourceRange is already defined as a range in Line 5. getRange(sourceRange) is trying to refine an existing range - hence the error.

copyTo


Complete code (with trouble shooting statements)

function template(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName("Sprzedaż template");
  const numRows = sourceSheet.getRange("AB84").getValue();
  Logger.log("the value in cell "+sourceSheet.getRange("AB84").getA1Notation()+" = "+numRows)
  const sourceRange = sourceSheet.getRange("B84:Z84").offset(0,0,numRows);
  Logger.log(sourceRange)// notice how this just returns the "Range" object with no other details
  Logger.log("Range to copy 'FROM': sheet="+sourceSheet.getName()+", range="+sourceRange.getA1Notation()) // notice how this returns the actual range
  const destinationSheet = ss.getSheetByName("Sprzedaż");
  const lastRow = destinationSheet.getLastRow()
  Logger.log("The last row number in "+destinationSheet.getName()+" = "+lastRow)
  Logger.log("Range to copy 'To': sheet="+destinationSheet.getName()+", range="+destinationSheet.getRange(lastRow+1,1).getA1Notation())
  sourceRange.copyTo(destinationSheet.getRange(lastRow+1,1),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
  Logger.log("Done")
}
6
  • Thanks. Tried it out but this time I get an error message saying that target range coordinates exceed sheet dimensions. The range I would like to copy is made up by 25 columns and flexible number of rows. Sheet where I would like to paste it to is having more columns than 25. Don't understand where problem sits..
    – Dariusz
    Jul 28 at 8:56
  • It's pretty hard to assist when we don't have any information about your spreadsheet or the actual error message, or knowing which line is generating the error message I have a feeling that your problem (and solution) is probably the same as this question from StackOverflow. But you've also got to help yourself by troubleshooting. I've added my full code to the answer and you can see the Logger.log statements that return information about the code/spreadsheet; there are other ways to get this (and more) information) but this is a start.
    – Tedinoz
    Jul 28 at 12:19
  • Thanks Tedinoz. That's helpful. I know where the problem is now. GetLastRow indicates first empty Row based off all columns in targeted sheet. The problem is I have last two columns where I stored functions that are dragged down to the very bottom of sheet. How can I change it to refer to last row in column B?
    – Dariusz
    Jul 29 at 12:04
  • One of favourite topics. Much depends on whether the column contains any blank cells. Read the excellent post ("Determining the last row in a single column") and answers on StackOverflow. @Mogsdad has a very simple solution where there are no blank cells. @soMarios has a good answer that allows for empty cells. Just adapt one or other of these answers to your scenario; when you do this, you can use Logger to return the resulting value for LastRow and compare to your expected result.
    – Tedinoz
    Jul 30 at 2:05
  • superb. that close a loop and works perfectly! thanks a lot Tedinoz and sorry for long thread and questions. Appreciate patience and help!
    – Dariusz
    Jul 30 at 6:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.