Next.js and Google Sheets

5.17.23

Google Sheets and/or Microsoft Excel spreadsheets are industry standards for data management, financial analysis, presentations and reports, project management (Google Sheets actually makes a great scrum board) and general organization. Be it Google Sheets or Excel, these spreadsheets have been an integral tool for every job I have ever had. Even though Microsoft now has Excel Online which works the same way as Google Sheets, being able to access and edit your spreadsheets from any web browser, Google Sheets still has the huge advantage of being totally free. You can’t really beat just typing sheets.new into any browser to have a fully functional and collaborative spreadsheet.

On top of the aforementioned uses for Google Sheets, there are some creative ways developers are using it including using Google Sheets as databases for web apps. Through the use of APIs, you can programmatically perform CRUD (create, read, update, delete) operations on the data in a Google Sheet. Let’s break that down a little. That means having a database with the comfort zone UI of an Excel spreadsheet that can be shared with other users. You will also have real-time updates and not have to worry about infrastructure management such as maintaining database servers and creating backups. Since this is Google, another nice thing about this is the integration possibilities. Google Sheets plays very nicely with a myriad of other tools for automation and extended functionality within the Google ecosystem.

You knew this was coming, the disadvantages of using Google Sheets as the database for your app. Off the bat, Google Sheets is not an ACID compliant database which would make it illegal in certain specific cases. There is practically no scalability because it’s not designed to handle large datasets or heavy traffic. You are obviously locked into the spreadsheet tabular data structure which also means you can’t perform complex queries, manipulation or aggregation.

So using Google Sheets as the primary database for an app seems a little impractical, but using it for a specific feature like using it with contact forms can be very cool. Google provides an officially supported Node.js client library for accessing Google APIs that you can pretty easily integrate into a Next.js project. One of Next.js’s core features is the serverless API routing system. This is what makes Next.js feel like a full-stack framework. A Next.js serverless API handler means you don’t need a traditional backend server running and the code will only execute when you need it to. Using a Next.js serverless API with Google Sheets means this feature will have no extra costs to maintain. Google Sheets also has a cool feature that can email the users of the spreadsheet when new data is entered (or changed) which means you can even skip integration with a third party email provider like SendGrid.

I’m not going to turn this into a full blown tutorial but I will share how it is done and the core code to make this work. If you are totally unfamiliar with GCP (Google Cloud Platform), feel free to email me for some help. Otherwise in your GCP console, you will have to enable the Google Sheets API, create some credentials, create a service account, and create some API keys. You will also need the specific spreadsheet ID which is the part of the url that comes right after https://docs.google.com/spreadsheets/d/. You will also need a frontend form component that is sending a POST request to your serverless API route. For stuff like this I usually like to use react-hook-form for validation. Once your form is set up, make sure you npm i googleapis to install the Google Node.js client library. Then in the API directory in the Next.js project, drop this code in.

import { google } from 'googleapis';

export default async function handler(req, res) {
if (req.method === 'POST') {
const { Name, Email, Subject, Message } = req.body;

    const auth = new google.auth.GoogleAuth({
        credentials: {
            client_email: process.env.GOOGLE_CLIENT_EMAIL,
            client_id: process.env.GOOGLE_CLIENT_ID,
            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: "YOUR SPREADSHEET ID",
        range: 'Sheet1!A2:D', // The range of cells where you want the data entered.
        valueInputOption: 'USER_ENTERED',
        requestBody: {
            values: [[Name, Email, Subject, Message]],
        },
    });

    res.status(201).json({ message: 'Data successfully entered to google sheet', response });
} else {
    res.status(200).json({ message: 'Success' });
}

}