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!

Answer 2

Check this article: http://leifwells.com/2016/06/09/ionic-2--angular-2-using-a-google-spreadsheet-as-a-data-source/

You don't need any external package, but to perform a http request to your sheet.

Considering the above you can try something like this:

1. Publish to the web you sheet:

   Go to your google sheet, then select File > Publish to the Web option which will end up giving you a URL which has an id inside of it which is important. In our case the url looks like this:

   https://docs.google.com/spreadsheets/d/15Kndr-OcyCUAkBUcq6X3BMqKa_y2fMAXfPFLiSACiys/pubhtml

   The id that is in this url is:

   15Kndr-OcyCUAkBUcq6X3BMqKa_y2fMAXfPFLiSACiys

2. Create a Google sheet provider (using as reference the first link):

import { Injectable } from '@angular/core';
import { Http } from '@angular/http';
import { map } from 'rxjs/operators';

@Injectable()
export class GoogleDriveProvider {
  data: any = null;

  constructor(public http: Http) { }

  public getSheetData(): Observable<any> {
    const sheetId = '15Kndr-OcyCUAkBUcq6X3BMqKa_y2fMAXfPFLiSACiys';
    const url = `https://spreadsheets.google.com/feeds/list/${sheetId}/od6/public/values?alt=json`;

    return this.http.get(url)
      .pipe(
        map((res: any) => {
          const data = res.feed.entry;

          const returnArray: Array<any> = [];
          if (data && data.length > 0) {
            data.forEach(entry => {
              const obj = {};
              for (const x in entry) {
                if (x.includes('gsx$') && entry[x].$t) {
                  obj[x.split('$')[1]] = entry[x]['$t'];
                }
              }
              returnArray.push(obj);
            });
          }
          return returnArray;
        })
      );
  }
}

Note: The example is using Angular 8.