Google Sheets API in Angular

Question

I am trying to find how to use the Google Sheets API in Angular. There is no reference to Angular on the documentation page. I have tried this and it doesn't seem to be working.

Can someone direct me on how I can use the Google Sheets API inside of my Angular application?

I am currently using Angular 8

Answer 1

I made an Angular library exactly for this use case!

With ng-google-sheets-db you can load data from Google Sheets in a breeze and use Google Sheets as your (read-only) backend for your Angular app! You may check out the Stackblitz example app.

Installation

ng add ng-google-sheets-db

or

npm install ng-google-sheets-db

Usage

Google Sheets

1. Create a Google Sheet:
   • The first row must be the header.
   • The following rows are your entries, one entry per row.
   • You may have an active column, with which you can enable or disable rows/entries.
   • A Google Sheets demo spreadsheet is available here.
2. Share your sheet:
   • [File] → [Share] → On the bottom of the modal at "Get Link" click [Change to anyone with the link] to be "Viewer".
   • Get the Spreadsheet ID (i.e. 1gSc_7WCmt-HuSLX01-Ev58VsiFuhbpYVo8krbPCvvqA): It is part of the Google spreadsheet URL.
   • Get the Sheet Name: The name of the worksheet can be found at the bottom of your Google spreadsheet.
3. Optional: It may be a good idea to enable 2-Step Verification for your Google account, if you have not done it yet :wink:.

Google Cloud Platform (GCP)

A good overview guide is the Get started as a Workspace developer.

1. Create a new project in the Google Cloud Console.
2. Enable Google Sheets API: [APIs & Services] → [Enable APIs and Services] → Search for "Google Sheets API" → [ENABLE].
3. Create an API key: [APIs & Services] → [Credentials] → [+ CREATE CREDENTIALS] → [API key] → [RESTRICT KEY] → In "Application restrictions" choose "HTTP referrers (web sites)" with "Website restrictions" and in "API restrictions" choose "Restrict key" and select "Google Sheets API" → [SAVE].
4. Get the generated API key.

Angular

Add GoogleSheetsDbService to your app's module as a provider and Angular's HttpClientModule to the imports:

import { HttpClientModule } from '@angular/common/http';

import { API_KEY, GoogleSheetsDbService } from 'ng-google-sheets-db';

@NgModule({
  ...
  imports: [
    HttpClientModule,
    ...
  ],
  providers: [
    {
      provide: API_KEY,
      useValue: <YOUR_GOOGLE_SHEETS_API_KEY>,
    },
    GoogleSheetsDbService
  ],
  ...
})
export class AppModule { }

Import and inject into your component's constructor:

import { GoogleSheetsDbService } from 'ng-google-sheets-db';

@Component({
  ...
})
export class YourComponent implements OnInit {
  characters$: Observable<Character[]>;

  constructor(private googleSheetsDbService: GoogleSheetsDbService) { }

  ngOnInit(): void {
    this.characters$ = this.googleSheetsDbService.get<Character>('1gSc_7WCmt-HuSLX01-Ev58VsiFuhbpYVo8krbPCvvqA', "Characters", characterAttributesMapping);
  }

Attributes Mapping

The attributesMapping maps the Google spreadsheet columns to to your outcome object.

const attributesMapping = {
  id: "ID",
  name: "Name",
  email: "Email Address",
  contact: {
    _prefix: "Contact ",
    street: "Street",
    streetNumber: "Street Number",
    zip: "ZIP",
    city: "City",
  },
  skills: {
    _prefix: "Skill ",
    _listField: true,
  },
};

For example, the Google spreadsheet column Email Address is mapped to the outcome object attribute email.

Nested objects

contact is an example of a nested object. You may define a _prefix as a prefix for all columns of the nested object. Please note that the _prefix may need a trailing whitespace.

Lists

skills is an example of a list. You need to set _listField and a _prefix for all columns of the list. In this example, all columns starting with _Skill _ and an increasing number are part of the list, i.e. Skill 1, Skill 2, etc. Please note that the _prefix may need a trailing whitespace.

It comes with a step by step usage guide and a demo application!