Connecting NextJS Mini Project with Google Spreadsheets as Database

Did you know that we can use Google Sheets as our database?
Yes! We can do that 👍.

Today, I will share step by step to do it.
I will use one of the Javascript Frontend Library to do this, it's NextJS.

So, let's get started! 🚀

Create Your Google Project

1. Go to your google console page
2. Create your project, or you can visit through this Link 🔗
3. 🔙 Back to APIs & Services page, click on ENABLE APIS AND SERVICES
4. Search for Google Sheets API then enable it 🖱️
5. 🔙 Back again to APIs & Services page, click Credentials, CREATE CREDENTIALS, Service Account. Or through this Link 🔗
6. Input required field and you can ignore optional field (of course 😅)
7. 🔙 Back to your Credentials page, under Service Accounts you will see your email. Copy that!
8. Open your Service Account. Click on ADD KEY, choose JSON as type. Then your credentials will be automatically downloaded. This will be used when connecting you NextJS to the sheets.
9. Now you can create new spreadsheets or use your existing one, Open it
10. Share it to your Service Account Email you just copied a second ago
11. This step is done 🎊

Create NextJS App

Reference: NextJS Docs

1. Create an app
   

   npx create-next-app [APP_NAME]
   # or
   yarn create next-app [APP_NAME]
   

2. 🏃‍♂️ Run your app with npm run dev, wait until server started

3. Without making any changes, you can access you page on localhost:3000. You will see awesome Next starter page 😎
   
   

4. Continue 🚀, Setup Environment Variables

5. Create env.local file. You will need set this key-value pair. The value you can get on downloaded .JSON file before from google console
   

   GOOGLE_SHEETS_PRIVATE_KEY=[YOUR KEY]
   GOOGLE_SHEETS_CLIENT_EMAIL=[YOUR ACCOUNT EMAIL]
   SPREADSHEET_ID=[YOU CAN GET THIS ON URL OF YOUR SHEETS]
   

6. We will need googleapis package to help us doing this, so install it
   

   npm i googleapis
   

7. Next, create a file for connecting to our sheet. For me is under libs/sheets.js
   
   

import { google } from 'googleapis';
export async function getEmojiList() {
  try {
    const target = ['https://www.googleapis.com/auth/spreadsheets.readonly'];
    const jwt = new google.auth.JWT(
      process.env.GOOGLE_SHEETS_CLIENT_EMAIL,
      null,
      (process.env.GOOGLE_SHEETS_PRIVATE_KEY || '').replace(/\\n/g, '\n'),
      target
    );

    const sheets = google.sheets({ version: 'v4', auth: jwt });
    const response = await sheets.spreadsheets.values.get({
      spreadsheetId: process.env.SPREADSHEET_ID,
      range: 'emoji', // sheet name
    });

    const rows = response.data.values;
    if (rows.length) {
      return rows.map((row) => ({
        title: row[2],
        subtitle: row[3],
        code: row[4],
        browser: row[5],
        short_name: row[17],
        emojipedia_slug: row[18],
        descriptions: row[19],
      }));
    }
  } catch (err) {
    console.log(err);
  }
  return [];
}

Description:

• We will specify our target connection

const target = ["https://www.googleapis.com/auth/spreadsheets.readonly"];

• Declare our jwt for authentication

const jwt = new google.auth.JWT(
    process.env.GOOGLE_SHEETS_CLIENT_EMAIL,
    null,
    (process.env.GOOGLE_SHEETS_PRIVATE_KEY || '').replace(/\\n/g, '\n'),
    target
);

• Get the sheet data, don't forget to change sheet name

const sheets = google.sheets({ version: 'v4', auth: jwt });
const response = await sheets.spreadsheets.values.get({
   spreadsheetId: process.env.SPREADSHEET_ID,
   range: 'emoji', // sheet name
});

• Declare the row as your sheet data

