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.