Google Sheet - Reference a folder that is inside the worksheet folder-CodePudding

I am a beginner in creating Scripts in Google Sheets, so I would like some help to reference a folder that is inside the spreadsheet folder.

I would like to create a script that checks if there are more than 3 files in a given folder, if so, I would like it to return an error on the screen.

Important point: the files that need to be checked will always be in a folder that is inside the spreadsheet folder, so I would need to reference this, in the CMD it would be something like .\FolderWithFiles.

In this case, I cannot use the ID a of the folder which I want to be checked, because this is a model worksheet that will be duplicated several times.

Any idea how I can do this?

CodePudding user response：

To reference a folder that is inside the spreadsheet folder in Google Sheets, you can use the DriveApp class and the getFoldersByName method to get the folder and then use the getFiles method to access the files in the folder.

Here's an example of how you could do this in a Google Sheets script:

// Get the current spreadsheet
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

// Get the parent folder of the spreadsheet
var parentFolder = spreadsheet.getParentFolder();

// Get the folder with the files you want to check
var folderWithFiles = parentFolder.getFoldersByName("FolderWithFiles").next();

// Get the files in the folder
var files = folderWithFiles.getFiles();

// Check if there are more than 3 files in the folder
if (files.length > 3) {
  // If there are more than 3 files, show an error on the screen
  Browser.msgBox("Error: There are more than 3 files in the folder");
}

This script uses the getActiveSpreadsheet method to get the current spreadsheet, and then uses the getParentFolder method to get the parent folder of the spreadsheet. Next, it uses the getFoldersByName method to get the folder with the files you want to check, and then uses the getFiles method to get the files in the folder. Finally, it checks if there are more than 3 files in the folder and shows an error on the screen if that is the case.

Note that this script assumes that the folder with the files you want to check is named "FolderWithFiles" and is a direct child of the parent folder of the spreadsheet. You can adjust the getFoldersByName method call to specify the exact path to the folder if it is not in the immediate parent folder of the spreadsheet.

CodePudding user response：

I believe your goal is as follows.

You want to check the number of files in the folder including the active Spreadsheet you are using.
When the number of files is more than 3, you want to show an error.

In this case, how about the following sample script?

Sample script:

function myFunction() {
  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.");
  }
}

When this script is run, the number of files in the folder including the active Spreadsheet is checked. When the number of files is more than 3, an error like Your expected error. occurs.
If you want to use another Spreadsheet instead of the active Spreadsheet, please modify const ss = SpreadsheetApp.getActiveSpreadsheet(); to const ss = SpreadsheetApp.openById("###spreadsheetId###");.

Reference:

getParents()