在Drive的Sales目录下为无匹配的潜在客户创建新文件夹

Hey there! I’ve put together a Google Apps Script that’ll handle exactly this task—automatically checking your Sales folder against your potential client list in Google Sheets, and creating any missing folders without you having to do it manually. Here’s how it works:

Quick Prep Steps #

First, get these details ready to plug into the script:

• The ID of your Sales folder in Google Drive (grab this from the folder’s URL—it’s the long string between /folders/ and any trailing ?).
• Confirm where your client names live in your Sheet: let’s assume they’re in Column A, starting at row 2 (skip the header row). Adjust later if your setup is different.

The Full Script #

Open your Google Sheet, head to Extensions > Apps Script, replace the default code with this:

function createMissingCustomerFolders() {
  // --- CONFIGURE THESE VALUES FIRST ---
  const SALES_FOLDER_ID = "YOUR_SALES_FOLDER_ID"; // Replace with your Sales folder ID
  const SHEET_NAME = "Sheet1"; // Replace with your actual sheet name
  const CLIENT_NAME_COLUMN = 1; // Column A = 1, B=2, etc.
  const START_ROW = 2; // Skip header row (row 1)
  // ------------------------------------

  // Grab the Sales folder and map existing subfolder names
  const salesFolder = DriveApp.getFolderById(SALES_FOLDER_ID);
  const existingFolderNames = new Set();
  
  // Populate the set with existing folder names (trimmed to avoid whitespace mismatches)
  const existingFolders = salesFolder.getFolders();
  while (existingFolders.hasNext()) {
    existingFolderNames.add(existingFolders.next().getName().trim());
  }

  // Pull client names from the Sheet
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  const lastRow = sheet.getLastRow();
  
  if (lastRow < START_ROW) {
    Logger.log("No client names found in the sheet—nothing to do!");
    return;
  }

  // Clean up the sheet data: trim whitespace, filter out empty cells
  const clientNames = sheet.getRange(START_ROW, CLIENT_NAME_COLUMN, lastRow - START_ROW + 1, 1)
    .getValues()
    .flat()
    .map(name => name.trim())
    .filter(name => name !== "");

  // Check each client name and create folders if missing
  clientNames.forEach(clientName => {
    if (!existingFolderNames.has(clientName)) {
      salesFolder.createFolder(clientName);
      Logger.log(`Created new folder: ${clientName}`);
    } else {
      Logger.log(`Folder already exists: ${clientName}`);
    }
  });

  // Optional: Pop up a confirmation when done
  SpreadsheetApp.getUi().alert("Folder check/creation finished! Check script logs for details.");
}

How to Run It #

1. Swap out the placeholder values in the // CONFIGURE THESE VALUES FIRST section with your actual details.
2. Save the script (click the floppy disk icon) and name it something like CustomerFolderAutomation.
3. Run the script for the first time—you’ll need to authorize it (follow the prompts, and if you see a "Google hasn’t verified this app" warning, click Advanced > Go to [Script Name] to grant permissions).
4. Run createMissingCustomerFolders again, and it’ll handle the rest!

Key Notes #

• We use a Set for existing folder names because it makes checking if a folder exists way faster than looping through folders every time.
• The script trims whitespace from both sheet names and folder names to avoid silly mismatches (like "Customer1 " vs "Customer1").
• Empty cells in the sheet are skipped so we don’t create blank folders by accident.
• You can check exactly what happened by going to View > Logs in the Apps Script editor.

Troubleshooting Tips #

• "Permission denied" error? Make sure you have edit access to both the Sales folder and the Google Sheet, and that you authorized the script correctly.
• Folders not showing up? Double-check your Sales folder ID is correct, and that your client names don’t have forbidden characters (like / \ : * ? " < > |—Drive blocks these).

内容的提问来源于stack exchange，提问作者Gonzalo