Archives

All posts by Eric Saupe

Screenshot from 2014-03-27 14:23:55

Recently we started dumping some data from the database into a shared spreadsheet on Google Docs.  With all of this data it would be helpful to visualize it in some way.  After searching the net it seems there isn’t really a good way to create a chart that is dynamic with what data is in the spreadsheet.  What I want is to have my graph update every time a new row is added to the sheet without having to edit the graph directly.  The solution was to write a custom Google Apps Script to do just that.

To add a Google Apps Script to a document open the document and click Tools -> Script editor…  This opens up a page for editing scripts.  If a prompt appears for what type of script you want to do just select Blank.  Now you just need to paste in this code below.

To make this work for you just change the tabName variable to whatever your tab is that you want to use the graph in, change the start row and start column of where your data begins and what column to end reading.  You don’t need to specify an endRow because we will just be reading all of the data until there is no more.  After that you can customize where the graph will be located, how wide it is, and the titles of the axis.

You’ll see that this method will get called anytime the document is edited or opened by someone who can edit.  Viewers who cannot edit will not trigger the event even on open.  That’s all there is to it.  dynamic Google spreadsheet graphs are now just a click away and once you have it set up you’ll never have to touch it again.

If you have any other Google Apps Scripts tips leave them below in the comments.

send-file-iconupload-files-icon-ts7tteez

I have never found a good list of the default styles that ReportLab gives you when you run getSampleStyleSheet() so I wrote a method to spit them out and what they look like.  Below is that list along with the PDF output of each of the styles.  They are grouped into two categories, byAlias and byName.  Normally I just use the name but you can use either.

  • byAlias
    • ‘title’: <ParagraphStyle ‘Title’>
    • ‘h1’: <ParagraphStyle ‘Heading1’>
    • ‘h2’: <ParagraphStyle ‘Heading2’>
    • ‘h3’: <ParagraphStyle ‘Heading3’>
    • ‘h4’: <ParagraphStyle ‘Heading4’>
    • ‘h5’: <ParagraphStyle ‘Heading5’>
    • ‘h6’: <ParagraphStyle ‘Heading6’>
    • ‘bu’: <ParagraphStyle ‘Bullet’>
    • ‘df’: <ParagraphStyle ‘Definition’>
  • byName
    • ‘Title’: <ParagraphStyle ‘Title’>
    • ‘Heading1’: <ParagraphStyle ‘Heading1’>
    • ‘Heading2’: <ParagraphStyle ‘Heading2’>
    • ‘Heading3’: <ParagraphStyle ‘Heading3’>
    • ‘Heading4’: <ParagraphStyle ‘Heading4’>
    • ‘Heading5’: <ParagraphStyle ‘Heading5’>
    • ‘Heading6’: <ParagraphStyle ‘Heading6’>
    • ‘Bullet’: <ParagraphStyle ‘Bullet’>
    • ‘Definition’: <ParagraphStyle ‘Definition’>
    • ‘Normal’: <ParagraphStyle ‘Normal’>
    • ‘Italic’: <ParagraphStyle ‘Italic’>
    • ‘BodyText’: <ParagraphStyle ‘BodyText’>
    • ‘Code’: <ParagraphStyle ‘Code’>

Screenshot from 2014-03-26 12:07:45

square

AngularJS has a ton of built in directives and functions to make your app come alive very quickly.  One thing I noticed it was lacking was a built in on-enter event.  For example, if a user is in an input field when they hit enter I want it to do something, maybe not necessarily submit the form.  Luckily, AngularJS gives you the ability to create your own directives and add them into your app.  Below is a simple directive that you can add to your app that allows you to add ng-enter to elements in your partials.  Pass it a function and watch as you hit enter and that function is executed flawlessly.

That’s it.  Now just add ng-enter="myFunction()" to any element in your partial that detects keystrokes. This has helped me a ton and added a lot of easy functionality to an already great AngularJS system.  If you have any other great directives or AngularJS tips please leave them below in the comments.

afire-logo

Firebase is a great way to connect applications to a database that will sync data in realtime.  Tying this in with AngularJS allows you to create a single page application that updates dynamically and as data changes without page refreshes.  This allows you to create applications that can give your users access to data the moment it is updated and changed.  They have a great binding called AngularFire that links the two services together nicely.

