优化Google表格.appendRow（） [英] Optimizing Google Sheets .appendRow()
问题描述
我一直在创建脚本来遍历文件夹结构，并将特定数据复制到Google表格中的行。文件夹结构与此相似
- 根文件夹
- 街道姓名1
- 102
- 104
- etc $
- 街道名称2
$ b- 98-110
$ b
- 街道姓名1
经过一番努力，我想出了这个代码
// GetFolders函数通过第一级和第二级子文件夹
使用while循环
//需要folderID字符串，存储数组，以及作为参数传递的
活动表
函数GetFolders（folderID，dataArray，sheet）
{
var rootFolder = DriveApp.getFolderById（folderID） .getFolders（）;
while（rootFolder.hasNext（））
{
var subFolder = rootFolder.next（）;
var subFolderName = subFolder.getName（）;
var subFolderID = subFolder.getId（）;
//将第一个子文件夹名追加到数组
dataArray [0] = subFolderName;
var subFolderIterator = DriveApp.getFolderById（subFolderID）.getFolders（）;
while（subFolderIterator.hasNext（））
{
var subFolder2 = subFolderIterator.next（）;
var subFolder2Name = subFolder2.getName（）;
//将第二个子文件夹名追加到数组
dataArray [1] = subFolder2Name;
//追加第二个子文件夹超链接到数组
dataArray [2] = subFolder2.getUrl（）;
//默认标志栏空白字符串
dataArray [3] ='';
//检查subFolder2中名称为
的连字符if（subFolder2Name.indexOf（' - '）> -1）//如果连字符存在于文件夹名称
{
//将标志附加到dataArray
dataArray [3] ='X';
} // end if
//将数组内容写入电子表格中的下一个空行
sheet.appendRow（dataArray）;
} // end while
} // end while
} // end GetFolders（）
起初，这看起来非常奏效，在达到超时限制之前写了大约3,500行。但是，我有超过12,000行需要写入（最好是一次执行）。查看谷歌的最佳实践页面告诉我，这可能会更有效率，但我不知道如何去做这件事。从我的研究看来，配料似乎是我最好的选择。有谁知道在我的脚本中实现这一点的好方法吗？
您可以通过使用FileIterator的继续令牌来保存状态PropertiesService。下面的脚本检查脚本是否接近5分钟的时间配额限制，保存连续标记并创建一个触发器，让设置时间后函数再次运行：
function processFiles（）{
var files，continuationToken;
var sheet = SpreadsheetApp.openById（SHEET_ID）
.getSheets（）[0];
var startTimeInMillisec = new Date（）。getTime（）;
var triggers = ScriptApp.getProjectTriggers（）;
for（var i = 0; i< triggers.length; i ++）{//删除此项目的以前创建的触发器以避免混乱
ScriptApp.deleteTrigger（triggers [一世]）; $！
if（！PropertiesService.getScriptProperties（）。getProperty（continuationToken））{//如果没有保存标记，从头开始
files = DriveApp.getFiles（）;
$ b} else {
continuationToken = PropertiesService.getScriptProperties（）
.getProperty（continuationToken）;
文件= DriveApp.continueFileIterator（continuationToken）; //将继续令牌传递给DriveApp以继续迭代我们离开的位置
}
while（files.hasNext（））{
var file = file.next（）;
//在此处理文件
var elapsedTimeInMillisec = new Date（）。getTime（） - startTimeInMillisec;
if（elapsedTimeInMillisec> 290000）{//检查我们是否接近5分钟的时间限制
continuationToken = files.getContinuationToken（）;
PropertiesService.getScriptProperties（）
.setProperty（continuationToken，continuationToken）;
ScriptApp.newTrigger（processFiles）//设置函数在5分钟后再次执行
.timeBased（）
.after（300000）
.create （）;
return;
}
}
}
I have been creating a script to iterate through a folder structure and copy specific data to rows in a Google Sheet. The folder structure is similar to this
- Root folder
- Street Name 1
- 102
- 104
- etc
- Street Name 2
- 98-110
- Street Name 1
After some work, I came up with this code
// GetFolders function goes through first and second level subfolder
using while loop
// Requires folderID string, an array for storage, and an active sheet to be
passed as arguments
function GetFolders(folderID, dataArray, sheet)
{
var rootFolder = DriveApp.getFolderById(folderID).getFolders();
while (rootFolder.hasNext())
{
var subFolder = rootFolder.next();
var subFolderName = subFolder.getName();
var subFolderID = subFolder.getId();
//append first subfolder name to array
dataArray[0] = subFolderName;
var subFolderIterator = DriveApp.getFolderById(subFolderID).getFolders();
while (subFolderIterator.hasNext())
{
var subFolder2 = subFolderIterator.next();
var subFolder2Name = subFolder2.getName();
//append second subfolder name to array
dataArray[1] = subFolder2Name;
//append second subfolder hyperlink to array
dataArray[2] = subFolder2.getUrl();
//default flag column to blank string
dataArray[3] = '';
//check subFolder2 for hyphen in name
if (subFolder2Name.indexOf('-') > -1) //if a hyphen is present in foldername
{
//append flag to dataArray
dataArray[3] = 'X';
} //end if
//write array contents to next empty row in spreadsheet
sheet.appendRow(dataArray);
} //end while
} //end while
} //end GetFolders()
At first, this appeared to work wonderfully, writing about 3,500 rows before hitting the timeout limit. However, I have over 12,000 rows that need to be written (preferrably with one single execution). Looking at Google's "Best Practices" page tells me that this can be more efficient, but I'm at a loss with how to go about doing this. From my research, it seems that batching would be my best option. Does anyone know a good way to implement this in my script?
You can persist states by saving continuation token of the FileIterator using PropertiesService. The script below checks if the script is nearing the 5-minute time quota limit, saves the continuation token and creates a trigger for the function to run again after set time:
function processFiles() {
var files, continuationToken;
var sheet = SpreadsheetApp.openById(SHEET_ID)
.getSheets()[0];
var startTimeInMillisec = new Date().getTime();
var triggers = ScriptApp.getProjectTriggers();
for (var i=0; i < triggers.length; i++) { //delete previously created triggers for this project to avoid clutter
ScriptApp.deleteTrigger(triggers[i]);
}
if (!PropertiesService.getScriptProperties().getProperty("continuationToken")) { //if no token was saved, start from the beginning
files = DriveApp.getFiles();
} else {
continuationToken = PropertiesService.getScriptProperties()
.getProperty("continuationToken");
files = DriveApp.continueFileIterator(continuationToken); //pass continuation token to DriveApp to continue iterating where we left off
}
while (files.hasNext()) {
var file = file.next();
// process file here
var elapsedTimeInMillisec = new Date().getTime() - startTimeInMillisec;
if (elapsedTimeInMillisec > 290000) { //check if we are nearing the 5 minute time limit
continuationToken = files.getContinuationToken();
PropertiesService.getScriptProperties()
.setProperty("continuationToken", continuationToken);
ScriptApp.newTrigger("processFiles") //set the function to execute again after 5 minutes
.timeBased()
.after(300000)
.create();
return;
}
}
}
这篇关于优化Google表格.appendRow（）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
