CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Tyrone Hirt
Tyrone Hirt

Reputation: 341

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

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?

Upvotes: 0

Views: 63

Answers (1)

Tanaike
Tanaike

Reputation: 201378

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:

Upvotes: 1

Related Questions