Error message while automating data entry from web app contact form to google sheet

Question

I am trying to automate data entry from a contact form on my web app to a google sheet such that when ever the user submits the contact from, the form inputs (name, email, message etc) are automatically entered and arranged on the google sheet. However, whenever I test the functionality I get the error message

"An error occurred. Please try again".

Please I dont know where Im getting it wrong. This is my contact form code :

import { useForm, SubmitHandler } from 'react-hook-form';

interface IFormInputs {
  name: string;
  businessName: string;
  email: string;
  phoneNumber: string;
  message: string;
}

export default function ContactForm() {
  const { register, handleSubmit, formState: { errors } } = useForm<IFormInputs>();

  const onSubmit: SubmitHandler<IFormInputs> = async (data) => {
    try {
      console.log('Submitting form data:', data); // Log the data being sent

      const response = await fetch('https://script.google.com/macros/s/AKfycbzvMAauhjdECZa-pZo5tTxPM1UrjQbS2jTJsbyiWYAy3LxoPMC9ZM116834mNzBT1ulLw/exec', {
        method: 'POST',
        headers: {
          'Content-Type': 'application/json',
        },
        body: JSON.stringify(data),
      });

      console.log('Response status:', response.status); // Log the response status

      if (response.ok) {
        const result = await response.json();
        console.log('Response result:', result); // Log the result
        if (result.success) {
          alert('Form submitted successfully!');
        } else {
          alert('Failed to submit form. Please try again.');
        }
      } else {
        alert('Failed to submit form. Please try again.');
      }
    } catch (error) {
      console.error('Error submitting form:', error); // Log the error
      alert('An error occurred. Please try again.');
    }
  };

  return (
    <form onSubmit={handleSubmit(onSubmit)} className="space-y-6">
      <div>
        <label htmlFor="name" className="block text-sm font-medium text-gray-100">Name *</label>
        <input
          type="text"
          id="name"
          {...register("name", { required: "Name is required" })}
          className="mt-1 block w-full rounded-md border-light-green shadow-sm focus:border-light-green focus:ring focus:ring-light-green focus:ring-opacity-50 bg-white text-charcoal-gray"
        />
        {errors.name && <p className="mt-1 text-sm text-red-600">{errors.name.message}</p>}
      </div>

      <div>
        <label htmlFor="businessName" className="block text-sm font-medium text-gray-100">Business Name</label>
        <input
          type="text"
          id="businessName"
          {...register("businessName")}
          className="mt-1 block w-full rounded-md border-light-green shadow-sm focus:border-light-green focus:ring focus:ring-light-green focus:ring-opacity-50 bg-white text-charcoal-gray"
        />
      </div>

      <div>
        <label htmlFor="email" className="block text-sm font-medium text-gray-100">Email *</label>
        <input
          type="email"
          id="email"
          {...register("email", { 
            required: "Email is required",
            pattern: {
              value: /\S+@\S+\.\S+/,
              message: "Invalid email address"
            }
          })}
          className="mt-1 block w-full rounded-md border-light-green shadow-sm focus:border-light-green focus:ring focus:ring-light-green focus:ring-opacity-50 bg-white text-charcoal-gray"
        />
        {errors.email && <p className="mt-1 text-sm text-red-600">{errors.email.message}</p>}
      </div>

      <div>
        <label htmlFor="phoneNumber" className="block text-sm font-medium text-gray-100">Phone Number</label>
        <input
          type="tel"
          id="phoneNumber"
          {...register("phoneNumber")}
          className="mt-1 block w-full rounded-md border-light-green shadow-sm focus:border-light-green focus:ring focus:ring-light-green focus:ring-opacity-50 bg-white text-charcoal-gray"
        />
      </div>

      <div>
        <label htmlFor="message" className="block text-sm font-medium text-gray-100">Message *</label>
        <textarea
          id="message"
          rows={4}
          {...register("message", { required: "Message is required" })}
          className="mt-1 block w-full rounded-md border-light-green shadow-sm focus:border-light-green focus:ring focus:ring-light-green focus:ring-opacity-50 bg-white text-charcoal-gray"
        ></textarea>
        {errors.message && <p className="mt-1 text-sm text-red-600">{errors.message.message}</p>}
      </div>

      <div>
        <button
          type="submit"
          className="w-full flex justify-center py-2 px-4 border border-transparent rounded-md shadow-sm text-sm font-medium text-white bg-light-green hover:bg-dark-green focus:outline-none focus:ring-2 focus:ring-offset-2 focus:ring-light-green"
        >
          Send Message
        </button>
      </div>
    </form>
  );
}

This is the code on my Google sheet Appscript:

function doPost(e) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  try {
    // Log the incoming data
    Logger.log('Incoming data: ' + JSON.stringify(e.postData.contents));
    
    // Parse the incoming data
    const data = JSON.parse(e.postData.contents);
    
    // Append the data to the sheet
    sheet.appendRow([data.name, data.businessName, data.email, data.phoneNumber, data.message]);
    
    // Return a success response
    return ContentService.createTextOutput(JSON.stringify({ success: true })).setMimeType(ContentService.MimeType.JSON);
  } catch (error) {
    Logger.log('Error: ' + error);
    return ContentService.createTextOutput(JSON.stringify({ success: false, error: error.message })).setMimeType(ContentService.MimeType.JSON);
  }
}

I have set up a trigger for the appscript code to run on submit. The column headers on the sheet are (name, businessName, email, phoneNumber, message) written exactly like that as it appears on the code. I have also written them as they appear in their placeholder format and it did not work. I have made sure I use the same web url generated on app script deploy on my web app however, Everytime I still get the same error message

Answer 1

Using Axios instead of Fetch

To answer your question the problem of your code is on the frontend side fetching the response.ok in your script. However, There is no way to respond POST request in appscript with the status code of 200 or ok = true therefore the workaround is to create split script, answered by someone in our community 200-HTTP-Status.

The fetch does work but you need to add more parameters in the headers such as "Access-Control-Allow-Origin": "*" to allow any domain to access the resource. But base on what I tried and research I don't see any information that fetch has any return values in doPost() in Google App Script.

To make it work since you are using typescript frontend framework install the node package module axios.

I refactored the code and which I also research using these methods is to use another library Axios it is also a promise-based HTTP Client for node.js. Axios accepts responses from Google App Script because it handles HTTP requests and responses. Google Apps Script can serve as a web service, returning JSON or other data formats that Axios can easily process.

You can read reference here on how to install and add to your project. Axios

Frontend Code:

import axios from 'axios'

const onSubmit: SubmitHandler<IFormInputs> = async (data) => {
  try {
    console.log('Submitting form data:', data); // Log the data being sent

    const url = "Web App Url";

    axios.post(url, JSON.stringify(data)).then(response => {
      console.log(response); // to check the response from the apps script.
      return response.status == 200 && response.data.success ? alert('Form submitted successfully!') : alert('Failed to submit form. Please try again.');
    });
  } catch (error) {
    console.error('Error submitting form:', error); // Log the error
    alert('An error occurred. Please try again.');
  }
};

Sample Web HTML: