Effortless Data Management: Connecting Google Sheets to Your ReactJS Project

Ciao 👋👋👋

While working with creating my own portfolio in ReactJS, it was quite a headache to open code for updating any detail or information every time. Although I had a constants.js to manage all my constant data, but still, I had to open VS Code, edit the constants.js and then redeploy the website again and again. Being a software developer, it's our only goal to simplify things using logics and with the least human efforts.

But I finally found a way bypass the coding and reiterating the deployment process again and again. In this article, we are going to discuss how we can connect Google Sheets with our existing or new ReactJS Projects.

Step 1: Create a Google Sheets Document

First thing first. You need a 'Google' account. Once you have it:

• Sign in to your Google account if you aren't already logged in.

• Go to Google Sheets

• Create a new Google Sheets document or utilize an existing one to store the data you want to access in your React project.

• Add data to your google sheet, making sure that all the entries on the first row are your headings.

Step 2: Publish Your Google Sheets Document to the Web as CSV

• In your Google Sheets document, navigate to File > Publish to the web.

• From the popup that appears, select 'Entire Document' or any particular sheet that you want, then from the 2nd dropdown, select 'Comma-Separated values (.csv)'

• Click the Publish button and then 'OK' browser pop-up that appears, to make your csv file accessible via a public link.

• Copy the generated link (e.g., https://docs.google.com/spreadsheets/d/e/<YOUR-DOCUMENT-LINK>/pub?output=csv) for future use.

Now that we are done with setting up our Google Sheet, let's head back to our code.

Step 3: Set Up Your React Project

• Create a new React project if you don't already have one, using your preferred method (e.g., create-react-app).

   npx create-react-app google-sheets-react
   cd google-sheets-react
   code .

Once you are inside your coding directory in VS Code (Thanks to code . command), install axios, an HTTP client, to make requests to the Google Sheets API.

npm install axios

Step 4: Fetch Data from Google Sheets

• Import Axios, React, useEffect and useState in your React component where you want to fetch your data. Here I am using FetchCSVData.js

import React, { useEffect, useState } from 'react';
import axios from 'axios';

export default function FetchCSVData(props) {
    // ...
}

This component will handle the fetching and parsing of CSV data from Google Sheets.

• Set up the component's state using the useState hook to store the fetched CSV data.

const [csvData, setCsvData] = useState([]);

• Utilize the useEffect hook to trigger the CSV data fetching when the component mounts.

useEffect(() => {
    fetchCSVData();
}, []);

The empty dependency array ensures that this effect runs only once, simulating the behavior of componentDidMount in class components.

• Define the fetchCSVData function, which will be responsible for fetching CSV data from Google Sheets.

const fetchCSVData = () => {
    const csvUrl = 'YOUR_GOOGLE_SHEETS_CSV_URL_HERE'; // Replace with your Google Sheets CSV file URL
    axios.get(csvUrl)
        .then((response) => {
            // ...
        })
        .catch((error) => {
            // Handle errors
        });
}

Remember the link that Google Sheets gave you and I asked you to copy it at a safe place, replace 'YOUR_GOOGLE_SHEETS_CSV_URL_HERE' with that actual URL of your Google Sheets CSV file.

• Define the parseCSV function, which will parse the CSV text into an array of objects.

function parseCSV(csvText) {
    const rows = csvText.split(/\r?\n/); // Split CSV text into rows, handling '\r' characters
    const headers = rows[0].split(','); // Extract headers (assumes the first row is the header row)
    const data = []; // Initialize an array to store parsed data
    for (let i = 1; i < rows.length; i++) {
        const rowData = rows[i].split(','); // Split the row, handling '\r' characters
        const rowObject = {};
        for (let j = 0; j < headers.length; j++) {
            rowObject[headers[j]] = rowData[j];
        }
        data.push(rowObject);
    }
    return data;
}

• Inside the fetchCSVData function, use Axios to make a GET request to the Google Sheets CSV file URL.

axios.get(csvUrl)
    .then((response) => {
        const parsedCsvData = parseCSV(response.data);
        setCsvData(parsedCsvData);
        console.log(parsedCsvData);
    })
    .catch((error) => {
        console.error('Error fetching CSV data:', error);
    });

This code fetches the CSV data, parses it using the parseCSV function, and sets the parsed data in the component's state.

Step 5: Use your data as needed

Now that we are done with all the parts, if you have followed each and every part of this article correctly, you will be able to see your data in the console log as well from the csvData state that we just set.

Here it the entire code for your reference along with all the comments

import { useEffect, useState } from 'react'
import axios from 'axios'; // Import Axios

export default function FetchCSVData(props) {
    const [csvData, setCsvData] = useState([]);

    useEffect(() => {
        fetchCSVData();    // Fetch the CSV data when the component mounts
    }, []); // The empty array ensures that this effect runs only once, like componentDidMount

    const fetchCSVData = () => {
    const csvUrl = 'YOUR_GOOGLE_SHEETS_CSV_URL_HERE'; // Replace with your Google Sheets CSV file URL

        axios.get(csvUrl)    // Use Axios to fetch the CSV data
            .then((response) => {
                const parsedCsvData = parseCSV(response.data);        // Parse the CSV data into an array of objects
                setCsvData(parsedCsvData);        // Set the fetched data in the component's state
                console.log(parsedCsvData);        // Now you can work with 'csvData' in your component's state.
            })
            .catch((error) => {
                console.error('Error fetching CSV data:', error);
            });
    }

    function parseCSV(csvText) {
        const rows = csvText.split(/\r?\n/);        // Use a regular expression to split the CSV text into rows while handling '\r'
        const headers = rows[0].split(',');        // Extract headers (assumes the first row is the header row)
        const data = [];        // Initialize an array to store the parsed data
        for (let i = 1; i < rows.length; i++) {
            const rowData = rows[i].split(',');          // Use the regular expression to split the row while handling '\r'
            const rowObject = {};
            for (let j = 0; j < headers.length; j++) {
                rowObject[headers[j]] = rowData[j];
            }
            data.push(rowObject);
        }
        return data;
    }
    return csvData;
}

Conclusion

Incorporating Google Sheets into your ReactJS projects can be a game-changer, allowing you to update content seamlessly without the need for code changes and redeployment. With this simple yet powerful approach, you can keep your website's data up-to-date with ease. Google Sheets updates the documents automatically whenever some new changes are detected, and hence, eliminating the need for you to update code each time, unless a new heading is required.

By following the steps outlined in this guide, you've learned how to connect your React project to Google Sheets, fetch data dynamically, and make your web development process more efficient. Now, you're equipped with a valuable tool to create data-driven, easily maintainable React applications.

Happy coding 🎉