Google脚本可将电子表格中的工作表复制到新电子表格，并在特定单元格后命名新电子表格

问题描述

我有一个包含多个工作表的google电子表格，我想将每个工作表复制到一个新的电子表格中，并希望新的电子表格以特定单元格中的文本命名.我很高兴多次运行该脚本，所以我想让它复制活动工作表.

I have a google spreadsheet with multiple sheets within it, I would like to copy each individual sheet into a new spreadsheet and to have the new spreadsheet named after text in a specific cell. I am happy to run the script multiple times so I figured to have it copy the active sheet.

即我拥有的=电子表格称为颜色"-表格1 =红色"，表格2 =蓝色"，表格3 =黄色"，等等.

i.e.What I have =Spreadsheet called "Colours" - Sheet 1="red", Sheet 2= "blue", Sheet 3= "yellow", etc.

我想要的=

电子表格称为红色".电子表格称为蓝色"，电子表格称为黄色"

Spreadsheet called "Red". Spreadsheet called "blue", Spreadsheet called "yellow"

到目前为止，我有这个脚本，但是它告诉我找不到脚本功能:saveAsSpreadsheet，以获取更多信息"

So far I have this script but its telling me "Script function not found: saveAsSpreadsheet For more information"

function copyDocument() {
var ss = SpreadsheetApp.getActiveSpreadsheet(); // Get current active spreadsheet.
var sstocopy = ss.getActiveSheet(); // Get spreadsheet with DriveApp.
var sheet = ss.getActiveSheet(); // Get current active sheet.
var sheet_name = sheet.getRange("i2").getValue(); // Get the value of cell B1, used to name the new spreadsheet.
var folder = DriveApp.getFolderById("xxxxxxxxxxxxx"); // Get the ID of the folder where you will place a copy of the spreadsheet.
sstocopy.makeCopy(sheet_name,folder); // Make a copy of the spreadsheet in the destination folder.

任何帮助将不胜感激.

推荐答案

您需要将电子表格作为文件而不是电子表格打开才能使用makeCopy函数.

You need to open your spreadsheet as a file, not as a spreadsheet to be able to use makeCopy function.

因此，您代码中的这一行是不正确的:

So this line in your code is not correct:

var sstocopy = ss.getActiveSheet(); // Get spreadsheet with DriveApp.

应该是:

var sstocopy = DriveApp.getFileById(ss.getId()); // Get spreadsheet with DriveApp.

因此正确的代码如下:

function copyDocument() {
    var ss = SpreadsheetApp.getActiveSpreadsheet(); // Get current active spreadsheet.
    var sstocopy = DriveApp.getFileById(ss.getId()); // Get spreadsheet with DriveApp.
    var sheet = ss.getActiveSheet(); // Get current active sheet.
    var sheet_name = sheet.getRange("i2").getValue(); // Get the value of cell B1, used to name the new spreadsheet.
    var folder = DriveApp.getFolderById("xxxxxxxxxxxxx"); // Get the ID of the folder where you will place a copy of the spreadsheet.
    sstocopy.makeCopy(sheet_name,folder); // Make a copy of the spreadsheet in the destination folder.

回答您的评论:

出于您的目的，应按以下方式修改代码:

For your purpose the code should be modified in this way:

var sheet = SpreadsheetApp.getActiveSheet(); // Get current active sheet.
var sheet_name = sheet.getRange("i2").getValue(); // Get the value of cell B1, used to name the new spreadsheet.

var folder = DriveApp.getFolderById("xxxxxxxxxxxxx"); // Get the ID of the folder where you will place a copy of the spreadsheet.

var newSS = SpreadsheetApp.create(sheet_name); // create new blank spreadsheet in a root folder
var asFile = DriveApp.getFileById(newSS.getId()); // get new spreadsheet as a file

folder.addFile(asFile); // add this file to destination folder
DriveApp.getRootFolder().removeFile(asFile); // remove a file from root folder

var copiedSheet = sheet.copyTo(newSS); // copy active sheet to new spreadsheet
copiedSheet.setName(sheet_name); // rename copied sheet
newSS.deleteSheet(newSS.getSheetByName('Sheet1')); // remove "Sheet1" sheet which was created by default in new spreadsheet

这篇关于Google脚本可将电子表格中的工作表复制到新电子表格，并在特定单元格后命名新电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！