NestGoogleSheetConnector

A Google Sheet integration for NestJs.

Installation

To use this module you will need:

• create a Google Cloud service account
• install the module
• provide the module with the credentials of the google cloud service account
• provide permissions to your service account on the spreadsheets you want to manipulate

Create a Google Cloud service account

1. Access the Google APIs Console while logged into your Google account.

2. Create a new project and give it a name. Create a new project

3. Click on ENABLE APIS AND SERVICES. Enable Google Sheets API

4. Find and enable the Google Sheet API.

5. Create new credentials to the Google Sheets API. Select Other UI from the dropdown and select Application Data. Then click on the What credentials do I need? button.

6. On the next screen, choose a name for your service account, assign it a role of Project->Editor, and click Continue.

7. The credentials JSON file will be downloaded by your browser.

   The credentials file allows anyone to access your cloud resources, so you should store it securely. More information from Google.

8. Find the downloaded file and rename it to service_account.json.

Install the module

npm install @tonylyjones/nest-google-sheet-connector

Provide the module with the credentials of the google cloud service account

import { GoogleSheetModule } from "nest-google-sheet-connector"

@Module({
  imports: [
    GoogleSheetModule.register(credentials), // credentials is a JSON object downloaded from Google Cloud Platform
  ],
  controllers: [AppController],
  providers: [AppService],
})
export class AppModule {}

Provide permissions to your service account on the spreadsheets you want to manipulate

1. Create or select an existing Google Sheet.
2. Open the service_account.json file and find the client_email property.
3. Click on the Share button in the top right, and add the email address of the service account as an editor.

If you want only to allow the account read access to the spreadsheet, assign it the Viewer role instead.

4. Take note of the ID of the Google Sheet document, which is contained in its URL, after the /d element. So, for example, if the URL of your document is https://docs.google.com/spreadsheets/d/1234567890123abcf/edit#gid=0, the ID will be 1234567890123abcf.

Documentation

Inject the service into a constructor

import { GoogleSheetConnectorService } from "../../lib/nest-google-sheet-connector"

@Injectable()
export class AppService {
  constructor(
    private googleSheetConnectorService: GoogleSheetConnectorService
  ) {}
}

This service contain google sheet providers methods

Features

Troubleshooting

Google Cloud requires an SSL certificate for API requests. Here is how to certify your local environment under NestJs:

1. Get your certificate

openssl req -x509 -out localhost.crt -keyout localhost.key \
 -newkey rsa:2048 -nodes -sha256 \
 -subj '/CN=localhost' -extensions EXT -config <( \
  printf "[dn]\nCN=localhost\n[req]\ndistinguished_name = dn\n[EXT]\nsubjectAltName=DNS:localhost\nkeyUsage=digitalSignature\nextendedKeyUsage=serverAuth")

2. Modify the NestFactory in the src/main.ts file

import { NestFactory } from "@nestjs/core"
import { AppModule } from "./app.module"

async function bootstrap() {
  const fs = require("fs")
  const keyFile = fs.readFileSync(__dirname + "/../localhost.key")
  const certFile = fs.readFileSync(__dirname + "/../localhost.crt")
  const app = await NestFactory.create(AppModule, {
    httpsOptions: {
      key: keyFile,
      cert: certFile,
    },
  })
  await app.listen(3000)
}
bootstrap()