如何在Google Apps Script中根据单元格值匹配云端硬盘文件列表？

Hey there! Let's break down why your script isn't returning any Drive files, and get it working properly.

The Core Issue: Permissions with Simple Triggers #

The biggest problem here is that onEdit() as a simple trigger doesn't have permission to access Google Drive. Simple triggers (like the default onEdit or onOpen) run with limited authorization—they can only interact with the spreadsheet itself, not external services like Drive that require explicit user permission.

Even when you hardcoded 'title contains "Rome"', the script still couldn't access Drive because it was running under the restricted simple trigger context.

Step 1: Convert to an Installable Trigger #

To fix the permission issue, you need to replace the simple onEdit trigger with an installable edit trigger:

1. Open your Google Sheets script editor (Extensions > Apps Script).
2. Click the clock icon (Triggers) in the left sidebar.
3. Click "Add Trigger" at the bottom right.
4. Configure it like this:
   • Choose which function to run: onEdit
   • Choose which deployment to run: Head
   • Select event source: From spreadsheet
   • Select event type: On edit
5. Click "Save"—you'll be prompted to authorize the script. Follow the steps to grant access (you may need to click "Advanced" and "Go to [Script Name]" to proceed).

Step 2: Optimize Your Script (Optional but Useful) #

Your original script appends rows every time you edit, which will lead to duplicate results over time. Here's an improved version that clears old results and only triggers when you edit a specific cell (like A1):

function onEdit(e) {
  var activeSheet = e.source.getActiveSheet();
  var range = e.range;
  
  // Only run if the edited cell is A1 (adjust this to your input cell)
  if (range.getA1Notation() !== "A1") return;
  
  var input = range.getValue().trim();
  
  // Clear previous results if input is empty
  if (!input) {
    activeSheet.getRange("A2:A").clearContent();
    return;
  }
  
  // Search Drive for files with matching title
  var searchString = 'title contains "' + input + '"';
  var result = DriveApp.searchFiles(searchString);
  
  // Clear old results before adding new ones
  activeSheet.getRange("A2:A").clearContent();
  
  var currentRow = 2;
  while (result.hasNext()) {
    var file = result.next();
    activeSheet.getRange(currentRow, 1).setValue(file.getName());
    currentRow++;
  }
}

Key improvements here:

• Only triggers when you edit cell A1 (prevents accidental runs from other cells)
• Clears old results before displaying new ones
• Handles empty input by clearing results
• Uses setValue() instead of appendRow() to keep results in a consistent area

Additional Checks #

If it still doesn't work after setting up the installable trigger:

• Double-check that the files you're searching for are in your personal Drive (not a shared drive—if they are, you'll need to use DriveApp.getSharedDriveById("DRIVE_ID").searchFiles() instead)
• Make sure the search term matches exactly (capitalization matters! "rome" won't find "Rome Adventure" unless you use lowercase in your search string)

Give these steps a try, and your script should start pulling up matching Drive files in no time!

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