	Action
{{col[0]}}	{{col[1]}}			View

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] (https://www.youtube.com/watch?v=K6Vcfm7TA5U) by [Fireship.io](http://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](https://console.cloud.google.com/apis/library) 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". ![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/avwmmu6y9nul20djrmdm.png) 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. ![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/enoixycils9y4xtbtie8.png) 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. ![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0cbo2yd8yuksv31nfv69.png) 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](https://developers.google.com/sheets/api/guides/concepts). ## Create & Customize Google Sheet Visit: [Google Sheet](https://docs.google.com/spreadsheets) 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](https://unsplash.com/).com and pick out some cool images to include under the Image column. ![Google Sheet](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jna1jbqoxldyxh84ex7g.png) ## Create Nuxt 3 Application ```javascript 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: ```javascript // app.vue ``` If you would like, you can read about the [Google API NPM Package](https://www.npmjs.com/package/googleapis). ## 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. ```javascript // 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: ```javascript 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: ```javascript 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](https://nuxt.com/docs/guide/going-further/runtime-config). ## 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](https://nuxt.com/docs/api/composables/use-fetch) function. ```javascript // 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](https://nuxt.com/docs/guide/directory-structure/composables) ## Pages Directory and File-based Routing Add an *index.vue* file within the newly created *pages* folder and insert the following code: ```javascript // pages/index.vue ``` 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. ![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/huecokajslrxx138j4y6.png) ## Dynamically Routing to a Single Row of Data Create a \[row\].vue file component in the pages directory and add the following: ```javascript // pages/[row].vue ``` 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. ## Thank You Thanks for checking out the article and I hope it's able to help someone along their coding journey. 🎉 You can look at the full source code and find the demo [here](https://github.com/edwardz8/nuxt3-googlesheet).

ID: {{ data.values[0][0] }}

Title: {{ data.values[0][1] }}

edwardzzz

How to use Google Sheets with Nuxt 3

Join Our Newsletter. No Spam, Only the good stuff.