Sheetster Ruby Developer Guide

Owned by Robert Avery
Last updated: Sept 22, 2012Version comment

Introduction

Ruby developers need a simple way of creating and developing Docs-based apps, and DocsFree provides the Sheetster REST api to allow for seamless access.

Many Ruby developers are comfortable with the Prototype.js Javascript library, which works seamlessly with the DocsFree Sheetster web application which is a prototype.js based AJAX client to the DocsFree Sheetster REST api.

This article will give you an understanding of how to use Ruby in conjunction with the developer tools available in DocsFree Sheetster.

Example Use Case: Invoicing Application

In our simple use case we are automating the creation of sales Invoices.

Since we are going to be using ruby to make HTTP requests, data manipulation is done via HTTP GETs.

This simple capability to access DocsFree Sheetster from any language and system capable of HTTP GET requests is one of many reasons why a REST API is so powerful. DocsFree server's REST API can do all the heavy lifting and deliver results in many forms.

The goal of this example will be to insert rows of data into a spreadsheet template we have already, then return the result as a XLS byte output.

Using Sheetster and Ruby this is pretty straight forward. Here are the steps:

  1. Declare our REST functions
  2. Setup a variable (resp) to call our REST commands
  3. Assign the output of the REST commands to different variables
  4. Set values in the spreadsheet template

Our previously uploaded Workbook with ID of 1234 is an Invoice template with row 22 being the start of where we wish to place our line item data.

To insert a lineitem in this invoice template, we will give the cell C22 the value of quantity of items, D22 our description of the item, and F22 the cost of each item.

require 'net/http' # We will start off by telling ruby it's going to be using http. 
quantity = 5 # declare quantity, description and cost of items to add to invoice template
description = usb thumb drives
cost = 15
insertRow = 'http://127.0.0.1:8080/workbook/id/1234/xml/row/insert/Sheet1/22' # Insert new row at line 22 
addCellC = 'http://127.0.0.1:8080/workbook/id/1234/xml/cell/add/Sheet1!C22/' + quantity # Add the data to our cells
addCellD = 'http://127.0.0.1:8080/workbook/id/1234/xml/cell/add/Sheet1!D22/' + description
addCellF = 'http://127.0.0.1:8080/workbook/id/1234/xml/cell/add/Sheet1!F22/' + cost
getXls = 'http://127.0.0.1:8080/workbook/id/1234/xhtml/sheet/get/Sheet1/' # Get the sheet as XHTML
resp = Net::HTTP.get_response(URI.parse(insertRow)) # make the variable resp the output of the insertRow call
didRowwork = resp.body # body of the response, value should be true
resp = Net::HTTP.get_response(URI.parse(addCellC)) # same as above but for the addCell call 
didCellwork = resp.body # again value should be true
resp = Net::HTTP.get_response(URI.parse(addCellD))
didCellwork = resp.body
resp = Net::HTTP.get_response(URI.parse(addCellF))
didCellwork = resp.body
resp = Net::HTTP.get_response(URI.parse(getXls)) # get the entire sheet
XLS = resp.body # the value of XLS should now be a .xls file.

This is cool because using very simple ruby we are able to do very complex manipulations of spreadsheets using the Sheetster web service.

There was no need to
This can be further automated any way you can think of! We can check for errors and integrate this with an existing ruby application if we so desire. All we need to do is add some checks to parse didCellwork or didRowwork. They will either return true or false.