How to Build a Quote Gallery App using Google Sheets

Cover Image - Quote Gallery App

Developers commonly use SQL or NoSQL databases to store and organize data. But do you know that there's a more lightweight, flexible and intuitive alternative? It's called a spreadsheet, a very familiar type of document for most people.

In this beginner-friendly tutorial, we shall build a simple quote gallery app. The app can do the following:

• Fetch quote data from "database"
• Display fetched data in app
• Add new quote and update the "database"

The "database" that we are using in this app is Google Sheets! That's right, no fancy SQL or NoSQL databases, just the simple spreadsheet we all know. Let's begin!

Getting Started

Before getting into the coding part of this project, we must first have the following ingredients:

• A Google Developers account
• A Google Drive account
• Node.js and npm installed in your device

Step 1: Connect Google Sheets API

First, we need to connect the Google Sheets API to our app. So head to https://console.developers.google.com/ to sign in or create a free Google Developers account.

Then, add a new project and name it appropriately.

google1.PNG

After creating the project, you will see the dashboard. Click the Enable APIs and Services button as shown in the screenshot below.

google2.PNG

Then, you will be redirected to the API Library. Scroll down and click on Google Sheets API.

googl.gif

Once you click that, you will be taken to the page shown below. Create your credentials.

google4.PNG

Follow the screenshots below to answer the questions. Part 1 is below.

google5.PNG

Here is Part 2.

google6.PNG

A JSON file (credentials.json) will then be downloaded to your machine. This credentials.json file will be used to connect our app to the API.

Important: You must save this credentials.json file safely because you won't be able to regenerate it once you lose it. Unless you disable the API and restart everything.

Copy the Service Account ID. We will need it in the next step.

Step 2: Create a Google Sheets

Now, let's create a Google Sheets. Log into your Google Drive account to create a Sheets document.

sheets.gif

On your Sheets, give each column a name and add some data like it's a database. In my example, I have the Quotes, Quoter and Category columns.

image.png

Remember the Service Account ID we copied earlier? Click on the Share button on the top right of the Sheets window and add that ID as an Editor, as shown in the image below.

Capture.PNG

The final task to do in this window is the Sheets ID, which is located in the URL of the Sheets.

Capture.PNG

Copy this Sheet ID because we will need it to tell our app which Sheets to access.

Step 3: Set up React App

Make sure you have Node.js or npm installed, then create a new React project with:

npx create-react-app quote-gallery

Then install google-spreadsheet with:

npm install google-spreadsheet

Next, copy your credentials.json file into the app's src folder. Import it in App.js:

const creds = require('./credentials.json')

Step 4: Read Data from Sheets

Our app is finally ready to use our Sheets as a database. To read data from our Sheets, let's first create an array called rows, where we will save all the data in rows from our Sheets.

const [rows, setRows] = useState([]);

We initialize it as an empty array. Then, we create a getSheet() function.

This function will:

1. Get the Sheets doc we want by its ID we copied earlier
2. Initialize Authorization to access the Sheets using our creds.client_email and creds.private_key
3. Loads data from our Sheets doc. Then, get data from the first worksheet and get all its rows.
4. Finally, we save the retrieved rows to our rows array which we initialized earlier.

  async function getSheet(){
    //1. 
    const doc = new GoogleSpreadsheet('Sheet ID');

    //2.
    await doc.useServiceAccountAuth({
       client_email: creds.client_email,
       private_key: creds.private_key,
    });

    //3.
    await doc.loadInfo(); 
    const sheet = doc.sheetsByIndex[0]; //get 1st worksheet
    const rows = await sheet.getRows(); //get all its rows
    //4.
    setRows(rows);
  }

If we console.log(rows), we can see that the data retrieved is an array of GoogleSheetRows and each element contains its own properties such as its column names: Category, Quoter, Quotes and their respective values.

image.png

So now that we have successfully retrieved all our row data, our app should display this data nicely.

Step 5: Displaying Data

If we want to get the quote, quoter and category of our first row, it is simply: rows[0].Quotes, rows[0].Quoter and rows[0].Category. So to do this for every row in our rows array, we can use rows.map(). Let's see how this works.

You can choose any CSS library or framework to do this step. For this tutorial, I am using Material UI.

I created a Grid component as the container that will contain all the data. Inside this component is the rows.map()function which returns each row in the format like so:

row.PNG

Here's what it looks like in code:

<Grid className= "container" container justify="center">
        {rows.map((row, index)=>{
          return(
            <Grid className= "card" key={index} xs={12} sm={3}>
              <p>{`${row.Quotes}`}</p>
              <p style={{fontStyle:"italic"}}>{`- ${row.Quoter}`}</p>
              <Chip className= "chip" label={`${row.Category}`}/>
            </Grid>
          )
        })}
</Grid>

And the result will be:

image.png

Each row data is organized into the style we wanted. With the Quotes, Quoter and Category displayed accordingly for every element in rows.

But a database is not that useful if it's read-only right? Let's implement adding a new quote and updating the Sheets from our app.

Step 6: Add a New Quote

In the app, create a simple form with the values we need to update our Sheets database.

image.png

Then, initialize 3 states that I will need to add a new row: category, quoter and quote.

const [category, setCategory] = useState("Category");
const [quoter, setQuoter] = useState("");
const [quote, setQuote] = useState("");

In the button Add New Quote, let's create the handler addNewRow() function which will execute onClick.

<Button onClick={addNewRow}>Add new quote</Button>

This function will:

1. Check if all the fields are filled with values
2. Get our Sheets by its ID
3. Authorize with credentials
4. Loads the Sheets doc. Get the first worksheet.
5. Add the new row by passing an object with the 3 states: quote, quoter and category as the values, and their respective column names as the object keys.
6. Retrieve the new rows data and update the rows array.

In code:

async function addNewRow(){
    //1.
    if(category!=="Category" && quote !== "" && quoter !== "" ){
      //2.
      const doc = new GoogleSpreadsheet('Sheet ID');

      //3.
      await doc.useServiceAccountAuth({
        client_email: creds.client_email,
        private_key: creds.private_key,
      });

      //4.
      await doc.loadInfo(); 
      const sheet = doc.sheetsByIndex[0];

      //5.
      await sheet.addRow({
          Quotes: quote, 
          Quoter: quoter,
          Category: category 
      });

      //6.
      const rows = await sheet.getRows();
      setRows(rows);

     //reset values
      setQuote('');
      setQuoter('');
      setCategory('Category');
      alert('Thank you for adding a quote! :)');  //show message
    }else{
      alert("Some data is missing!");
    }
  }

Let's see how it works in our app!

addQuote.gif

It works perfectly. We can go to our Sheets and check that the data has indeed been added as a new row and that the new quote appears in the app.

Conclusion

In this tutorial, we can see how easy it is to set up and have Google Sheets as a convenient database to store and retrieve data in an organized manner. While it is definitely a useful tool, there are currently some limitations to the Google Sheets API.

According to this article, Sheets has a hard limit of 5,000,000 cells (including blank cells), so any data larger than that would not fit. Also, queries became impractically slow around the 500,000 cell mark which makes using Sheets quite a poor choice at handling large amounts of data.

Still, this is perfectly fine choice for small apps like this quote gallery app. Thanks for reading this article. Please like and share if it is helpful. For more information on this tutorial, feel free to visit its repo and demo in the links below. Cheers!

• See repo: https://github.com/victoria-lo/quote-gallery
• See demo: https://quote-gallery.vercel.app