DEV Community

loading...
Cover image for Create a Google Sheet from a React Application With One Click

Create a Google Sheet from a React Application With One Click

Jesse Smith Byers
(she/her) Full Stack Web Developer with a background in science teaching, district administration, and curriculum.
・6 min read

Check out the first article in this series for background on my project. In this article, I'll explain how I modified some existing resources to set up Google Sheets API integration with one of my old frontend-only React projects. If you want to follow along, try to implement the following steps into a new branch of one of your own existing React applications, and learn to export your data into a new sheet with one click!

1. Setting up Google Integration

First, you will need to set up a new Google Cloud Platform project and enable the Sheets API. Follow the directions under Step 1 of the Browser Quickstart Guide. At the end of this step, you will need to jot down your CLIENT_ID and API_KEY, which will be stored securely.

Note: The directions in Step 1 of the Browser Quickstart are misleading. Make sure you click on the "Create API key" button within your own Google Developer Console, and NOT from the Quickstart directions page!

2. Storing and Securing Secrets

Next, you will need to use dotenv to store and secure the CLIENT_ID and API_KEY. Install this dependency by running the following command in your terminal:

npm install dotenv

Create an empty .env file in your root directory. Next, make sure that you add .env to your .gitignore file, so that the .env file will not be tracked.

Now, you can store your CLIENT_ID and API_KEY in the .env file, like so:

REACT_APP_CLIENT_ID=<ENTER-YOUR-CLIENT-ID-HERE>
REACT_APP_API_KEY=<ENTER-YOUR-API-KEY-HERE>
Enter fullscreen mode Exit fullscreen mode

In Step 5, we will be able to access these variables by requiring dotenv, and then using the process.env namespace to access the values:

require('dotenv').config()
process.env.REACT_APP_API_KEY
process.env.REACT_APP_CLIENT_ID

3. Modifying the index.html file

At the bottom of the body element of your index.html file, you will need to add a script element to connect to the Google APIs Client for JavaScript. See below:

<!DOCTYPE html>
<html lang="en">
  <head>
    ...
  </head>

  <body>

    <div id="root"></div>

    <!-- JAVASCRIPT GOOGLE CLIENT  -->
    <script async defer src="https://apis.google.com/js/api.js"></script>

  </body>
</html>
Enter fullscreen mode Exit fullscreen mode

4. Update an Existing Component, and Pass Data as Props to New Component

Choose a component in your React application that already has access to the data that you want to include on your spreadsheet report. We will import a new GoogleDownload component into the existing component, and then render the new component, passing any data that will be included in the spreadsheet as props to the new GoogleDownload component.

...
import {GoogleDownload} from './GoogleDownload'

    ...

    return (
        <div>

            ...

            <GoogleDownload data={data}/>

        </div>
    )
}


Enter fullscreen mode Exit fullscreen mode

5. Create a New Re-Usable GoogleDownload Component

In this step, you will create a component that includes three buttons, and all of the logic required to:

  1. Sign in and authorize read and write access to a user's google sheets,
  2. Sign out from google, and
  3. Kick off the spreadsheet creation process.

Most of the code here is adapted from Step 2 of the Browser Quickstart Guide. However, I needed to make some modifications to convert the vanilla JavaScript into code that would integrate properly within a React component.

Here is the complete code for the re-usable GoogleDownload component, but I will break it down section by section below.

import React, { useEffect } from 'react';
import { createGoogleSheet } from '../spreadsheet/sheets'
require('dotenv').config()

export function GoogleDownload(data) {

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


     // On load, called to load the auth2 library and API client library.

      function handleClientLoad() {
        window.gapi.load('client:auth2', initClient);
      }

     // Initializes the API client library and sets up sign-in state listeners.

      function initClient() {
        window.gapi.client.init({
          apiKey: process.env.REACT_APP_API_KEY,
          clientId: process.env.REACT_APP_CLIENT_ID,
          discoveryDocs: ["https://sheets.googleapis.com/$discovery/rest?version=v4"],
          scope: "https://www.googleapis.com/auth/spreadsheets"
        }).then(function () {

          // Listen for sign-in state changes.
  window.gapi.auth2.getAuthInstance().isSignedIn.listen(updateSigninStatus);

          // Handle the initial sign-in state.
          updateSigninStatus(window.gapi.auth2.getAuthInstance().isSignedIn.get());
        }, function(error) {
            console.log(error) // add something for error handling
        });
      }

      // Called when the signed in status changes, to update the UI appropriately. After a sign-in, the API is called.

      function updateSigninStatus(isSignedIn) {
        var authorizeButton = document.getElementById('authorize_button');
        var signoutButton = document.getElementById('signout_button');
        var downloadButton = document.getElementById('download_button');

        if (isSignedIn) {
          authorizeButton.style.display = "none";
          signoutButton.style.display = "block";
          downloadButton.style.display = "block";
        } else {
          authorizeButton.style.display = "block";
          signoutButton.style.display = "none";
          downloadButton.style.display = "none";
        }
      }

      // Sign in the user upon button click.

      function handleAuthClick(event) {
        window.gapi.auth2.getAuthInstance().signIn();
      }

      // Sign out the user upon button click.

      function handleSignoutClick(event) {
        window.gapi.auth2.getAuthInstance().signOut();
      }




    return (
        <div>

            <button id="authorize_button"  onClick={handleAuthClick} display={updateSigninStatus} className="block googleauth">Authorize Google Login</button>

            <button id="signout_button"  onClick={handleSignoutClick} display={updateSigninStatus} className="block googlesignout">Sign Out of Google</button>

            <button id="download_button" onClick={() => createGoogleSheet(data.data)} className="block google" display={updateSigninStatus}>Download Data to Google Sheets</button> 

        </div>
    )
}
Enter fullscreen mode Exit fullscreen mode

