DEV Community

Cover image for Connecting NextJS Mini Project with Google Spreadsheets as Database
Frastyawan Nym
Frastyawan Nym

Posted on • Edited on

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 πŸ–±οΈ 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](https://dev-to-uploads.s3.amazonaws.com/i/1s90tzp88i3tgxq1f800.JPG)
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]


Enter fullscreen mode Exit fullscreen mode
  1. 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**
Enter fullscreen mode Exit fullscreen mode

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

} catch (err) {
console.log(err);
}
return [];
}

### Description:
- We will specify our target connection
Enter fullscreen mode Exit fullscreen mode

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

- Declare our jwt for authentication
Enter fullscreen mode Exit fullscreen mode

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

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

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

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

export default function IndexPage({ emojis }) {
return (
<>


Title - FrasNym


{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](https://www.npmjs.com/package/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](https://twitter.com/frasnym) 😁

Until then... πŸš€
Enter fullscreen mode Exit fullscreen mode

Top comments (14)

Collapse
 
athdromeda profile image
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'
Enter fullscreen mode Exit fullscreen mode

googleapis & next version:

...
    "googleapis": "^109.0.0",
    "next": "13.0.0",
...
Enter fullscreen mode Exit fullscreen mode

Anybody have solution?

Collapse
 
athdromeda profile image
Ath Athoillah

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

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

Your most welcome! πŸ₯³

Collapse
 
gandarufu profile image
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
}

Collapse
 
_denb profile image
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!

Collapse
 
_denb profile image
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

Collapse
 
lucasromerodb profile image
Luke

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

Collapse
 
__junaidshah profile image
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 )

Collapse
 
manuelobre profile image
Manuel Obregozo

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

Collapse
 
jalinog profile image
Joshua Andrew Jourdain

i am getting this error....

Missing required parameters: spreadsheetId, range

Collapse
 
sergo profile image
Sergo

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

const response = await sheets.spreadsheets.values.get({
        spreadsheetId: process.env.SHEET_ID,
        range, //not customRange
});
Enter fullscreen mode Exit fullscreen mode
Collapse
 
olpeh profile image
Olavi Haapala

Thanks! This was super helpful

Collapse
 
frasnym profile image
Frastyawan Nym

It is my pleasure, to be able to help 😊