I have a folder on Google with 18k files and need to list some variables from them into Google sheet. A solution for this was found on Insert image from Google Drive into Google Sheets
However, because it's 18k files long - the code times out after 4 minutes (about 2k files). I've tried a workaround to get all 18k files in using the continuation token but for some reason, upon restarting the file listing starts back again from 0 instead of continuing from the last scanned file.
Essentially, the script now;
- Scans a Google Drive Folder -> Create a list of files
- Logs this list, and uses the continuation token function (and instructs to stop at 4.5 minutes)
- Pulls specific data into Google Sheet, and terminates at 4.5 minutes
- Upon restarting, the continuation script looks at the log and continues where it left off
- Unfortunately, I just started coding and somehow broke 1-4 :(
What am I doing wrong?
/* Modified with token access from @hubgit and http://stackoverflow.com/questions/30328636/google-apps-script-count-files-in-folder
for this stackexchange question http://webapps.stackexchange.com/questions/86081/insert-image-from-google-drive-into-google-sheets by @twoodwar
*/
function listFilesInFolder(folderName) {
var sheet = SpreadsheetApp.getActiveSheet();
sheet.appendRow(["Name", "Date", "Size", "URL", "Download", "Description", "Image"]);
//**Error is likely happening here**
// Logs the name of every file in the User's Drive
// this is useful as the script may take more that 5 minutes (max execution time)
var userProperties = PropertiesService.getUserProperties();
var continuationToken = userProperties.getProperty('CONTINUATION_TOKEN');
var start = new Date();
var end = new Date();
var maxTime = 1000*60*4.5; // Max safe time, 4.5 mins
if (continuationToken == null) {
// first time execution, get all files from Drive
var files = DriveApp.getFiles(); // make sure that is variable is saving the actual files in the desired folder.
} else {
// not the first time, pick up where we left off
var files = DriveApp.continueFileIterator(continuationToken);
}
while (files.hasNext() && end.getTime() - start.getTime() <= maxTime) {
var file = files.next();
//change the folder ID below to reflect your folder's ID (look in the URL when you're in your folder)
var folder = DriveApp.getFolderById("[INSERT_FOLDER_ID_HERE");
var contents = folder.getFiles();
var cnt = 0;
var file;
while (contents.hasNext()) {
var file = contents.next();
cnt++;
data = [
file.getName(),
file.getDateCreated(),
file.getSize(),
file.getUrl(),
"https://docs.google.com/uc?export=download&confirm=no_antivirus&id=" + file.getId(),
file.getDescription(),
"=image(\"https://docs.google.com/uc?export=download&id=" + file.getId() +"\")",
];
sheet.appendRow(data);
Logger.log(file.getName());
end = new Date();
}
};
// Save your place by setting the token in your user properties
if(files.hasNext()){
var continuationToken = files.getContinuationToken();
userProperties.setProperty('CONTINUATION_TOKEN', continuationToken);
} else {
// Delete the token
PropertiesService.getUserProperties().deleteProperty('CONTINUATION_TOKEN');
}
};