如何通过Google表单脚本将重命名后的文件名同步至关联Google Sheets的F列

I'll modify your existing script to automatically write the exact renamed filename into Column F of your linked Google Sheet, which solves both the timestamp mismatch and formula auto-apply problems you mentioned.

Updated Complete Script #

function onFormSubmit1() { 
  var form = FormApp.getActiveForm(); 
  var responses = form.getResponses();
  var latestResponse = responses[responses.length - 1];
  
  // Extract form submission data
  var gender = latestResponse.getItemResponses()[0].getResponse(); 
  var age = latestResponse.getItemResponses()[1].getResponse(); 
  var source = latestResponse.getItemResponses()[2].getResponse(); 
  var fileId = latestResponse.getItemResponses()[3].getResponse(); 
  
  // Generate formatted date and new filename
  var now = new Date(); 
  var formattedDate = Utilities.formatDate(now, Session.getScriptTimeZone(), "yyyy.MM.dd HH:mm:ss"); 
  var newFilename = formattedDate + ' - ' + source +' - ' + gender + ' - ' + age; 
  
  // Rename the target file
  var file = DriveApp.getFileById(fileId); 
  file.setName(newFilename); 
  
  // Write the new filename to Column F of the latest submission row
  var linkedSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var latestRow = linkedSheet.getLastRow();
  var filenameCell = linkedSheet.getRange(latestRow, 6); // Column 6 = Column F
  filenameCell.setValue(newFilename);
  
  // Keep your original source folder reference (replace with actual ID)
  var sourceFolder = DriveApp.getFolderById('folderidcoderemoved'); 
}

Key Improvements & Explanations #

• Eliminates Timestamp Discrepancy: We use the exact same formattedDate variable that's used to rename the file to populate Column F. No more mismatches between the sheet's Timestamp and the renamed file's date.
• Auto-applies to New Rows: Instead of relying on formulas, the script directly targets the latest row (added by the form submission) and writes the filename to Column F. This works every time a new entry is submitted, no manual formula setup needed.
• Cleaner Code Structure: I added variables like latestResponse and newFilename to make the script more readable and maintainable.

Important Setup Notes #

1. Verify Trigger Configuration:
   • Open the Google Script Editor, go to Edit > Current project's triggers
   • Add a new trigger: select onFormSubmit1 as the function, set event source to Form, event type to On form submit
2. Check Form Question Order: Ensure the indices in getItemResponses()[0] to [3] match the order of questions in your form (gender, age, source, file ID). Adjust the numbers if your form's question order is different.
3. Replace Folder ID: Swap 'folderidcoderemoved' with your actual source folder's ID.

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