Build a CRUD API using the Google Sheets API
Tags: webdev, googlecloud
Published: 2021-07-29 21:20:15

**Written by [Georgey V B](https://blog.logrocket.com/author/georgeyvb/)** ✏️

As the name suggests, the Google Sheets API lets you connect an existing spreadsheet, parse its available data, and pass it to your web application. The latest is version 4.0, which provides the user control over many other properties — such as complete access to cell formatting, including setting colors, text styles, and more. Imagine having a completely free database with an intuitive user interface that helps you view your data and organize it according to your needs while acting as a CMS. How cool is that?

Without further ado, let’s jump right into creating your own CRUD API using the Google Sheets API.

Here’s what we’ll cover in this article:

1.  [Setting up a new project in Google Cloud Console](#setting-up-new-project-google-cloud-console)
2.  [Linking the Google Sheet](#linking-google-sheet)
3.  [Setting up the CRUD API](#setting-up-crud-api)
4.  [Making your first request](#making-your-first-request)
5.  [Implementing CRUD operations](#implementing-crud-operation)

## Setting up a new project in Google Cloud Console

As with accessing any API service, we must first set up authentication and authorization. Head over to [Google Cloud](https://cloud.google.com) and sign up if you haven't made an account yet. Then, follow the steps below to make a new project.

![Setting up a new project in Google Cloud Console](https://blog.logrocket.com/wp-content/uploads/2021/07/new-project-google-cloud-console.png)

Click **New Project**, give it a suitable name, and click **Create**.

Next, click the navigation menu and go to **APIs** **and** **Services**.

![APIs and Services panel in Google Cloud Console](https://blog.logrocket.com/wp-content/uploads/2021/07/apis-services-google-cloud-console.png)

You will be then redirected to the API library. Search for the Google Sheets API and enable it.

![Enable the Google Sheets API](https://blog.logrocket.com/wp-content/uploads/2021/07/google-sheets-api-download.png)

Head back to the APIs and Services dashboard. Go to **Credentials** and create a new credential.

![Credentials panel in APIs and Services tab](https://blog.logrocket.com/wp-content/uploads/2021/07/credentials-apis-services.png)

Click **Service Account**. Give it a name, set the rest as it is, and click **Done**.

You have now created a bot account for the spreadsheet that has permissions to read and write operations on the sheet.

Copy the service email we created. This will come into use when we connect the spreadsheet to the Google Cloud project.

Click the service account email, and then move along to **Keys**.

![Create a new key in Google Cloud Console](https://blog.logrocket.com/wp-content/uploads/2021/07/keys-google-cloud-console.png)

Go ahead and create a new key, setting the file type as JSON. A file will be downloaded shortly, and if possible you should move it to the folder where you expect to set up the starting files.

## Linking the Google Sheet

Now we’ll connect our spreadsheet to the Google Cloud project. Head over to [Google Docs](https://docs.google.com/spreadsheets) and make a new spreadsheet. Name the spreadsheet.

Enter in some dummy data so that we have something to fetch while testing the API.

Now, let’s add the service account and assign it the Editor role, which gives it permissions to read, write, update, and delete data.

![Giving the service email editor privileges in the Google Sheet](https://blog.logrocket.com/wp-content/uploads/2021/07/service-email-google-sheet-access.png)

Click **Share** and add the service email we recently copied, make sure you give it editor access, and un-check **Notify People.**

That’s all you have to do! Now let’s head over to the code editor and set up the starting files for the API.

## Setting up the CRUD API

We’ll be using a couple of packages for the API: [Express](https://expressjs.com/), [dotEnv](https://www.npmjs.com/package/dotenv), and [googleapis](https://www.npmjs.com/package/googleapis). Before we download those, let’s initialize npm using the following command:

```bash
npm init -y
```

Now install the packages:

```bash
npm install express dotenv googleapis
```

Add nodemon as a dev dependency (this will ensure the development server restarts whenever we make any code changes):

```bash
npm install nodemon --save-dev
```

With all that done, create a new file called `index.js`.

Start by requiring `dotenv` and then initialize `express`.

```javascript
require('dotenv').config();
const express = require('express');
const app = express();

app.listen(3000 || process.env.PORT, () => {
  console.log('Up and running!!');
});
```

Create a new script in the `package.json` file:

```json
"dev": "nodemon index.js"
```

And if all works fine, nodemon will restart the server every time we save the file.

```bash
npm run dev
```

## Making your first request

With all that done, let’s see whether or not our spreadsheet is actually linked with the Google Cloud project.

Import the following from the googleapis package:

```javascript
const { google } = require('googleapis');
```

Create a GET route:

```javascript
app.get('/', async (req, res) => {
  res.send("Hello Google!");
});
```

Create an `auth` token next, consisting of a `keyFile` that points to the `credentials.json` file we downloaded and `scopes` that provide complete access to perform read and write operations.

```javascript
const auth = new google.auth.GoogleAuth({
  keyFile: 'credentials.json',
  scopes: 'https://www.googleapis.com/auth/spreadsheets'
});
```

| ![Auth tokens in Google Sheets](https://blog.logrocket.com/wp-content/uploads/2021/07/auth-tokens-google-sheets-dev.png) |
|:--:|
| <b>Source: [Google Sheets for Developers](https://developers.google.com/sheets/api/guides/authorizing).</b>|

You can always refer to the [official Google Developers documentation](https://developers.google.com/sheets/api/quickstart/nodejs) for additional help with this.

Next up, define `client`, the latest version of the API, and the `spreadsheetId`.

```javascript
const client = await auth.getClient();
const googleSheet = google.sheets({ version: 'v4', auth: client });
const spreadsheetId = your_spreadsheetid
```

Get the spreadsheet ID from the URL of the Google spreadsheet, like so:

```
https://docs.google.com/spreadsheets/d/{_your_database_id_}/edit#gid=0
```

In the above example, `gid` is the sheet ID.

You should probably store this sensitive information in an environment file. Create a `.env` file and store the spreadsheet ID as shown:

```
SPREADSHEET_ID=your_spreadsheet_id
```

And finally, point it out to the environment variable:

```
const spreadsheetId = process.env.SPREADSHEET_ID
```

With all that done, let’s now finally make a request!

```javascript
const getMetaData = await googleSheet.spreadsheets.get({
  auth,
  spreadsheetId,
  range: 'Sheet1!A:B'
});

res.send(getMetaData);
```

Make sure you name the variables as we did above because it is also the shorthand for writing `auth: auth`.

Every API call takes in two parameters, which are `auth` and the `spreadsheetId`. The `range` defines the range of cells to be edited. If you’re not sure of the values, you can always make use of the spreadsheet’s interface. We’ll be using when it comes to reading cell values in the next section.

For now, go ahead and make a GET request to the root URL on `localhost:3000`. If you have followed through with all the steps, you’ll get a long response back from the API.

![GET request response](https://blog.logrocket.com/wp-content/uploads/2021/07/get-request-response.png)

## Implementing CRUD operation

### 1\. Read cell values

For now, comment out the previous request, and let’s actually read the cell values we have entered.

To read cell values, we’ll use the `spreadsheets.values.get` method.

```javascript
const getSheetData = await googleSheet.spreadsheets.values.get({
  auth,
  spreadsheetId,
  range: 'Sheet1!A:B'
});

res.send(getSheetData);
```

As I said before, the method always takes in `auth` and `spreadsheetId`. The `range` parameter defines the cell area to read and write upon. In this case, we’ll only make changes to the first two columns, A and B.

Go ahead and make a GET request.

![Make a GET request without restricting cell values](https://blog.logrocket.com/wp-content/uploads/2021/07/get-request-info-unrestricted.png)

The response contains a bunch of information, including the cell values, the color of the cells, geo-location, and time zone. Let’s target the cell values here.

```javascript
res.send(getSheetData.data.values);
```

The response looks much more concise now.

![GET request response with concise cell values](https://blog.logrocket.com/wp-content/uploads/2021/07/get-request-response-concise.png)

Note that we are also getting the actual column headings in these results. You may want to omit those and send back only the cell values underneath the first row.

Here’s how we can change the `range`. Select the area you want to include in your response. The selected area is denoted by a range. In our example, it’s from column A to column B.

![Select the range in your Google Sheet](https://blog.logrocket.com/wp-content/uploads/2021/07/google-sheets-range.png)

Since we need to include the cell values under the column headings in row one, we can start selecting from row two instead. Hence, the new range is now `Sheet1!A2:B`.

The response looks much better now!

![The final GET request, with all changes made](https://blog.logrocket.com/wp-content/uploads/2021/07/final-get-request.png)

### 2\. Create and post data

With that done, let’s move on to posting data into the spreadsheet.

Set up a POST route:

```javascript
app.post('/post', async (req, res) => {
  res.send("Data submitted!");
});
```

Follow the same procedure as above, setting up the `auth` token and defining the spreadsheet ID.

To post data, we’ll use the `spreadsheets.values.append` method. The Google API will append values into the spreadsheet depending on the number of values passed into the request.

The method remains the same. We’ll pass in `auth`, `spreadsheetId`, and a `range`. Along with that, we now pass in two more properties: `valueInputOption` and `resource`.

```javascript
const response = await googleSheet.spreadsheets.values.append({
  auth,
  spreadsheetId,
  range: 'Sheet1!A2:B',
  valueInputOption: 'USER_ENTERED',
  resource: {
    values: [['NextJS', 'The framework for Production']]
  }
});

res.send(response)
```

`valueInputOption` can take two options, `"RAW"` or `"USER_ENTERED"`. If `"RAW"`, then whatever the user has entered will be stored as it is. If you use `"USER_ENTERED"`, the user input will always be parsed when passed — if the user enters a number, it’ll be parsed as a number.

This is really helpful in certain use cases — for instance, let’s say you’re building a React form that sends in the submitted data to a spreadsheet. I’ll use the example of a simple spreadsheet with a score corresponding to each subject.

![Example for creating and posting data](https://blog.logrocket.com/wp-content/uploads/2021/07/create-and-post-data.png)

If the `valueInputOption` is set to `"USER_ENTERED"`, the data gets posted and is recognized as a number. But if I set the parameter to `"RAW"` and pass the score as a string, the data gets posted, but Google Sheets doesn’t appear to treat the score as a number.

![Parsing strings and numbers in post data](https://blog.logrocket.com/wp-content/uploads/2021/07/create-and-post-data-2.png)

The `resource` takes in the cell values to be added to the spreadsheet. You can also enter multiple entries by adding another set of arrays.

```javascript
resource: {
  values: [
        ['NextJS', 'The framework for Production'], 
        ['Jest', 'The testing framework for React']
  ]
}
```

Go ahead and make a POST request. You can make use of any API tester like [Postman](https://postman.com) for help with this.

### 3\. Update cell values

To update cell values, we will use the `spreadsheets.values.update` method.

Go ahead and make a PUT route.

```javascript
app.put('/update', async (req, res) => {
  res.send("Updated cell!");
});
```

The method takes in `auth` and `spreadsheetId` as usual. Make sure the `range` points out to a single row only, unless you’re updating multiple rows.

Here, I will specify `range:` `"Sheet1!A2:B2"`, which is the second row only. The rest all remains the same. You can set `valueInputOption` to either `"RAW"` or `"USER_ENTERED"`. And finally, enter the cell values you want to replace through `resource`.

```javascript
const response = await googleSheet.spreadsheets.values.update({
  auth,
  spreadsheetId,
  range: 'Sheet1!A2:B2',
  valueInputOption: 'USER_ENTERED',
  resource: {
    values: [['Jamstack', 'Future of the Web']]
  }
});

res.send(response)
```

Go ahead and make a PUT request on the API tester. The cell values should be updated now.

### 4\. Delete cell values

The Sheets API recommends using a POST request in order to use the `spreadsheets.values.clear` method.

| ![Deleting cell values](https://blog.logrocket.com/wp-content/uploads/2021/07/google-sheets-api-documentation.png) |
|:--:|
| <b>Source: [Google Sheets API documentation](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/clear)</b>|

So, we’ll make a new POST route.

```javascript
app.post('/delete', async (req, res) => {
  res.send("Deleted Cell successfully!");
});
```

This method is quite straightforward. All you need to do is specify the spreadsheet row and column through the `range` property.

```javascript
const response = await googleSheet.spreadsheets.values.clear({
  auth,
  spreadsheetId,
  range: "Sheet1!A5:B5"
});
```

Make a new request to the `/delete` route to see the changes.

Well, congratulations! That’s something new! We have implemented CRUD operations using Google Sheets. If you ever get stuck, you can take a look at [this repo on my GitHub](https://github.com/GeoBrodas/google-sheets-crud).

## Conclusion

Looks like we discovered a whole new database that is free and has a clean interface for managing data. You can use it with a range of languages, including Python, Go, Ruby, and many more.

Although there is a limit on the number of requests you can make — 100 requests per 100 seconds — if you look at the bright side, the Google Sheets API provides a great way for you to start learning APIs and can be used to integrate small-scale projects.

---

## [LogRocket] (https://logrocket.com/signup/): Full visibility into your web apps

[![LogRocket Dashboard Free Trial Banner] (https://blog.logrocket.com/wp-content/uploads/2017/03/1d0cd-1s_rmyo6nbrasp-xtvbaxfg.png)](https://logrocket.com/signup/)

[LogRocket] (https://logrocket.com/signup/) is a frontend application monitoring solution that lets you replay problems as if they happened in your own browser. Instead of guessing why errors happen, or asking users for screenshots and log dumps, LogRocket lets you replay the session to quickly understand what went wrong. It works perfectly with any app, regardless of framework, and has plugins to log additional context from Redux, Vuex, and ngrx/store.

In addition to logging Redux actions and state, LogRocket records console logs, JavaScript errors, stacktraces, network requests/responses with headers + bodies, browser metadata, and custom logs. It also instruments the DOM to record the HTML and CSS on the page, recreating pixel-perfect videos of even the most complex single-page apps.

[Try it for free] (https://logrocket.com/signup/).