Google表格脚本-将整个外部电子表格复制到当前电子表格 [英] Google Sheets Script - copy entire external spreadsheet to current spreadsheet

问题描述

我有一个每周更新的Google电子表格.它包含多个工作表，每个工作周的名称不同.

是否可以使用Google脚本将所有表格从该电子表格复制到另一个电子表格?我的主要目标是复制整个电子表格，并使运行脚本的工作表具有所有工作表和源工作表中的数据.如果此复制过程仅复制数据，而不复制另一张工作表中的公式，那也很好.

预先感谢！

埃里克

解决方案

我已将此代码编写为从源工作表运行，但是可以很容易地将其改编为反过来运行.这样只会得到单元格值而不是公式，并且当前它会创建一个新电子表格并附加日期.

function copyEntireSpreadsheet(){
  var ss,ssName,sheet,sheetName,data,destination
  ss = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  ssName = SpreadsheetApp.getActive().getName();
  destination = SpreadsheetApp.create(ssName + " - " + new Date().toLocaleString());
  for (var i = 0; i < ss.length; i++){
    sheet = ss[i];
    sheetName = sheet.getSheetName();
    data = sheet.getSheetValues(1, 1, sheet.getLastRow(), sheet.getLastColumn());
    destination.insertSheet(sheetName)
    destination.getSheets()[i].getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).setValues(data);
  }
}

由于它在循环中插入了一张纸，因此在脚本运行结束时会得到一张空白纸，但是可以手动删除该空白纸，也可以在函数关闭之前在循环的末尾将其删除.

I have google spreadsheet that updates weekly. It contains multiple sheets, which have different names from week to week.

Is there a way using google scripts to copy all of the sheets from that spreadsheet to another spreadsheet? My main goal is to copy the entire spreadsheet, and have the worksheet that I run the script from have all of the sheets and data from the source sheet. It would also be great if this copy process only copied the data, and not the formulas from the other sheet.

Thanks in Advance!

Eric

解决方案

I've written this to be ran from the source sheet but it can be easily adapted to run the other way round. This will only get the cell values not formulas and currently it creates a new spreadsheet with the date appended.

function copyEntireSpreadsheet(){
  var ss,ssName,sheet,sheetName,data,destination
  ss = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  ssName = SpreadsheetApp.getActive().getName();
  destination = SpreadsheetApp.create(ssName + " - " + new Date().toLocaleString());
  for (var i = 0; i < ss.length; i++){
    sheet = ss[i];
    sheetName = sheet.getSheetName();
    data = sheet.getSheetValues(1, 1, sheet.getLastRow(), sheet.getLastColumn());
    destination.insertSheet(sheetName)
    destination.getSheets()[i].getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).setValues(data);
  }
}

Because it inserts a sheet inside the loop you will get one blank sheet at the end of the script run but this could be deleted manually or at the end of the loop before the function closes.

这篇关于Google表格脚本-将整个外部电子表格复制到当前电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！