One thing that I had problems with was filtering data returned by Firebase with the great AngularJS filter method.  Because AngularFire returns a dictionary with methods and elements the AngularJS filter has problems filtering the data correctly.  The AngularJS template rendering can render the data just fine but the filter does not work.  To fix that you’ll need to employ the AngularFire method orderByPriority before your AngularJS filter.  Here is an example.

In my Controller I access the list of stores using Firebase and $firebase.  At this point my $scope has the list of stores with some methods used by AngularFire to update data.  The partial above will render the list just fine but without orderByPriority the search filter would not work.  The orderByPriority converts an object returned by $firebase into an array.  This array will work with any normal AnguarJS filter.

Here is the definition from the API of AngularFire.

The orderByPriority filter is provided by AngularFire to convert an object returned by $firebase into an array. The objects in the array are ordered by priority (as defined in Firebase). Additionally, each object in the array will have a$id property defined on it, which will correspond to the key name for that object.

That’s all there is to it.  I hope this helps someone else figure out this problem as I spent a long time trying to get my filter to work with Firebase objects.

typeahead

Twitter Typeahead.js has been updated and there are a lot of changes.  I’m going to give a an explanation for a few of their examples and explain the key features and changes that they have made.  For those looking to upgrade to this new version it will require a rewrite of your current Typeahead.js methods so please be aware of that.  The biggest changes are outlined in their changelog.

The most important change in 0.10.0 is that typeahead.js was broken up into 2 individual components: Bloodhound and jQuery#typeahead. Bloodhound is an feature-rich suggestion engine. jQuery#typeahead is a jQuery plugin that turns input controls into typeaheads.

Let’s head to the examples.

The most basic of basic.  Here we have a static list of local data.  Bloodhound needs to be initialized with your data and you can see it handles splitting data up by white space with their tokenizers.  This makes things a lot easier because in previous versions you needed to make sure the tokens to be searched through were already split up by white space.  The Bloodhound object is then initialized and the typeahead object is created.  This is a lot more code but it makes the search more robust as we will see with a more advanced example using prefetch and remote data.

Here we see a very similar example to the one above except that we are getting our data from a URL.  The prefetch variable works to get a set of data and cache it locally.  Even if the page is reloaded that data stays with the user’s browser until it expires or is removed.  As the user begins their search it will have that data ready and on hand but if there is no data to be found in the prefetch Typeahead.js has another URL, stored in the remote variable, that it can make a query to and get data back about what the user is searching for.  These two in conjunction work fantastically well for supplying the user with a set of data that has the speed of being static but can be dynamic by querying the URL for more data as the user goes.  Again we initialize the Bloodhound object and add it to the typeahead initialization.  We are again using the Handlebars template rendering engine to make the search results look great so that hasn’t changed.

The changes are drastic but not hard.  This is a major update to Twitter Typeahead.js and because of that there is deprecation.  My other posts are still useful for older versions but may not apply to versions going forward.  I hope this was helpful in getting you up to speed with the latest version of Twitter Typeahead.js.  If you have any questions or comments feel free to leave them below.

bootstraplogo

If you’ve spent any time looking at the Bootstrap docs you’ll notice their nav bar on the right.  Very slick example of both Scrollspy and Affix.  I have written blog posts about both of these topics in conjunction with each other titled Bootstrap ScrollSpy Pitfalls and Fixes.  In my example I use a very simple styling for my nav using nav-pills.  This works out alright but what if we want to get exactly what Bootstrap is doing?  Well, let’s figure out just how they do it.

They are using very similar code to what I had in the pitfalls and tricks post so I’ll bring that in here.

Now that we have a simple navigation let’s style it to have sub menus not be shown until the item is in view of the user, just like Bootstrap.

We’ve added some sub menu items to a few of the nav elements.  At this point if you were to render the page it would show all of the items in the list regardless of it being a sub menu or not.  We want to change that so we only see specific sub menus when they are active.  To do this we just need to add a little bit of CSS that will trigger the display of the sub menus.  When the menu item is not active the sub menus are hidden.  When the menu item is activated it displays the sub menus below.

That’s all there is to it! No tricky JavaScript required, it’s all CSS.   Now just adjust the padding, change the color, and add sub menus to your heart’s content.  You now have a side nav that looks and acts just like Bootstrap’s does in their docs.

A Visual Guide to SQL Joins

