We all must have heard about CRUD(Create, Retrieve, Update and Delete) operation. In this blog, let’s see how we can do CRUD operation on Google Spreadsheet using Node.js,
When we are going to implement APIs,
What is Google Spreadsheet?
Google Spreadsheets is a Web-based application that allows users to create, update and modify spreadsheets and share the data live online. The Ajax-based program is compatible with Microsoft Excel and CSV (comma-separated values) files. Spreadsheets can also be saved as HTML.
What exactly is Node JS?
Node.js is an open-source, cross-platform JavaScript run-time environment that executes JavaScript code server-side.
The Node run-time environment includes everything you need to execute a program written in JavaScript.
Node.js came into existence when the original developers of JavaScript extended it from something you could only run in the browser to something you could run on your machine as a standalone application.
Now you can do much more with JavaScript than just making websites interactive.
Prerequisites
To run this simple example, all you’ll need is
Node.js & npm installed and a Google account.
Step 1: Turn on the Google Sheets API & Create the credentials file
Step 1.1: Use this URL link to create a new console project.
Step 1.2: To enable the spreadsheet API, please check the screenshot.
Step 1.3: In search box, type the word google sheet and click the card to enable the spreadsheet API
Step 1.4: Once the google spreadsheet API enables, then you need to create credentials, Use this URL link to create a credentials
Step 1.5: then click the “create credentials” from the dropdown and select the OAuth client ID.
Step 1.6: Select the checkbox “other” option and click the create.
Step 1.7: then download the credentials file and we can use this file in our sample code.
Step 2: Install The Client Library
Run the following commands to install the libraries using npm:
$ npm install googleapis@27 --save
Related: How To Deploy NodeJS App to Heroku
Step 3: Set up the sample in NodeJS
Create a ".js” file in your working directory and here i named it like "spreadsheet.js" and copy the following code:
var fs = require('fs');
var readline = require('readline');
var google = require('googleapis');
var googleAuth = require('google-auth-library');
var SCOPES = ['https://www.googleapis.com/auth/spreadsheets'];
var TOKEN_PATH = './.credentials/token.json'; // first time if you run the sample code this file will generate,
Var SPREADSHEET_JSON_FILE = ‘download the credentials file ***.json’ //use the download the credentials file here
// Store token to disk be used in later program executions.
function storeToken(token) {
try {
fs.mkdirSync(TOKEN_DIR);
} catch (err) {
if (err.code !== 'EEXIST') {
throw err;
}
}
fs.writeFile(TOKEN_PATH, JSON.stringify(token));
console.log('Token stored to ' + TOKEN_PATH);
}
// Get and store new token after prompting for user authorization, and then
// execute the given callback with the authorized OAuth2 client.
function getNewToken(oauth2Client, callback) {
var authUrl = oauth2Client.generateAuthUrl({
access_type: 'offline',
scope: SCOPES
});
console.log('Authorize this app by visiting this url: ', authUrl);
var rl = readline.createInterface({
input: process.stdin,
output: process.stdout
});
rl.question('Enter the code from that page here: ', function(code) {
rl.close();
oauth2Client.getToken(code, function(err, token) {
if (err) {
console.log('Error while trying to retrieve access token', err);
return;
}
oauth2Client.credentials = token;
storeToken(token);
callback(oauth2Client);
});
});
}
// Create an OAuth2 client with the given credentials, and then execute the given callback function.
function authorize(credentials, callback) {
var clientSecret = credentials.installed.client_secret;
var clientId = credentials.installed.client_id;
var redirectUrl = credentials.installed.redirect_uris[0];
var auth = new googleAuth();
var oauth2Client = new auth.OAuth2(clientId, clientSecret, redirectUrl);
// Check if we have previously stored a token.
fs.readFile(TOKEN_PATH, function(err, token) {
if (err) {
getNewToken(oauth2Client, callback);
} else {
oauth2Client.credentials = JSON.parse(token);
callback(oauth2Client);
}
});
}
Also Read: 10 JavaScript concepts every Node.js developer should know
Step 3.1: CREATE (or) UPDATE
CREATE or UPDATE both the operations can be done by the below mentioned code so copy and paste this code for applying both,
var create = function () {
function listMajors(auth) {
var sheets = google.sheets('v4');
var SPREADSHEET_ID = "************";
var range = "**********";
var requestInsert = {
auth: auth,
spreadsheetId: SPREADSHEET_ID,
range: range,
valueInputOption: 'RAW',
resource : {
range: range,
'majorDimension': 'ROWS',
'values': [["name","list"]]
}
};
sheets.spreadsheets.values.update(requestInsert, function(err, response) {
if (err) {
throw err;
}
console.log(response,' record Inserted.');
});
}
// Load client secrets from a local file.
fs.readFile(SPREADSHEET_JSON_FILE, function processClientSecrets(err, content) {
if (err) {
console.log('Error loading client secret file: ' + err);
return;
}
// Authorize a client with the loaded credentials, then call the
// Google Sheets API.
authorize(JSON.parse(content), listMajors);
});
}
// TODO: create operation
create();
Step 3.1: READ
var Read = function () {
function listMajors(auth) {
var sheets = google.sheets('v4');
var SPREADSHEET_ID = "************";
var range = "**********";
var request = {
auth: auth,
spreadsheetId: SPREADSHEET_ID,
range: range
};
//To get all rows form spreadsheet
sheets.spreadsheets.values.get(request, function(err, response) {
if (err) {
throw err;
}
console.log(response,' records.');
});
}
// Load client secrets from a local file.
fs.readFile(SPREADSHEET_JSON_FILE, function processClientSecrets(err, content) {
if (err) {
console.log('Error loading client secret file: ' + err);
return;
}
// Authorize a client with the loaded credentials, then call the
// Google Sheets API.
authorize(JSON.parse(content), listMajors);
});
}
//TODO: read operation
Read()
Step 3.1: DELETE
var delete = function () {
// Print the names and majors of students in a sample spreadsheet:
function listMajors(auth) {
var sheets = google.sheets('v4');
var SPREADSHEET_ID = "************";
var range = "**********";
var sheetId = "****";
var deleteRequest = {
auth: auth,
spreadsheetId: process.env.SPREADSHEET_ID,
resource : {
'requests': [
{
'deleteDimension': {
'range': {
'sheetId': sheetId,
'dimension': 'ROWS',
'startIndex': range,
'endIndex': range+1
}
}
}
]
},
};
sheets.spreadsheets.batchUpdate(deleteRequest, function(err, response) {
if (err) {console.log(err);}
console.log(response,'Deleted');
});
}
// Load client secrets from a local file.
fs.readFile(SPREADSHEET_JSON_FILE, function processClientSecrets(err, content) {
if (err) {
console.log('Error loading client secret file: ' + err);
return;
}
// Authorize a client with the loaded credentials, then call the
// Google Sheets API.
authorize(JSON.parse(content), listMajors);
});
}
//TODO: Delete operation
delete()
4: Run the sample
$ node spreadsheet.js
Note: Access permission
The first time you run the sample, it will prompt you to authorize access:
Browse to the provided URL in your web browser.
If you have not logged into your Google account, then you will be prompted to log in. If you are logged into multiple Google accounts, then you will be asked to select one account to use for the authorization.
Now, click the Accept button.
Copy the given code, paste it into the command-line prompt, and press Enter.
That’s all guys! We done it. Here we used NodeJS and we can also use “Google App Script” to apply this CRUD operation. If you’re interested to know then let us know your opinions in comment box, will surely discuss about it in coming days.
Mad over reading? Then never miss out anything from our blog works, there you can read about all latest technologies & it’s updates which would be perfect for your 15 minutes tea break! What else guys hit the subscribe link and go crazy over learning.
