We need to access a Google Sheet for basic CRUD operations from Angular. There are several great node modules available that will allow us to perform CRUD operations on Google Sheets but most require OAuth2 and we want to keep this fairly straight forward by using a Google Service Account and JWT (JSONWeb Token).
The best fit module for this is:
[google-spreadsheet](https://www.npmjs.com/package/google-spreadsheet)
This handy little module takes care of the authentication as well as providing the hooks to the Google Sheet. But since this is a Node.js module we are trying to run in a browser we have to create a workaround. Here’s a quick overview of what we need to do to get this up and running. First we’ll enable the Google Sheets API and create a servive account. Next we’ll extend Webpack in Angular to help setup our workaround. Then we’ll configure our components and, for this example, write a method that writes to the Google Sheet.
This is going to be a little lengthy so I won’t go over creating an Angular project. All right let’s get started!
First enable the Google Sheets API and create the Service Account:
Go to the Google Developers Console and navigate to the API section. You should see a dashboard.
Click on “Enable APIs” or “Library” which should take you to the library of services that you can connect to. Search and enable the Google Sheets API.
Go to Credentials and select “Create credentials”.
Select “Service Account” and proceed forward by creating this service account. Name it whatever you want. I used SheetBot.
Under “Role”, select Project > Owner or Editor, depending on what level of
access you want to grant.
Select JSON as the Key Type and click “Create”. This should automatically
download a JSON file with your credentials.
Rename this credentials file as credentials.json and create a sheets directory in your src/assets directory of your project.
The last super important step here is to take the “client email” that is in your credentials file and grant access to that email in the sheet that you’re working in. If you do not do this, you will get an error when trying to access the sheet.
Configure Angular:
Now let’s start configuring the Angular project to play nice with the Node.js packages we’ll be installing.
Edit the tsconfig.app.json and “node” to the “types”:[] section and paste this right below it “typeRoots”: [ “../node_modules/@types” ]
The two should look like this:
“`
“types”: [ “node” ],
“typeRoots”: [ “../node_modules/@types” ]
“`
***Dont forget your commas***
Since we’ll be mocking Node.js we need to add in the Node typings to the angular project. Install them by running this from the terminal:
“`
npm install @types/node –save
“`
Now let’s extend Webpack. We’ll be following some of the steps that
Vojtech Masek provides in this [article](https://medium.com/angular-in-depth/google-apis-with-angular-214fadb8fbc5?)
Install the Angular Custom-Webpack: “`
npm i -D @angular-builders/custom-webpack
“`
Now we have to tell Angular to use the correct builders for the custom webpack. Open up angular.json and replace the builder in architect with:
“`
“builder”: “@angular-builders/custom-webpack:browser”,
“`
then paste this in to the options section right below:
“`
“customWebpackConfig”: {
“path”: “./extra-webpack.config.js”
},
“`
It should look like this:
“`
“architect”: {
“build”: {
“builder”: “@angular-builders/custom-webpack:browser”,
“options”: {
“customWebpackConfig”: {
“path”: “./extra-webpack.config.js”
},
“`
and under serve replace builder with:
“`
“builder”: “@angular-builders/custom-webpack:dev-server”,
“`
It should look like this:
“`
“serve”: {
“builder”: “@angular-builders/custom-webpack:dev-server”,
“`
More details about using Custom Webpacks can be found in Angular’s builder
[docs](https://github.com/just-jeb/angular-builders/tree/master/packages/custom-webpack#Custom-webpack-dev-server).
In your projects root directory create a new javascript file and name it:
extra-webpack.config.js
paste this code into it:
“`
const path = require(‘path’);
module.exports = {
resolve: {
extensions: [‘.js’],
alias: {
fs: path.resolve(__dirname, ‘src/mocks/fs.mock.js’),
child_process: path.resolve(
__dirname,
‘src/mocks/child_process.mock.js’
),
‘https-proxy-agent’: path.resolve(
__dirname,
‘src/mocks/https-proxy-agent.mock.js’,
),
},
},
};`
“`
So what does all of that do? We are telling WebPack to use the mock javascript files instead of trying to call additional Node.js modules. Let’s create the mocks, first create a folder in the projects src folder and name it mocks. In the mocks folder create three javascript files:
child_process.mock.js
fs.mock.js
http-proxy-agent.mock.js
paste this code into the mocks for child_process and fs:
“`
module.exports = {
readFileSync() {},
readFile() {},
};
“`
For the http-proxy mock use this code:
“`
module.exports = {};
“`
These mock methods let the node.js modules think they are running correctly but
in reality don’t do anything. Now we need to provide a way for node to
handle process and Buffer calls since node isn’t able to access the global
variables from the browser side. To do so install these two packages:
“`
npm i -D process buffer
“`
Now add these to the Application imports in polyfills.ts:
“`
import * as process from ‘process’;
(window as any).process = process;
import { Buffer } from ‘buffer’;
(window as any).Buffer = Buffer;
“`
and add this to the head section of index.html:
“`
“`
Ok almost there! At this point we have Angular configured with the mocks and are ready to install a few more modules. First let’s install google-spreadsheet:
“`
npm i google-spreadsheet –save
“`
Depending on the platform you are on you may receive some warning errors
indicating that optional fsevent was not being installed. Since it’s listed as an optional module I ignored it. I’m working on a Windows 10 device and had to install these modules to make the compiler happy:
eslint
fs
child_process
net
tls
Wait, didnt we just mock fs and child_process? Yes but the compiler still sees them listed as dependencies and wants them installed. Now that we have everything installed and configured let’s try it out.
Wrapping up
I added a contact component and created a contact form with an onSubmit function. The onSubmit function passes the jsonObject to the addRows method for the Google Sheet. Here’s what my contact.component.html looks like:
“`
Sheet Crud Example
“`
Not very elegant, and yes I need to add formReset to it, but it gets the job done for now. For the contact.component.ts I added these two imports first:
“`
import { FormGroup, FormBuilder, FormControl } from ‘@angular/forms’;
import {HttpClient} from ‘@angular/common/http’;
“`
The Form imports are to build the form while the HttpClient will be used by the google-spreadsheet module for sending the JWT for authenticating and for our CRUD operations.
I then added the node related cont’s:
“`
const GoogleSpreadsheet = require(‘google-spreadsheet’);
const creds = require(‘../../assets/sheet-api/credentials.json’);
“`
If you forgot to install the node types (npm i @types/node) you will get an error becasue TypeScript doesn’t use require. If you get a message telling you to convert the require to an import just ignore it.
Next I configured my constructor:
“`
constructor(private formBuilder: FormBuilder, private http: HttpClient, ) {
this.contactForm = new FormGroup({
fullName: new FormControl(),
email: new FormControl(),
message: new FormControl()
});
}
“`
Then I setup the onSubmit method:
“`
contactForm: FormGroup;
onSubmit() {
const jsonObject = this.contactForm.value;
console.log(‘Your form data : ‘, this.contactForm.value);
const doc = new GoogleSpreadsheet(‘***YOUR GOOGLE SHEETID***’);
doc.useServiceAccountAuth(creds, function(err) {
doc.addRow(1, jsonObject, function (err) {
if (err) {
console.log(err);
}
});
});
}
“`
So what exactly are we doing here? Well, we take the current contactForm values and assign them to jsonObject. Then we put them out to the log and create a new GoogleSpreadSheet. It’s important that you replace ***YOUR GOOGLE SHEETID*** with the actual ID of the Google Sheet you are trying to work with. You can find it by opening up the Google Sheet in your browser. The ID is that really long hash string between the /d/ and the /edit:
https://docs.google.com/spreadsheets/d/***2he8jihW5d6HGHd3Ts87WdRKqwUeH-R_Us8F3xZQiR***/edit#gid=0
doc then calls the useServiceAccountAuth method in google-spreadsheet and passes the credentials.json as a wrapper to call addRow. This authenticates the session and let’s us add a row to the existing sheet. If you have the browser console open you will see a couple of warnings. The first warning (which looks intimidating but is not) is a compliler warning:
Critical dependency: the request of a dependency is an expression
You may also see a console log that says:
Error: incorrect header check
This is because of the function (err) in the useServiceAccountAuth method. The function (err) is a node error callback that Angular cannot process correctly and we haven’t coded around.
…and that wraps it up. Why am I writing a message like this to a Google Sheet instead of using an email component? I’m using Google Sheets as the backend for a webapp and I use a script in Google Sheets to forward the message as an email and for logging purposes.
Check out the google-spreadsheet [repository](https://github.com/theoephraim/node-google-spreadsheet)
for additional details on the calls you can make and how to use the module.
From the blog Home | Michael Duquette by Michael Duquette and used with permission of the author. All other rights reserved by the author.