复制整个电子表格，仅保留值 [英] Copy an entire Spreadsheet, Just Preserve the Values

问题描述

我想将包含20多个工作表的整个电子表格复制到云端硬盘中的其他位置；但是，我只想保留每个单元格中的硬值和格式，而不是公式(基本上只是对值进行快照).我一直在研究如何写这篇文章，但是我对什么是最好的方法没有一个明确的想法.我刚刚开始学习条件查询，例如在我的Google表格培训中循环播放，任何帮助将不胜感激.谢谢！

I want to copy an entire spreadsheet of some 20+ sheets to a different location in Drive; however, I only want to preserve the hard values and formatting in each cell, and not the formulas (basically just taking a snapshot of the values). I have been playing around with how to write this but I don't have a solid idea on what's the best approach. I'm just starting to learn conditionals like looping in my google sheets training, any help would be appreciated. Thanks!

绿色的那些单元格都是vlookup，它们从我在另一个电子表格中拥有的数组更新.想法是获取数组中所有正确的数据，让该工作表完全填入当天正确的值，然后最好将其另存为google工作表，但仅保存这些值，以便在存在该事实之后可以对其进行编辑是数组数据中的错误.

Those cells in green are all vlookups and they update from an array I have on another spreadsheet. The idea is to get all the right data in the array, have this sheet fully fill out with the correct values for the day, then save it preferably as a google sheet but just the values, so that they are editable after the fact if there was an error in the array data.

推荐答案

• 您要将源电子表格复制到Google云端硬盘中的特定文件夹中.
• 您要使用在名称上加盖日期的日期作为文件名.
• 您不想复制公式.您只想复制显示值.
• 在电子表格中，包含了许多vlookups和其他外部参考单元格的公式.
• 您想使用Google Apps脚本实现这一目标.
• You want to copy the source Spreadsheet in the specific folder in your Google Drive.
• You want to use a date stamped on it for a name as the filename.
• You don't want to copy the formulas. You want to copy only displaying values.
• In your Spreadsheet, the formulas of many vlookups and other outside reference cells are included.
• You want to achieve this using Google Apps Script.

如果我的理解是正确的，那么这个答案如何?请认为这只是几个可能的答案之一.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

用于上述目标的示例脚本的流程如下.

The flow of the sample script for the above goal is as follows.

1. 将源电子表格中的所有工作表复制为临时工作表.
2. 在复制的图纸上，单元格仅被值覆盖.这样，可以删除公式.
3. 复制源电子表格.
4. 删除源电子表格中的临时表.
5. 删除目标电子表格中的原始工作表.
6. 将复制的电子表格移动到特定文件夹.

示例脚本:

在运行脚本之前，请设置源电子表格ID和目标文件夹ID.

Sample script:

Before you run the script, please set the source Spreadsheet ID and the destination folder ID.

function myFunction() {
  var spreadsheetId = "###"; // Please set the source Spreadsheet ID.
  var destFolderId = "###";  // Please set the destination folder ID.

  // Copy each sheet in the source Spreadsheet by removing the formulas as the temporal sheets.
  var ss = SpreadsheetApp.openById(spreadsheetId);
  var tempSheets = ss.getSheets().map(function(sheet) {
    var dstSheet = sheet.copyTo(ss).setName(sheet.getSheetName() + "_temp");
    var src = dstSheet.getDataRange();
    src.copyTo(src, {contentsOnly: true});
    return dstSheet;
  });
  
  // Copy the source Spreadsheet.
  var destination = ss.copy(ss.getName() + " - " + new Date().toLocaleString());
  
  // Delete the temporal sheets in the source Spreadsheet.
  tempSheets.forEach(function(sheet) {ss.deleteSheet(sheet)});
  
  // Delete the original sheets from the copied Spreadsheet and rename the copied sheets.
  destination.getSheets().forEach(function(sheet) {
    var sheetName = sheet.getSheetName();
    if (sheetName.indexOf("_temp") == -1) {
      destination.deleteSheet(sheet);
    } else {
      sheet.setName(sheetName.slice(0, -5));
    }
  });

  // Move file to the destination folder.
  var file = DriveApp.getFileById(destination.getId());
  DriveApp.getFolderById(destFolderId).addFile(file);
  file.getParents().next().removeFile(file);
}

注意:

• 在此示例脚本中，使用了此答案的脚本.
• 这是一个简单的示例脚本.因此，请根据您的实际情况对此进行修改.
• 副本(名称)
• copyTo(目标，选项)
• map()
• forEach()
• addFile()
• removeFile()

这篇关于复制整个电子表格，仅保留值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！