Building a Pick-Me Game with Next.js and Google Sheets Integration API

Google Sheets Integration API

In this article, we will delve into the exciting world of web development by creating a pick-me game using Next.js. Not only will we build an engaging user interface, but we’ll also explore the integration of Google Sheets to store user data effortlessly.

Prerequisites

• Next.js
• Google Sheets API

Getting Started with Next.js

To start our project, make sure you have Node.js installed, and then run the following commands:

npx create-next-app pick-me-game
cd pick-me-game
npm run dev

Now, you have a basic Next.js project up and running!

Rendering the User Interface

We need a page to input participant information fill.js and a page for the host random.js to randomly choose the winner.

In this article, I’ll focus on how to apply the Google Sheets API, so I won’t explain too much about other logic. I’ll share the git repository so you can read my logic after this article.

// pages/fill.js
export default function Fill() {
    return (
        <div>
            <div className="w-full max-w-xs m-auto mt-4">
                <form className="bg-white shadow-md rounded px-8 pt-6 pb-8 mb-4">
                    <div className="mb-4">
                        <input
                            name="name"
                            required
                            className="shadow appearance-none border rounded w-full py-2 px-3 text-gray-700 leading-tight focus:outline-none focus:shadow-outline"
                            type="text"
                            placeholder="Name" />
                    </div>
                    <div className="mb-6">
                        <input
                            name="phone"
                            required
                            className="shadow appearance-none border rounded w-full py-2 px-3 text-gray-700 leading-tight focus:outline-none focus:shadow-outline"
                            placeholder="Phone number" />
                    </div>
                    <div className="flex items-center justify-center">
                        <button className="shadow appearance-none border rounded w-full py-2 px-3 text-gray-700 leading-tight focus:outline-none focus:shadow-outline"
                            type="submit">
                            Join random game
                        </button>
                    </div>
                </form>
            </div>
        </div>
    )
}

// pages/random.js
export default function Random() {
    //...
    return (
        <div className='btn-start-game'>
            <button className="btn-game-start border-purple-800 rounded p-3 px-8 bg-white text-purple-600 hover:text-purple-800 border-4 hover:border-purple-800 hover:bg-white font-mono absolute shadow-2xl"
                onClick={findWinner}>
                START
            </button>
        </div>
    )
}

Storing Participant Data in Google Sheets

Setup Google API account

Go to https://console.cloud.google.com/ and create an account if you don't have one. Then go to Credentials and Create credentials > Create service account.

google service account

For this mini project we need 2 keys GOOGLE_CLIENT_EMAIL and GOOGLE_PRIVATE_KEY.

Note: Create a service account with owner permission.

One more key for Google Sheets is GOOGLE_SHEET_ID, You can get it in the link to your sheet.

For example, in https://docs.google.com/spreadsheets/d/1-6iugU-V9UrO7EDkVt-5x21LN5HeYAzHWgSku9Yy3TA

The key is 1-6iugU-V9UrO7EDkVt-5x21LN5HeYAzHWgSku9Yy3TA. And don't forget to share the editor permission for the GOOGLE_CLIENT_EMAIL.

Create api get Google Sheet data

We need 3 APIs for GET, CREATE, UPDATE.

GET

// pages/api/get
import { NextApiRequest, NextApiResponse } from 'next'
import { google } from 'googleapis'

export default async function handler(
    req: NextApiRequest,
    res: NextApiResponse
) {
    try {
        // prepare auth
        const auth = new google.auth.GoogleAuth({
            credentials: {
                client_email: process.env.GOOGLE_CLIENT_EMAIL,
                private_key: process.env.GOOGLE_PRIVATE_KEY?.replace(/\\n/g, '\n')
            },
            scopes: [
                'https://www.googleapis.com/auth/drive',
                'https://www.googleapis.com/auth/drive.file',
                'https://www.googleapis.com/auth/spreadsheets'
            ]
        })
        const sheets = google.sheets({
            auth,
            version: 'v4'
        })
        const response = await sheets.spreadsheets.values.get({
            spreadsheetId: process.env.GOOGLE_SHEET_ID,
            range: 'A:C',
        })
        return res.status(200).json({
            data: response.data
        })
    } catch (e) {
        console.error(e)
        return res.status(500).send({ message: 'Something went wrong' })
    }
}

CREATE

// pages/api/submit
import { NextApiRequest, NextApiResponse } from 'next'
import { google } from 'googleapis'

