Google Sheets API provided by Google is a good tool if you want to manage your database with Google Sheets. It is free to use and easy to implement. In this post, I’ll guide you through the steps on how to integrate it in your Node.js app to append values to the sheet. I’ll provide you with the extra links to help you if you want to implement more functionalities.

Quick tip: We’ll be doing all the sheets related work in the sheets folder in our app. So that we can then use it wherever and however we want to use.

Note: Google has provided all these files in their GitHub Repository. I changed them a bit to make them easier to use.

Getting client_secret.json

First, we would be needing a client_secret.json file which will help us authenticate our requests with the Google account. So, follow the steps to get that.

  1. Go to Google Developer Console.
  2. Create a new project and name it according to your app. I’ll be naming it Node-Sheets-API.
  3. Then make sure this project is selected. (Its name should appear next to the Google API logo in the header.) If not then select it from the dropdown in the header.
  4. Go to the Dashboard.
  5. Click on Enable APIs and Services.
  6. Search for Google Sheets API. Click on Google Sheets API result and then click Enable.
  7. Click on Credentials in the sidebar. Then click on the OAuth consent screen in the tab.
  8. Fill out the details according to your needs and click Save.
  9. Then click on the Credentials tab and click on Create Credentials.
  10. Select OAuth client ID.
  11. Select Other and name your client (I named in Node Client.) Now you can choose Web Application if you want to restrict origins and all. I’ll go with Others.
  12. Now click on the Download JSON icon in the client row.
  13. Rename the downloaded file to json.

So now that you have your client_secret.json file, move it to your application root folder (Not in the sheets folder). Now we can finally start with the coding part. B)

Installing the Google APIs library

Run the following command to install the googleapis library.

npm install googleapis -–save

Creating the config file

Config.js file will be responsible for the authentication part.
Create a file named config.js in your sheets folder and paste the following code in it.

const fs = require('fs');
const readline = require('readline');
const { google } = require('googleapis');
const OAuth2Client = google.auth.OAuth2;
const SCOPES = ['https://www.googleapis.com/auth/spreadsheets'];
const TOKEN_PATH = 'credentials.json';

/**
 * Create an OAuth2 client with the given credentials, and then execute the
 * given callback function.
 * @param {Object} credentials The authorization client credentials.
 * @param {function} callback The callback to call with the authorized client.
 */
const authorize = function (credentials, callback) {
	const { client_secret, client_id, redirect_uris } = credentials.installed;
	const oAuth2Client = new OAuth2Client(client_id, client_secret, redirect_uris[0]);

	// Check if we have previously stored a token.
	fs.readFile(TOKEN_PATH, (err, token) => {
		if (err) return getNewToken(oAuth2Client, callback);
		oAuth2Client.setCredentials(JSON.parse(token));
		callback(oAuth2Client);
	});
}

/**
 * Get and store new token after prompting for user authorization, and then
 * execute the given callback with the authorized OAuth2 client.
 * @param {google.auth.OAuth2} oAuth2Client The OAuth2 client to get token for.
 * @param {getEventsCallback} callback The callback for the authorized client.
 */
const getNewToken = function (oAuth2Client, callback) {
	const authUrl = oAuth2Client.generateAuthUrl({
		access_type: 'offline',
		scope: SCOPES,
	});
	console.log('Authorize this app by visiting this url:', authUrl);
	const rl = readline.createInterface({
		input: process.stdin,
		output: process.stdout,
	});
	rl.question('Enter the code from that page here: ', (code) => {
		rl.close();
		oAuth2Client.getToken(code, (err, token) => {
			if (err) return callback(err);
			oAuth2Client.setCredentials(token);
			// Store the token to disk for later program executions
			fs.writeFile(TOKEN_PATH, JSON.stringify(token), (err) => {
				if (err) console.error(err);
				console.log('Token stored to', TOKEN_PATH);
			});
			callback(oAuth2Client);
		});
	});
}

