I’ve had a few queries from fiddler users about how to deal with formulas. Fiddler is primarly for abstracting spreadsheet values so they can be manipulated and updated without worrying too much about the physical details of where they are. Sheets formulas are of course very concerned about where sheet data is relative to each component of the formula – which makes it tricky to support.

Fiddler does have a number of abstracted range manipulation methods (mainly to help with formatting – see  Fiddler and rangeLists) but those same methods can be used to play around with formulas too.

Preserving formulas

Let’s use a fiddler example from another article –  Currency exchange rates library for Apps Script – optimized for the parsimonious – swop.cx

This is the table we created there

amountfromtowhenrateconvertednameFromnameTo
100USDEUR2016-05-010.877905317987.79053179United States dollarEuro
1000EURUSD2016-05-011.1390751139.075Euro
United States dollar
250GBPCHF2016-05-011.406301788351.575447Pound sterlingSwiss franc
1000GBPEUR2016-05-011.2816848521281.684852Pound sterlingEuro
5.99GBPAUD2016-05-011.91419376311.46602064Pound sterlingAustralian dollar
100USDEUR 0.841476724384.14767243United States dollarEuro
1000EURUSD 1.1883871188.387Euro
United States dollar
250GBPCHF 1.26103789315.2594724Pound sterlingSwiss franc
1000GBPEUR 1.1717505021171.750502Pound sterlingEuro
5.99GBPAUD 1.8909979111.32707748Pound sterlingAustralian dollar
11.48AUDGBP 0.52882131446.070868689Australian dollarPound sterling
11.48AUDGBP2016-05-010.52241315355.997303003Australian dollarPound sterling

Let’s say now that this table had a new column, which is a formula to calculate the average rate for each currency each time it appears, so the table looks like this

amountfromtowhenrateconvertednameFromnameToaverage rate
100USDEUR2016-05-010.877987.7905United States dollarEuro0.8597
1000EURUSD2016-05-011.13911139.0750EuroUnited States dollar1.1637
250GBPCHF2016-05-011.4063351.5754Pound sterlingSwiss franc1.3337
1000GBPEUR2016-05-011.28171281.6849Pound sterlingEuro1.2267
5.99GBPAUD2016-05-011.914211.4660Pound sterlingAustralian dollar1.9026
100USDEUR 0.841584.1477United States dollarEuro0.8597
1000EURUSD 1.18841188.3870EuroUnited States dollar1.1637
250GBPCHF 1.2610315.2595Pound sterlingSwiss franc1.3337
1000GBPEUR 1.17181171.7505Pound sterlingEuro1.2267
5.99GBPAUD 1.891011.3271Pound sterlingAustralian dollar1.9026
11.48AUDGBP 0.52886.0709Australian dollarPound sterling0.5256
11.48AUDGBP2016-05-010.52245.9973Australian dollarPound sterling0.5256

With this formula filled down for the average rate column

=sumproduct(H$2:H=H2,G$2:G=G2,E$2:E)/sumproduct(H$2:H=H2,G$2:G=G2)
preserve this formula

Fiddler values

Since fiddler is designed to deal with values,  pulling this into fiddler then dumping it out again would convert the formula to values – so we may need a way to preserve formulas in some cases. In this specific case of course you could just do the work with JavaScript – but sometimes it’s necessary to create or preserve a formula instead.

fiddler.getRangeList()

Fiddler has as getRangeList method which can be used to get a set of ranges that make up 1 or more columns just by specifying the column name.

const rangeList = fiddler.getRangeList(['some column', 'another column', ...])
fiddler.getRangeList

It returns a Sheet.RangeList so we can use all the regular sheets methods.

preserving the formula

This code will just open a sheet read it into fiddler and write it out again, but will prettify the column headings. However it will also convert the formulas to values. Notice that it also gets the rangeList that refers to the average rate column for use later, as well as the current formulas in that range


const fiddler = bmPreFiddler.PreFiddler().getFiddler({
id: '1hVB-54VyLrulcoO50Sa7_JL0sxh-QplvV1Zkwfdn4SY',
sheetName: 'rxr'
})


// retain the average rate formula, preserve its current value for later
const ar = fiddler.getRangeList('average rate').getRanges()[0]
const arf = ar.getFormulas()

// dump the updated values, and may as well decorate the column headers too
fiddler
.setHeaderFormat({
backgrounds: 'tomato',
fontColors: 'white',
fontWeights:'bold'
})
.dumpValues()
preserving the formula

restoring the formula

Once you’ve fiddled with the values, you’ll want to restore the formulas to their original value.

  // restore a preserved formula
ar.setFormulas(arf)
javascript restoring the formula

the result

preserving formula result

Creating formulas

Of course preserving formulas will only work if things are not moving around. If you are adding new columns or rows with fiddler, or moving them around then restoring, a formula is likely to give the wrong result. In this case, you’ll need to create the formula and fill it down using the rangeLists returned by fiddler.

Modifying the sheet contents

Let’s enhance the example a bit. We want to do the same work as before, but also want to add some more columns with some language translation in it, so we’ll use the sheets googletranslate which is a very handy function in sheets – handier than using the Google Translate API directly. So it would be useful if we could create some formulas on the fly to use it.

Preparation

