Google Apps Script – Count files that are inside a worksheet subfolder

December 5, 2022

I’m having trouble creating a script in Google Apps Script that checks the number of files in a subfolder of the spreadsheet folder.

A friend here on the forum helped me to create a script that checks the spreadsheet folder, but now I need to modify it to check the subfolder of the spreadsheet folder.

The script I have at the moment is this one:

/** Written by Tanaike */ 
function CheckForFiles() {
      const ss = SpreadsheetApp.getActiveSpreadsheet(); // This is your active Spreadsheet.
      const parentFolder = DriveApp.getFileById(ss.getId()).getParents();
      if (parentFolder.hasNext()) {
        const files = parentFolder.next().getFiles();
        let count = 0;
        while (files.hasNext()) {
          const file = files.next();
          // console.log(file.getName()); // When you use this line, you can see the filename of the files.
          count++;
        }
        if (count >= 3) {
          throw new Error("Your expected error.");
        }
      } else {
        throw new Error("Spreadsheet has no parent folder.");
      }
    }

Important point: note that the spreadsheet folder is a template folder, that is, it will be duplicated and its name will be changed several times, for this reason I need the script to be something like "Check how many files there are inside the subfolder FolderToBeCheck which is inside the folder of this worksheet, if the number of files is more than 3, return an error"

How can I achieve this?

Test Folder

>Solution :

In your situation, how about the following modification?

Modified script:

In this modification, from your showing image, it supposes that the folder name of the subfolder is FolderToBeCheck. In your question, it seems that the folder name is FolderToBeChecked. Please be careful about this difference.

function CheckForFiles() {
  const subFolderName = "FolderToBeCheck"; // Please set your subfolder name.

  const ss = SpreadsheetApp.getActiveSpreadsheet(); // This is your active Spreadsheet.
  const parentFolder = DriveApp.getFileById(ss.getId()).getParents();
  if (parentFolder.hasNext()) {
    const innerFolder = DriveApp.getFoldersByName(subFolderName);
    if (innerFolder.hasNext()) {
      const files = innerFolder.next().getFiles();
      let count = 0;
      while (files.hasNext()) {
        const file = files.next();
        // console.log(file.getName()); // When you use this line, you can see the filename of the files.
        count++;
      }
      if (count >= 3) {
        throw new Error("Your expected error.");
      }
    } else {
      throw new Error("Folder of 'FolderToBeCheck' was not found.");
    }
  } else {
    throw new Error("Spreadsheet has no parent folder.");
  }
}