Google sheets returning null value for getRange

Question

trying to code a function that, when a menu item is selected, certain data is called and duplicated at the bottom of the open sheet on a spreadsheet - currently the data is being called from an 'Examples' sheet row by row, so I'm trying to pass rowNumber through to the duplicateLastRow function - and currently on this it returns a null value and I can't figure out why.

I'm also very open to different ways of doing this, as my menu has ~80 items, and therefore 80 function definitions, and I feel like 500 lines of code is excessive to copy and paste stuff on command. Code included below, and linked sheet made public here: https://docs.google.com/spreadsheets/d/1yOJ8VdhbkbC9vw5or4geV-mv-PCk-WA86L8-F7-WGM8/edit?usp=sharing The project in script editor is called copyRowFromOriginToDestination, and the script I'm working on is called testFileBlarg - Code2 script is the working but huge version of the code that I'm trying to remove dialog prompts and lines from.

Cheers much in advance!

function onOpen() {
  var ui = SpreadsheetApp.getUi();

  ui.createMenu('testRow')
   .addSubMenu(ui.createMenu('Life Sciences')
   .addSubMenu(ui.createMenu('NRG')
    .addItem('Highlight/Alamy', 'callHANRG') 
    ))
  .addToUi();

}

function callHANRG(rowNumber){
  duplicateLastRow(3);
}

function duplicateLastRow(rowNumber){

  var originSheetName = "Example Rows";
  var ss = SpreadsheetApp.getActiveSpreadsheet(); // This is an object that represents the open spreadsheet
  var originSheet = ss.getSheetByName(originSheetName); // This object represents the sheet we are copying FROM
  var destinationSheet = ss.getActiveSheet() // getSheetByName(destinationSheetName); // This object represents the sheet we are copying TO

  destinationSheet.insertRowAfter(destinationSheet.getLastRow()); // This adds a row to the end of the destination sheet so it doesn't fill up!

  var originRow = originSheet.getRange(rowNumber, 1, 1, originSheet.getLastColumn() ); // This object represents the row to be copied
  var destinationRow = destinationSheet.getRange(destinationSheet.getLastRow()+1, 1); // This object represents the row we are copying to

  originRow.copyTo(destinationRow); // This actions the copying
 }

From what I understand from your code is that you want to write a function to copy data from one worksheet to other, correct? — Parag Jadhav
yes, that is what I want, unless there is a better way to generate the data in the worksheet - I've been looking into arrays but I really know very little about all this — Kate Snowdon
I'm not sure how your function allows me to vary the row number by menu item selected? Sorry! — Kate Snowdon

Parag Jadhav Parag Jadhav · Accepted Answer · 2017-09-20T11:13:00

If you are want to copy data from one worksheet to other refer the code below to get started.

Update:

To use this function as a common function to copy data, for example -

function callHANRG(rowNumber){
  copy(3, "A2:I20");
}

And then in Copy() function,

function Copy(rowNumber, rangeToBeCopied)
{
    var sss = SpreadsheetApp.openById('<SPREADSHEET_ID>');
    var ss = sss.getSheetByName('Front');
    var range = ss.getRange(rangeToBeCopied);
    var data = range.getValues();

   //If your copying data to different spreadsheet then only use this line or 
   //else use var ts = sss.getSheetByName('frontBackup'); and comment the two lines below.
    var tss = SpreadsheetApp.openById('<SPREADSHEET_ID>');
    var ts = tss.getSheetByName('frontBackup');
    ts.getRange(rowNumber, 1, data.length, data[0].length).setValues(data);
}

Google sheets returning null value for getRange

1 Answers