const rows = response.data.values;
if (rows.length) {
    return rows.map((row) => ({
        title: row[2],
        subtitle: row[3],
        code: row[4],
        browser: row[5],
        short_name: row[17],
        emojipedia_slug: row[18],
        descriptions: row[19],
    }));
}

That was a lot of text, now let's get the easy part 😬

Populate Your Data

This is my index.js file

import Head from 'next/head';
import { getEmojiList } from '../libs/sheets';

export default function IndexPage({ emojis }) {
  return (
    <>
      <Head>
        <title>Title - FrasNym</title>
        <meta name="viewport" content="initial-scale=1.0, width=device-width"/>
      </Head>
      {emojis[0].title}
    </>
  );
}

export async function getStaticProps(context) {
  const emojis = await getEmojiList();
  return {
    props: {
      emojis: emojis.slice(1, emojis.length), // remove sheet header
    },
    revalidate: 1, // In seconds
  };
}

You can already see our data from emojis variable that passed through props.

As you expect, We load the data from Google Sheets via our getEmojiList function. Then we call that function from getStaticProps function and pass it as props to our component.

On getStaticProps, we pass revalidate: 1 so we hope that NextJS page will render when our sheets updated (At most once every second).

Summary

This tools will help us to do a small project with database easily.
💡 FYI, You can insert to sheet via API too. You can connect this sheet with Your NodeJS with google-spreadsheet package, for example.

Closing

That's all I can share with you for now 😊
If you have any question, you can post it here.
Or maybe you can reach me on my Twitter 😁

Until then... 🚀

Comments

[Ath Athoillah]

Hi, I get problem with child_process:

./node_modules/.pnpm/google-auth-library@8.6.0/node_modules/google-auth-library/build/src/auth/googleauth.js:17:0
Module not found: Can't resolve 'child_process'

googleapis & next version:

...
    "googleapis": "^109.0.0",
    "next": "13.0.0",
...

Anybody have solution?

[Ath Athoillah]

Ok, try to change getStaticProps() to getServerSideProps() and move all getEmojiList() body function to getServerSideProps() and it's work

[danforejtek]

Thank you very much! I was trying google way of doing it developers.google.com/sheets/api/q... from their documentation and it was total hell.
Your way is much simpler and understandable. JWT is much better.

[Frastyawan Nym]

Your most welcome! 🥳

[Gandarufuuu]

Sheesh, best tutorial I've found so far. Thanks so much for that!
To everyone who is on a newer next.js version with the app-directory:

• Rename "page.tsx" to "page.js".
• Place code from "index.js" in "page.js".
• Inside "page.js" before return add:

const data = await getData()

• Delete getStaticProps function and replace with

async function getData(){
const res = await getDataFromSheets();
const data = res.slice(1, res.length)
return data
}

[Daniele Bertella]

Hi thanks for the useful guide it's very simple and complete! Question for you or maybe someone else. Did you try to cache this requests to be able to use them in different routes without the need to share props between them? I'm trying to use cache from react with no luck, I just opened an issue on googleapi repo too but maybe somebody already found a solution here. Thanks again!

[Daniele Bertella]

UPDATE: I closed the issue, FYI it turned out react cache doesn't cache request between different server components while unstable_cache from next/cache does hope I will save some time if you are stamping on the same issue

[Luke]

Thanks a lot! GitHub repo starter here: nextsheet.vercel.app/

[Junaid]

will this be valid if i have users logged in and i want them to store there data into there respective google sheets (asking for permission from them obviously )

[Manuel Obregozo]

Way simpler than other docs I found online. Simple and concise.
Good post!

[Joshua Andrew Jourdain]

i am getting this error....

Missing required parameters: spreadsheetId, range

[Sergo]

You need to use the keyword 'range', like:

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

[Olavi Haapala]

Thanks! This was super helpful

[Frastyawan Nym]

It is my pleasure, to be able to help 😊