Google Drive Spreadsheets from NodeJS

I have been trying to upload a spreadsheet to Google Drive from a cron job in NodeJS and found a lot of inconsistencies in the documentation and the libraries. Also, I didn't find a good example to follow.

What I was trying to do is generate a weekly report and share it with some people. First of all, we need to create a Google Service Account. We will use it to interact with Google Drive without asking the users if they allow us to upload files to their Drive. Basically, we can upload anything to the Service Account without an authorization.


Google Setup

1. Create a Google Service Account at the Developer Console
2. In the API manager, enable the Drive API
3. In the API Manager credentials settings, Add Credentials for OAuth 2.0 client ID. Paste the Client ID and Client secret somewhere, we will use them later.
4. Also in the API Manager credentials settings, Add credentials for Service account. Paste the service account email somewhere to use it later
5. Enter the Service Account and download the P12 key. It must be converted to a PEM certificate to be used with Node. To convert it run the following command:
   

   openssl pkcs12 -in googleapi-privatekey.p12 -out googlekey.pem -nocerts -nodes

   It will ask for a password, it's usually notasecret

The Code

1. Create the worksheet. For this step I will use this library: https://github.com/chuanyi/msexcel-builder. It's pretty simple.

     function createWorkbook() {
       var workbook = excelbuilder.createWorkbook('', '')
   
       var sheet1 = workbook.createSheet('sheet1', 10, 12);
   
       sheet1.set(1, 1, 'I am title');
       for (var i = 2; i < 5; i++) {
         sheet1.set(i, 1, 'test'+i);
       }
       return new Promise(function(resolve, reject) {
         workBook.generate(function(err, jszip) {
           if (err) {
               reject(err);
           } else {
               resolve(jszip.generate({type: 'nodebuffer'}));
           }
         });
       });
     }
   

2. Getting a token to make the requests to Google Drive

     var googleapis = require('googleapis');
     var Promise = require('bluebird');
   
     var SCOPES = ['https://www.googleapis.com/auth/drive'];
   
     function getToken() {
       var pemFile = __dirname + '/googlekey.pem';
       var jwt = new googleapis.auth.JWT(
           "account-1@xxxxxxxx.iam.gserviceaccount.com",
           pemFile,
           null,
           SCOPES
       );
       return new Promise(function(resolve, reject) {
           jwt.authorize(function(err, result) {
               if (err) {
                   reject(err);
               } else {
                   resolve(result);
               }
           });
       });
     } 
   

3. With the token, we can send authorized requests to Google. Now we need the OAuth2.0 authentication:

     function authorize() {
       // The first parameter is clientSecret and the second parameter is clientId
       var oauth2Client = new googleapis.auth.OAuth2('xxxxxxxx', 'xxxxxxxxxxx.apps.googleusercontent.com', '');
       return getToken()
       .then(function(result) {
           oauth2Client.setCredentials({
               access_token: result.access_token
           });
           return oauth2Client;
       });
     }
   

4. At this step, we have everything we need (the Spreadsheet and the authorization). Let's make a function to insert the file in the drive:

     function insertDriveFile(auth, fileName, data, mimeType) {
       var drive = googleapis.drive('v2');
       return new Promise(function(resolve, reject) {
           drive.files.insert({
               auth: auth,
               uploadType: 'multipart',
               convert: true,
               media: {
                   'mimeType': mimeType || 'text/plain',
                   body: data
               },
               resource: {
                   title: fileName
               }
           }, function(err, results) {
               if (err) {
                   reject(err);
               } else {
                   resolve(results);
               }
           });
       });
     }
   

5. With the file created, we need to share it to check the final results:

   function shareFileWith(auth, fileInfo, mailToShare) {
       var drive = googleapis.drive('v2');
       return new Promise(function(resolve, reject) {
           drive.permissions.insert({
               auth: auth,
               fileId: fileInfo.id,
               sendNotificationEmails: true,
               resource: {
                   value: mailToShare,
                   role: 'writer',
                   type: 'user'
               }
           }, function(err, result) {
               if (err) {
                   reject(err);
               } else {
                   resolve(result);
               }
           });
       });
   }
   

6. Finally, let's put all the functions together:

   authorize()
   .then(function(driveAuth) {
      createWorkbook()
      .then(function(workBookBuffer) {
         return insertDriveFile(driveAuth, 'File From node', workBookBuffer, 'application/vnd.ms-excel');
      })
      .then(function(fileInfo) {
         return shareFileWith(driveAuth, fileInfo, 'xxxxx@gmail.com');
      })
   })
   

And that's it. Check out your Google Drive and you should have a file named File From Node in the Shared with Me folder.

A bit deeper into Promises and Mongoose

On the internet you can find thousands of tutorials about promises and how to use them.
I recommend this webpage http://www.promisejs.org/ if you are starting with promises. I don't pretend to cover promises basics in this post.

