从电子表格复制到具有动态标题的另一个电子表格 [英] copy from spreadsheet to another spreadsheet with dynamic title
问题描述
我目前正在一个项目中,我需要将数据从电子表格中的一个特定工作表复制到另一个具有动态标题(带有日期的文本)的电子表格中.可以使用ID或名称移动数据,但是使用新创建的电子表格似乎很麻烦.所有功能都将在同一个应用脚本中:创建复制
I'm currently working on a project where to I need to copy data from one specific sheet in a spreadsheet to another spreadsheet with a dynamic title ( text with date). moving the data with ID or by name is ok but with a freshly created spreadsheet seems tuff. all the function will be in the same app script: creation copy
function titleAsDate() {
var currentDate = Utilities.formatDate(new Date(), "GMT+8", "dd-MM-yyyy HH:mm:ss")
SpreadsheetApp.create("Report of the " + currentDate)
}
function copyWithValues() {
let spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
let sourceSheet = spreadSheet.getSheetByName('Sources');
let sourceRange = sourceSheet.getDataRange();
let sourceValues = sourceRange.getValues();
let rowCount = sourceValues.length;
let columnCount = sourceValues[0].length;
let targetSheet = spreadSheet.getSheetById('Target');
let targetRange = targetSheet.getRange(1, 1, rowCount, columnCount);
targetRange.setValues(sourceValues);
}
推荐答案
说明:
-
SpreadsheetApp.create(name)返回电子表格对象,以便您可以直接使用无需额外的代码即可直接使用此功能.
Explanation:
SpreadsheetApp.create(name) returns a spreadsheet object so you can directly use the output of this function directly without the need of extra code.
当您手动创建新的电子表格文件时,新生成的电子表格将具有一张名为
Sheet1
的工作表.因此,您可以使用 sheet.setName(name)函数将工作表的名称更改为Target
.此函数还会返回 sheet 对象(targetSheet
),然后可以用来设置值.The newly generated spreadsheet will have one sheet with the name
Sheet1
as when you manually create a new spreadsheet file. Therefore, you can use the sheet.setName(name) function to change the name of the sheet toTarget
. Also this function returns a sheet object (targetSheet
) which can then be used to set the values.这是一个功能中的所有代码:
This is all the code in one function:
function copyWithValues() { const spreadSheet = SpreadsheetApp.getActiveSpreadsheet(); const sourceSheet = spreadSheet.getSheetByName('Sources'); const sourceRange = sourceSheet.getDataRange(); const sourceValues = sourceRange.getValues(); const currentDate = Utilities.formatDate(new Date(), "GMT+8", "dd-MM-yyyy HH:mm:ss"); // new code const targetSpreadsheet = SpreadsheetApp.create("Report of the " + currentDate); // new code let rowCount = sourceValues.length; let columnCount = sourceValues[0].length; let targetSheet = targetSpreadsheet.getSheetByName('Sheet1').setName("Target"); // new code let targetRange = targetSheet.getRange(1, 1, rowCount, columnCount); targetRange.setValues(sourceValues); }
如果要将
titleAsDate
用作帮助函数,而该函数将由copyWithValues
调用,则可以使用此代码并仅执行copyWithValues
:If you want to use
titleAsDate
as a helper function which will be called bycopyWithValues
, then you can use this code and execute onlycopyWithValues
:// helper function, used by copyWithValues function titleAsDate() { const currentDate = Utilities.formatDate(new Date(), "GMT+8", "dd-MM-yyyy HH:mm:ss"); return SpreadsheetApp.create("Report of the " + currentDate); // new code } // main function, you should execute this function function copyWithValues() { const spreadSheet = SpreadsheetApp.getActiveSpreadsheet(); const sourceSheet = spreadSheet.getSheetByName('Sources'); const sourceRange = sourceSheet.getDataRange(); const sourceValues = sourceRange.getValues(); const targetSpreadsheet = titleAsDate(); // new code let rowCount = sourceValues.length; let columnCount = sourceValues[0].length; let targetSheet = targetSpreadsheet.getSheetByName('Sheet1').setName("Target"); // new code let targetRange = targetSheet.getRange(1, 1, rowCount, columnCount); targetRange.setValues(sourceValues); }
这篇关于从电子表格复制到具有动态标题的另一个电子表格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!