copyInfo from one sheet to another sheet in google sheet with script

user_17405543 0
Viewed 32

I want to copy some data from sheet 1 to sheet 2, but every time I tried to run the below-attached code it send me an error which I mention in the comment.

function copyInfo() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ss.getSheetByName("Sheet 1");
  var pasteSheet = ss.getSheetByName("Sheet 2");

  // get source range
  var source = copySheet.getRange(2,1,38,17);
  // get destination range, "TypeError: Cannot call method "getRange" of null."
  var destination = pasteSheet.getRange(pasteSheet.getLastRow()+1,1,38,17);

  // copy values to destination range
  source.copyTo(destination,{contentsOnly: true});
}

Thanks in advance.

google-apps-script
2 Answers

Your code seems correct and is working for me, Can you try to run this code and check if its logging the correct name of your google sheet that you are using,

function copyInfo() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  Logger.log(ss.getName());
}

For better access to a particular google sheet you can use the following method to access the file rather than using getActiveSpreadsheet

var ss = SpreadsheetApp.openById("your sheet id");

You can even try the debug window to check what is the present values of different variables and get some idea of possible errors.

user_172055060

The error you are receiving is due to the fact that pasteSheet is null.

This is most likely due to one of the following reasons:

  • your spreadsheet does not have any sheet named Sheet 2 - I suggest you double check the name of the sheets from your spreadsheet;

  • the active spreadsheet you are retrieving is not the one you are needing for copying the data;

As suggested above, you can indeed make use of the openById method in order to retrieve the needed spreadsheet and afterwards get the actual sheets.

Reference

user_117720350
Related