将电子表格文件复制到Google云端硬盘，然后将所有公式替换为值 [英] Copy a spreadsheet file to Google Drive and replace all the formulas with values

问题描述

我有一个脚本可以复制/克隆Google Spreadsheet和所有选项卡，可以正常工作，但我希望将其粘贴为值.

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.

这是我目前所拥有的

function cloneGoogleSheet() {
  var destFolder = DriveApp.getFolderById("your_drive_folder_id"); 
  DriveApp.getFileById("your_drive_source_file_id").makeCopy("particular_file_name", destFolder); 
}

推荐答案

解决方案:

这些注释提供了每个步骤的说明:

Solution:

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();             
  });
}

请注意:

如果这些值来自 importrange 公式，那么您需要手动允许它，然后再用这些值覆盖工作表.因为如果您不允许这样做，那么 importrange 不会返回任何内容.

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.

在这种情况下:

• 运行脚本的版本(在原始问题中)

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

转到新创建的电子表格，并手动允许 importranges

go to the newly created spreadsheet and manually allow the importranges

运行以下脚本:

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();             
});
}

如果从新创建的电子表格文件执行此代码，即绑定了脚本，则可以使用 SpreadsheetApp.getActive().如果从独立脚本或绑定到另一个电子表格的脚本运行代码，则需要指定新创建的电子表格的 url ，因此可以使用 openById 或如上例所示的 openByUrl .

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.

这篇关于将电子表格文件复制到Google云端硬盘，然后将所有公式替换为值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！