CODEWITHSUNDEEP
google-apps-script
Tan Al
Tan Al

Reputation: 3

How to exceed the 30mins time on Apps Script

I am pulling file list on spreadsheet and some folders will take 1 hour to get all the data. How can I exceed my time limit to get all the data? Currently limit is 30mins.

Here is my code:

function myFunction() {
  
    var sheet = SpreadsheetApp.getActiveSheet();
    sheet.clear();
    sheet.appendRow(["Market","Folder","Employee","FileName", "Date Last Updated", "Size", "URL", "ID", "Description", "Type"]);
    

    var folders = DriveApp.getFoldersByName('Root Folder 1');
    var foldersnext = folders.next();
    var subfolders = foldersnext.getFolders();
  

    while (subfolders.hasNext()) {
    var subfolderdata = [];
    var mysubfolders = subfolders.next();
    var files = mysubfolders.searchFolders("title contains 'Cap'");

    var getfilez = files.next();

    var mysubfiles = getfilez.getFiles();
   
    while (mysubfiles.hasNext()) {
      var smyfile = mysubfiles.next();
      var sfname =  smyfile.getName();
      var sfdate =  smyfile.getLastUpdated(); 
      var sfsize =  smyfile.getSize();
      var sfurl =  smyfile.getUrl();
      var sfid =  smyfile.getId();
      var sfdesc =  smyfile.getDescription();
      var sftype =  smyfile.getMimeType();

      subfolderdata = [ 
        (foldersnext),
        (getfilez),(mysubfolders),
        sfname,
        sfdate,
        sfsize,
        sfurl,
        sfid,
        sfdesc,
        sftype
      ];
     
      sheet.appendRow(subfolderdata); }}}

Upvotes: 0

Views: 139

Answers (1)

ADW
ADW

Reputation: 4247

Just to put everything in one place:

By using appendRow() you are hitting the sheet for each row. That is slow and inefficient. See Best Practices.

Instead, create an array of subfolderdata and post it to the sheet once after collecting all the data. See if that helps the script finish in less than 30min.

To achieve this, initialise an array right at the begining:

var postDataArray = [];

Then, for the header row of the sheet, instead of:

sheet.appendRow(["Market","Folder","Employee","FileName", "Date Last Updated", "Size", "URL", "ID", "Description", "Type"]);

add the header row to the array:

postDataArray.push(["Market","Folder","Employee","FileName", "Date Last Updated", "Size", "URL", "ID", "Description", "Type"]);

Then inside the while loop, instead of

sheet.appendRow(subfolderdata);

use

postDataArray.push(subfolderdata);

And then after the while loop, use the following to post the data to the sheet:

sheet.getRange(1, 1, postDataArray.length, postDataArray[0].length).setValues(postDataArray);

postDataArray.length is the number of rows.

postDataArray[0].length is the number of columns.

PS: Best to test the code for a few hundred rows instead of waiting for 30 minutes to figure out it is broken.

Add a counter that breaks the while loop once it hits say 500.

Upvotes: 2

Related Questions