我用一个脚本把我所有的文件从驱动器到一个谷歌电子表格的名称，url…
我的问题是有很多文件和脚本在30分钟内运行，并且超过了应用程序脚本的时间权限。
如何加快这个脚本的速度?
function listFilesAndFolders() {
var folderid = " "; // change FolderID
var sh = SpreadsheetApp.getActiveSheet();
sh.clear();
sh.appendRow(["parent","folder", "name", "update", "Size", "URL", "ID", "description", "type"]);
try {
var parentFolder =DriveApp.getFolderById(folderid);
listFiles(parentFolder,parentFolder.getName())
listSubFolders(parentFolder,parentFolder.getName());
} catch (e) {
Logger.log(e.toString());
}
}
function listSubFolders(parentFolder,parent) {
var childFolders = parentFolder.getFolders();
while (childFolders.hasNext()) {
var childFolder = childFolders.next();
Logger.log("Fold : " + childFolder.getName());
listFiles(childFolder,parent)
listSubFolders(childFolder,parent + "|" + childFolder.getName());
}
}
function listFiles(fold,parent){
var sh = SpreadsheetApp.getActiveSheet();
var data = [];
var files = fold.getFiles();
while (files.hasNext()) {
var file = files.next();
data = [
parent,
fold.getName(),
file.getName(),
file.getLastUpdated(),
file.getSize(),
file.getUrl(),
file.getId(),
file.getDescription(),
file.getMimeType()
];
sh.appendRow(data);
}
}
而不是使用Sheet.appendRow()收集数组中的每一行rows，当所有循环完成时使用.getRange().setValues()。
function listFilesAndFolders() {
var folderid = " "; // change FolderID
var sh = SpreadsheetApp.getActiveSheet();
sh.clear();
let rows = [["parent","folder", "name", "update", "Size", "URL", "ID", "description", "type"]];
try {
var parentFolder =DriveApp.getFolderById(folderid);
listFiles(parentFolder,parentFolder.getName(), rows )
listSubFolders(parentFolder,parentFolder.getName(), rows );
sh.getRange(sh.getLastRow()+1,1,rows.length,rows[0].length).setValues(rows);
} catch (e) {
Logger.log(e.toString());
}
}
function listSubFolders(parentFolder,parent, rows ) {
var childFolders = parentFolder.getFolders();
while (childFolders.hasNext()) {
var childFolder = childFolders.next();
Logger.log("Fold : " + childFolder.getName());
listFiles(childFolder,parent, rows )
listSubFolders(childFolder,parent + "|" + childFolder.getName(), rows );
}
}
function listFiles(fold,parent, rows ){
var sh = SpreadsheetApp.getActiveSheet();
var files = fold.getFiles();
while (files.hasNext()) {
var file = files.next();
rows.push([
parent,
fold.getName(),
file.getName(),
file.getLastUpdated(),
file.getSize(),
file.getUrl(),
file.getId(),
file.getDescription(),
file.getMimeType()
]);
}
}
我相信你的目标是这样的。
- 你想减少脚本的处理成本。
修改点:
- 在您的脚本中，
appendRow用于循环。在这种情况下，过程成本变得很高。Ref: Author me - 我认为当使用驱动API时，过程成本可能会减少一点。
当这些要点反映在您的脚本中时，如何进行以下修改?本次修改使用的是Google Apps Script库。这个Google Apps脚本库的作者是我。
用法:
1。安装Google Apps Script库
你可以在这里看到如何安装谷歌应用程序脚本库[FilesApp]。
2。启用Drive API
修改后的脚本使用Drive API。因此，请在高级谷歌服务中启用驱动器API。
3。示例脚本。
请将以下脚本复制粘贴到电子表格的脚本编辑器中。并且，请将顶部文件夹ID设置为folderId。如果你使用var folderId = "root";，所有文件在你的谷歌驱动器检索。
function myFunction() {
var folderId = "###"; // Please set the top folder ID.
var header = ["parent", "folder", "name", "update", "Size", "URL", "ID", "description", "type"]; // This is from your script.
var obj = FilesApp.createTree(folderId, null, "files(name,modifiedTime,size,webViewLink,id,description,mimeType)");
var values = [header, ...obj.files.flatMap(({ folderTreeByName, filesInFolder }) => {
const f = [folderTreeByName.join("|"), folderTreeByName.pop()];
return filesInFolder.length == 0 ? [[...f, ...Array(7).fill(null)]] : filesInFolder.filter(({ mimeType }) => mimeType != MimeType.FOLDER).map(({ name, modifiedTime, size, webViewLink, id, description, mimeType }) => [...f, name || null, new Date(modifiedTime), size || 0, webViewLink, id, description || null, mimeType]);
})];
SpreadsheetApp.getActiveSheet().clear().getRange(1, 1, values.length, values[0].length).setValues(values);
}
引用:
- FilesApp of Google Apps Script library (Author me)
- Files: Drive API v3列表
这将列出您的驱动器中的所有文件和文件夹到Sheet1
var level = 1;
function getFnF1(folder = DriveApp.getRootFolder()) {
let tree = JSON.parse(PropertiesService.getScriptProperties().getProperty('FnF'));
//Logger.log(JSON.stringify(tree));
if (tree.level < level) {
tree.level = level;
PropertiesService.getScriptProperties().setProperty('FnF', JSON.stringify(tree));
}
const files = folder.getFiles();
let row = Array.from([...Array(level).keys()], ((x, i) => { if (i == level - 1) { x = folder.getName(); } else { x = ''; } return x; }));
tree.txt.push(row);
row = Array.from([...Array(level).keys()], ((x, i) => { if (i == level - 1) { x = 'bold'; } else { x = 'normal'; } return x; }));
tree.fwt.push(row);
PropertiesService.getScriptProperties().setProperty('FnF', JSON.stringify(tree));
if (files.hasNext()) {
let row = Array.from([...Array(level).keys()], ((x, i) => { if (i == level - 1) { x = 'Files:'; } else { x = ''; } return x; }));
tree.txt.push(row);
tree.fwt.push(['normal']);
PropertiesService.getScriptProperties().setProperty('FnF', JSON.stringify(tree));
}
while (files.hasNext()) {
let file = files.next();
let row = Array.from([...Array(level + 1).keys()], ((x, i) => { if (i == level) { x = file.getName(); } else { x = ''; } return x; }));
tree.txt.push(row);
tree.fwt.push(['normal']);
PropertiesService.getScriptProperties().setProperty('FnF', JSON.stringify(tree));
}
const subfolders = folder.getFolders()
while (subfolders.hasNext()) {
let subfolder = subfolders.next();
level++;
getFnF1(subfolder);
}
level--;
}
function getFilesAndFolders1() {
const fldr = null;
const ss = SpreadsheetApp.getActive();
ss.toast("Entry");
const sh = ss.getSheetByName('Sheet1');
sh.clearContents();
SpreadsheetApp.flush();
PropertiesService.getScriptProperties().setProperty('FnF', JSON.stringify({ txt: [], fwt: [], level: 0 }));
getFnF1();
//Logger.log(PropertiesService.getScriptProperties().getProperty('FnF'));
let tree = JSON.parse(PropertiesService.getScriptProperties().getProperty('FnF'));
const l = tree.level + 1
tree.txt.forEach(r => {
if (r.length < l) {
//Array.from(Array(l - r.length).keys()).forEach(e => r.push(''));
r.splice(r.length, 0, ...Array(l - r.length).fill(''));
}
});
tree.fwt.forEach(r => {
if (r.length < l) {
//Array.from(Array(l - r.length).keys()).forEach(e => r.push('normal'));
r.splice(r.length, 0, ...Array(l - r.length).fill('normal'));
}
});
//Logger.log(JSON.stringify(tree));
sh.getRange(1, 1, tree.txt.length, tree.level + 1).setValues(tree.txt);
sh.getRange(1, 1, tree.fwt.length, tree.level + 1).setFontWeights(tree.fwt);
PropertiesService.getScriptProperties().deleteProperty('FnF');
ss.toast("EOF");
}