基于Google表格列重命名并下载Google Drive中表单提交的图片

Alright, I’ve solved this exact problem a few times for teams using Google Forms to collect media. The best approach here is to use Google Apps Script—it’s native, avoids external auth mess, and ties directly into Forms, Sheets, and Drive. Let’s break it down:

Before diving into code, grab these two critical IDs:

• Drive Folder ID: Open your image storage folder, look at the URL—https://drive.google.com/drive/folders/[THIS_PART_IS_YOUR_ID]
• Response Sheet ID: Open the Google Sheet auto-generated by your form, check the URL—https://docs.google.com/spreadsheets/d/[THIS_PART_IS_YOUR_ID]/edit
• Make sure you have edit access to both resources.

Open your form’s response Sheet, go to Extensions > Apps Script, paste this code, and tweak the placeholder values to match your setup:

function renameUploadedImages() {
  // Replace these with your actual IDs and column indexes
  const TARGET_FOLDER_ID = "YOUR_DRIVE_FOLDER_ID";
  const RESPONSE_SHEET_ID = "YOUR_RESPONSE_SHEET_ID";
  const NEW_NAME_COLUMN_INDEX = 2; // Use 0 for first column, 1 for second, etc.
  const IMAGE_LINK_COLUMN_INDEX = 3;

  // Get core objects
  const folder = DriveApp.getFolderById(TARGET_FOLDER_ID);
  const sheet = SpreadsheetApp.openById(RESPONSE_SHEET_ID).getSheetByName("Form Responses 1");
  // If you renamed your response tab, update the name above

  // Skip header row, get all submission data
  const submissions = sheet.getDataRange().getValues().slice(1);

  submissions.forEach(submission => {
    const newFileName = submission[NEW_NAME_COLUMN_INDEX];
    const imageLink = submission[IMAGE_LINK_COLUMN_INDEX];

    // Skip rows missing critical data
    if (!newFileName || !imageLink) return;

    // Extract file ID from the form's Drive link
    const fileIdMatch = imageLink.match(/id=([a-zA-Z0-9_-]+)/);
    if (!fileIdMatch) {
      console.log(`Couldn't extract file ID from link: ${imageLink}`);
      return;
    }
    const fileId = fileIdMatch[1];

    try {
      const imageFile = DriveApp.getFileById(fileId);
      // Double-check the file is in your target folder to avoid accidental renames
      const fileParent = imageFile.getParents().next();
      if (fileParent.getId() !== TARGET_FOLDER_ID) return;

      // Preserve the original file extension (jpg, png, etc.)
      const fileExtension = imageFile.getBlob().getContentType().split("/")[1];
      const finalFileName = `${newFileName}.${fileExtension}`;

      imageFile.setName(finalFileName);
      console.log(`Renamed successfully: ${imageFile.getName()}`);
    } catch (error) {
      console.log(`Failed to rename file ${fileId}: ${error.message}`);
    }
  });
}

• Adjust NEW_NAME_COLUMN_INDEX to match the column in your Sheet that has the value you want to use for renaming (e.g., if it’s the 5th column, use 4 since arrays are 0-indexed).
• Update IMAGE_LINK_COLUMN_INDEX to point to the column where your form stores the uploaded image’s Drive link.
• If your form allows multiple images per submission, modify the code to split the comma-separated links in the sheet cell and loop through each one.

1. Test First: Click the run button in the Apps Script editor. You’ll need to authorize the script—look past the "Google hasn’t verified this app" warning (it’s your own script!) and grant access.
2. Automate: To avoid running this manually every time, set up a trigger:
   • In Apps Script, click the clock icon (Triggers) on the left sidebar.
   • Add a new trigger, select renameUploadedImages as the function, choose "On form submit" as the event source, and save. Now it’ll run automatically every time someone submits your form.

• Duplicate Filenames: Drive will auto-add (1) to duplicate names. If you need to handle this more gracefully, add a check for existing files in the folder before renaming.
• File Permissions: Ensure the script has access to all uploaded files—if users submit images from their own Drive, make sure they’ve set sharing permissions to allow your account access.

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