Automated form submission to Google Sheets using JavaScript

[miar]

Issue with JavaScript form submission to Google Sheets

I’m working on a browser script that extracts data from web pages and automatically sends it to a Google Sheets document through a form I created. The script should grab information and submit it without user interaction.

Current approach:

var submitForm = document.createElement("form");

submitForm.action = "https://docs.google.com/forms/d/e/SAMPLE_FORM_ID/formResponse";
submitForm.method = "POST";
submitForm.className = "auto-submit";
submitForm.innerHTML = [
  "<input name='entry.123456' value='" + dateValue + "'/>",
  "<input name='entry.789012' value='" + userEmail + "'/>",
  "<input name='entry.345678' value='" + accountNumber + "'/>"
].join("");
submitForm.submit();

console.log(submitForm.innerHTML);

Problems I’m facing:

• No data appears in the spreadsheet after running the script
• Cannot detect any network activity in browser dev tools
• No way to check if Google accepted the submission

Alternative approach:

Instead of using formResponse endpoint, maybe I should target the regular form view and populate it programmatically. The form URL structure is like: docs.google.com/forms/d/FORM_ID/viewform

How can I inject values into the actual form fields and trigger submission from my script? The script runs from a different page than where the form is hosted.

[Tom42Gamer]

This is a CORS issue - Google Forms expects specific headers and doesn’t play nice with regular POST requests. I hit the same wall last year building a data collection tool. Here’s what actually works: ditch the POST and use GET with URL parameters instead. Try window.open('https://docs.google.com/forms/d/e/FORM_ID/formResponse?entry.123456=' + encodeURIComponent(dateValue) + '&entry.789012=' + encodeURIComponent(userEmail), '_blank'). Completely sidesteps CORS. Also, inspect the form HTML to verify those entry IDs - they change whenever you mess with the form structure.

[Isaac_Cosmos]

The Problem:

You’re attempting to automate Google Form submissions using JavaScript, but your POST requests to the formResponse endpoint are failing silently, with no data appearing in your spreadsheet and no network activity detected in the browser’s developer tools. The current approach uses a dynamically created form, and you’re considering alternatives like directly manipulating the form’s HTML elements.

Understanding the “Why” (The Root Cause):

The primary reason your initial approach fails is likely due to Cross-Origin Resource Sharing (CORS) restrictions imposed by Google Forms. The formResponse endpoint is designed for user interactions and has strict security measures that often block automated submissions from scripts running on different domains. Directly submitting a POST request from a separate website often lacks the necessary headers and authorization that Google Forms expects. The browser might prevent the request entirely before it even reaches the server.

Step-by-Step Guide:

1. Use Google Apps Script: The most reliable solution is to bypass the direct form submission altogether. Create a Google Apps Script that acts as an intermediary. This script will receive data via a web app, and then write directly to your Google Sheet using the Sheets API. This eliminates CORS issues and provides robust error handling.

   • Create a Google Apps Script Project: Open Google Apps Script (script.google.com).

   • Write the Script: Here’s a basic example:

     function doGet(e) {
       return HtmlService.createTemplateFromFile('index').evaluate();
     }
     
     function doPost(e) {
       const data = JSON.parse(e.postData.contents);
       const ss = SpreadsheetApp.getActiveSpreadsheet();
       const sheet = ss.getSheetByName('Sheet1'); // Replace 'Sheet1' with your sheet name
       sheet.appendRow([data.dateValue, data.userEmail, data.accountNumber]); 
       return ContentService.createTextOutput('Success!');
     }
     

     You’ll also need an index.html file (in the script editor) for the web app’s interface. This is optional if your JavaScript just sends a POST request with the data.

   • Deploy as a Web App: In the Apps Script editor, go to “Deploy” → “New deployment”. Choose “Web app,” set the permissions appropriately (at least “Anyone, even anonymous”), and deploy. You’ll get a unique URL.

2. Modify Your JavaScript: Update your JavaScript to send a POST request to your newly deployed Google Apps Script web app URL instead of the Google Forms formResponse endpoint. Use fetch for reliable asynchronous communication:

   const appScriptUrl = 'YOUR_DEPLOYED_WEB_APP_URL'; // Replace with your URL
   
   fetch(appScriptUrl, {
       method: 'POST',
       headers: {
           'Content-Type': 'application/json'
       },
       body: JSON.stringify({
           dateValue: dateValue,
           userEmail: userEmail,
           accountNumber: accountNumber
       })
   })
   .then(response => response.text())
   .then(data => console.log(data)) // Check for success/error message
   .catch(error => console.error('Error:', error));
   

3. Verify the Data: Check your Google Sheet to confirm that the data is being correctly appended.

Common Pitfalls & What to Check Next:

• Incorrect Entry IDs: Double-check the entry.* IDs in your code. They are unique identifiers specific to your Google Form and can change if you modify the form’s structure. Inspect the form’s HTML source (using your browser’s developer tools) to find the correct IDs.
• JSON Parsing: If using a more complex data structure, ensure your Apps Script correctly parses the JSON data received from the POST request. console.log(e.postData.contents); inside your doPost function can help debug this.
• Web App Permissions: Verify your deployed web app has the necessary permissions to access and write to your Google Sheet.
• Deployment Settings: Make sure your web app is correctly deployed as a public web app with the right permissions.

Still running into issues? Share your (sanitized) config files, the exact command you ran, and any other relevant details. The community is here to help!

[Claire29]

use fetch() instead of submitting the form directly, since google blocks cross-origin requests. try fetch(formUrl, {method: 'POST', body: formData}). also, make sure to check the entry ids from the actual form source.