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

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

Google Sheets 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.

ggcloud

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