Copy A Spreadsheet File To Google Drive And Replace All The Formulas With Values

I have a script which copys/clones an Google Spreadsheet and all tabs, which works fine but it I would like it to paste as values. Here is what i have at the moment function cloneG

Solution 1:

Answer :

The comments provide an explanation of each step:

functioncloneGoogleSheet() {
  // get the destination folderconst destFolder = DriveApp.getFolderById("your_drive_folder_id"); 
  // make a copy of a spreadsheet fileconst file = DriveApp.getFileById("your_drive_source_file_id")
                       .makeCopy("particular_file_name", destFolder); 
  // get the spreadsheet file you just createdconst ss = SpreadsheetApp.openById(file.getId());
  // get all the sheets of the spreadsheet fileconst sheets = ss.getSheets();
  // for every sheet copy only the contents
  sheets.forEach(sh=>{
     let rng = sh.getDataRange();
     rng.copyTo(rng, {contentsOnly:true});
     SpreadsheetApp.flush();             
  });
}

Please note:

If the values are coming from an importrange formula then you need to manually allow it before you overwrite the sheet with the values. Because if you don't allow it, then importrange does not return anything.

In this case:

• run the version of your script (in your original question)

• go to the newly created spreadsheet and manually allow the importranges

• run the following script:

  functioncopyValues(){
     const ss = SpreadsheetApp.getActive(); // in case you execute the script 
     bound to the spreadsheet
     //  const ss = SpreadsheetApp.openByUrl("spreadsheet_url") // in case you 
     run it from standalone or other script
     // get all the sheets of the spreadsheet fileconst sheets = ss.getSheets();
     // for every sheet copy only the contents
     sheets.forEach(sh=>{
       let rng = sh.getDataRange();
       rng.copyTo(rng, {contentsOnly:true});
       SpreadsheetApp.flush();             
  });
  }
  

If you execute this code from the newly created spreadsheet file, namely you have a script bound to that, then you can use SpreadsheetApp.getActive(). If you run the code from a standalone script or a script bound to another spreadsheet, then you need to specify the url of the newly created spreadsheet, therefore you can use either openById or openByUrl as in the example above.