We’ll do the same as before to set up the fiddler and preserve the sumproduct formula, but this time we also want to do 2 things

  • get rid of any previously translated columns in case we want to do different languages this time
  • add new columns for selected languages
  // fiddle around with range lists

const fiddler = bmPreFiddler.PreFiddler().getFiddler({
id: '1hVB-54VyLrulcoO50Sa7_JL0sxh-QplvV1Zkwfdn4SY',
sheetName: 'rxr'
})
// translate into these languages
const languages = ['en', 'fr', 'it', 'de']
const columnNames = languages.map(l => `translation ${l}`)

// dispose of any current translation columns
fiddler.filterColumns(name => !name.startsWith("translation "))

// add the new ones
columnNames.forEach(name => fiddler.insertColumn(name))

// retain the average rate formula, preserve its current value for later
const ar = fiddler.getRangeList('average rate').getRanges()[0]
const arf = ar.getFormulas()

// dump the updated values, and may as well decorate the column headers too
fiddler
.setHeaderFormat({
backgrounds: 'tomato',
fontColors: 'white',
fontWeights:'bold'
})
.dumpValues()

// restore a preserved formula
ar.setFormulas(arf)
preparing to add translation

Translate model formula

We’ll need a function to create the google translate function

  // here's the translate formula
const makeTranslate = (from = "en", to = "en", range) => `GOOGLETRANSLATE(${range.getA1Notation()},"${from}","${to}")`
model formula

Get source ranges

We’ll be translating the to and from currency names into the various languages in the list, so to construct the formula, we’ll need to know where those columns are – Since we’re filling down, we’ll only need the first cell in each

  // and we'll use these to make the formula model
const [fromRange, toRange] = fiddler.getRangeList(['nameFrom', 'nameTo'], { numberOfRows: 1 }).getRanges()
get input column ranges

Construct the formulas

Now we have the list of the languages to implement, a function to create a model formula, and the first cell of each of the inputs, we can create the completed model formula for each language

  // now we can construct the forumulas
const formulas = languages.map(l => `=${makeTranslate("en", l, fromRange)} & " to " & ${makeTranslate("en", l, toRange)} `)
completed model formulas

Fill down the target columns

We can then just fill down the each language column with its copy of the completed model formula. If the formula suppors ArrayFormula, you don’t even need to fill it down – but GoogleTranslate does not.

  fiddler.getRangeList(columnNames)
.getRanges()
.forEach((columnRange, i) =>
columnRange.offset(0, 0, 1, 1)
.setFormula(formulas[i]).autoFill(columnRange, SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES))
filling the columns

the result

Here’s the created sheet

creating and preserving formulas with fiddler

The formulas

A sample formula

=GOOGLETRANSLATE(G2,"en","fr") & " to " & GOOGLETRANSLATE(H2,"en","fr") 
sample formula

The whole thing

Here’s the whole example with all the code combined

const frx = () => {
// fiddle around with range lists

const fiddler = bmPreFiddler.PreFiddler().getFiddler({
id: '1hVB-54VyLrulcoO50Sa7_JL0sxh-QplvV1Zkwfdn4SY',
sheetName: 'rxr'
})
// translate into these languages
const languages = ['en', 'fr', 'it', 'de']
const columnNames = languages.map(l => `translation ${l}`)

// dispose of any current translation columns
fiddler.filterColumns(name => !name.startsWith("translation "))

// add the new ones
columnNames.forEach(name => fiddler.insertColumn(name))

// retain the average rate formula, preserve its current value for later
const ar = fiddler.getRangeList('average rate').getRanges()[0]
const arf = ar.getFormulas()

// dump the updated values, and may as well decorate the column headers too
fiddler
.setHeaderFormat({
backgrounds: 'tomato',
fontColors: 'white',
fontWeights: 'bold'
})
.dumpValues()

//now we can start fiddling with formulas

// restore a preserved formula
ar.setFormulas(arf)

// build some complex formulas from scratch and fill them down

// here's the translate formula
const makeTranslate = (from = "en", to = "en", range) => `GOOGLETRANSLATE(${range.getA1Notation()},"${from}","${to}")`
// because the formula we're using doesn't support array formulas we'll need to use 1 row and fill down

// and we'll use these to make the formula model
const [fromRange, toRange] = fiddler.getRangeList(['nameFrom', 'nameTo'], { numberOfRows: 1 }).getRanges()

// now we can construct the forumulas
const formulas = languages.map(l => `=${makeTranslate("en", l, fromRange)} & " to " & ${makeTranslate("en", l, toRange)} `)

// these are the places the formula is going
fiddler.getRangeList(columnNames)
.getRanges()
.forEach((columnRange, i) =>
columnRange.offset(0, 0, 1, 1)
.setFormula(formulas[i]).autoFill(columnRange, SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES))

}
whole thing

Summary

Of course this doesn’t cover every case of formula manipulation, but it does show how to expose the native ranges yet still address them by the abstracted columns names. I may add some more complex formula shortcuts at some point  if there proves to be a demand for it.

Links

bmPreFiddler (13JUFGY18RHfjjuKmIRRfvmGlCYrEkEtN6uUm-iLUcxOUFRJD-WBX-tkR)

bmSwopCx (19Y6OgrMB_bqEtTvSBHnwKkmbWhyi4jdkQ5FxQXVN-i1Jzx2PKziPqQhd) github

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