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

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.

Preserving formulas

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

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

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


const frxy = () => {

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

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

preserving formula 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

const frxy = () => {

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

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

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

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

const frxg = () => {
const fiddler = bmPreFiddler.PreFiddler().getFiddler({
id: '1hVB-54VyLrulcoO50Sa7_JL0sxh-QplvV1Zkwfdn4SY',
sheetName: 'fxr'
})
const targetSheet = fiddler.getSheet().getParent().getSheetByName("Sheet5")

// filter out columns from the data
const deleteColumns = ['when', 'nameFrom', 'nameTo']
const columnNames = fiddler.getHeaders().filter(columnName=>!deleteColumns.includes(columnName))
fiddler.dumpValues(targetSheet, columnNames)

}
dump selected columns

dumpValues result

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