How-to Read & Write Google Sheets with React
Tags: googlesheets, react, glideapp, javascript
Published: 2020-06-16 07:50:06

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/38t73ni351mycksba2dx.png)

I want to read and write Google Sheets with React to update the data for an app I built with [Glideapps](http://glideapps.com/), a no-code app builder that uses Google Sheets as the data source.

It took me quite a while to figure out how to do that. But turns out the solution is quite straight-forward.

TL;DR:
> 1. Generate a Google service account key file, get `private_key` and `client_email` values.
> 2. Get your `spreadsheet_id` and `sheet_id`.
> 3. Share your spreadsheet with editing permission with the email address in your `client_email` field.
> 4. Make sure the 1st row in your spreadsheet contains the header fields. These values will be the "keys" in your data objects.
> 5. Install [google-spreadsheet](https://github.com/theoephraim/node-google-spreadsheet) and use it to connect to Google Sheets. There are very good examples in the [docs here](https://github.com/theoephraim/node-google-spreadsheet), and I have included a snippet of how to append to the spreadsheet at the bottom of this post.

Here are the detailed step-by-step instructions with screenshots:

1) Go to Google's developer console:
https://console.developers.google.com/

Click on "Select a project" then click on "New project", give it a name.
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/hqnlbcwsx1hz8cfvdu9e.png)
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/unzhytcswdrkzxzk8axa.png)

Click on "Credentials" then click on "Manage service accounts", then click on "Create service account".
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/8all6pslaxc0m6iw1qxl.png)
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/8p35wmavh2ed140e6gc5.png)

Add a name and description, then click "Create"
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/bdhaiv7y8076g2t98ech.png)

Click "Continue" on the "Service account permissions" page, as it is optional.

Click "Done" on "Grant users access to this service account" page, as it is also optional.

Click "Create key" under the Actions column.
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/ckakhiozdujqn7kb6qjy.png)

Select the default "JSON" and Google will download a key file to your default download folder.
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/nhx6cwhvt4msosks1t8y.png)

If you open the JSON file, there are two fields you'll need: "private_key" and "client_email"
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/aao6hr5fp79jwwa1etgq.png)

2) Go to your Google Sheets and find the `spreadsheet_id` and `sheet_id`
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/u7g8td0i3r75g1msq1tv.png)

3) *IMPORTANT* Remember to share your spreadsheet with the email address in your key file with the key `client_email`
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/tsrtlcjih59ikshzukdo.png)

You should see the "person added" message right away.
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/p5fhd9z2j1vhgvj3suef.png)

4) Make sure your 1st row contains the column names
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/zdk6rx8elavyu2pipdx1.png)

5) There are very good examples on [Googe-Spreadsheet](https://github.com/theoephraim/node-google-spreadsheet) for reading and writing to the Spreadsheet.

Here is a snippet for appending a new row to the spreadsheet I wrote:
```javascript
import { GoogleSpreadsheet } from "google-spreadsheet";

// Config variables
const SPREADSHEET_ID = process.env.REACT_APP_SPREADSHEET_ID;
const SHEET_ID = process.env.REACT_APP_SHEET_ID;
const CLIENT_EMAIL = process.env.REACT_APP_GOOGLE_CLIENT_EMAIL;
const PRIVATE_KEY = process.env.REACT_APP_GOOGLE_SERVICE_PRIVATE_KEY;

const doc = new GoogleSpreadsheet(SPREADSHEET_ID);

const appendSpreadsheet = async (row) => {
  try {
    await doc.useServiceAccountAuth({
      client_email: CLIENT_EMAIL,
      private_key: PRIVATE_KEY,
    });
    // loads document properties and worksheets
    await doc.loadInfo();

    const sheet = doc.sheetsById[SHEET_ID];
    const result = await sheet.addRow(row);
  } catch (e) {
    console.error('Error: ', e);
  }
};

const newRow = { Name: "new name", Value: "new value" };

appendSpreadsheet(newRow);

```