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:

function cloneGoogleSheet() {
  // get the destination folder
  const destFolder = DriveApp.getFolderById("your_drive_folder_id"); 
  // make a copy of a spreadsheet file
  const file = DriveApp.getFileById("your_drive_source_file_id")
                       .makeCopy("particular_file_name", destFolder); 
  // get the spreadsheet file you just created
  const ss = SpreadsheetApp.openById(file.getId());
  // get all the sheets of the spreadsheet file
  const 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:

  function copyValues(){
     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 file
     const 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.