type SheetForm = {
    name: string
    phone: string
    status: number
}
export default async function handler(
    req: NextApiRequest,
    res: NextApiResponse
) {
    if (req.method !== 'POST') {
        return res.status(405).send({ message: 'Only POST request are allowed' })
    }
    const body = req.body as SheetForm
    try {
        // prepare auth
        const auth = new google.auth.GoogleAuth({
            credentials: {
                client_email: process.env.GOOGLE_CLIENT_EMAIL,
                private_key: process.env.GOOGLE_PRIVATE_KEY?.replace(/\\n/g, '\n')
            },
            scopes: [
                'https://www.googleapis.com/auth/drive',
                'https://www.googleapis.com/auth/drive.file',
                'https://www.googleapis.com/auth/spreadsheets'
            ]
        })
        const sheets = google.sheets({
            auth,
            version: 'v4'
        })
        const response = await sheets.spreadsheets.values.append({
            spreadsheetId: process.env.GOOGLE_SHEET_ID,
            range: 'A1:C1',
            valueInputOption: 'USER_ENTERED',
            requestBody: {
                values: [
                    [body.name, body.phone, body.status]
                ]
            }
        })
        return res.status(200).json({
            data: response.data
        })
    } catch (e) {
        console.error(e)
        return res.status(500).send({ message: 'Something went wrong' })
    }
}

UPDATE

// pages/api/update
import { NextApiRequest, NextApiResponse } from 'next'
import { google } from 'googleapis'

type SheetData = []
export default async function handler(
    req: NextApiRequest,
    res: NextApiResponse
) {
    if (req.method !== 'POST') {
        return res.status(405).send({ message: 'Only POST request are allowed' })
    }
    const body = req.body as SheetData
    try {
        // prepare auth
        const auth = new google.auth.GoogleAuth({
            credentials: {
                client_email: process.env.GOOGLE_CLIENT_EMAIL,
                private_key: process.env.GOOGLE_PRIVATE_KEY?.replace(/\\n/g, '\n')
            },
            scopes: [
                'https://www.googleapis.com/auth/drive',
                'https://www.googleapis.com/auth/drive.file',
                'https://www.googleapis.com/auth/spreadsheets'
            ]
        })
        const sheets = google.sheets({
            auth,
            version: 'v4'
        })
        const response = await sheets.spreadsheets.values.update({
            spreadsheetId: process.env.GOOGLE_SHEET_ID,
            range: 'A:C',
            valueInputOption: 'USER_ENTERED',
            requestBody: {
                values: body
            }
        })
        return res.status(200).json({
            data: response.data
        })
    } catch (e) {
        console.error(e)
        return res.status(500).send({ message: 'Something went wrong' })
    }
}

Handle logic

Handle participant validation information and add participant data to Google Sheets via API.

// fill.js
import { useEffect, useState } from 'react'
// ...
const [data, setData] = useState([])
const [isLoad, setIsLoad] = useState(false)

const fetchData = async () => {
    const req = await fetch('/api/get')
    const res = await req.json()
    if (res.data && res.data.values) {
        setData(res.data.values)
    }
    setIsLoad(true)
}
useEffect(() => {
    fetchData()
}, [])
const handleClick = async (e) => {
    e.preventDefault()
    const name = document.querySelector('#name').value
    const phone = document.querySelector('#phone').value
    const status = 1
    let checkPhone = 0
    if (data.length > 0) {
        for (let i = 0; i <= data.length; i++) {
            // break condition     
            if (data[i] && data[i][1] == phone) {
                console.log(data[i][1])
                setErrorText('Joined phone number!')
                setError(true)
                checkPhone = 1
                break;
            }
        }
    }
    if (checkPhone == 1) {
        return false
    }
    //...
    const form = {
        name,
        phone,
        status
    }
    const response = await fetch('/api/submit', {
        method: 'POST',
        headers: {
            'Accept': 'application/json',
            'Content-Type': 'application/json'
        },
        body: JSON.stringify(form)
    })
    const content = await response.json()
    console.log(content)
}
// ...

Random the winner

// ramdom.js
import { useEffect, useState, useRef } from 'react'
// ...
const [gameState, setGameState] = useState(false)
const [data, setData] = useState([])
const [showLabel, setShowLabel] = useState(false)
const [index, setIndex] = useState(null)

const handleClick = (state) => {
    setGameState(state)
}
const fetchData = async () => {
    const req = await fetch('/api/get')
    const res = await req.json()
    if (res.data && res.data.values) {
        setData(res.data.values)
    }
}
useEffect(() => {
    fetchData()
}, [])
const findWinner = async () => {
    var winnerIdx = Math.floor(Math.random() * data.length)
    var newData = []
    if (data[winnerIdx][2] == 0) {
        findWinner()
    }
    setLoading(true)
    setTimeout(() => {
        setIndex(winnerIdx)
        setShowLabel(true)
        setLoading(false)
    }, 5000)
    // Update data
    data.forEach((item, i) => {
        newData[i] = item
        if (winnerIdx == i) {
            newData[i] = [item[0], item[1], 0]
        }
    })
    const response = await fetch('/api/update', {
        method: 'POST',
        headers: {
            'Accept': 'application/json',
            'Content-Type': 'application/json'
        },
        body: JSON.stringify(newData)
    })
    const content = await response.json()
    console.log(content)
    fetchData()
}
// ...

Demo game

After register here register

join game as participant

The data after registration is here: https://docs.google.com/spreadsheets

The host can start the game here: Start game

start game

Click start to choose the winner

pick the winner random

You see the demo here: pickme.bunhere.com

pickme demo

End

Author: bunhere.com

I am always looking for feedback on my writing, so please let me know what you think. ❤️