Repository files navigation

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

constattributesMapping={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});

ng add ng-google-sheets-db

or

npm install ng-google-sheets-db

1. Create aGoogle Sheet:
   • The first rowmust be the header.
   • The following rows are your entries, one entry per row.
   • You may have anactive column, with which you can enable or disable rows/entries.
   • A Google Sheets demo spreadsheet is availablehere.
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 theSpreadsheet ID (i.e.1gSc_7WCmt-HuSLX01-Ev58VsiFuhbpYVo8krbPCvvqA): It is part of the Google spreadsheet URL.
   • Get theSheet 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 enable2-Step Verification for your Google account, if you have not done it yet 😉.

A good overview guide is theGet started as a Workspace developer.

1. Create a new project in theGoogle Cloud Console.
2. Enable Google Sheets API: [APIs & Services] → [Enable APIs and Services] → Search for "Google Sheets API" → [ENABLE].
3. Create anAPI 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.

AddGoogleSheetsDbService to your app's module as a provider and Angular'sHttpClientModule 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],  ...})exportclassAppModule{}

Import and inject into your component's constructor:

import{GoogleSheetsDbService}from'ng-google-sheets-db';@Component({  ...})exportclassYourComponentimplementsOnInit{characters$:Observable<Character[]>;constructor(privategoogleSheetsDbService:GoogleSheetsDbService){}ngOnInit():void{this.characters$=this.googleSheetsDbService.get<Character>('1gSc_7WCmt-HuSLX01-Ev58VsiFuhbpYVo8krbPCvvqA',"Characters",characterAttributesMapping);}

TheattributesMapping maps the Google spreadsheet columns to to your outcome object.

constattributesMapping={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 columnEmail Address is mapped to the outcome object attributeemail.

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.

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.

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

Get all rows from the Google spreadsheet as anObservable of objects or a given type as type variableT.

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

Get "active" rows from the Google spreadsheet as anObservable of objects or a given type as type variableT. You may have anactive column with nameisActiveColumnName, with which you can enable or disable rows/entries."Active" rows have the valuetrue,1 oryes. You may also define your ownactiveValues.

Want to see an example of how to useng-google-sheets-db? Check out the demo application inprojects/demo or onStackBlitz.

MIT