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
amount | from | to | when | rate | converted | nameFrom | nameTo |
100 | USD | EUR | 2016-05-01 | 0.8779053179 | 87.79053179 | United States dollar | Euro |
1000 | EUR | USD | 2016-05-01 | 1.139075 | 1139.075 | Euro |
United States dollar
|
250 | GBP | CHF | 2016-05-01 | 1.406301788 | 351.575447 | Pound sterling | Swiss franc |
1000 | GBP | EUR | 2016-05-01 | 1.281684852 | 1281.684852 | Pound sterling | Euro |
5.99 | GBP | AUD | 2016-05-01 | 1.914193763 | 11.46602064 | Pound sterling | Australian dollar |
100 | USD | EUR | 0.8414767243 | 84.14767243 | United States dollar | Euro | |
1000 | EUR | USD | 1.188387 | 1188.387 | Euro |
United States dollar
| |
250 | GBP | CHF | 1.26103789 | 315.2594724 | Pound sterling | Swiss franc | |
1000 | GBP | EUR | 1.171750502 | 1171.750502 | Pound sterling | Euro | |
5.99 | GBP | AUD | 1.89099791 | 11.32707748 | Pound sterling | Australian dollar | |
11.48 | AUD | GBP | 0.5288213144 | 6.070868689 | Australian dollar | Pound sterling | |
11.48 | AUD | GBP | 2016-05-01 | 0.5224131535 | 5.997303003 | Australian dollar | Pound 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
amount | from | to | when | rate | converted | nameFrom | nameTo | average rate |
100 | USD | EUR | 2016-05-01 | 0.8779 | 87.7905 | United States dollar | Euro | 0.8597 |
1000 | EUR | USD | 2016-05-01 | 1.1391 | 1139.0750 | Euro | United States dollar | 1.1637 |
250 | GBP | CHF | 2016-05-01 | 1.4063 | 351.5754 | Pound sterling | Swiss franc | 1.3337 |
1000 | GBP | EUR | 2016-05-01 | 1.2817 | 1281.6849 | Pound sterling | Euro | 1.2267 |
5.99 | GBP | AUD | 2016-05-01 | 1.9142 | 11.4660 | Pound sterling | Australian dollar | 1.9026 |
100 | USD | EUR | 0.8415 | 84.1477 | United States dollar | Euro | 0.8597 | |
1000 | EUR | USD | 1.1884 | 1188.3870 | Euro | United States dollar | 1.1637 | |
250 | GBP | CHF | 1.2610 | 315.2595 | Pound sterling | Swiss franc | 1.3337 | |
1000 | GBP | EUR | 1.1718 | 1171.7505 | Pound sterling | Euro | 1.2267 | |
5.99 | GBP | AUD | 1.8910 | 11.3271 | Pound sterling | Australian dollar | 1.9026 | |
11.48 | AUD | GBP | 0.5288 | 6.0709 | Australian dollar | Pound sterling | 0.5256 | |
11.48 | AUD | GBP | 2016-05-01 | 0.5224 | 5.9973 | Australian dollar | Pound sterling | 0.5256 |
With this formula filled down for the average rate column
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.
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
restoring the formula
Once you’ve fiddled with the values, you’ll want to restore the formulas to their original value.
the 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
Translate model formula
We’ll need a function to create the google translate function
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
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
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.
the result
Here’s the created sheet
The formulas
A sample formula
The whole thing
Here’s the whole example with all the code combined
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