im trying to get files in different folders and get some cell values from them and put it in a sheet. but i get below error
Exception: Cannot retrieve the next object: iterator has reached the end
im using below code and when i run it. it returns error on line 14 it seems code returns some values from first and second folders but for third one it returns error
after running macro it gives log below:
5:24:43 PM Notice Execution started
5:24:45 PM Info [[]]
5:24:45 PM Info [[]]
5:24:46 PM Error
Exception: Cannot retrieve the next object: iterator has reached the end.
list_files_in_folders @ macros.gs:14
my code : line 14 error
function list_files_in_folders(){
var sh = SpreadsheetApp.getActiveSheet();
var mainfolder = DriveApp.getFolderById('id-here'); // I change the folder ID here
var mfolders = mainfolder.getFolders();
var data = [];
data.push(['Person Name','File Name','Value 1','Value 2']);
while (mfolders.hasNext){
var mfolder = mfolders.next();
var personfolder = DriveApp.getFolderById(mfolder.getId());
var pfiles = personfolder.getFiles();
data.push([personfolder.getName,,,]);
while(pfiles.hasNext){
var pfile = pfiles.next(); //error here
var personfile = SpreadsheetApp.openById(pfile.getId());
var value1 = personfile.getSheetValues(2,9,1,1);
//var value2 = personfile.getSheetValues();
Logger.log(value1);
data.push(["",pfile.getName,value1,""]);
}
}
sh.getRange(1,1,data.length,data[0].length).setValues(data);
}
CodePudding user response：
Folder or File hasNext() is a method, not a property.
Replace both
mfolders.hasNext
pfiles.hasNext
With
mfolders.hasNext()
pfiles.hasNext()
Reference
CodePudding user response：
You can make your list_files_in_folders() function easier to adapt by doing report building in one function and the recursive subfolder iteration in another, using closures to process the files.
More lines of code are required, but making changes becomes much easier, and you can apply these functions in other use cases more smoothly — just modify the _fileAction and _folderAction closures as required.
This pattern also lets you handle errors and timeouts more gracefully.
/** @NotOnlyCurrentDoc */
'use strict';
function list_files_in_folders() {
const mainFolder = DriveApp.getFolderById('...put folder ID here...');
const data = generateReport_(mainFolder);
SpreadsheetApp.getActiveSheet().getRange('A1')
.offset(0, 0, data.length, data[0].length)
.setValues(data);
}
/**
* Generates a report based on spreadsheets organized by subfolder.
*
* @param {DriveApp.Folder} folder A folder with files and subfolders.
* @return {String[][]} A 2D array that contains a report of files organized by subfolder.
*/
function generateReport_(folder) {
const data = [];
const _errorHandler = (error) => console.log(error.message);
const _prefix = (folderName, nestingLevel) => ' '.repeat(nestingLevel) folderName;
const _folderAction = (folder, nestingLevel) => {
data.push([_prefix(folder.getName(), nestingLevel), '', '', '']);
};
const _fileAction = (file) => {
const ss = getSpreadsheetFromFile_(file, _errorHandler);
if (ss) {
data.push(['', ss.getName(), ss.getSheetValues(2, 9, 1, 1), '']);
} else {
data.push(['', file.getName(), '(not a Google Sheet)', '']);
}
};
const timelimit = new Date().getTime() 4 * 60 * 1000; // stop when 4 minutes have passed
const error = processFilesInFolderRecursively_(folder, _folderAction, _fileAction, _errorHandler, 1, timelimit);
if (error) {
data.push([error.message, '', '', '']);
_errorHandler(error);
}
return [['Person Name', 'File Name', 'Value 1', 'Value 2']].concat(
data.length ? data : [['(Could not find any files. Check folder ID.)', '', '', '']]
);
}
/**
* Iterates files in a folder and its subfolders, executing
* _folderAction with each folder and _fileAction with each file.
*
* @param {DriveApp.Folder} folder The folder to process.
* @param {Function} _folderAction The function to run with each folder.
* @param {Function} _fileAction The function to run with each file.
* @param {Function} _errorHandler The function to call when an error occurs.
* @param {String} nestingLevel Optional. A number that indicates the current folder nesting nevel.
* @param {Number} timelimit Optional. The moment in milliseconds that indicates when to stop processing.
* @return {Error} An error when the function ran out of time, otherwise undefined.
* @license https://www.gnu.org/licenses/gpl-3.0.html
*/
function processFilesInFolderRecursively_(folder, _folderAction, _fileAction, _errorHandler, nestingLevel, timelimit) {
// version 1.2, written by --Hyde, 1 December 2022
nestingLevel = nestingLevel || 0;
const outOfTime = new Error('Ran out of time.');
if (new Date().getTime() > timelimit) {
return outOfTime;
}
const files = folder.getFiles();
while (files.hasNext()) {
if (new Date().getTime() > timelimit) {
return outOfTime;
}
try {
_fileAction(files.next());
} catch (error) {
_errorHandler(error);
}
}
const subfolders = folder.getFolders();
while (subfolders.hasNext()) {
const folder = subfolders.next();
_folderAction(folder, nestingLevel);
const error = processFilesInFolderRecursively_(folder, _folderAction, _fileAction, _errorHandler, nestingLevel 1, timelimit);
if (error) {
return error; // outOfTime
};
}
}
/**
* Gets a spreadsheet object from a file object.
*
* @param {DriveApp.File} file The file that contains a spreadsheet.
* @param {Function} _errorHandler The function to call when an error occurs.
* @return {SpreadsheetApp.Spreadsheet} The spreadsheet object, or null if file is not a spreadsheet or cannot be accessed.
* @license https://www.gnu.org/licenses/gpl-3.0.html
*/
function getSpreadsheetFromFile_(file, _errorHandler) {
// version 1.0, written by --Hyde, 9 October 2022
if (file.getMimeType() !== 'application/vnd.google-apps.spreadsheet') {
return null;
}
let ss;
try {
ss = SpreadsheetApp.open(file);
} catch (error) {
_errorHandler(error);
return null;
}
return ss;
}