google script : listing files from google drive by get folderID from sheet range

Question

what I'm trying to do?

I'm trying to list all files in my subfolder "Annual Reports" on my Google Drive to my google spreadsheet but

I have a tons of folders and subfolders on my google drive

and that caused an error "exceeded maximum execution time" when I tried to run the code to get list of all folders and files

The structure of my google drive folder is

- My Drive

  - Subfolder 1: Documents by country or issue

       - Subfolder2 :Albania 

            - Subfolder3 :Annual Reports >files


      - Subfolder2 : Bulgaria 
            - Subfolder3 : Annual Reports >files

and so on..for every country

So,I manage to set my subfolder "Documents by country or issue" to be rootfolder and get ID list of 'Annual Report' folder

as in this script code

var sheet = SpreadsheetApp.getActiveSheet();
function generateFolderTree() {
  sheet.clear();

  sheet.appendRow(["Folder Name", "Rootfolder name", "ID"]);

  var parent = DriveApp.getFoldersByName("Documents by country or issue").next();

  getChildFolders(parent);

}

function getChildFolders(parent) {

  var childFolders = parent.getFolders();

  var parentName = parent.getName();

  while (childFolders.hasNext()) {

    var childFolder = childFolders.next();

    fileName = childFolder.getName();
    root = parentName + "/" + fileName;
    fileID = childFolder.getId();

    if (fileName == "Annual Reports") {
      sheet.appendRow([fileName, root, fileID]);
    } else if (fileName == "_Annual Reports") {
      sheet.appendRow([fileName, root, fileID]);
    }

and it works , I got the result on my sheet like this(can't upload pic,don't know why)

column A || column B || column C

Folder Name || Rootfolder name || ID

Annual Reports || Colombia/Annual Reports||
0Bz02kQQ7_a6paTM1SmdpZ01HYjQ

_Annual Reports || Slovakia (SK)/_Annual Reports ||0BwW8rsOAeCzneUlDUk1pcGNlVU0

Annual Reports || Switzerland (CH)/Annual Reports ||0Bz02kQQ7_a6pZTJsWDR0elJTaDQ

So I want to know ***how to write a script to create new sheet for each country and

get the list of files inside folder from folderID(like get value from ranges--column C) that I got on my spreadsheet.

Or Is there recommended way to make this more easier?

Thank you.

You should use the getFiles() method from class Folder which will "Gets a collection of all files that are children of the current folder.". — Pierre-Marie Richard
To simplify this task, you could use 1) Continuation tokens to remember states of File Iterator 2) PropertiesService for caching and retrieving your continuation token 3) Triggers for scheduling function calls. Keep track of time to make sure you don't exceed maximum execution time. Also, you could get all subfolders and files recursively. I'll share code examples if you are interested — Anton Dementiev
@AntonDementiev of course I'm interested in every way to make this works — PTN

Pierre-Marie Richard Pierre-Marie Richard · Accepted Answer · 2017-07-12T12:38:35

Here is a sample:

function createSheetForEachCountry() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  var data = sheet.getRange(2, 2, sheet.getLastRow()-1, 2).getValues(); // Get country name and folder id

  for(var i = 0;i<data.length;i++){ // For all the id on the main sheet

    if(data[i][0] != "" && data[i][1] != ""){ // Check if we got some data
      var newSheet = ss.insertSheet(data[i][0]); // Create the sheet with the country name
      var files = DriveApp.getFolderById(data[i][1]).getFiles(); // Get files from folder

      while(files.hasNext()){ // While there is files
        var file = files.next(); // Look on the next file
        newSheet.appendRow([file.getId()]); // And add his id on the sheet
      }
    }
  }
}

The comments should let you know how it work.

google script : listing files from google drive by get folderID from sheet range

1 Answers