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.
Joins
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.
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.
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.
const byAlaSql = () => {
// alasql imports
const {
exec,
createAndInsertFromData
} = bmAlasql.GasSql
// 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()
prettify headers
Which will do this
For more info on formatting with fiddler see Formatting sheet column data with fiddler
Links
bmAlasql (1OcbRxA2lKJfR49RtAzl_5XN2_jnT6ByzJRKRihv8VXf1leGEGevlLzYf)
and if you’re planning to use fiddler, you’ll need either the vanilla fiddler library
bmFiddler (13EWG4-lPrEf34itxQhAQ7b9JEbmCBfO8uE4Mhr99CHi3Pw65oxXtq-rU)
or the handy wrapper
bmPreFiddler (13JUFGY18RHfjjuKmIRRfvmGlCYrEkEtN6uUm-iLUcxOUFRJD-WBX-tkR)
All are also on github under github.com/brucemcpherson
Related
You may have come across my post on SQL for Apps Script it's here where I shared a library for using ...
Read More
This is (probably) the last in a series of posts describing how Fiddler for Sheets can be used to help ...
Read More
In 2 ways to create and preserve formulas with fiddler for Google Apps Script I gave some examples of how to ...
Read More
I've had a few queries from fiddler users about how to deal with formulas. Fiddler is primarly for abstracting spreadsheet ...
Read More
If you use some of my libraries, you may have come across the Fiddler class, which abstracts sheet data and ...
Read More
If you use some of my libraries, you may have come across the Fiddler class, which abstracts sheet data and ...
Read More
Header formatting with fiddler shows how to use fiddler to easily set formats for your sheet headings and Formatting sheet column ...
Read More
Header formatting with fiddler shows how to use fiddler to easily set formats for your sheet headings. here's how to ...
Read More
When playing around with sheet data, I always find myself using the Fiddler object - some more posts on this ...
Read More
Another place that Fiddler can be handy is if you are populating a sheet with data retrieved from an API ...
Read More
A common thing to do is to update a master sheet from an update sheet - where you want to ...
Read More
Fiddler is a way to handle spreadsheet data in a functional way, as described in A functional approach to fiddling ...
Read More
I wondered if there might be more functional programming, more declarative approach to the problem of fiddling around with ...
Read More
This is a follow on from the article on Pull libraries inline to your Apps Script project (which you should probably ...
Read More
1000 pages and counting Most years I do a post on 'a year in Apps Script', looking back over the ...
Read More
In Merging sheets from multiple sources and encrypting selected columns I published some code for selectively copying columns from multiple input ...
Read More