如何简单连接Google Docs与Google Sheets？解决myFunction(e)取值报错

Hey there! Let's break down what's causing that error and get your form-to-letter workflow up and running smoothly.

First, Why That Error Happens #

The TypeError: Cannot read property "values" from undefined pops up because:

• When you click the "Run" button directly in the script editor, there's no form submission event triggering the function. The e parameter only exists when the function is triggered by a real form submission (or a spreadsheet edit trigger).
• Without that event data, e is undefined, so trying to access e.values throws an error.

Fixing the Code & Building the Full Workflow #

Your code had a few small logic gaps (like not referencing the new document you created properly), so here's a complete, working version tailored to your needs:

Complete Working Function #

function generatePurchaseLetter(e) {
  // Pull data from the form submission event
  // Note: e.values[0] is the timestamp, adjust indexes based on YOUR form's field order!
  const customerName = e.values[1];
  const customerProduct = e.values[2]; // Update this index to match where your product field is in the form

  // Create a new Google Doc for the letter
  const letterDoc = DocumentApp.create(`Purchase Inquiry - ${customerName}`);
  const docBody = letterDoc.getBody();

  // Add your letter content (format as needed)
  docBody.appendParagraph(`Dear ${customerName},`);
  docBody.appendParagraph(""); // Add a blank line
  docBody.appendParagraph(`I like your ${customerProduct}. Are you interested in selling?`);
  // Feel free to add more lines here (like your contact info, next steps, etc.)

  // Save and close the document (don't forget the parentheses after saveAndClose!)
  letterDoc.saveAndClose();

  // Optional: Add the doc link back to the spreadsheet for easy access
  const activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = activeSheet.getLastRow();
  activeSheet.getRange(lastRow, 4).setValue(letterDoc.getUrl()); // Writes to column D, adjust as needed
}

Setting Up the Trigger (Critical Step!) #

To make this function run automatically when someone submits the form, you need to set up a Form Submit Trigger:

1. Open your Google Sheet, go to Extensions > Apps Script to open the editor.
2. Click the clock icon (Triggers) on the left sidebar.
3. Click Add Trigger and configure these settings:
   • Choose function to run: generatePurchaseLetter
   • Choose deployment source: From spreadsheet
   • Choose event type: On form submit
4. Click Save, and follow the prompts to grant the necessary permissions (Google will warn you it's an unverified app—this is normal for your own scripts, just proceed through the safety steps).

How to Test It #

• Don't run the function directly in the script editor—that will still throw the same error because there's no e event data.
• Instead, open your Google Form, submit a test entry with a name and product. Your spreadsheet will capture the data, and the trigger will automatically run the function to create your letter.
• If you need to debug, add console.log(e.values) at the start of the function, then check the logs via View > Logs in the script editor to see exactly what data is coming through.

Bonus: Use a Template for Consistent Formatting #

If you want pre-formatted letters (with logos, headers, etc.), create a template Doc first, then modify the code to copy the template instead of making a blank doc:

// Replace the DocumentApp.create line with this:
const templateDocId = "YOUR_TEMPLATE_DOC_ID"; // Grab this from your template doc's URL
const copiedDoc = DriveApp.getFileById(templateDocId).makeCopy(`Purchase Inquiry - ${customerName}`);
const letterDoc = DocumentApp.openById(copiedDoc.getId());
const docBody = letterDoc.getBody();

// Then replace placeholders in your template (e.g., {{Name}} and {{Product}})
docBody.replaceText("{{Name}}", customerName);
docBody.replaceText("{{Product}}", customerProduct);

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