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

1.2 Companies

1.3 Types of joins

1.3.1 inner join (the default)

1.3.2 left join

1.3.3 right join

1.3.4 full join

2 Introduction to Fiddler

2.1 A selection of ways to populate a fiddler

2.1.1 With a spreadsheet shaped array of values

2.1.2 With an array of JavaScript objects

2.1.3 Directly from a sheet

2.1.4 Using the preFiddler

2.1.4.1 Accessing Fiddler from preFiddler

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

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

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

8.5 Handly helper for fiddler

8.6 Optimize updates to Google Sheets with fiddler fingerprints

8.7 Fiddler and rangeLists

8.8 Formatting sheet column data with fiddler

8.9 Header formatting with fiddler

8.10 Populating sheets with API data using a Fiddler

8.11 A functional approach to updating master sheet with Fiddler

8.12 Unique values with data fiddler

8.13 Fiddler – A functional approach to fiddling with sheet data

8.14 Find nearest matches in color schemes with Apps Script

8.15 Detect fake news with Google Fact Check tools

8.16 Content oriented color mixing with Apps Script

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

Joins

Billionaires

Companies

Types of joins

inner join (the default)

left join

right join

full join

Introduction to Fiddler

A selection of ways to populate a fiddler

With a spreadsheet shaped array of values

With an array of JavaScript objects

Directly from a sheet

Using the preFiddler

Accessing Fiddler from preFiddler

Example

Initialization

Join Definition

Left definition

Right definition

compareKeys

Output template

Inner join

Left join

Right join

Full join

All together

Contrast with SQL

And another thing

Links

Related