克隆具有公式/导入范围的Googlsheet [英] Clone Googlsheet which has formulas/importranges

问题描述

我想克隆/复制一个Google工作表并将其放置在驱动器中，我目前具有的脚本适用于该元素，但由于公式/导入范围，其内容显示为空白

I would like to clone/copy a google sheet and locate it in a drive, The script i have at the moment is working for this element but the contents appear blank becuase of formulas/importranges

请帮助您.当前脚本是

function cloneGoogleSheet2() {
  const destFolder = DriveApp.getFolderById("xxxxxxxxxxx"); 
   const file = DriveApp.getFileById("xxxxxxxxxxxxxxx").makeCopy("xxxxxxxxx", destFolder); 
  const ss = SpreadsheetApp.openById(file.getId());
  const sheets = ss.getSheets();
  sheets.forEach(sh=>{
     let rng = sh.getDataRange();
     rng.copyTo(rng, {contentsOnly:true});
     SpreadsheetApp.flush();             
  });
}

参考下面类似的问题

推荐答案

说明:

不幸的是:

不能以编程方式允许

• importranges .因此，您需要设置从源电子表格中获取值，然后将其粘贴到新创建的(目标)电子表格中.

• importranges can not be allowed programmatically. So you need to set get the values from the source spreadsheet and paste them to the newly created (target) spreadsheet.

copyTo ，因此可以改用 getValues 和 setValues .

逻辑是遍历源工作表，并为每张工作表获取值并将其复制到相应的目标工作表中.

The logic is to iterate over the source sheets and for every sheet get values and copy them to the corresponding target sheets.

function cloneGoogleSheet2() {
  const destFolder = DriveApp.getFolderById("folder_id"); 
  const source_id = "spreadsheet_id_to_copy";
  const source_ss = SpreadsheetApp.openById(source_id);
  const file = DriveApp.getFileById(source_id).makeCopy("new_file", destFolder);
  const target_ss = SpreadsheetApp.openById(file.getId());
  const source_sheets = source_ss.getSheets();
  const target_sheets = target_ss.getSheets();
  source_sheets.forEach((sh,i)=>{
     let values = sh.getDataRange().getValues();
     target_sheets[i].getDataRange().setValues(values);
     SpreadsheetApp.flush();
  })
}

这篇关于克隆具有公式/导入范围的Googlsheet的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！