Stay always connected!
  1. Andy Helck
  2. Sierra/ Millennium/ Encore
  3. Tuesday, 05 March 2019
  4.  Subscribe via email
Free Software Share!

There is a pretty basic workflow that most of use are familiar with in Sierra: First, work in Create Lists refining your search criteria until you have a data set you like. Second, export that data to .CSV file on your Desktop. Finally, use Excel to import the data file and start to work with it in a full featured spreadsheet application.

The workflow can be cumbersome what with the export/import field specifiers, etc. A typical cycle time might be 5 or so minutes and requires attention to detail. It would be nice to automate the entire process. Google Sheets and its associated scripting language offer the ability to do that. At our library I've create a number of custom spreadsheets where each one runs a specific query and populates itself with the data from that query.

The breakthrough allowing this is the fairly recent addition of the JSON option in Create Lists and the work that III has done providing users with access to API technology. The beauty of JSON is that you can write your search criteria in the familiar Create Lists way, and then copy out the gist of that search in 'JSON format.' What the API's do is allow a software program (in this case a script associated with your Google Sheets) to retrieve data from the Sierra databases without running the Sierra Desktop app. One of the many different queries available are ones that use a JSON formatted search (ala Create Lists) and return specified fields.

The only slight hiccup to getting started right away is that you will need to contact your System Administrator to obtain the necessary credentials to use the Sierra RESTful APIs. This has been covered in various presentations at IUG conferences in recent years.

Find below a link to a shared Google Sheets. I have had to remove my login credentials and leave them blank (or my System Admin would be unhappy) but they are clearly marked where they go. If you want to experiment make a copy of the spreadsheet in your own drive, obtain the needed credentials ('secret' and 'key') and plug them in using the Script editor from Tools menu. You will see that when the spreadsheet opens there is a new Menu item with a function to execute.

A couple more notes. When you get your credentials and try to run the script, Google will want you to give permission to run the script. This is standard behavior and is well documented in Google's online documentation. In addition to changing the login credentials, you will also have to change the specific URLs used in the script to access your own library's database -- and not mine.

Finally, the query is slow to execute, taking upwards of a full minute or two. On the other hand you can tie the script to a time based trigger and have it execute automatically as often in one day as you need.

Here is the link to the shared spreadsheet

Also I've attached the code in a text file to study. In theory you could probably do all of this in Excel's VBA script but I haven't tried it. You would be translating from JavaScript to Basic and I'm sure it could be done.

Attachments (1)
  1. Andy Helck
  2. 1 year ago
  3. #1237
Here is the link I should have included in the original posting

This allows anyone with the link to access (and copy, please) the spreadsheet

There are no comments made yet.
Accepted Answer Pending Moderation
This is great. It really helped me with the authorization flow. Thanks!

There are no comments made yet.
  1. more than a month ago
  2. Sierra/ Millennium/ Encore
  3. # 1
  • Page :
  • 1

There are no replies made for this post yet.
Be one of the first to reply to this post!