Google-sheets – Copying file with cell data only

google sheetsgoogle-apps-script

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:

https://webapps.stackexchange.com/questions/49952/how-to-paste-a-cell-range-automatically-values-only/116120#116120

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');
  copyValuesOnly(file);
  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")

Best Answer

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.

Related Solutions

Google-sheets – How to pass a range into a custom function in Google Spreadsheets

So I've searched long and hard for a good answer to this and here is what I have found:

an unmodified range parameter passes in the values of cells in the range, not the range itself (as Gergely explained) ex: when you do this =myFunction(a1:a2)
to use a range in your function you need to first pass the range in as a string (ex: =myFunction("a1:a2") ), then turn it into a range with the following code inside the function:
```
Function myFunction(pRange){
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var range = sheet.getRange(pRange);
}
```
Finally, if you want the benefits of passing in a range (like intelligently copying range references to other cells) AND you want to pass it in as a string, you have to use a default function that accepts a range as a parameter and outputs a string you can use. I detail both ways I have found below, but I prefer the 2nd.

For all Google spreadsheets:
=myFunction(ADDRESS(row(A1),COLUMN(A1),4)&":"&ADDRESS(row(A2),COLUMN(A2),4))

For the new Google Sheets:

=myFunction(CELL("address",A1)&":"&CELL("address",A2))

Google-sheets – Create a function to copy range (values only) to a new tab at specified intervals

I wasn't quite sure what you meant by Range as an input to your custom function. Is this the source range or destination range?

Anyways, this is something that should hopefully get you started. There is no timer element, nor is it a custom function. But can be converted to one easily.

The manual timer element can be achieved by adding a trigger. In script editor, go to resources, current project triggers. You can attach a script to a date trigger of your choosing.

function backup(){
/* Edit the vars below this line for your needs */
var sourceSheet  = "Sheet2" ;  // Enter the name of the sheet with the source data
var sourceRange = "A1:B20" ; // Enter the range of the cells with the source data
var targetSheet = "Sheet3" ; // Enter the name of the target sheet  
var targetRange = "A2:B21" ; // Enter the range of cells you wish to copy data to. Note this must be same size as source range.
/* No need to edit below this point */  


var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(sourceSheet);
var values = sheet.getRange(sourceRange).getValues();
ss.getSheetByName(targetSheet).getRange(targetRange).setValues(values);

}

Best Answer

Related Solutions

Google-sheets – How to pass a range into a custom function in Google Spreadsheets

Google-sheets – Create a function to copy range (values only) to a new tab at specified intervals

Related Topic