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 selectively write values, thus preserving formulas in other columns.
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, where one of the columns has a formula that we don’t want to lose after manipulating the values with Fiddler
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
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 by selectively writing out column values. Fiddler has new and enhanced methods to assist with this.
fiddler.dumpValues([sheet][,columnNames])
This is an enhancement to the existing dumpValues, which will dump all the values maintained by fiddler to the given sheet, or to the source sheet by default. You can now specify specific columnNames to be dumped. It also returns the range/value pairs it created in case they come in useful.
/** * RangeValuePair - an object that contains the range and values * @typedef {Object} RangeValuePair * @property {string} name - any name for identification(usually a column name) * @property {*[[]} values - The values - ready for use with setValues * @property {Range} range - The range it applies to */
// dumps all values to current sheet fiddler.dumpValues()
// dumps all values to a different sheet fiddler.dumpValues(sheet)
// dumps selected columns to current sheet const rvpairs = fiddler.dumpValues(null, columnNames)
// dumps selected columns to another sheet const rvpairs = fiddler.dumpValues(sheet, columnNames)
dump values
fiddler.dumpColumns(columnNames[, sheet])
This returns the array of range/value pairs that contain the fiddler maintained values for the column name(s) given that is generated by fiddler.getDumper(). It also writes the values to the given sheet, or the default sheet if none given. Normally you wouldn’t need this function as it’s called by fiddler.dumpValues(), but it’s exposed in case you have some more complex use case.
/** * RangeValuePair - an object that contains the range and values * @typedef {Object} RangeValuePair * @property {string} name - any name for identification(usually a column name) * @property {*[[]} values - The values - ready for use with setValues * @property {Range} range - The range it applies to */
const rvpairs = fiddler.dumpColumns(columNames)
fiddler.getDumper
fiddler.getDumper(columnNames)
This returns an array of range/value pairs that contain the fiddler maintained values for the column name(s) given. These can be used to selectively write out values for designated columns – leaving the formulas in the other columns intact. Normally you wouldn’t need this function as it’s called by fiddler.dumpColumns(), but it’s exposed in case you have some more complex use case.
/** * RangeValuePair - an object that contains the range and values * @typedef {Object} RangeValuePair * @property {string} name - any name for identification(usually a column name) * @property {*[[]} values - The values - ready for use with setValues * @property {Range} range - The range it applies to */
const rvpairs = fiddler.getDumper(columNames)
fiddler.getDumper
Preserving the formula
This code will just open a sheet read it into fiddler and write it out again. However it will only write out selected columns. In this case everything except ‘average rate’ which it will leave untouched, as the formula we wanted to preserve is in that column. You can of course also use this same technique if you are only upating a column or two in the sheet and don’t want to write them all out again – but there’s not a great performance benefit in that.
// when we come to dumping values, we want to omit any special ones - in this case the average rate has some formulas we want to preserve const columnsToDump = fiddler.getHeaders().filter(columnName=>columnName !== 'average rate')
// and dump the values selected values only to the current sheet fiddler.dumpValues (null , columnsToDump )
}
preserving the formula
the result
fiddler.dumpFormats([sheet])
In normal operation, dumpValues also updates any formats you’ve set. However, when selectively dumping values, this behavior is disabled by design. If you have formats to set, you can explicitly set them. Here’s the example updated to prettify the headers too
// when we come to dumping values, we want to omit any special ones - in this case the average rate has some formulas we want to preserve const columnsToDump = fiddler.getHeaders().filter(columnName=>columnName !== 'average rate')
// we can format without writing out the values fiddler .setHeaderFormat({ backgrounds: 'green', fontColors: 'white', fontWeights: 'bold' }) .dumpFormats()
// and dump the values selected values only to the current sheet fiddler.dumpValues (null , columnsToDump )
}
dumpformats
the result
fiddler.filterColumns
Filter columns can be used to completely remove columns from data – see this example
const frxf = () => { // fiddle around with range lists
// let's put the result to some other sheet const targetSheet = fiddler.getSheet().getParent().getSheetByName("Sheet5")
// filter out columns from the data const deleteColumns = ['when', 'nameFrom', 'nameTo'] fiddler .filterColumns(columnName=>!deleteColumns.includes(columnName)) .dumpValues(targetSheet)
}
filterColumns
filterColumns result
By contrast, dumpValues (sheet, columnNames) doesn’t remove columns from data. It just dumps the columns specified – leaving the spaces for the ones not dumped.
// filter out columns from the data const deleteColumns = ['when', 'nameFrom', 'nameTo'] const columnNames = fiddler.getHeaders().filter(columnName=>!deleteColumns.includes(columnName)) fiddler.dumpValues(targetSheet, columnNames)
bruce mcpherson is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. Based on a work at http://www.mcpher.com. Permissions beyond the scope of this license may be available at code use guidelines