Segun Tuase

Posted on Mar 14, 2022 • Updated on Apr 11, 2022

Submitting Custom form data to Google sheets via React Js

#javascript #react #webdev #programming

In this article we will be discussing how to receive custom form data through React JS, often times when we need to do this we always have to pass through No-code APIs and other Middleware APIs that will generate links for us but Google already made sure this can work by creating a script in Google scripts and deploying it.

Requirements to do this include

Working Knowledge of React Js
Google Sheets
Basic Knowledge of Html

The first thing we are going to do is to create react app

You can learn how to do this with the create-react-app command that sets up a working react environment https://create-react-app.dev/

Then you clear up the unnecessary files you don’t need

The first thing we’ll do is to create our react form.

  import React, {useState, useRef} from 'react'


const Form = () => {
    const formRef = useRef(null)


  return (
    <div>
        <form method="post" ref={formRef} name="google-sheet">
        <div className="form-style">
            <input type=""  name="name" placeholder='Your Name *' />
        </div>  
        <div className="form-style">
            <input type="email" name="email" placeholder='Your Email *' />
        </div>
        <div className="form-style">
            <input type="number" name="phone" placeholder='Your Phone *' />
        </div>
        <div className="form-style">
            <input type="submit" name="submit" value="Login" />
        </div> 
        </form>
    </div>
  )
}

export default Form

In this little snippet, we built a form that allows the user to enter details like their name, email, and phone number. We also included a submit button that sends the data to the formRef mutable object created by the useRef hook.

The next step is to open our google spreadsheet that will be used to save the data then add each form input name as the column heading.

We then proceed to Extensions → App Scripts then copy this code into the app script, this script creates a function that accepts the data from the post request and stores it in the google sheets.

var sheetName = 'Sheet1'
        var scriptProp = PropertiesService.getScriptProperties()

        function intialSetup () {
          var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
          scriptProp.setProperty('key', activeSpreadsheet.getId())
        }

        function doPost (e) {
          var lock = LockService.getScriptLock()
          lock.tryLock(10000)

          try {
            var doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
            var sheet = doc.getSheetByName(sheetName)

            var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
            var nextRow = sheet.getLastRow() + 1

            var newRow = headers.map(function(header) {
              return header === 'timestamp' ? new Date() : e.parameter[header]
            })

            sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])

            return ContentService
              .createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))
              .setMimeType(ContentService.MimeType.JSON)
          }

          catch (e) {
            return ContentService
              .createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
              .setMimeType(ContentService.MimeType.JSON)
          }

          finally {
            lock.releaseLock()
          }
        }

This is the environment of the script

You proceed to save then add permissions. The next thing is to run the script then deploy the script. Make sure you change the access to "Anyone", it will then generate a link which will be used to POST the data to the Spreadsheet.

When this has been completed we’ll return to our react app to build the function that sends the data in the form to the Spreadsheet using the API link that was generated.

We proceed to add the function that will submit the form, this function is a fetch request using the post method. It posts the form data and it uses the Url gotten from our previous image/ deployment.


const scriptUrl = "get yours by practicing"

    const handleSubmit = (e) =>{
        e.preventDefault()

        fetch(scriptUrl, {method: 'POST', body: new FormData(formRef.current)})
        .then(res => {
            console.log("SUCCESSFULLY SUBMITTED")
        })
        .catch(err => console.log(err))
    }

The "form" tag should have the property of onSubmit ={handleSubmit} which calls the handleSubmit function during submission.

The final code and the full code is below with the styling with styled-component


import React, {useState, useRef} from 'react'
import styled from 'styled-components'

const Form = () => {
    const formRef = useRef(null)
    const scriptUrl = "get yours by practicing"
    const [loading, setLoading] = useState(false)

    const handleSubmit = (e) =>{
        e.preventDefault()
        setLoading(true)

        fetch(scriptUrl, {
        method: 'POST', 
        body: new FormData(formRef.current),

    }).then(res => {
            console.log("SUCCESSFULLY SUBMITTED")
            setLoading(false)
        })
        .catch(err => console.log(err))
    }

  return (
    <FormStyle>
        <div className="container">
        <form  ref={formRef} onSubmit={handleSubmit} name="google-sheet">
        <div className="input-style">
            <label htmlFor='name'>
                Name
            </label>
            <input type="text" id="name"  name="name" placeholder='Your Name *' />
        </div>  
        <div className="input-style">
            <label htmlFor='name'>Email</label>
            <input type="email" name="email" placeholder='Your Email *' />
        </div>
        <div className="input-style">
            <label htmlFor='name'>Phone No</label>
            <input type="number" name="phone" placeholder='Your Phone *' />
        </div>
        <div className="input-style">

            <input type="submit" value={loading ? "Loading..." : "SEND MESSAGE"} />
        </div> 
        </form>
        </div>
    </FormStyle>  
  )
}

export default Form

const FormStyle = styled.div`
    display: flex;
    justify-content: center;
    align-items: center;

    .container{
        background-color: #F9CFF2;
        margin-top: 10%;
        padding: 10rem 10rem 10rem 10rem;
        display: flex;
        justify-content: center;
        align-items: center;
        @media(max-width: 610px){
            padding: 4rem 4rem 4rem 4rem;
        }

        .input-style{
            padding-top: 0.8em;
            display: flex;
            flex-direction: column;
            gap: 0.8em;

            label{
                font-family: 'Poppins', sans-serif;
            }

            input{
                outline: none;
                border: none;
                padding: 0.8em;
                border-radius: 2em;
            }
            input[type=submit]{
                background-color: #52154E;
                color: #FFFFFFFF;
                font-weight: bold;


            }
        }

    }

`

If you have any issues with the code be sure to comment below or hit me up on twitter @SegunTuase

Top comments (16)

Precious Egwuenu • Aug 22 '23

Hey, Mine is saying this.

Access to XMLHttpRequest at 'script.google.com/macros/s/AKfycby...' from origin 'localhost:3020' has been blocked by CORS policy: Response to preflight request doesn't pass access control check: No 'Access-Control-Allow-Origin' header is present on the requested resource.

Shehjad Ali • Sep 9 '22

I didn't understand from the part - "We then proceed to Extensions → App Scripts then copy this code into the ". What are we doing here?

Segun Tuase • Sep 10 '22

You’re trying to create a script on google sheets that collates data

Anaturuchi • Nov 24 '22

Thanks for this. If you are here and you having a problem with this, you can check this video I made about it: Video

lilyntalmers • Jun 23 '22

Hey @tuasegun I tried but it's not sending data to my google spreadsheet. Do I need to put the spreadsheet id in the Apps Script? Also, can you please share a repository as a working example?

Segun Tuase • Jun 26 '22

Hello, do you still need help with this. I could schedule a meeting with you.

Darshan V • Jul 14 '22

yes i need

Segun Tuase • Jul 14 '22

Shoot me a google meet on tuase360@gmail.com

Haitham Ali • Oct 7 '22

THANK YOU. Worked first try. Nothing happened in the webpage (obviously) but the data popped up in the sheet. Now to display the thank you message and disable the form after submit.