module.exports = {
    authorize,
    google
};

The comments provided by Google explains pretty much all of the functionality. In the last two four lines we are exporting the authorize function and the google object. They will be used in our main sheets.js file to authorize the methods that we will be using.
We’ll be creating the credentials.json file in the end when we’ll first run this app.

Creating the sheets.js file

The sheets.js file will have all of the functionalities that we will be needing for our database (Just for the tutorial purpose, I’ll be showing you the append function.
So, our append function will be as follows.

const append = (range, values) => {
	fs.readFile('client_secret.json', (err, content) => {
		if (err) return console.log('Error loading client secret file:', err);
		// Authorize a client with credentials, then call the Google Sheets API.
		authorize(JSON.parse(content), (auth) => {
			const sheets = google.sheets({ version: 'v4', auth });
			const valueInputOption = 'USER_ENTERED';
			const resource = {values};
			sheets.spreadsheets.values.append({
                spreadsheetId, range, valueInputOption, resource
            }, (err, result) => {
				if (err) {
					console.log(err);
				} else {
					console.log("Success!");
				}
			});
		});
	});
}

Let’s brief you with what is happening in this.

First, you are checking for the client_secret.json file (which should be in your root folder). Using this we get our credentials object needed as the first parameter to authorize function. Then the callback function will give us the auth object which will be used to create a secure request to Google Sheets API v4. Now the sheets object that we are creating next will have all of our methods required to edit our sheet. One such method is append which is in spreadsheets.values in the sheets object.

Google has provided a list of all other methods you can use to create a sheet, update values, delete values, etc. here. Google has also provided a snippets.js which has all the methods for updating, creating, appending, deleting values.

The append function in the sheets.spreadsheets.values requires 4 parameters:

  1. SpreadSheet ID: ID of the Google Sheet file.
  2. Range: The group of cells in the sheets in which to make changes. Sheets use A1 notation for the range.
  3. ValueInputOption: This determines how your input data should be interpreted. We will set this to USER_ENTERED. For more options, click here.
  4. Resource: This will be the object of the values to be updated.

Note: The values in our append function (not the Google one) is a 2D array. I will show an example in the end.

Quick tip: Create a const spreadsheetId with the id of your sheet in the top of the sheets.js file. Google uses a structure for the URL of sheets.

https://docs.google.com/spreadsheets/d/{{spreadsheetId}}/edit#gid={{sheetId}}

The final sheets.js will look something like this, including the require statements, spreadsheetId and the exports statement.

const {authorize, google} = require('./config');

// Change it with your id.
const spreadsheetId = "1qpyC0XzvTcKT6EISywvqESX3A0MwQoFDE8p-Bll4hps";

const append = (range, values) => {
	fs.readFile('client_secret.json', (err, content) => {
		if (err) return console.log('Error loading client secret file:', err);
		// Authorize a client with credentials, then call the Google Sheets API.
		authorize(JSON.parse(content), (auth) => {
			const sheets = google.sheets({ version: 'v4', auth });
			const valueInputOption = 'USER_ENTERED';
			const resource = {values};
			sheets.spreadsheets.values.append({
                spreadsheetId, range, valueInputOption, resource
            }, (err, result) => {
				if (err) {
					console.log(err);
				} else {
					console.log("Success!");
				}
			});
		});
	});
}

module.exports = {
    append
};

That’s it. You can use it anywhere with the following code.

const sheets = require('PATH_TO_SHEETS.JS');

sheets.append(
    "Sheet1!A1",
    [
        ["data 1", "data 2", "data3"]
    ]
);

Finishing Up

Now, the first time when you’ll run this you’ll be asked to authorize the app by visiting the URL provided with it. Go to that URL. It will give you a code which you’ll have to then paste back in the terminal or cmd. This will create your credentials.json file needed in the config.json file.

Note: Google also provides REST APIs for this. If REST API is your thing you can use this although I’ve never used this :p.