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.

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.

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.

Get a Service Account Key

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

From there, click the Keys tab and add an new JSON 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.

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.

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.

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

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.