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:

• Head over to Google Cloud and sign up if you haven’t made an account yet.
• Click New Project, give it a suitable name, and click Create.
• Click the navigation menu and go to APIs and Services.
• Search for the Google Sheets API and enable it.
• Head back to the APIs and Services dashboard. Go to Credentials and create a new credential.
• 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:

• Head over to Google Docs and make a new spreadsheet.
• Name the spreadsheet and enter some dummy data so that you have something to fetch while testing the API.
• 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:

npm init
npm install express dotenv googleapis
npm install --save-dev nodemon

Create a new file called index.js and require dotenv and initialize express:

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

Making Your First Request

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

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

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

const client = await auth.getClient();
const spreadsheetId = 'your-spreadsheet-id';

app.get('/', async (req, res) => {
  const response = await client.spreadsheets.values.get({
    spreadsheetId,
    range: 'Sheet1!A1:B2',
  });
  res.send(response.data);
});

Implementing CRUD Operations

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

Read Cell Values

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

app.get('/read', async (req, res) => {
  const response = await client.spreadsheets.values.get({
    spreadsheetId,
    range: 'Sheet1!A1:B2',
  });
  res.send(response.data);
});

Create and Post Data

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

app.post('/create', async (req, res) => {
  const resource = {
    valueInputOption: 'USER_ENTERED',
    insertDataOption: 'INSERT_ROWS',
    range: 'Sheet1!A1:B2',
    resource: {
      values: [
        ['Value 1', 'Value 2'],
      ],
    },
  };
  const response = await client.spreadsheets.values.append({
    spreadsheetId,
    range: 'Sheet1!A1:B2',
    resource,
  });
  res.send(response.data);
});

Update Cell Values

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

app.put('/update', async (req, res) => {
  const resource = {
    valueInputOption: 'USER_ENTERED',
    range: 'Sheet1!A1:B2',
    resource: {
      values: [
        ['Updated Value 1', 'Updated Value 2'],
      ],
    },
  };
  const response = await client.spreadsheets.values.update({
    spreadsheetId,
    range: 'Sheet1!A1:B2',
    resource,
  });
  res.send(response.data);
});

Delete Cell Values

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

app.post('/delete', async (req, res) => {
  const response = await client.spreadsheets.values.clear({
    spreadsheetId,
    range: 'Sheet1!A1:B2',
  });
  res.send(response.data);
});