DEV Community

loading...
Cover image for Connecting NextJS Mini Project with Google  Spreadsheets as Database

Connecting NextJS Mini Project with Google Spreadsheets as Database

frasnym profile image Frastyawan Nym ・4 min read

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 πŸ–±οΈ Alt Text
  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. Alt Text
  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 😎

    Alt Text

  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 [];
}
Enter fullscreen mode Exit fullscreen mode

Description:

  • We will specify our target connection
const target = ["https://www.googleapis.com/auth/spreadsheets.readonly"];
Enter fullscreen mode Exit fullscreen mode
  • 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
);
Enter fullscreen mode Exit fullscreen mode
  • 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
});
Enter fullscreen mode Exit fullscreen mode
  • 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],
    }));
}
Enter fullscreen mode Exit fullscreen mode

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
  };
}
Enter fullscreen mode Exit fullscreen mode

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... πŸš€

Discussion (4)

pic
Editor guide
Collapse
danforejtek profile image
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.

Collapse
frasnym profile image
Frastyawan Nym Author

Your most welcome! πŸ₯³

Collapse
olpeh profile image
Olavi Haapala

Thanks! This was super helpful

Collapse
frasnym profile image
Frastyawan Nym Author

It is my pleasure, to be able to help 😊