Google-sheets – Continuation token: Generate file listing of a google folder into a google sheet

google sheetsgoogle-apps-scriptgoogle-apps-script-triggers

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;

  1. Scans a Google Drive Folder -> Create a list of files
  2. Logs this list, and uses the continuation token function (and instructs to stop at 4.5 minutes)
  3. Pulls specific data into Google Sheet, and terminates at 4.5 minutes
  4. Upon restarting, the continuation script looks at the log and continues where it left off
  5. 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');
      }

    };

Best Answer

Lets start with

var maxTime = 1000*60*4.5; // Max safe time, 4.5 mins

This is obsolete. You could safely add one more minute as the current script execution time limit is 6 mins. Ref. https://developers.google.com/apps-script/guides/services/quotas

Second:

Instead of using Logger.log() use console.log() as the first is lost every time that a new instance of the script is executed but the second will be available for 7 seven days in the script execution page.

NOTE: Since you have 18k files I don't think that it's a good idea to log every file name as it's very unlikely that you will read those logs.

Third:

Double / triple check that the continuationToken is saved and isn't deleted before all the files been processed.