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.

Page Content hide

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

billionaires

Companies

companies on the right

companies

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

inner join

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.

left join

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.

right join

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.

full join

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

sql inner join

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:

inner join

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

formatting with fiddler

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

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 ...

A fourth way to preserve and create formulas with Fiddler for Sheets, plus some more new methods

This is (probably) the last in a series of posts describing how Fiddler for Sheets can be used to help ...

A third way to preserve formulas with fiddler, plus 2 new methods

In 2 ways to create and preserve formulas with fiddler for Google Apps Script I gave some examples of how ...

2 ways to create and preserve formulas with fiddler for Google Apps Script

I've had a few queries from fiddler users about how to deal with formulas. Fiddler is primarly for abstracting spreadsheet ...

Handly helper for fiddler

If you use some of my libraries, you may have come across the Fiddler class, which abstracts sheet data and ...

Optimize updates to Google Sheets with fiddler fingerprints

If you use some of my libraries, you may have come across the Fiddler class, which abstracts sheet data and ...

Fiddler and rangeLists

Header formatting with fiddler  shows how to use fiddler to easily set formats for your sheet headings and Formatting sheet column ...

Formatting sheet column data with fiddler

Header formatting with fiddler  shows how to use fiddler to easily set formats for your sheet headings. here's how to ...

Header formatting with fiddler

When playing around with sheet data, I always find myself using the Fiddler object - some more posts on this ...

Populating sheets with API data using a Fiddler

Another place that Fiddler can be handy is if you are populating a sheet with data retrieved from an API ...

A functional approach to updating master sheet with Fiddler

A common thing to do is to update a master sheet from an update sheet - where you want to ...

Unique values with data fiddler

Fiddler is a way to handle spreadsheet data in a functional way, as described in A functional approach to fiddling ...

Fiddler – A functional approach to fiddling with sheet data

  I wondered if there might be more functional programming, more declarative approach to the problem of fiddling around with ...

Color scales, custom schemes and proxies with Apps Script

A couple of my favorite topics - object proxies and fiddling with colors - in the same article today. We'll ...

Find nearest matches in color schemes with Apps Script

Here's another article on playing around with color in Apps Script. Let's say you have a defined list of colors ...

Detect fake news with Google Fact Check tools

I came across Google's FactCheckTools API today, so I thought it might be fun to add to my bmApiCentral Apps ...