如何通过HTML表单结合JavaScript向Google Sheet推送数据

Hey there! Let's get your HTML form data pushing to Google Sheets without a hitch. I've put together a complete, tested setup below, with explanations for each part so you know exactly what's going on.

First, make sure your form has proper name attributes for each input—this matches up with column headers in your Google Sheet and makes grabbing values way easier. Here's a simple, working example:

<form id="sheetForm">
  <label for="fullName">Full Name:</label>
  <input type="text" id="fullName" name="fullName" required>
  
  <label for="emailAddress">Email:</label>
  <input type="email" id="emailAddress" name="emailAddress" required>
  
  <label for="feedback">Feedback:</label>
  <textarea id="feedback" name="feedback" required></textarea>
  
  <button type="submit">Send Data</button>
</form>

<!-- Optional: For user feedback after submission -->
<div id="statusAlert"></div>

Add this script (either in a <script> tag at the end of your HTML body or an external JS file) to handle form submission and send data to your Google Apps Script endpoint:

const form = document.getElementById('sheetForm');
const statusAlert = document.getElementById('statusAlert');

form.addEventListener('submit', async (e) => {
  e.preventDefault(); // Stop the form from reloading the page
  
  // Turn form data into a usable object
  const formData = new FormData(form);
  const submissionData = Object.fromEntries(formData);
  
  try {
    const response = await fetch('https://script.google.com/macros/s/XXXXXXXXXXXXXXXXXXXXXXXXXXXXX/exec', {
      method: 'POST',
      mode: 'no-cors', // Critical for avoiding CORS issues with Google's endpoint
      headers: {
        'Content-Type': 'application/x-www-form-urlencoded',
      },
      body: new URLSearchParams(submissionData).toString(), // Encode data correctly for the API
    });
    
    // Let the user know it worked
    statusAlert.textContent = 'Success! Your data has been sent.';
    statusAlert.style.color = 'green';
    form.reset(); // Clear the form after a successful submit
  } catch (error) {
    // Handle any errors that pop up
    statusAlert.textContent = 'Oops! Something went wrong. Please try again.';
    statusAlert.style.color = 'red';
    console.error('Submission error details:', error);
  }
});

• Google Apps Script Permissions: When you deployed your script, make sure you selected "Anyone, even anonymous" under "Who has access". Skip this, and you'll get permission denied errors left and right.
• Sheet Column Matching: The name attributes in your form inputs should exactly match the column headers in your Google Sheet (case doesn't strictly matter, but consistency prevents confusion). For example, if your sheet has a column "Email Address", use name="emailAddress" in your input.
• Local Testing: If you're testing on your computer, use a live server tool (like VS Code's Live Server extension) instead of opening the HTML file directly. Direct file access can trigger CORS issues even with no-cors mode enabled.
• Endpoint URL: Triple-check that you copied the correct deployment URL—no extra spaces or typos!

If things aren't working, open your browser's DevTools (press F12) and check the Console tab. It'll show you detailed error messages, like if your endpoint is incorrect or permissions are misconfigured.

内容的提问来源于stack exchange，提问作者Giovanni De Stefanis