onEdit(e)触发器无法触发创建Google Drive文件夹，仅手动运行有效求排查

Got it, let's break down why your onEdit(e) works manually but not automatically, and fix it step by step.

核心原因：简单触发器的权限限制 #

Your current onEdit is a simple trigger in Google Apps Script. Simple triggers run anonymously—meaning they don't have permission to access services that require OAuth authorization, like DriveApp.createFolder(). When you run the function manually, it uses your personal account permissions (which have access to Drive), so it works. But when it triggers automatically on edit, it lacks the necessary permissions to create the folder.

解决方案：改用可安装触发器 #

To get around this permission issue, you need to replace the simple onEdit trigger with an installable onEdit trigger. Installable triggers run under your user account, so they have full access to authorized services like Drive.

Step 1: Create the installable 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 in the bottom right corner.
4. Configure the trigger settings:
   • Choose which function to run: Select your function (I recommend renaming it to something like createFolderOnEdit to avoid confusion with the simple trigger, but you can use the existing name too).
   • Choose which deployment to run: Select Head (the latest version of your script).
   • Select event source: Choose From spreadsheet.
   • Select event type: Choose On edit.
   • (Optional) Set restrictions like specific sheets or ranges if you only want it to trigger for certain edits.
5. Click Save—you'll be prompted to authorize the script to access your Drive and Spreadsheet. Follow the prompts to complete authorization.

Step 2: Optimize your code (recommended) #

To make your function more robust and avoid unnecessary triggers, here's an improved version of your code:

function createFolderOnEdit(e) {
  // 1. Validate the edited range: only trigger if column 6 (F) is edited, and skip header row (row 1)
  const editedRange = e.range;
  if (editedRange.getColumn() !== 6 || editedRange.getRow() <= 1) {
    return;
  }

  // 2. Get the folder name and skip if it's empty
  const folderName = editedRange.getValue();
  if (!folderName.trim()) {
    SpreadsheetApp.getUi().alert("文件夹名称不能为空！");
    return;
  }

  // 3. Try to create the folder with error handling
  try {
    const parentFolder = DriveApp.getFolderById('1LOfkVGrV-juzUiwbU8owHj74T9wMAz');
    parentFolder.createFolder(folderName);
    SpreadsheetApp.getUi().alert(`文件夹 "${folderName}" 创建成功！`);
  } catch (error) {
    SpreadsheetApp.getUi().alert(`创建文件夹失败：${error.message}`);
  }
}

Key improvements here:

• Only triggers when column 6 (the one you're pulling the folder name from) is edited, not any edit in the sheet.
• Skips the header row to avoid accidental triggers.
• Checks for empty folder names to prevent errors.
• Adds error handling and user alerts to let you know if the operation succeeds or fails.

Important Notes #

• Double-check that the parent folder ID (1LOfkVGrV-juzUiwbU8owHj74T9wMAz) is correct, and that your account has permission to create subfolders in it.
• If you renamed your function (like to createFolderOnEdit), make sure you select the correct function when setting up the installable trigger.
• If you run into authorization issues, verify that your Google Account allows third-party scripts to access your Drive (check your Account > Security settings).

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