Create your developer portfolio page powered by Google sheets

In today’s day and age, it’s imperative to have a personal website in order to maintain a prominent online presence. The most basic version of this would be a page that links to several other sites, like Linktree. But in this case, we gain the flexibility of maintaining it ourselves, which in turn adds to our tech expertise.

The choice

Once we decide to embark on that path, the next logical question is, what would be the tech stack for the site. The choice for a frontend is straightforward. Since we are looking for a modern framework with possibly server-side rendering support, Next.js would be the obvious choice.

Then comes the choice for the backend. As it would be a fairly simple site, it does not make sense for the solution to have a complex database. But also, we would not want to keep it so tightly coupled with the codebase that we store it in a JSON file in the file system. We need a middle ground. And that is where a not-so-well-known DB solution could be leveraged.

That solution is using Google sheets as a database. I know it sounds weird if you are hearing about it for the first time but think about it. A sheet is a structured set of data organized into rows and columns. The columns can be thought of as being exactly the same as the columns of a DB and the rows can be thought of as the actual data that needs to be stored.

Wouldn’t it be awesome that whenever we needed to modify the details of our site, we just head to the sheet and add/modify one of the rows and save it which then magically reflects on our site? That is what we are going to achieve today.

The setup

Frontend

The frontend setup is as easy as creating a new Next.js repo which is done using the create-next-app command. Here’s how that works:

  • Navigate to a folder where you wish to create the project.
  • Run the following command

npx create-next-app Portfoliowithsheets

And Next.js would take care of the rest.

After the script has completed running, there would be a new folder created with the name `PortfolioWithSheets’. Navigate into it and start the application to see the welcome screen.

cd Portfoliowithsheets

yarn dev

This brings up the familiar start page for Next.js projects.

Backend

As we decided that the backend is going to be a google sheet, it is obvious that we need to create a new one. But there is one more aspect to it:

How would the Next.js app communicate with the sheet?

That is where an important utility comes into the picture. That is sheets.best. It would help communicate with our google sheet via ReST APIs. This means we can perform well-known ReST operations like GET, PUT, POST, and communicate with our sheets database. Sounds amazing right? Let’s set that up.

Create your sheet

Go to the Chrome browser address bar and type sheets.new to generate a new sheet. The first row will be used as metadata so provide the column names in the first row. Insert some data in the second row. My sheet currently looks like this:

We can add more links later

Linking to sheets.best

Next, head over to sheets.best and create an account. You will reach this screen.

Click on the +connection button

In the connection link, paste the link of the google sheet that was created in the previous step. Make sure to change the settings to Anyone on the internet with this link can view else it will not work.

Once the connection is added, we reach this screen.

Testing the link

In order to test the API for the sheet that we linked just now, click on the DETAILS button and copy the connection URL. Paste it in the browser address bar and you should be able to see the JSON response of the details that we configured in the sheet earlier.

With that in place, the backend is ready. Now let us connect it to the frontend and build some nice UI.

UI library

Before integrating the API with the UI, let us install Chakra which is the UI library that we would be using for our portfolio. Here is the command for that:

yarn add @chakra-ui/react @emotion/react@^11 @emotion/styled@^11 framer-motion@^4

After that installs, we need to go to pages/_app.js and wrap the root with ChakraProvider so that it looks like this

import { ChakraProvider } from “@chakra-ui/react”;

import ‘../styles/globals.css’;

function MyApp({ Component, pageProps }) {

  return (

    <ChakraProvider>

      <Component {…pageProps} />

    </ChakraProvider>

  )

}

export default MyApp

And now we’re all set to use chakra UI in our project.

We will rewrite the code in the pages/index.js using Chakra components so that it becomes responsive. We replace the h1 tag with the Heading component, a tag with the Link component and div with the Flex component, etc. This is what the code looks like after replacing all the elements with Chakra components:

export default function Home() {

  return (

    <div>

      <Head>

        <title>Developer portfolio</title>

        <meta name=”description” content=”developer portfolio” />

        <link rel=”icon” href=”/favicon.ico” />

      </Head>

      <main className={styles.main}>

        <Heading size=”2xl” mb={6}>

          Peter <Link href=”https://nextjs.org” color=”blue.500″>Parker</Link>

        </Heading>

        <Text px=”10%” textAlign=”center”>

          Hi There! Welcome to my little place on the internet. Here, you will find links to other sites where I am active.

        </Text>

        <Flex flexWrap=”wrap” alignItems=”center” justifyContent=”center” maxW=”800″ mt={10}>

          <Box as=”a” p={4} m={4} href=”/” borderWidth={1} rounded=”lg” flexBasis={[‘auto’, “50%”]}>

            <Heading as=”h3″ size=”md” mb=”2″>Twitter &rarr;</Heading>

            <Text fontSize=”lg”>This is where I post small snippets of code useful to other devs</Text>

          </Box>

        </Flex>

      </main>

      <footer className={styles.footer}>

        Created with 🖤 by @kokaneka

      </footer>

    </div>

  )

}

Notice how the information about Twitter is hardcoded. That is what will be coming from the sheets once we integrate it into the app in the next step.

API integration

We will now try to get hold of the information in the Google sheet inside of our Next.js page. For that, we will be using the getServerSideProps() method so that the page gets rendered on the server-side and we get benefits such as performance boost and higher SEO scores. We add the method to the same file, index.js and whatever we return from that file will be passed as props to the component while rendering it (all on the server-side).

export async function getServerSideProps() {

  const res = await fetch(`https://sheet.best/api/sheets/11587a23-ea75-4c81-afae-5cc4bf8ffc65`);

  const data = await res.json()

  return {

    props: {

      data

    }

  }

}

Notice that we just need to make a fetch() call to the sheet.best URL that we tested earlier and pass on the result JSON as props to the component.

Once that is done, we need to iterate over all the items in the data prop and show them on the UI. But before that, let us add a description column to the sheet and add a short description of the platform which we missed in the first place. Adding a new data column is as easy as creating a new column in the sheet!

That done, let’s write the code for iterating

<Flex flexWrap=”wrap” alignItems=”center” justifyContent=”center” maxW=”800″ mt={10}>

  {data.map(d=> (

    <Box as=”a” p={4} m={4} href={d.link} borderWidth={1} rounded=”lg” flexBasis={[‘auto’, “50%”]}>

      <Heading as=”h3″ size=”md” mb=”2″>{d.platform} &rarr;</Heading>

      <Text fontSize=”lg”>{d.description}</Text>

    </Box>

  ))}

</Flex>

And with that last bit, we’re done. We now have a fully functional personal dev page that lists all our social media links powered by a Google sheets backend!

Let’s add a few more links and information to the sheet

And now, we test it out

And thanks to Chakra UI, it is 100 percent compatible with mobile devices!

Spicing it up!

We now know how easy it is to add fields to our “DB” but let us demonstrate that one more time. Let’s say we want to give these boxes specific colored borders. How would we proceed?

It’s easier than you think:

  • Just add a new column to the sheet called ‘color’ and fill the values with your favorite colors.
  • Then set the borderColor property to the one that is read from the sheet and you’re done.

Conclusion

You can deploy this site that we just developed for free by following the instructions on vercel.com. What we accomplished today would take not more than 30 mins to implement and that just goes to show the simplicity of the solution that we implemented. If you are looking for a database solution for simple use cases like these, definitely give sheets.best a try.

Leave a Reply

Your email address will not be published. Required fields are marked *