Zach

Posted on Feb 11, 2023

How to use Google Sheets with Nuxt 3

#vue #nuxt #api #javascript

This guide will walk you through how to use the Google Sheets API as a database and pair it with a Nuxt.js 3 application.

This post is inspired by a video made here by Fireship.io. Jeff does a nice job explaining the why and benefits of integrating Google Sheets as a database with a front-end framework. The video is close to 2 years old and it's using Next.js as the front-end framework and got me curious about how one would go about adding this to a Nuxt.js project in 2023; after a quick search I wasn't able to find anything out there close to what I was wanting to build, so I figured I would learn how to create it and share it with anyone else who might want to try out this cool, mini-stack.

You can either follow along from the 3:32 to 5:07 minute mark on the video or follow the written instructions below if you'd like a walkthrough on how to set up a Google API Key for the Google Sheets API.

Google Sheets API

Naturally, a Google account is required to proceed with this article. Once you're signed in, visit the: Google Cloud Platform Console and search for "Sheets". You should be taken to a screen similar to the one in the image below. If Google Sheets hasn't been "Enabled" before now you will need to do so. Once it's enabled, click on the same button that should now say: "Manage".

This should direct you to the APIs and Services console where you can create and manage API keys and credentials. On the left sidebar, select "Credentials". If no keys are present under the API Keys section, select the "+Create Credentials" dropdown near the top of the page and choose API Key. If a key has been created previously, scroll to the far right and select "SHOW KEY". We will need this for our .env file once we create the project.

On the same page, in the Service Accounts section, there should also be an "App Engine default service account". Once you click on the email, you will be redirected to the IAM and Admin page. From there, navigate to the Keys tab and select "Add Key" and "Create new key". This will download a file that I renamed to secrets.json. Keep this file somewhere easily accessible as its contents will soon be added to the root of the Nuxt 3 project.

That's it for the prerequisites needed to get started. Let's now move onto making a Google Sheet. You can read more about the Google Sheets API.

Create & Customize Google Sheet

Visit: Google Sheet and create a blank project.

Fill the spreadsheet out with similar headings included in the image below. Then select "Share", give it a name and grant general access to "Anyone with a link"; press "Done".

It's completely optional but you can go to unsplash.com and pick out some cool images to include under the Image column.

Create Nuxt 3 Application

npx nuxi init nuxt3-sheets
cd nuxt3-sheets
npm install

npm i googleapis

code .
npm run dev

The app.vue file needs to be replaced with the following content:

// app.vue

<template>
  <div>
    <NuxtPage />
  </div>
</template>

If you would like, you can read about the Google API NPM Package.

Create and Populate secrets.json File

Remember the file that was downloaded earlier from the Google Cloud Platform? We need its content now; it should look similar to the object below, except with a much longer private key.

// secrets.json

{
    "type": "service_account",
    "project_id": "yourname",
    "private_key_id": "331xxxxxb7ecdxxxxxxxxxxxxxxxx14268xxxxxxc6",
    "private_key": "-----BEGIN PRIVATE KEY-----\nMkcEvQIZG2SAxj5Kdr8zT0=\n-----END PRIVATE KEY-----\n",
    "client_email": "yourname@appspot.gserviceaccount.com",
    "client_id": "103xxxxxxxxxxxxx666",
    "auth_uri": "https://accounts.google.com/o/oauth2/auth",
    "token_uri": "https://oauth2.googleapis.com/token",
    "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
    "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/yourname%40appspot.gserviceaccount.com"
  }

Now, create a .env file in the root directory and add the following variables and replace the values with the Sheet ID and Google API Key:

SPREAD_SHEET_ID="1OARnxxxxxxxxxxxxxxxxxxxxxxxxoQsy8"
GOOGLE_API_KEY="AIzaSxxxxxxxxxxxxxxxxxxxxxxxxtTxNv0"

The Google Spreadsheet ID can be found within the URL of the sheet; it's the long number value directly after: spreadsheets/d/.

Find the Google API Key generated earlier and place that as the second value. Then in the nuxt.config.ts file add:

export default defineNuxtConfig({
    runtimeConfig: {
        public: {
            GOOGLE_API_KEY: process.env.GOOGLE_API_KEY,
            SPREAD_SHEET_ID: process.env.SPREAD_SHEET_ID
        }
    }
})

The runtime configuration has changed a bit since Nuxt 2 and you can read about what's different here.

Composables

Create a useSheet.js file within a composables folder in the root of the project. Here a few functions are needed. The first getVars will read the environment variables passed through with the useRuntimeConfig() function. The second function, allRows will destructure these 2 values and pass them into the template literal url string and fetch and return the correct json data.