Your SQL database can be huge.  So many tables, so much data, combining it can be hard.  Even once you’ve sorted out what data you want it can be a real task to visualize what joins you need so that you only grab the data you want.  The more efficient the join the better.  I found this nifty image on SQL joins that I think does a great job at showing how the various joins work and does a great job explaining how to use them.

SQL Joins

I hope this helps someone else as I know I am always having a hard time figuring out which joins to use and when.

replogo

ReportLab is full of different objects that you can place anywhere around the screen.  Following our previous code, we are making a list of elements that we want to draw onto a document.  ReportLab will handle all of the page breaks and lining things up but we can manipulate that a bit to help ourselves get a really clean looking PDF.  You can review the last two guides in the series by clicking these links, ReportLab and Django – Part 1 – The Set Up and a Basic Example, ReportLab and Django – Part 2 – Headers and Footers with Page Numbers.

The two most common elements to get your document to look nice are Paragraphs and Tables.  Paragraphs are, just as they sound, a paragraph of text that can contain some formatting to make it look nice.  It can even include images.  You can set a ParagraphStyle for the entire paragraph or put XML tags inside the Paragraph to get various styles throughout one object.

Paragraphs

A Paragraph is an element that spans the entire width of the area that it is given.  It holds text or images and will wrap when it reaches the end of the drawable area.  You can set a style for a Paragraph by creating a ParagraphStyle and applying it to your Paragraph.  ReportLab comes with a number of built in styles but I found that I preferred creating my own styles to give me more control.

In the example you can see that we first register some fonts.  Fonts are a very important feature of ReportLab because it allows you to use any TrueType Fonts you can find.  This is important because if you are going to be using any foreign language characters you’ll need a font that supports those characters.  If you don’t ReportLab will render them as black squares.

We then got the large amount of style sheets already created for us by ReportLab and then we add to it.  You can see that we add a new ParagraphStyle called ‘RightAlign’ that will use the Arial font and right align any text inside of it.  Simple enough.  To use it we do just like we did in the example in Part 2 but instead of using a prebuilt stylesheet like Heading1 we just call our own RightAlign.  Below is the example with the changed line highlighted.

There are a lot you can do within a Paragraph as well using inline tags.  If you wanted to make changes to a certain section of text and wanted to change it to be a different color, font, or size you can by using the <font> tags.  Adding an image is equally as simple by adding the <img> tag and specifying a file location.  Below is an example of a Paragraph with the same style we created above but with altered font and image added inline.

This creates a right aligned paragraph that has a bold red My followed by a normal Arial User Names and then a image of a smiley face with a height of 5.

Tables

Tables are the biggest help ever for getting things all lined up.  A Table is exactly like every other instance of a table you know.  It has rows and columns and you can specify their size and what is inside of them.  Tables by default will grow to contain whatever object is inside of them.  You can explicitly set the widths and heights of the columns and rows but your text/images can still overflow onto the outside of them.  Getting the widths and heights right is key.  Let’s make a basic table with some user data and set some table styles to make it look great.

This will create a table with all of our users on it, their usernames, and when they last logged in.  The table will be lined, that’s what the INNERGRID and BOX style does.  You can see that the table will span the entire page and that each column will take up a third of the document’s width.  You can customize these for varying column widths.  There are a lot of various customization options for tables.  Read them on the ReportLab docs.

I hope this was helpful.  I spent a lot of time figuring these things out as the documentation and examples are sparse.  If there are any problems you have that you want to know about let me know in the comments below.

Git-Logo-1788C

We recently moved our git repositories over to Amazon Web Services.  We ran into one issue with it which was that now our git requests needed to have a key pair attached.  The Internet was not very kind on explaining how to do this very well so I’m documenting it here.

The key pair you’ll want to use is the one generated for you by AWS.  It is the same one you use when you ssh into your AWS EC2 instances.  Take this key pair file, which I will call keypair.pem from now on, and we’ll need to move it and configure our git to use it.

  1. Copy the keypair.pem into your .ssh folder.
  2. Create/open your .ssh config file.
  3. In your config file
  4.  To clone the repository use the following
  5. To update existing projects on your machine to use the new address follow the instructions below
  6. Open your project’s git config file
  7. Change the url line to be

That’s it. Hope this helps anyone who is getting the error ‘Error: Permission denied (publickey)’.  There are other ways to do this but this way was pretty easy.