API Builder

API Builder Spreadsheet Import

Currently API Builder does not provide a means of importing data into a model from a spreadsheet. For example, if you have an ArrowDB model and want to populate it with data from a spreadsheet, you need to manually populate the data via POST API calls.

In this blog post, we’ll discuss one method of implementing a spreadsheet import feature using a custom API.

The way we will accomplish this is use a spreadsheet hosting service that exposes a spreadsheet as an API and then our custom API will read data from the spreadsheet API and post it to the model.

My ArrowDB model, employee, is shown below:


var Arrow = require('arrow');
var Model = Arrow.createModel('employee', {
fields: {
fname: {
type: String
},
lname: {
type: String
},
title: {
type: String
},
age: {
type: Number
}
},
connector: 'appc.arrowdb',
actions: [
'create',
'read',
'update',
'delete',
'deleteAll'
]
});
module.exports = Model;

It is worth noting that the technique described in this blog post applies to any model and not just ArrowDB models.

Sheetlabs

There are several services that enable you to host and expose a spreadsheet as APIs. Sheetlabs is one such solution and is the one I chose to use for this example.

  • Create your free account at Sheetlabs (https://www.sheetlabs.com)
  • Make sure your spreadsheet has column names that are identical to your model field names
  • Upload your spreadsheet to Sheetlabs and create an API
  • Optionally, set Sheetlab API permission to Authenticated to allow any authenticated user in your Sheetlab org to access the API

When you are done, you will see your API and path similar to the screenshot below:

687474703a2f2f692e696d6775722e636f6d2f465955454a67392e706e67


Note the path to your sheet in the Base URL in the screenshot above (e.g. /LBRE/employee). You will use this later when you call the custom API

Make sure that you can call your API from Curl or Postman as follows:


curl -X GET \
  https://sheetlabs.com/LBRE/employee \
  -H 'authorization: Basic '

API Builder Custom API

The custom API, importsheet, is shown below:


var Arrow = require('arrow');
var b64encodedauth = '';
var importsheet = Arrow.API.extend({
    group: 'custom',
    path: '/api/importsheet',
    method: 'GET',
    description: 'this is an api that imports data from a spreadsheet in sheetlabs',
    parameters: {
        'modelName': {
            description: 'Model Name'
        },
        'sheetPath': {
          description: 'Path to Sheetlabs spreadsheet (e.g. /LBRE/employee)'
        },
        'timeDelay': {
          description: 'Delay in milliseconds between writes to the model (e.g. 500 for ArrowDB Dev, 100 for ArrowDB Prod, ...)'
        }
    },
    action: function(req, resp, next) {
        var http = require("https");
        var options = {
          "method": "GET",
          "hostname": "sheetlabs.com",
          "port": null,
          "path": req.query.sheetPath,
          "headers": {
            "cache-control": "no-cache",
            "authorization": "Basic "+b64encodedauth
          }
        };

        var request = http.request(options, function (response) {
          var chunks = [];

          response.on("data", function (chunk) {
            chunks.push(chunk);
          });

          response.on("end", function () {
            var data = JSON.parse(Buffer.concat(chunks).toString());
            var model = Arrow.getModel(req.query.modelName);
            var dataLen = data.length;
            if(dataLen>0){
              var counter = 0;
              var writeErrors = 0;
              var myInterval = setInterval(function() {
                counter++;
                if(counter >= dataLen){
                    clearInterval(myInterval);
                    if(writeErrors>0) {
                      resp.response.status(500);
                      resp.send({success: false, msg: "Not all spreadsheet values written, perhaps rate limit has been exceeded"});
                      next();
                    } else {
                      resp.response.status(200);
                      resp.send({success: true});
                      next();
                    }
                }
                model.create(data[counter-1], function(err, instance){
                  if(err) {
                    writeErrors++;
                  } else {
                    instance.set(data[counter-1]);
                  }
                });
              }, req.query.timeDelay);
            }
          });
        });

        request.on("error", function (e) {
          resp.response.status(500);
          resp.send({success: false, msg: "Error accessing spreadsheet URL, error = "+JSON.stringify(e)});
          next();
        });

        request.setTimeout(5000, function(){
          resp.response.status(504);
          resp.send({success: false, msg: "HTTP Timeout accessing spreadsheet URL"});
          next();
        });
        request.end();
    }
});
module.exports = importsheet;


In the code above, I am using a Javascript timer, myInterval, to slow down the POSTS to ArrowDB so as not to exceed the rate limiting that exists in ArrowDB. The delay is passed in as a parameter to the Custom API call as parameter, timeDelay.

  • Place importsheet.js in the /api folder of your API Builder project
  • The code will work either way, but if you’re concerned about your data, then enable Authentication in Sheetlab and set the b64encodedauth variable to your Base64 encoded username:password from Sheetlab.
  • Run and/or publish your project
  • Call the API with your model name and sheet API path (e.g. https://localhost:8080/api/importsheet?modelName=employee&sheetPath=/LBRE/employee&timeDelay=250)
  • Your spreadsheet data should be imported into your model

You can get importsheet.js here