请求完善Google Sheets软件错误追踪脚本：添加备注弹窗与行迁移

Got it! Let's expand your existing script to include the two missing functionalities. Below is the complete modified code with clear comments, plus a critical note about trigger setup since we're using UI prompts:

function onEdit(e) {
  // Grab the active range, sheet, and row number
  const range = e.range;
  const activeSheet = range.getSheet();
  const targetRow = range.getRow();
  
  // Only execute if we're in the "Form Responses" sheet, editing column A, and selecting "Resolved"
  if (activeSheet.getName() === "Form Responses" && range.getColumn() === 1 && e.value === "Resolved") {
    // 1. Existing functionality: Add timestamp to column K and calculate duration in column L
    range.offset(0, 10).setValue(new Date());
    range.offset(0, 11).setFormulaR1C1('=R[0]C[-1]-R[0]C[-8]-((weeknum(R[0]C[-1])-weeknum(R[0]C[-8]))*2)');
    
    // 2. Prompt for user notes and write to column C
    const ui = SpreadsheetApp.getUi();
    const noteResponse = ui.prompt(
      "Add Resolved Bug Notes",
      "Enter any additional context or user notes for this fix:",
      ui.ButtonSet.OK_CANCEL
    );
    
    // Write the note to column C if user clicks OK
    if (noteResponse.getSelectedButton() === ui.Button.OK) {
      activeSheet.getRange(targetRow, 3).setValue(noteResponse.getResponseText());
    }
    
    // 3. Move the row to "Resolved" sheet (top below header) and delete from original sheet
    const resolvedSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Resolved");
    // Copy the entire row (values only, to keep the calculated duration intact)
    activeSheet.getRange(targetRow, 1, 1, activeSheet.getLastColumn())
      .copyTo(resolvedSheet.getRange(2, 1), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    // Remove the original row from Form Responses
    activeSheet.deleteRow(targetRow);
  }
}

Critical Trigger Setup Note #

The default simple onEdit trigger won’t work here because UI prompts require authorization (simple triggers can’t access authorized services). You’ll need to set up an installable onEdit trigger instead:

1. Open your Google Sheet, go to Extensions > Apps Script
2. In the script editor, click the clock icon (Triggers) in the left sidebar
3. Click Add Trigger
4. Configure these options:
   • 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 and authorize the script when prompted

Quick Breakdown of New Features #

• User Notes Prompt: Creates a dialog with a text input box. If the developer clicks OK, their input is saved directly to column C of the resolved row.
• Row Movement: Copies the entire row (values only, so the calculated duration doesn’t break) to the second row of the "Resolved" sheet (keeping it at the top below the header), then deletes the original row from "Form Responses".

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