Optimizing Folders and GDoc Creation using Google script

Question

I want to create some folders (a,b,c) in each subFolder of A (A.1, A.2, A.3) which will reside in folder A and then create multiple GDocs in folder b.

The directory in the Google Drive:

Code:

  //get the values from two cols.
  var chapters = currSheet.getRange("B2:B").getValues();
  var subChapters = currSheet.getRange("C2:C").getValues();

  //get the count of Chapter and the SubChapters and calls the getSubFoldersM() for subFolder and GDoc creation.
  var count = 1;
  var length = subChapters.length;
  console.log("Chapter: " + chapters[0]);
  var ParentFolder = getFolder.createFolder("Chapter "+chapters[0]);
  getSubFoldersM(ParentFolder, subChapters[i] ,count,link);

  var lastchaptersValue = chapters[0];
  for (i = 0; i < length; i++) 
  {
    if (subChapters[i] === subChapters[i + 1]) 
    {
      count++;
      continue;
    }
    else 
    {
      if ( chapters[i] != lastchaptersValue ) 
      {
        console.log("Chapter: " + chapters[i]);
        var ParentFolder = getFolder.createFolder("Chapter "+chapters[i]);
      }
      lastchaptersValue = chapters[i];
    }
    console.log(subChapters[i], count);
    getSubFoldersM(ParentFolder, subChapters[i] ,count,link);
    count = 1;
  }  

  //function for creation of Subfolders and GDocs Creation
  function getSubFoldersM(rootFolder,uniqueChapter,finalCount,rootName){
    var subFolder = rootFolder.createFolder("Chapter "+uniqueChapter);
      subFolder.createFolder("Spreadsheet Links"); 
      subFolder.createFolder("Images"); 
      var solutionFolder = subFolder.createFolder("Solutions");

      var name = subFolder.getName();
      for (var i=0;i<finalCount;i++)
      { 
        var doc = DocumentApp.create(rootName+'_'+name+''+columnToLetterM(i+1)+'_EL'),
            docFile = DriveApp.getFileById( doc.getId() );
        solutionFolder.addFile( docFile );
        DriveApp.getRootFolder().removeFile(docFile);
      }
  }

This is the simple piece of code that gets me my required directory. But it is taking a LOT of time as there is nearly 10k data. Roughly estimated, 9.5k rows of data it took 5 minutes to create all the folders and GDocs. Also, I am using folderIterator() every time in another for loop which I'm told takes a lot of time.

How to make this code more time-efficient?

score 0 · Answer 1 · answered Apr 16 '20 at 09:56

A possible optimization to your code is batching the requests instead of doing an API Call in each loop execution.

Considerations:

Please mind that this has some limitations:

You're limited to 1000 calls in a single batch request. If you need to make more calls than that, use multiple batch requests.

So, you will have to divide your loop process in chunks of 1000 requests at most.

Approach:

The idea is to build a Drive API request to create the new file inside the desire folder. This request can be stored in an array for each chunk. Then you will be able to pass all these requests at once using the batch call operation, reducing considerably the network and processing overhead.

Unfortunately Apps Script has no native support of doing that.

Consider migrating to a native solution in Java, Python or another language. You can find documentation on how to get started with them here;
If you want to stick to Apps Script there is a very good explanation on how to batch requests in this StackOverflow answer. If you prefer you can use a custom Apps Script library to make batch requests, here is the link

Optimizing Folders and GDoc Creation using Google script

1 Answers1

Considerations:

Approach: