Plugging React-Admin To Google Sheets

One day, François came to us with a funny idea about a new use for react-admin. Why not use a Google Sheet as a back-end for react-admin? I don't know where he got the idea, but I was immediately seduced by the concept.

So I took this challenge with two objectives in mind: 1. to prove that it is feasible, and 2. to go as far as possible in less than three days. I'm writing this article to show you what I've done, where I've landed, and what can be done to go further.

Drawing the Problem

Let's start by describing what such an application involves and how the different parts fit together.

I've identified two actors. The first one is the Google Sheet spreadsheet itself, which contains an online spreadsheet editor. And on the second one is a react application based on react-admin.

A quick research showed me that Google Sheets exposes a free API:

I also found a tutorial on how to call it from a browser: Quick Browser Start. It will help me to establish the communication between the react-admin application and the spreadsheet.

I've drawn the following diagram to visualise what I want to do:

• In red (back-end): Google Sheet API
• In green (front-end): Google Sheet Client
• In blue (front-end): React-admin Application

It is now clear that the problem is getting a browser and an API to communicate. And for this, react-admin offers the perfect solution because it allows the use of a customised data provider.

The ra-data-google-sheet Library

To solve the problem, I created a library named ra-data-google-sheet, which provides a custom data provider for a Google Sheet API. But it also includes an authentication provider to handle private sheets.

I'll first explain how to format data inside a spreadsheet to make it readable by react-admin, and then how to use the library in a real application.

Formatting Data

Because it's an experimental project, the data should be formatted in a specific way.

In the following example, I'm using devs and projects as resources.

You can download these data as CSV on Github.

Introducing The Google API Client

To fetch a Google API, the best solution is to use the gapi JS library. It's a script distributed by Google. Once loaded, you can access it using the global variable window.gapi.

// In packages/ra-data-google-sheets/src/googleAPI.js
const GOOGLE_API_SCRIPT = 'https://apis.google.com/js/api.js';

const createGoogleApiScript = onload => {
    const script = document.createElement('script');
    script.type = 'text/javascript';
    script.src = GOOGLE_API_SCRIPT;
    script.onload = onload;
    document.body.appendChild(script);
};

createGoogleApiScript(() => {
    // window.gapi is now available
});

Loading The Google API Client In The React Application

Before starting, you'll need to create credentials using the Google Developer Console. You should generate an API Key and an OAuth 2.0 identifier (called Client ID in the library).

The ra-data-google-sheet package provides helpers for setting up gapi and loading it with the appropriate API Key and identitifer.

As the gapi client is required to run the application, you should load it before mounting the react-admin application.

// In packages/demo/src/index.js
import React from 'react';
import ReactDOM from 'react-dom';
import { loadGoogleApi } from 'ra-data-google-sheets';

import App from './App'; // Contains your react app

loadGoogleApi({
    apiKey: 'Your API Key',
    clientId: 'You Client ID',
}).then(() => {
    // The Google API is now loaded,
    // we can render the application.
    ReactDOM.render(<App />, document.getElementById('root'));
});

Setting Up The Application

The application is just a classic react-admin app, running with create-react-app.

import React from 'react';
import { Admin, Resource } from 'react-admin';
import { Helmet } from 'react-helmet';

import { authProvider } from './authProvider';
import { dataProvider } from './dataProvider';

import { devs } from './devs';
import { projects } from './projects';

const App = () => {
    return (
        <>
            <Helmet>
                <title>Developper Time Sheet</title>
            </Helmet>
            <Admin authProvider={authProvider} dataProvider={dataProvider}>
                <Resource name="devs" {...devs} />
                <Resource name="projects" {...projects} />
            </Admin>
        </>
    );
};

export default App;

Setting Up the Data Provider

The library provides a data provider factory, which takes the spreadsheet ID as a parameter:

import { googleSheetsDataProvider } from 'ra-data-google-sheets';

const dataProvider = googleSheetsDataProvider('1cagTLWhyPnlFpgCcu19W4O0wBCzXxEyDb5yBK_jg-PU');

The demo app shows an example where several spreadsheets are used.

Setting Up the Auth Provider

The library also provides an authentication provider factory:

import { googleAPIAuthProvider } from 'ra-data-google-sheets';

const authProvider = googleAPIAuthProvider();

Where I Landed

After a few days of playing with the Google API, I can answer that, yes, it is possible to use Google Sheets as a back-end API for react-admin.

Here is a quick list of the features I've implemented:

• Logging in using Google OAuth
• Listing, creating and editing data from a spreadsheet
• Combining data from several spreadsheets
• Handling relationships between several spreadsheets (or tabs)

You can already use ra-data-google-sheets to build a prototype without ever writing a single line of server code, or to offer an intuitive interface for managing data stored in a Google Sheet.

It reminds me of the beginning of every project. Why set up a complex back-end with a server, a database and an API, when a simple spreadsheet can do the job?

From my perspective, this demonstrates the flexibility of the react-admin architecture. Combined with the multiple features of Google Sheets (charts, polls, collaboration, live updates, connectors for tons of SaaS products, etc), it also radically increases the possible applications of react-admin.

What's Next

When I accepted François' challenge, I didn't expect to get that far in only 3 days. And even though I haven't covered all of react-admin features, the ones I implemented work fine with the Google Sheet API.

But I also hit a few bumps. In my opinion, the Google Sheet API documentation is the most important. I think this API is not made to work in a browser, because the client must be responsible for too many things like pagination or the ID management.

That's why, as far as Marmelab is concerned, this library will remain an experiment until a customer asks us to go further. In the meantime, all the code I wrote is free and open-source on Github: marmelab/ra-data-google-sheets. You can fork it, use it and improve it if you want.

Bonus

As a bonus, I've listed a few SaaS projects that have been designed as an API over the Google Sheets API. They look promising as they solve most of the Google Sheet shortcomings. I am thinking of testing them in my next projects.

Using REST:

• Stein: https://steinhq.com
• Sheety: https://sheety.co
• SheetDB: https://sheetdb.io
• Sheetsu: https://sheetsu.com
• Sheetson: http://sheetson.com

Using GraphQL:

• GraphQLsheet: https://graphqlsheet.com/

Another Approach:

• Node: https://github.com/theoephraim/node-google-spreadsheet/tree/95c3271b250bf4f9d99651a8269d1f76719e2168.

Credits: cover and thumbnail pictures by Adrien Amoros (me 😎).