Google Sheets as a Database

Choosing the right database for your app is difficult. If you have a small project or are just prototyping, Google Sheets actually works quite nicely as a primary database. It is especially practical for JAM-stack web apps that use server-side rendering. The following tutorial will teach you how to use Google Sheets as a database for Next.js.

⚠️ Be warned… Google Sheets does not scale like a true production database (because that’s not what it is for). It can only store 5 million cells, won’t do queries or joins, and has API quotas.

Initial Setup

Create a Spreadsheet

Create a spreadsheet and add some data to it. Make a note of the sheet ID in the URL.

Grab Google Sheets Spreadsheet ID

Grab Google Sheets Spreadsheet ID

Create a Next.js App

💡 This demo uses Next.js, but the process is the same for virtually any server-side application. Keep in mind, this code should only run server-side because it requires sensitive auth credentials that shouldn’t be exposed client-side.

Create a next app and install the googleapis package.

command line
npx create-next-app holy-sheet

cd holy-sheet
npm install googleapis

Sheets API Setup

Enable the Google Sheets API

From the Google Cloud console, enable the Google Sheets API.

Enable Google Sheets API

Enable Google Sheets API

Get a Service Account Key

Click manage, then go to the credentials tab. Click on the App Engine default service account.

Default service account

Default service account

From there, click the Keys tab and add an new JSON key.

Download service account key

Download service account key

Save the Key

This will download a JSON file to your system. Save it to the root of the project as secrets.json, but do NOT expose it publicly. Add it to gitignore to be safe.

file_type_git .gitignore
secrets.json

Next.js Environment Variables

When performing server-side rending, Next.js will look in the .env.local file for environment variables. Create the file and save the path of your service account.

file_type_config .env.local
GOOGLE_APPLICATION_CREDENTIALS=./secrets.json
SHEET_ID="Sheet ID found on Google Sheets URL"

Google Sheets Database

Authenticate the API

The getServerSideProps function runs on the server (node.js) to fetch data before the HTML is rendered by React. Google will look for the environment variable with the service account and use it to automatically authenticate. We just need to request the Google Sheets scope.

pages/posts/[id].js
import { google } from 'googleapis';

export async function getServerSideProps({ query }) {

    const auth = await google.auth.getClient({ scopes: ['https://www.googleapis.com/auth/spreadsheets.readonly'] });

    const sheets = google.sheets({ version: 'v4', auth });
}

Query the Sheet

Now let’s imagine we have a URL like this posts/:id, where the ID is a row in the spreadsheet. We get the ID from the URL, then use it to dynamically request a range of cells from the sheet. View more

pages/posts/[id].js
import { google } from 'googleapis';

export async function getServerSideProps({ query }) {

    // auth omitted...

    const { id } = query;
    const range = `Sheet1!A${id}:C${id}`;

    const response = await sheets.spreadsheets.values.get({
      spreadsheetId: process.env.SHEET_ID,
      range,
    });

    const [title, content] = response.data.values[0];
    console.log(title, content)

    return { 
        props: {
            title,
            content
        } 
    }
}

export default function Post({ title, content }) {
    return <article>
        <h1>{title}</h1>
        <div>{content}</div>
    </article>
}

Pretty simple! View examples in the official docs for more ways to read values from the spreadsheet.

Q&A Chat