You may be wondering how we got the Sheet1!A1:D200 value that's read from the sheetRange variable. If you select the first cell of the first column and press CMD+a it will select all cells. A1 represents the first column and data and the D200 reads how many rows we want to read. When you first select all of them it should say something like: A1:D5 or A1:D6 in the top left corner of the sheet, but I want the front-end of the application to read more than just the cells that are already populated, so that when we add more rows to the sheet they will automatically be read and displayed on the browser. This also saves me from having to go into Visual Studio Code every time we add a few more rows of data.

Finally, the third and final function within our composable file: singleRow is again going to destructure the variables held inside getVars and find a single row so it can be routed dynamically. For both the allRows and singleRow functions, we will pass the url variable to the useFetch function.

// composables/useSheet.js

let sheetRange = "Sheet1!A1:D200";

const getVars = () => {
    const SPREAD_SHEET_ID = useRuntimeConfig().public.SPREAD_SHEET_ID;
    const GOOGLE_API_KEY = useRuntimeConfig().public.GOOGLE_API_KEY;

    return { SPREAD_SHEET_ID, GOOGLE_API_KEY }
}

export async function allRows() {
    const { SPREAD_SHEET_ID, GOOGLE_API_KEY } = getVars();
    const url = `https://sheets.googleapis.com/v4/spreadsheets/${SPREAD_SHEET_ID}/values/${sheetRange}?key=${GOOGLE_API_KEY}`
    return await useFetch(url)
}

export async function singleRow(row) {
    const { SPREAD_SHEET_ID, GOOGLE_API_KEY } = getVars();

    const rowRange = `Sheet1!A${row}:D${row}`

    const url = `https://sheets.googleapis.com/v4/spreadsheets/${SPREAD_SHEET_ID}/values/${rowRange}?key=${GOOGLE_API_KEY}`
    return await useFetch(url)
}

Learn more about composables

Pages Directory and File-based Routing

Add an index.vue file within the newly created pages folder and insert the following code:

// pages/index.vue

<script setup>
import { allRows } from '@/composables/useSheet';

const headings = ref(null)
const result = ref([])
const { data } = await allRows()

headings.value = data.value.values[0]
result.value = [...data.value.values]
result.value.splice(0, 1)
</script>

<template>
    <div class="container">
      <table>
        <thead>
        <tr>
          <th v-for="col in headings" :key="col" v-html="col"></th>
          <th>Action</th>
        </tr>
        </thead>
        <tbody>
        <tr v-for="col in result" :key="col" >
          <td>{{col[0]}}</td>
          <td>{{col[1]}}</td>
          <td v-html="col[2]"></td>
          <td><img :src="col[3]" alt=""></td>
          <td> <NuxtLink :to="`/${col[0]}`">View</NuxtLink> </td>
        </tr>
        </tbody>
      </table>
    </div>
</template>

<style>
.container {
  display: flex;
  justify-content: center;
  font-family: sans-serif;
}

table {
  border-collapse: collapse;
}

thead {
  background: #f0f0f0;
  text-transform: uppercase;
}

tr{
  border-bottom: 1px solid #444;
}

th, td {
  padding: 4px 8px;
  border-left: 1px solid #444;
  border-right: 1px solid #444;
}

img {
    max-width: 100px;
    border-radius: 8px;
}
</style>

For this index.vue file, only the allRows function needs to be imported from the composable file. Then the function is awaited asynchronously and read from the data variable.

The headings variable will represent the table headings and the result represents the values array in the returned JSON.

In the new Vue.js Composition API, any variables using ref must be appended with .value when read. So, now the sheets API data.value.values[0] is passed to headings.value and can be looped through in the headings array inside the th tag.

The same is true for result where the spread operator is used and the data is spliced within each returned row of data, which, in the JSON, is an array.

To display the table data td for each table row tr, the column index begins at 0 as we loop through the result data.

Adding a UI library like TailwindCSS is beyond the scope of this tutorial as Nuxt has made it quite trivial to integrate one into an application so I'm rolling with just plain CSS and HTML for this one.

Dynamically Routing to a Single Row of Data

Create a [row].vue file component in the pages directory and add the following:

// pages/[row].vue

<script setup>
import { singleRow } from '@/composables/useSheet'

const route = useRoute()

const { data } = await singleRow(+route.params.row + 1)
</script>

<template>
    <h6>ID: {{ data.values[0][0] }}</h6>
    <h4>Title: {{ data.values[0][1] }}</h4>
    <p>Content: <span v-html="data.values[0][2]"></span></p>

    <br><br>
    <NuxtLink to="/">back</NuxtLink>
</template>

Here we just need the singleRow function from the composable file as well as the useRoute helper that's packaged along with Nuxt.js.

The singleRow takes an argument, which is the route.params of the given row we want to return. The route.params.id returns a string, so to convert that to a number we use +. For the content section, the v-html attribute is needed to set the text within the paragraph.

Note: The image tag that should be in the above code is breaking the markdown editor so I'm including it here. Inside an image tag, add: "data.values[0][3]" and bind it to :src.

It should be placed directly below the paragraph "Content" tag.