Fiddler now supports joins to merge matching columns from multiple sheets
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
Billionaires on the left
Companies
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
left join
The result is all the rows of tables in the left table with columns added from the right table where rows have matching keys.
right join
The result is all the rows of tables in the right table with columns added from the left table where rows have matching keys.
full join
The result is all the rows of tables of both the right table and left table with columns added where rows have matching keys.
If you already use Fiddler, you’ll know all this already.
In summary, fiddler converts sheet values to an array of JavaScript objects and allows you to do various operations such as filtering and mapping and can then dump the results to the same or another sheet. Usually all this can be done with minimal coding.
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
// option1 - an new fiddler with spreadsheet shaped values const fiddler1 = new bmFiddler.Fiddler().setValues([['Company', 'Sells'], ['Apple', 'Apple stuff'], ['Zara', 'Clothes']])
Initialized with values
With an array of JavaScript objects
// option2 - a new fiddler with an array of objects const fiddler2 = new bmFiddler.Fiddler().setData([{ Company: 'Apple', Sells: 'Apple stuff' }, { Company: 'Zara', Sells: 'Clothes' }])
array of objects
Directly from a sheet
// option 3 a new fiddler from a sheet const fiddler3 = new bmFiddler.Fiddler(SpreadsheetApp.getActiveSpreadsheet().getSheetByName('companies'))
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
// option 4 use the prefiddler wrapper - no need to include bmFiddler const fiddler4 = bmPreFiddler.PreFiddler({ sheetName: 'companies', // optional properties id: null, // null means use the current activespreadsheet createIfMissing: false // true means create the sheetName if it doesnt already exist })
bPreFiddler
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.
// if you are using the prefiddler, you can still create an empty fiddler without bothering to include bmFiddler const fiddler5 = new bmPreFiddler.PreFiddler().Fiddler()
Fiddler constructor is exported from Prefiddler
Example
I’ll be using bmPreFiddler (described earlier) for all sheet accesses. If you prefer you can use the native bmFiddler library.
Initialization
Irrespective of the type of join, here’s how to prepare the data from the left and right table.
// get a prefiddler instance const preFiddler = bmPreFiddler.PreFiddler()
// we'll need the join function from fiddler const { join } = new preFiddler.Fiddler()
// the right hand table is the master list of companies const rightFiddler = preFiddler.getFiddler({ sheetName: 'companies' })
// the left hand table is the list of transactions - in this case people const leftFiddler = preFiddler.getFiddler({ sheetName: 'billionaires' })
Initialization
Join Definition
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.
Left definition
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.
// create template arguments for join // although we're using a fiddler, the data can be any array of plain objects const left = { data: leftFiddler.getData(), // define how to create keys from each row of each of the tables // in this case we'll be comparing the company column, and not caring about case makeKey: (row) => row.Companies.toLowerCase().trim() }
left join defintion
Right definition
This has an extra function to rename any columns coming from the right table.
const right = { data: rightFiddler.getData(), // define how to create keys from each row of each of the tables // in this case we'll be comparing the company column, and not caring about case makeKey: (row) => row.Company.toLowerCase().trim(), // you can also define how to make the new column name to preserver where they came from // default is to keep the same name as input and drop duplicate names makeColumnName: (columnName) => rightFiddler.getSheet().getName() '_' columnName }
right definition
compareKeys
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.
// you can also define how to compare keys // in this case the leftkey can contain multiple companies separated by commas, and we also need to get rid of trailing and leading spaces // the default is leftKey === rightKey const compareKeys = (leftKey, rightKey) => leftKey.split(",") .map(f => f.replace(/^\s /, '').replace(/\s $/, '')) .filter(f => f !== '').indexOf(rightKey) !== -1
compareKeys
Output template
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.
// write to this spreadsheet const template = { id: '1cTRN6mGvH_vnWO2ehEFJkWi7Dpw9HcPNvg8zjrWKh4g', createIfMissing: true }
output template
Inner join
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
// do an inner join and dump to sheets preFiddler.getFiddler({ ...template, sheetName: 'innerJoin' }).setData(join({ left, right, compareKeys, joinType: 'inner' })).dumpValues()
inner join
Left join
Here’s left join, which will write the new joined table to a sheet
// do a left join and dump bmPreFiddler.PreFiddler().getFiddler({ ...template, sheetName: 'leftJoin' }).setData(join({ left, right, compareKeys, joinType: 'left' })).dumpValues()
left join
Right join
Here’s right join, which will write the new joined table to a sheet
// do a right join and dump bmPreFiddler.PreFiddler().getFiddler({ ...template, sheetName: 'rightJoin' }).setData(join({ left, right, compareKeys, joinType: 'right' })).dumpValues()
right join
Full join
Here’s full join, which will write the new joined table to a sheet
// do a full join and dump bmPreFiddler.PreFiddler().getFiddler({ ...template, sheetName: 'fullJoin' }).setData(join({ left, right, compareKeys, joinType: 'full' })).dumpValues()
full join
All together
Here’s the entire script for creating all these joins
const tJoin = () => {
// get a prefiddler instance const preFiddler = bmPreFiddler.PreFiddler()
// we'll need the join function from fiddler const { join } = new preFiddler.Fiddler()
// the right hand table is the master list of companies const rightFiddler = preFiddler.getFiddler({ sheetName: 'companies' })
// the left hand table is the list of transactions - in this case people const leftFiddler = preFiddler.getFiddler({ sheetName: 'billionaires' })
// create template arguments for join // although we're using a fiddler, the data can be any array of plain objects const left = { data: leftFiddler.getData(), // define how to create keys from each row of each of the tables // in this case we'll be comparing the company column, and not caring about case makeKey: (row) => row.Companies.toLowerCase().trim() } const right = { data: rightFiddler.getData(), // define how to create keys from each row of each of the tables // in this case we'll be comparing the company column, and not caring about case makeKey: (row) => row.Company.toLowerCase().trim(), // you can also define how to make the new column name to preserver where they came from // default is to keep the same name as input and drop duplicate names makeColumnName: (columnName) => rightFiddler.getSheet().getName() '_' columnName }
// you can also define how to compare keys // in this case the leftkey can contain multiple companies separated by commas, and we also need to get rid of trailing and leading spaces // the default is leftKey === rightKey const compareKeys = (leftKey, rightKey) => leftKey.split(",") .map(f => f.replace(/^\s /, '').replace(/\s $/, '')) .filter(f => f !== '').indexOf(rightKey) !== -1
// make fiddlers out of each type of join and dump
// write to this spreadsheet const template = { id: '1cTRN6mGvH_vnWO2ehEFJkWi7Dpw9HcPNvg8zjrWKh4g', createIfMissing: true }
// do an inner join and dump to sheets preFiddler.getFiddler({ ...template, sheetName: 'innerJoin' }).setData(join({ left, right, compareKeys, joinType: 'inner' })).dumpValues()
// do a left join and dump bmPreFiddler.PreFiddler().getFiddler({ ...template, sheetName: 'leftJoin' }).setData(join({ left, right, compareKeys, joinType: 'left' })).dumpValues()
// do a right join and dump bmPreFiddler.PreFiddler().getFiddler({ ...template, sheetName: 'rightJoin' }).setData(join({ left, right, compareKeys, joinType: 'right' })).dumpValues()
// do a full join and dump bmPreFiddler.PreFiddler().getFiddler({ ...template, sheetName: 'fullJoin' }).setData(join({ left, right, compareKeys, joinType: 'full' })).dumpValues()
}
the whole thing
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.
// get a prefiddler instance const preFiddler = bmPreFiddler.PreFiddler()
// the right hand table is the master list of companies const rightFiddler = preFiddler.getFiddler({ sheetName: 'companies' })
// the left hand table is the list of transactions - in this case people const leftFiddler = preFiddler.getFiddler({ sheetName: 'billionaires' })
// create an sql table from the right and left sheets tables createAndInsertFromData({ name: "billionaires", data: leftFiddler.getData() }) createAndInsertFromData({ name: "companies", data: rightFiddler.getData() })
// write to this spreadsheet const template = { id: '1cTRN6mGvH_vnWO2ehEFJkWi7Dpw9HcPNvg8zjrWKh4g', createIfMissing: true }
// do inner join and write to a new sheet preFiddler.getFiddler({ ...template, sheetName: 'innerJoinSql' }).setData(exec({ sql: "select * from billionaires join companies on companies.company = billionaires.companies" })).dumpValues()
}
sql version
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:
Tesla, SpaceX
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.
// do an inner join and dump to sheets preFiddler.getFiddler({ ...template, sheetName: 'innerJoin' }).setData(join({ left, right, compareKeys, joinType: 'inner' })).setHeaderFormat({ backgrounds: 'black', fontColors: 'white', fontWeights: 'bold' }).dumpValues()
bruce mcpherson is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. Based on a work at http://www.mcpher.com. Permissions beyond the scope of this license may be available at code use guidelines