在 Google Apps 脚本中将整个工作表替换为另一个工作表

我想要做的是将电子表格中特定工作表中的信息存储起来，并在每天结束时将其复制到第二个电子表格中。第二个电子表格将根据复制的信息运行复杂的数据透视表和报告，而无需全天运行。

我可以设置一个时间驱动触发器 https://developers.google.com/apps-script/guides/triggers/installable#time-driven_triggers它将每天在一小时内运行该作业。

我正在编写以下脚本，该脚本使用SpreadsheetApp.getActiveSpreadsheet https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#getActiveSpreadsheet()获取当前电子表格。然后获取要备份的单个工作表spreadsheet.getSheetByName https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getSheetByName(String)。然后使用sheet.copyTo https://developers.google.com/apps-script/reference/spreadsheet/sheet#copyTo(Spreadsheet)方法将当前工作表添加到新电子表格中。我通过查找 id 来获取新的电子表格SpreadsheetApp.openById https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#openById(String)都是这样的：

function startBackupJob() {

  var currentSpreadSheet = SpreadsheetApp.getActiveSpreadsheet()
  var masterSheet = currentSpreadSheet.getSheetByName("Sheet1")

  var backupSpreadSheetId = "#######################################";
  var backupSpreadSheet = SpreadsheetApp.openById(backupSpreadSheetId);

  // var backupSheet = backupSpreadSheet.getSheetByName("Sheet1");
  // backupSpreadSheet.deleteSheet(backupSheet);

  masterSheet.copyTo(backupSpreadSheet).setName("Sheet1");

}

我遇到的问题是copyTo将创建一个新的工作表而不是覆盖现有的电子表格。移动到新工作簿的目的是根据数据运行数据透视表，而不是重新连接它们以指向新工作表。

我可以删除上一张工作表，为新工作表腾出空间，但这也会杀死数据透视表上的引用，因此没有多大帮助。

有没有一种简单的方法可以将一个工作表的全部内容转移到另一个工作表？

这类似于（但不同于）以下问题：

• 如何编写脚本将电子表格的备份副本复制到存档文件夹？ https://stackoverflow.com/q/11839952/1366033- 但是，我不想移动整个文件，而是移动电子表格中的特定工作表。
• 如何使用谷歌脚本将详细信息表复制到另一个电子表格 https://stackoverflow.com/q/17099497/1366033 & 将一个电子表格复制到另一个电子表格并设置格式 https://stackoverflow.com/q/19791132/1366033- 但是复制会生成一个新工作表，而我需要替换现有工作表的内容
• 脚本，每天在特定时间将单元格从一张纸复制到另一张纸 https://stackoverflow.com/q/24500761/1366033- 但是，我确实想替换整个工作表，而不仅仅是工作表中的特定单元格。

Update

我也许可以通过打电话来做到这一点getRange https://developers.google.com/apps-script/reference/spreadsheet/sheet#getRange(Integer,Integer,Integer,Integer)在每张纸上然后使用getValues https://developers.google.com/apps-script/reference/spreadsheet/range#getValues() and setValues https://developers.google.com/apps-script/reference/spreadsheet/range#setValues(Object)像这样：

var currentValues = masterSheet.getRange(1, 1, 50, 50).getValues()
backupSheet.getRange(1, 1, 50, 50).setValues(currentValues)

但我担心主表具有与备份表不同的可用范围的边缘情况。我也不想在该范围内进行硬编码，而是希望它包含整个工作表。如果我打电话.getRange("A:E")那么两个工作表必须具有完全相同的行数，这是不可能的。

您的更新已经完成了大约 90%。诀窍是在将数据复制到目标工作表之前明确检查目标工作表的大小。例如，如果我做了这样的事情：

var cromulentDocument = SpreadsheetApp.getActiveSpreadsheet();
var masterSheet = cromulentDocument.getSheetByName('master');
var logSheet = cromulentDocument.getSheetByName('log');
var hugeData = masterSheet.getDataRange().getValues();
var rowsInHugeData = hugeData.length;
var colsInHugeData = hugeData[0].length;

/* cross fingers */
logSheet.getRange(1, 1, rowsInHugeData, colsInHugeData).setValues(hugeData);

...那么我的成功将完全取决于是否logSheet至少有那么大masterSheet。这是显而易见的，但更重要的是，如果logSheet is bigger那么边缘就会留下一些旧垃圾。不好。

让我们尝试一些别的东西。和以前一样，我们将获取主数据，但我们也会调整大小logSheet。如果我们不关心logSheet太大了，我们可能可以clear()其中的数据，但让我们保持整洁。

var cromulentDocument = SpreadsheetApp.getActiveSpreadsheet();
var masterSheet = cromulentDocument.getSheetByName('master');
var logSheet = cromulentDocument.getSheetByName('log');
var hugeData = masterSheet.getDataRange().getValues();
var rowsInHugeData = hugeData.length;
var colsInHugeData = hugeData[0].length;

/* no finger crossing necessary */
var rowsInLogSheet = logSheet.getMaxRows();
var colsInLogSheet = logSheet.getMaxColumns();

/* adjust logSheet length, but only if we need to... */
if (rowsInLogSheet < rowsInHugeData) {
  logSheet.insertRowsAfter(rowsInLogSheet, rowsInHugeData - rowsInLogSheet);
} else if (rowsInLogSheet > rowsInHugeData) {
  logSheet.deleteRows(rowsInHugeData, rowsInLogSheet - rowsInHugeData);
}

/* likewise, adjust width */
if (colsInLogSheet < colsInHugeData) {
  logSheet.insertColumnsAfter(colsInLogSheet, colsInHugeData - colsInLogSheet);
} else if (colsInLogSheet > colsInHugeData) {
  logSheet.deleteColumns(colsInHugeData, colsInLogSheet - colsInHugeData);
}

/* barring typos, insert data with confidence */
logSheet.getRange(1, 1, rowsInHugeData, colsInHugeData).setValues(hugeData);

这里发生的事情非常简单。我们计算出日志需要有多大，然后调整目标工作表的大小以匹配该数据。