I have been trying to copy file with cell data only but unsuccessfully. I have a Google sheet documnet with importxm function of about 2000 rows. When the function runs it populates the cells but sometimes the connection gets reset and I loose the data. The second function copyValuesOnly works fine for the same sheet. I am passing the range directly but it reuses the sheet every time. So if I automate it it will be writing the current data in the same sheet and it will overwrite it. I also have a function that I made to save file but I get the exact copy of the file (formulas not data only). Therefore, I am trying to combine both functions but it doesn't really work. How could I save the sheet values in new file instead of in another sheet. This the post I got one of the functions from:


This is what I am trying to copy the file:

function SaveFileData() {
  //file has to be at least readable by the person running the script
var currentdate = new Date(); 
  var datetime = "HW: " + currentdate.getDate() + "/"
                + (currentdate.getMonth()+1)  + "/" 
                + currentdate.getFullYear() + " @ "  
                + currentdate.getHours() + ":"  
                + currentdate.getMinutes() + ":" 
                + currentdate.getSeconds();
  var fileId = 'fileID'; 
  var destFolder = DriveApp.getFolderById('folderID');
  var file = DriveApp.getFileById('fileId');
  file.makeCopy(datetime, destFolder); 

function copyValuesOnly(file) {
  var source = file.getRange('Sheet2!D1:K2000');
  source.copyTo(file.getRange('Sheet1!A1:K2000'), {contentsOnly: true});

TypeError: Cannot call method "getRange" of undefined. (line 26, file "Logger")

1 Answer 1


Since file.getRange throws that error, that means file is undefined. Look at why that happens. Possibly fileId is wrong.

In any case, getRange is a method of Spreadsheet, not of File. You need SpreadsheetApp.openById to get a Spreadsheet object instead of DriveApp.getFileById which returns a File object.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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