Update Google Sheets

Snippet

Back

Send submitted form data to a Google Sheet

I am using `react-hook-form` to build a simple form. Once a user submits the form, the data will be inserted into my Google Sheet.

// /lib/googleSheet.js
const { google } = require('googleapis')
const sheets = google.sheets({
    version: 'v4',
    auth: process.env.GOOGLE_API_KEY,
})

const appendToSheet = async (data) => {
    const sheetId = 'process.env.NEXT_PUBLIC_GOOGLE_SHEET_ID'
    const range = 'Sheet1!A:A' //the range of the sheet you want to append to
    const valueInputOption = 'USER_ENTERED'
    const insertDataOption = 'INSERT_ROWS'
    const resource = {
        values: [Object.values(data)],
    }

    return sheets.spreadsheets.values.append({
        spreadsheetId: sheetId,
        range,
        valueInputOption,
        insertDataOption,
        resource,
    })
}

// /api/submit-sheet.js
export default async (req, res) => {
  try {
      const { data } = req.body
      await appendToSheet(data)
      res.status(200).json({ message: 'Success' })
  } catch (error) {
      res.status(500).json({ message: 'Error' })
  }
}

// in your form component
import useForm from 'react-hook-form'

const Form = () => {
    const { register, handleSubmit } = useForm()

    const onSubmit = async (data) => {
        try {
            const res = await fetch('/api/submit', {
                method: 'POST',
                headers: { 'Content-Type': 'application/json' },
                body: JSON.stringify({ data }),
            })
            const json = await res.json()
            console.log(json.message)
        } catch (error) {
            console.log(error)
        }
    }

    return (
        <form onSubmit={handleSubmit(onSubmit)}>
          <input
            type="text"
            name="name"
            placeholder="Name"
            ref={register}
          />
          <input
            type="email"
            name="email"
            placeholder="Email"
            ref={register}
          />
            <button type="submit">Submit</button>
        </form>
    )
}

export default Form

These components are responsible for creating a form that accepts user input, submitting the form data to an API route `/api/submit-sheet`, which in turn saves the data to a Google Sheet using the `appendToSheet` function.

The `appendToSheet` function is a module that uses the `googleapis` library to interact with Google Sheets API. It appends the data passed to it as a new row to a sheet with ID stored in the `process.env.NEXT_PUBLIC_GOOGLE_SHEET_ID` environment variable.

The `submit-sheet.js` API route receives the data sent by the form and passes it to the `appendToSheet` function to save to Google Sheets. It returns a success message if the data is saved successfully or an error message if the saving process fails.

The `Form` component uses the `react-hook-form` library to manage the form state and validation. When the user submits the form, it sends the form data as a POST request to the `/api/submit-sheet` API route, which handles the saving of data to Google Sheets. The success or error message is logged to the console.

Snippet: Update Google Sheets

Snippet

Update Google Sheets