Google表单关联表格数据计算器脚本部署求助

Hey Sergei! Let's break down why your current setup isn't working and how to fix it to get the result you want.

为什么你的代码在表格调试正常，但表单里不生效？ #

When you test your code directly in the spreadsheet's script editor, you're running it in your browser's context—so Browser.msgBox can pop up a window for you. But when a form submission triggers the function, the code runs on Google's servers, not in the user's browser. There's no browser window available to show that message box, which is why it doesn't work when someone submits the form.

Plus, if you tried adding this code to the Google Form's script editor instead of the spreadsheet's, SpreadsheetApp.getActiveSpreadsheet() won't work there—because the Form script's context is tied to the form, not the linked spreadsheet.

可行的解决方案（接近你的需求） #

Since we can't pop up a browser window directly after form submission, here are two great alternatives that will get the calculated value to the user:

1. Show the value in the form's confirmation page

This is the closest to your "popup" goal—users will see the value right after they submit the form. Here's how to set it up:

1. Open your Google Form, click the three dots in the top-right corner, and select Script editor.
2. Replace any existing code with this:

   function onFormSubmit(e) {
     // Get the linked responses spreadsheet
     var form = FormApp.getActiveForm();
     var spreadsheetId = form.getDestinationId();
     var responsesSheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName("Ответы на форму (1)");
     var sheet2 = SpreadsheetApp.openById(spreadsheetId).getSheetByName("Лист4");
   
     // Get the latest row and your calculated value
     var latestRow = responsesSheet.getLastRow();
     var latestCol = responsesSheet.getLastColumn();
     var calculatedValue = sheet2.getRange(latestRow, latestCol + 1, 1, 1).getValue();
   
     // Set a custom confirmation message with the value
     form.setConfirmationMessage(`Thanks for submitting! Your value: ${calculatedValue} $`);
   }
   

3. Set up a trigger to run this function when the form is submitted:
   • Click the clock icon (Triggers) in the script editor's top-right.
   • Click Add trigger.
   • For "Choose which function to run", select onFormSubmit.
   • For "Select event source", choose From form.
   • For "Select event type", choose On form submit.
   • Click Save.

Now, whenever someone submits the form, they'll see your custom message with the calculated value on the confirmation page.

2. Email the value to the respondent

If you want to send the value directly to the user's inbox (great for records), use this approach instead. You'll need your form to collect the respondent's email address (enable this in Form settings → Collect email addresses):

1. Open the linked responses spreadsheet, go to Extensions → Apps Script.
2. Replace your current code with this:

   function sendValueToRespondent(e) {
     var responsesSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Ответы на форму (1)");
     var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Лист4");
   
     var latestRow = responsesSheet.getLastRow();
     var latestCol = responsesSheet.getLastColumn();
     var calculatedValue = sheet2.getRange(latestRow, latestCol + 1, 1, 1).getValue();
   
     // Get the respondent's email from the form submission
     var respondentEmail = e.response.getRespondentEmail();
     if (respondentEmail) {
       MailApp.sendEmail(
         respondentEmail,
         "Your Calculated Form Value",
         `Hello! Here's your calculated value: ${calculatedValue} $`
       );
     }
   }
   

3. Set up the form submit trigger the same way as option 1 (in the spreadsheet's script editor):
   • Add a trigger for sendValueToRespondent, event source From form, event type On form submit.

Quick Notes #

• Always test the trigger by submitting the form yourself (don't just run the function in the script editor)—this simulates the real user experience.
• Make sure your sheet names ("Ответы на форму (1)", "Лист4") are exactly correct in the code (case-sensitive!).

内容的提问来源于stack exchange，提问作者Sergei I.