Create a CRUD API with Google Sheets: A Step-by-Step Guide

Unlock the Power of Google Sheets API: A Free Database with a User-Friendly Interface

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. The Google Sheets API makes this a reality, providing you with a robust tool to connect an existing spreadsheet, parse its available data, and pass it to your web application.

Getting Started with Google Cloud Console

To begin, you’ll need to set up a new project in Google Cloud Console. This involves creating a new project, enabling the Google Sheets API, and creating a service account with permissions to read and write operations on the sheet. Follow these steps to get started:

1. Head over to Google Cloud and sign up if you haven’t made an account yet.
2. Click New Project, give it a suitable name, and click Create.
3. Click the navigation menu and go to APIs and Services.
4. Search for the Google Sheets API and enable it.
5. Head back to the APIs and Services dashboard. Go to Credentials and create a new credential.
6. Click Service Account. Give it a name, set the rest as it is, and click Done.

Linking the Google Sheet

Now that you have set up your project, it’s time to connect your spreadsheet to the Google Cloud project. Here’s how:

1. Head over to Google Docs and make a new spreadsheet.
2. Name the spreadsheet and enter some dummy data so that you have something to fetch while testing the API.
3. Add the service account and assign it the Editor role, which gives it permissions to read, write, update, and delete data.

Setting Up the CRUD API

To set up the CRUD API, you’ll need to use a couple of packages: Express, dotEnv, and googleapis. Follow these steps:

1. Initialize npm using the command npm init.
2. Install the required packages using the command npm install express dotenv googleapis.
3. Add nodemon as a dev dependency using the command npm install --save-dev nodemon.
4. Create a new file called index.js and require dotenv and initialize express.

Making Your First Request

With everything set up, it’s time to make your first request. Here’s how:

1. Import the required packages from the googleapis package.
2. Create a GET route and define the auth token, consisting of a keyFile that points to the credentials.json file and scopes that provide complete access to perform read and write operations.
3. Define the client, the latest version of the API, and the spreadsheetId.
4. Make a GET request to the root URL on localhost:3000.

Implementing CRUD Operations

Now that you have made your first request, it’s time to implement CRUD operations. Here’s how:

Read Cell Values

To read cell values, you’ll use the spreadsheets.values.get method. Here’s how:

1. Use the spreadsheets.values.get method to read cell values.
2. Define the range parameter to specify the cell area to read and write upon.
3. Make a GET request to retrieve the cell values.

Create and Post Data

To create and post data, you’ll use the spreadsheets.values.append method. Here’s how:

1. Set up a POST route and define the auth token, spreadsheetId, and range.
2. Pass in the valueInputOption and resource properties to specify the cell values to be added to the spreadsheet.
3. Make a POST request to post data into the spreadsheet.

Update Cell Values

To update cell values, you’ll use the spreadsheets.values.update method. Here’s how:

1. Set up a PUT route and define the auth token, spreadsheetId, and range.
2. Specify the cell values to be updated through the resource property.
3. Make a PUT request to update the cell values.

Delete Cell Values

To delete cell values, you’ll use the spreadsheets.values.clear method. Here’s how:

1. Set up a POST route and define the auth token, spreadsheetId, and range.
2. Specify the spreadsheet row and column through the range property.
3. Make a POST request to delete the cell values.

Congratulations! You’ve Implemented CRUD Operations Using Google Sheets

With these steps, you’ve successfully implemented CRUD operations using Google Sheets. This free database with a user-friendly interface provides a great way to start learning APIs and can be used to integrate small-scale projects.