Hands-On Guide to Google Sheets API in TypeScript

Hello, this is Charu from Classmethod. In this blog, we are going to read the data written in the google spreadsheet through TypeScript. You might feel that the initial setting is a bit troublesome but we can go through it together!

Let's get started!

Step 1: Create a GCP project and issue a credential

Create a new project in GCP console.

Step 2: Enable Google Sheet API in your project and create a service account

Click on 'Enable API and Services'.

Search for 'Google Sheet API' and then enable it.

You should be able to view the API page. Now, click on the 'Create Credential' button.

Then, you can do the following settings as shown in the picture to create the credentials.

Next, you can create the service account as shown below. Give it a proper name and click on 'Continue'.

Step 3: Create the Key

You should be able to see your service account under the service account section. The next step is to make the key. For that, click on the pencil icon right next to your service account name.

Then click on the 'Key' option and select 'Create new key'.

Select JSON, click 'Create', then the key will be automatically downloaded.

The JSON will look like this,

We will be using client_email in our coding step.

Step 4: Create a Google Sheet and add the service account as Editor

Add the service account email address as an "Editor" in the spreadsheet you like to access.

Step 5: Write the Code

We will be writing the code in TypeScript. To start with it, we need to install few packages from the following command-

npm install typescript ts-node @types/node googleapis

Then you can write the following code-

import { google } from "googleapis";
import { JWT } from "google-auth-library";

// Load the credentials from the service account key file
const credentials = require("service-account-key.json");

// Create a new JWT client using the credentials
const client = new JWT({
  email: credentials.client_email,
  key: credentials.private_key,
  scopes: ["https://www.googleapis.com/auth/spreadsheets"],
});

// Authorize and create a Google Sheets API instance
const sheets = google.sheets({ version: "v4", auth: client });

async function main() {
  try {
    // Your spreadsheet ID
    const spreadsheetId = "spreadSheetID";

    // Read data from the spreadsheet
    const response = await sheets.spreadsheets.values.get({
      spreadsheetId,
      range: "A1:A", // Range to read
    });

    const values = response.data.values;

    if (!values) {
      console.log("No data found.");
    } else {
      console.log("Data:");
      values.forEach((row) => {
        console.log(row.join("\t"));
      });
    }
  } catch (error) {
    console.error("Error:", error);
  }
}

main();

In case you are wondering, from where to get the spreadsheet ID or range, then you can refer to this document.

Step 6: Compile and Run

To compile your TS code, run the following command-

tsc spreadsheetDemo.ts

This command will generate the JS file. To run your JS code, type the following command-

node spreadsheetDemo.js

If everything is set up correctly, you should see the data from your specified range printed to the console.

Conclusion:

You have just viewed your spread sheet data through code using Google Sheet API. This is just the beginning; you can now explore more advanced operations like writing data, updating, and deleting rows in your Google Sheets using the API. Refer to the Google Sheets API documentation for more details and examples.

Thank you!

Happy Learning :)