Here's what's happening, step=by-step:

First, set up your imports:

import React, { useEffect } from 'react';
import { createGoogleSheet } from '../spreadsheet/sheets'
require('dotenv').config()
Enter fullscreen mode Exit fullscreen mode

Next, set up the skeleton of your functional component:

export function GoogleDownload(data) {

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

    ...

    return (
        <div>

          ...

        </div>
    )
}
Enter fullscreen mode Exit fullscreen mode

Add functions to control the google authorization logic:

Note: These functions were largely pulled from Step 2 of the Browser Quickstart Guide, but I was unable to access gapi from within the React component. Instead, it can be accessed through the window object as window.gapi. In addition, I modified the logic for hiding and showing buttons based on signInStatus so that they would function in relation to React's update patterns.

     // On load, called to load the auth2 library and API client library.

      function handleClientLoad() {
        window.gapi.load('client:auth2', initClient);
      }

     // Initializes the API client library and sets up sign-in state listeners.

      function initClient() {
        window.gapi.client.init({
          apiKey: process.env.REACT_APP_API_KEY,
          clientId: process.env.REACT_APP_CLIENT_ID,
          discoveryDocs: ["https://sheets.googleapis.com/$discovery/rest?version=v4"],
          scope: "https://www.googleapis.com/auth/spreadsheets"
        }).then(function () {

          // Listen for sign-in state changes.
  window.gapi.auth2.getAuthInstance().isSignedIn.listen(updateSigninStatus);

          // Handle the initial sign-in state.
          updateSigninStatus(window.gapi.auth2.getAuthInstance().isSignedIn.get());
        }, function(error) {
            console.log(error) // add something for error handling
        });
      }

      // Called when the signed in status changes, to update the UI appropriately. After a sign-in, the API is called.

      function updateSigninStatus(isSignedIn) {
        var authorizeButton = document.getElementById('authorize_button');
        var signoutButton = document.getElementById('signout_button');
        var downloadButton = document.getElementById('download_button');

        if (isSignedIn) {
          authorizeButton.style.display = "none";
          signoutButton.style.display = "block";
          downloadButton.style.display = "block";
        } else {
          authorizeButton.style.display = "block";
          signoutButton.style.display = "none";
          downloadButton.style.display = "none";
        }
      }

      // Sign in the user upon button click.

      function handleAuthClick(event) {
        window.gapi.auth2.getAuthInstance().signIn();
      }

      // Sign out the user upon button click.

      function handleSignoutClick(event) {
        window.gapi.auth2.getAuthInstance().signOut();
      }

Enter fullscreen mode Exit fullscreen mode

Call the handleClientLoad function within the useEffect hook:

This will check if the user is already signed in and authorized to access Google Sheets.

    useEffect(() => {
        handleClientLoad();
    }, []);
Enter fullscreen mode Exit fullscreen mode

Add buttons for google sign in, sign out, and creating the spreadsheet:

Again, the first two buttons were adapted from Step 2 of the Browser Quickstart Guide, but slightly modified so that the updateSignInStatus() function would control the value of the display property. I also added onClick listeners to each button through JSX, rather than through vanilla JavaScript as in the Quickstart example. Finally, the download_button is wired up to call the createGoogleSheet() function when it is clicked, which is imported from sheets.js, and will pass the data needed for the spreadsheet as an argument.

            <button id="authorize_button"  onClick={handleAuthClick} display={updateSigninStatus} className="block googleauth">Authorize Google Login</button>

            <button id="signout_button"  onClick={handleSignoutClick} display={updateSigninStatus} className="block googlesignout">Sign Out of Google</button>

            <button id="download_button" onClick={() => createGoogleSheet(data.data)} className="block google" display={updateSigninStatus}>Download Data to Google Sheets</button> 
Enter fullscreen mode Exit fullscreen mode

At this point, the React application is completely wired up to integrate with the Google Sheets API, is able to sign in users and grant authorization to access Google Sheets, sign them out, and has passed data to the function that will create the spreadsheet. At this point, everything related to building the actual Google Sheets file will be completed in the sheets.js file, using vanilla JavaScript and guided by the documentation for the Google Sheets API.

If you'd like to check out my demo project, you can see the code here:

Next Up: Creating and Updating a Spreadsheet Using the Google Sheets API

Check out the last article in this series to see how I started writing logic in the sheets.js file to create a spreadsheet, add data, and add basic as well as conditional formatting. Here's a quick preview of the Sheets.js file.

Discussion (1)

Collapse
evinracher profile image
Kevin Arley Parra Henao

Is this safe? Is there a way someone can get access to your spreadsheet using the information on your application (i.e, know your client_key by inspecting the code)?