英文:
How to optimize copying large amounts of data in Google Apps Script?
问题
I have translated the details you provided into Chinese. Here is the translated text:
详细信息：
我正在使用Google Apps Script函数将大量数据（超过10,000行和115列）从一个电子表格复制到另一个电子表格。该函数大约需要2分钟才能完成，有时甚至更长。我不确定我是否遵循了Google Apps Script的最佳实践，我想知道在考虑到要复制的数据量的情况下，该函数所需的时间是否正常。源表格中没有任何公式，只有文本数据。我还想知道是否使用getValues()和setValues()是操作大型表格的最佳解决方案。
另外，我需要尽可能经常地在几个电子表格中触发此函数，同时尽量减少对源表格的负担，所以减少函数运行时间对我来说非常关键。
以下是我当前正在使用的代码：
function importData() {
let ss = SpreadsheetApp.openById("源表格ID").getSheetByName("2014/2021");
let ts = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Baza danych");
let sourceData = ss.getDataRange().getValues();
let targetRange = ts.getRange(1, 1, sourceData.length, sourceData[0].length);
targetRange.setValues(sourceData);
console.log("复制完成 - Baza");
}
我尝试过的方法：
我查阅了Apps Script的最佳实践并在论坛上寻找，发现使用getValues和setValues是一个良好的做法，但我仍在寻找是否有更优化和更快速的方法来在电子表格之间传输数据。
我期望得到的结果：
我希望了解是否有更好的实践或优化方法，可以显著缩短运行此函数所需的时间，考虑到要复制的数据量。如果没有更快的方法，我想知道这是否是最快的方法，以及复制2分钟对于大数据是否正常。
英文:
Details:
I'm using a Google Apps Script function to copy a large amount of data (over 10,000 rows and 115 columns) from one spreadsheet to another. The function takes about 2 minutes to finish, sometimes even longer. I'm not sure if I'm following the best practices for Google Apps Script, and I'm wondering if this is a normal amount of time for the function to take, given the amount of data being copied. The source sheet doesn't have any formulas only text data. I'm also wondering if usage of getValues() and setValues() is the best possible solution for operations with large sheets.
Additionally, I need to trigger this function in several spreadsheets as often as possible with the minimum charge to the source sheet so that's why the reduction of function time is so crucial to me.
Here is the current code I'm using:
function importData() {
let ss = SpreadsheetApp.openById("source-sheet-id").getSheetByName("2014/2021");
let ts = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Baza danych");
let sourceData = ss.getDataRange().getValues();
let targetRange = ts.getRange(1, 1, sourceData.length, sourceData[0].length);
targetRange.setValues(sourceData);
console.log("Zakonczono kopiowanie - Baza");
}
What I've tried:
I've looked into Apps Script Best Practises and looked on forums and found that the usage of getValues and setValues is a good practice but I'm still looking to find if there is even more optimal and faster way to transfer data between sheets.
What I'm expecting:
I'm hoping to learn if there are any better practices or optimizations I can make to this function to significantly reduce the time it takes to run, given the amount of data being copied. If not I'd like to learn if that's the fastest way possible and the 2 minutes of copying are normal for large data.
答案1
得分: 0
I believe your goal is as follows.
- 您希望减少脚本的处理成本。
在这种情况下，我认为使用 Sheets API 而不是 Spreadsheet 服务（SpreadsheetApp）可能能够稍微减少处理成本。参考链接 当在您的脚本中使用 Sheets API 时，脚本如下。
修改后的脚本：
在使用此脚本之前，请在高级谷歌服务中启用 Sheets API。
function importData() {
const srcSpreadsheetId = "###"; // 请设置源电子表格的 ID。
const srcSheetName = "2014/2021";
const dstSheetname = "Baza danych";
const dstSpreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
const { values } = Sheets.Spreadsheets.Values.get(srcSpreadsheetId, srcSheetName);
// Sheets.Spreadsheets.Values.clear({}, dstSpreadsheetId, dstSheetname); // 如果要清除目标工作表，请使用此行。
Sheets.Spreadsheets.Values.update({ values }, dstSpreadsheetId, dstSheetname, { valueInputOption: "USER_ENTERED" });
console.log("Zakonczono kopiowanie - Baza");
}
注意：
- 如果您想要使用更多的单元格，例如 10,000,000 单元格，这份报告（作者：我自己）可能会有帮助。
参考资料：
英文:
I believe your goal is as follows.
- You want to reduce the process cost of your script.
In this case, I thought that when Sheets API instead of Spreadsheet service (SpreadsheetApp) is used, the process cost might be able to be reduced a little. Ref When Sheets API is used for your script, it becomes as follows.
Modified script:
Before you use this script, please enable Sheets API at Advanced Google services.
function importData() {
const srcSpreadsheetId = "###"; // Please set the source Spreadsheet ID.
const srcSheetName = "2014/2021";
const dstSheetname = "Baza danych";
const dstSpreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
const { values } = Sheets.Spreadsheets.Values.get(srcSpreadsheetId, srcSheetName);
// Sheets.Spreadsheets.Values.clear({}, dstSpreadsheetId, dstSheetname); // If you want to clear the destination sheet, please use this line.
Sheets.Spreadsheets.Values.update({ values }, dstSpreadsheetId, dstSheetname, { valueInputOption: "USER_ENTERED" });
console.log("Zakonczono kopiowanie - Baza");
}
Note:
- When you want to use more cells like 10,000,000 cells, this report (Author: me) might be useful.
References:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论