Promises are a very simple but I had some problems at the beginning with a real life example. I only found very simple promises examples.

For this example we are going to use the Q library (https://github.com/kriskowal/q). In that page there are many examples on how to use the library.

We're also using Mongoose to save a client to the database.

For this example, the client id must be unique.

This is the client schema (client-model.js):

module.exports = function(app, mongoose){
 
 var Schema = mongoose.Schema;  

 var client = new Schema({
     id: String,
     name: String,
     lastName: String,
     gender: String
 });
 return mongoose.model('Client', client);
};

And this is the function that saves the client. We will validate an empty or repeated ID, empty name or empty lastName (this can be validated by Mongoose with required and unique, but for this example we'll do it manually)

var Q = require('q'),
 mongoose = require('mongoose');

module.exports = function(app) {

 var clientModel =  require('../models/client-model.js')(app, mongoose);

 var isNotEmptyOrNull = function(pValue) {
  if (!pValue) {
   return false;
  } else if (pValue.length < 1) {
   return false;
  }
  return true;
 };

 app.post('/api/client', function(pRequest, pResponse) {

  // Validate empty id, name and lastName
  Q.all([
   isNotEmptyOrNull(pRequest.body.id),
   isNotEmptyOrNull(pRequest.body.name),
   isNotEmptyOrNull(pRequest.body.lastName)
  ])
  .then(function(pResults) {
   // pResults is an array with the result of each function. In this case, we expect it to be [true, true, true]
    
   var findClient = Q.nbind(clientModel.find, clientModel);

   // Check if id, name and lastName is not null or empty
   var isValidObject = pResults.every(function(pResult) {
    return pResult;
   });

   if (!isValidObject) {
    // It's not a valid object, we send a bad request to the user
    throw 'Id, name and lastName are required';
   }

   return findClient({ id: pRequest.body.id});
  })
  .then(function(pFoundClient) {

   // The id already exists
   if (pFoundClient && pFoundClient.length > 0) {
    throw 'Id must be unique';
   }

   // Everything is ok, we save the client
   var clientToSave = new clientModel(pRequest.body);
   return Q.when(clientToSave.save());
  })
  .then(function() {

   return pResponse.send('Client saved');

  })
  .catch(function(pError) {
   // Catch any error
   console.log(pError);
   return pResponse.send(400, pError);
  });
 });

};

This is the explanation on Q.all from the Q API reference:

Returns a promise that is fulfilled with an array containing the fulfillment value of each promise, or is rejected with the same rejection reason as the first promise to be rejected. This method is often used in its static form on arrays of promises, in order to execute a number of operations concurrently and be notified when they all succeed

And this is nbind:

Creates a promise-returning function from a Node.js-style method, optionally binding it with the given variadic arguments.

With that example it should be clear how to use Q Promises and Mongoose to save the object. Please leave questions, comments or feedback. Thanks

NodeJS and ExpressJS - Load Controllers Dinamically

When you create an Express application you have some options for your routes file:

1. Put all your routes in a single file (Bad idea!)
2. Use as many requires as routes files (Not as bad as 1, but still no good)
3. Use an existing module like express-load for example (https://github.com/jarradseers/express-load)
4. You think 3 is too much overhead and you want to use your own code to 'require' the routes files

In this post, I'll focus in option #4.

If you prefer you can open this gist (https://gist.github.com/davibq/9633951).

This gist contains 3 files:

• The app.js file generated when you create an express project.
• An example of a route file with 2 GET endpoints
• Our custom routes initializer.

If you're a little lazy and don't want to open the gist, here is the routes-initializer file:

/* Requires */
var fs = require('fs');
 
/* Constants */
/**
 * Folder where the routes are placed
 * @type {String}
 */
var ROUTES_FOLDER = './routes';
 
/**
 * This is used to identify the Routes in the folder. 
 * If the file name doesn't contain this variable value, it's not loaded.
 * This way we avoid to load Readme.md as a controller, for example
 * @type {String}
 */
var INIT_ROUTES_IDENTIFIER = 'Route.js';
 
/* Main function */
module.exports = function(app) {
 console.log('Loading Routes...');
 
 var filesInRoutesDirectory = fs.readdirSync(ROUTES_FOLDER);
 
 filesInRoutesDirectory.forEach(function(pRouteFile) {
  if (pRouteFile.indexOf(INIT_ROUTES_IDENTIFIER)>=0) {
   require(ROUTES_FOLDER+'/'+pRouteFile)(app);
  }
 });
 
 console.log('Routes Loaded...');
};

In order to get this code working, you should change the ROUTES_FOLDER variable and the INIT_ROUTES_IDENTIFIER.
This piece of code is really simple, it loads the files in the directory 'ROUTES_FOLDER' and then makes a require of each file to initialize each file.


In case you have any feedback, please leave a comment.