You may have come across my post on SQL for Apps Script it’s here where I shared a library for using SQL syntax in Apps Script to do things like joining tables. For some cases, this might still be the best approach, but if you are using Fiddler for minimum fuss sheet data manipulation, you might find this update handy. It allows you match rows from sheets and join them in a variety of ways in a few lines of code. I’ll also contrast this with SQL later on.
Joining tables means taking the rows of 2 tables (a left and a right) and creating a combination table of matched rows. These left and right tables might be, for example, a table of invoices, and a table of customers. For all these examples, I’ll be using a table of billionaires, and the companies they work for.
Billionaires on the left
companies on the right
Types of joins
The precise nature of the ‘joining’ is defined by the type of join requested. Tables are joined by matching keys (typically one or more columns). Fiddler supports each of the types of joins below:
inner join (the default)
The result is rows of the data in the left and with columns from right table where the keys can be matched in both tables. This means that rows from the left table will be dropped if there is no corresponding row in the right table
The result is all the rows of tables in the left table with columns added from the right table where rows have matching keys.
The result is all the rows of tables in the right table with columns added from the left table where rows have matching keys.
The result is all the rows of tables of both the right table and left table with columns added where rows have matching keys.
Introduction to Fiddler
There are many articles on Fiddler on this site (see end of article for some of them). The initial introductory article is quite old now, but you can find it here Fiddler: a functional approach to fiddling with sheet data
If you already use Fiddler, you’ll know all this already.
A selection of ways to populate a fiddler
A fiddler can be initialized in a number of ways from the bmFiddler library.
With a spreadsheet shaped array of values
Directly from a sheet
Using the preFiddler
Pre fiddler is a wrapper library for fiddler that eliminates some of the coding needed to get a sheet and optionally create it if it doesnt exist. I use it all the time rather than using the fiddler library directly
Accessing Fiddler from preFiddler
getFiddler() will return a fiddler object, but just in case you need to access the plain Fiddler constructor you can get to it via prefiddler without bothering to include the bmFiddler library.
I’ll be using bmPreFiddler (described earlier) for all sheet accesses. If you prefer you can use the native bmFiddler library.
Irrespective of the type of join, here’s how to prepare the data from the left and right table.
The join process needs to know the data to join, how to create a key from the column(s) that contribute to the matching process, how to compare these keys, whether to rename columns in the resultant table, and the type of join required. Typically some of this could be different for both left and right tables.
The left table is our billionaires table. We’ll be matching on the Companies column for that. It’s not exactly straightforward as the Companies column could contain multiple company names – so for example matching Elon Musk (SpaceX,Tesla) would generate 2 entries – 1 for each, but we can easily take care of this complication in the compareKeys function later.
This has an extra function to rename any columns coming from the right table.
Here’s the function to compare keys. We can’t use the default one, as the left table could have a list of companies to compare against.
We’ll be writing an example of each of these joins to the same spreadsheet, so we can generalize a template for bmPreFiddler to use.
With the preparation done, the actual joins are one liners. Here’s an inner join, which will write the new joined table to a sheet
Here’s left join, which will write the new joined table to a sheet
Here’s right join, which will write the new joined table to a sheet
Here’s full join, which will write the new joined table to a sheet
Here’s the entire script for creating all these joins
Contrast with SQL
As previously mentioned, I do have a library that allows you to manipulate data from Apps Script using SQL syntax (SQL for Apps Script it’s here). Underneath the hood it uses alasql which offers much of the SQL vocabulary set.
Here’s an approximation of how to do an inner join from scratch using our example above.
but note the result isn’t quite the same as the Fiddler version
That’s because the matching process in the fiddler approach is code based, whereas the matching in SQL is whatever syntax SQL supports. In our example, Elon Musk is noted as belonging to 2 companies:
SQL would need to perform a complex Split and Select to generate multiple rows from 1, and that isn’t supported by AlaSQL dialect.
Here’s the Fiddler result for an inner join – see how Elon Musk appears twice – one for each company he is listed against:
And another thing
Finally, you’ll probably want to prettify the headings in sheets you’ll create. Fiddler can do that too. Just add .setHeaderFormat() to the fiddler somewhere.
Which will do this
For more info on formatting with fiddler see Formatting sheet column data with fiddler
and if you’re planning to use fiddler, you’ll need either the vanilla fiddler library
or the handy wrapper
All are also on github under github.com/brucemcpherson