ng-google-sheets-db - Angular Google Sheets DB

Use Google Sheets as your (read-only) backend for your Angular app!

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,
  },
};

googleSheetsDbService
  .get(
    "1gSc_7WCmt-HuSLX01-Ev58VsiFuhbpYVo8krbPCvvqA",
    "Characters",
    attributesMapping
  )
  .subscribe((characters: object[]) => {
    // Use the characters here
  });

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.

Methods

get(spreadsheetId: string, worksheetName: string, attributesMapping: object | string[]): Observable<T[]>

const allCharacters$: Observable<Character> =
  googleSheetsDbService.get<Character>(
    "1gSc_7WCmt-HuSLX01-Ev58VsiFuhbpYVo8krbPCvvqA",
    "Characters",
    attributesMapping
  );

Get all rows from the Google spreadsheet as an Observable of objects or a given type as type variable T.

getActive(spreadsheetId: string, worksheetName: string, attributesMapping: object | string[], isActiveColumnName: string = 'is_active', activeValues: string[] | string = null): Observable<T[]>

const activeCharacters$: Observable<Character> =
  googleSheetsDbService.getActive<Character>(
    "1gSc_7WCmt-HuSLX01-Ev58VsiFuhbpYVo8krbPCvvqA",
    "Characters",
    attributesMapping,
    "Active"
  );

Get "active" rows from the Google spreadsheet as an Observable of objects or a given type as type variable T. You may have an active column with name isActiveColumnName, with which you can enable or disable rows/entries. "Active" rows have the value true, 1 or yes. You may also define your own activeValues.

Demo Application

Want to see an example of how to use ng-google-sheets-db? Check out the demo application in projects/demo or on StackBlitz.

License

MIT