DEV Community

Cover image for Submitting Custom form data to Google sheets via React Js
Segun Tuase
Segun Tuase

Posted on • Updated on

Submitting Custom form data to Google sheets via React Js

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


Enter fullscreen mode Exit fullscreen mode

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.

submit-data-to-spreadsheet

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()
          }
        }



Enter fullscreen mode Exit fullscreen mode

This is the environment of the script

submit-data-to-spreadsheet

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.
submit-data-to-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))
    }



Enter fullscreen mode Exit fullscreen mode

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

submit-data-to-spreadsheet

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;


            }
        }

    }

`


Enter fullscreen mode Exit fullscreen mode

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

Top comments (18)

Collapse
 
preshpi profile image
Precious Egwuenu

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.

Collapse
 
jatinsaini135 profile image
Jatin Saini

Same With me as well

Collapse
 
anatugreen profile image
Anaturuchi

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

Collapse
 
shehjad_dev profile image
Shehjad Ali

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?

Collapse
 
tuasegun profile image
Segun Tuase

Youโ€™re trying to create a script on google sheets that collates data

Collapse
 
lilyntalmers profile image
lilyntalmers

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?

Collapse
 
tuasegun profile image
Segun Tuase

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

Collapse
 
darshan_v profile image
Darshan V

yes i need

Thread Thread
 
tuasegun profile image
Segun Tuase

Shoot me a google meet on tuase360@gmail.com

Collapse
 
stevelgtm profile image
ล tefan Hadbavnรฝ

If somebody will help this. When it is not working for you just run function initialSetup() at first.

Collapse
 
irishgeoff10 profile image
Geoff
Collapse
 
zlvzlv profile image
Zilvinaskli

I'm getting 401 response and cors error. Is there a workaround for that?

Collapse
 
devcreed profile image
Edun Elijah • Edited

Awesome ๐Ÿ‘Œ ๐Ÿ‘

The code that was pasted in the App script, is it a ready-made function?

Collapse
 
tuasegun profile image
Segun Tuase

Yes, something like that

Collapse
 
jduncan06 profile image
Jduncan06

I have used this in one of my projects and it worked great, but I tried to use it in a different project and it is not transferring my data

Collapse
 
haitham_ali_886731305fd56 profile image
Haitham Ali

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.