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 with formula manipulation and creation, as well as column insertion and deletion without caring too much about the physical layout of the sheet. We’ll use the same source sheet as usual(Let’s use a fiddler example from another article – Currency exchange rates library for Apps Script – optimized for the parsimonious – swop.cx) so you can compare the various methods described in
We’re starting with this layout, created in the various examples mentioned above.
Fiddler values and insertion and deletion
Since fiddler is designed to deal with values, pulling this into fiddler then dumping it out again would convert formulas to values – so we may need a way to preserve formulas. Similarly, the insert, delete, move and copy functions of fiddler are certainly simpler but can generally only be used for values, as formulas would not be automatically updated in the same way as they are in the Sheets API.
This article shows how a hybrid of the sheets API and Fiddler can give the best of both worlds – position abstraction plus formula re-evaluation.
getValues() versus getFormulas()
In previous examples, we’ve used range.getFormulas() along with fiddler.getRangeList() to get the formulas that needed preserving. For some background, when you getValue() from a cell that has a formula present, the value is evaluated from the formula. However, when you setValue(), the formula is set to empty and disappears. getFormula() and setFormula() allow you to get and set the formula directly. However, there’s an interesting quirk that allows you to use setValue() to also set a formula as long as the value looks like a regular formula, for example, ‘=a2+b2’
It’s this property that fiddler leverages to allow the fiddler.mapRows() to mix values and formulas.
fiddler.needFormulas()
To preserve formulas, firstly fiddler needs to know it’s going to have to, but we don’t want the overhead of always getting formulas when they are not needed, so you can use the needFormulas() method to set that up so using bmPreFiddler to create a fiddler from scratch would look like this example
create a fiddler that will need the current formulas
fiddler.mapRows((row , props) =>…
If you use fiddler, you probably use mapRows already to set values. The props argument has a bunch of useful stuff, one of them being any formulas present in the current row. In this example, we’ll do a mapRows which changes a column value but also preserves formulas for columns in a given list. The entire example does this
sets a list of columns whose formulas need preserving
opens a fiddler and gets the values and formulas
sets the amount column in each row to 100
copies the original formulas into any columns that used to have a formula for each row
The simplest way to delete columns in fiddler is to use fiddler.filterColumns(). However if you have some columns with formulas that might need re-evaluation because you’ve deleted some intervening columns, you’ll need to use the sheets api to delete columns.
This example does exactly the same as the previous, but it dumps the result to a different sheet. In addition, it then deletes some columns using the sheets API, after getting the column position from fiddler using fiddler.getHeaderIndex(). Note that when deleting columns with the sheets API, you should do it in reverse order as per this example, so that the column numbers don’t need tweaking between deletions.
const frxl = () => {
const preserve = ['average rate']
// fiddle with the values, dump it out and preserve values const fiddler = bmPreFiddler .PreFiddler() .getFiddler({ id: '1hVB-54VyLrulcoO50Sa7_JL0sxh-QplvV1Zkwfdn4SY', sheetName: 'rxr' }) .needFormulas() .mapRows((row, { rowFormulas }) => { row.amount = 1000 /// etc... preserve.forEach(f => row[f] = rowFormulas[f]) return row })
// now we can delete rows as normal ['from', 'to'].map(name => { const index = fiddler.getHeaderIndex(name) if (index === -1) throw new Error(`delete column ${name} not found`) return index 1 }).sort() .reverse() .forEach(index => targetSheet.deleteColumn(index))
}
deleting columns
Inserting columns
The simplest way to insert columns in fiddler is to use fiddler.insertColumns(). However if you have some columns with formulas that might need re-evaluation because you’ve inserted some intervening columns, you’ll need to use the sheets api to insert columns. Inserting is a lot more complex that deletion, not least because you also have the option of inserting before or after a given column.
Here’s a patterm to insert a set of columns that uses fiddler to evaluate where they are and compensates for insertions as it goes along. We’ll start with the same procedure as before, preserving some formulas, deleting some columns, but then inserting a few too.
Note that after the deletion phase, we just recreate the fiddler so it has all the new positions before beginning the insertion phase
const frxm = () => {
const preserve = ['average rate']
// fiddle with the values, dump it out and preserve values let fiddler = bmPreFiddler .PreFiddler() .getFiddler({ id: '1hVB-54VyLrulcoO50Sa7_JL0sxh-QplvV1Zkwfdn4SY', sheetName: 'rxr' }) .needFormulas() .mapRows((row, { rowFormulas }) => { row.amount = 1000 /// etc... preserve.forEach(f => row[f] = rowFormulas[f]) return row })
// now we can delete rows as normal ['from', 'to'].map(name => { const index = fiddler.getHeaderIndex(name) if (index === -1) throw new Error(`delete column ${name} not found`) return index 1 }).sort() .reverse() .forEach(index => targetSheet.deleteColumn(index))
// // INSERTION PATTERN to we'll need to recreate the fiddler to recalibrate after natively deleting, so we can find where things are now fiddler = new bmFiddler.Fiddler(targetSheet);
// insert a couple of new columns [{ names: ['name from 1', 'name from 2'], type: 'after', columnName: 'nameFrom' }, { names: ['name to 1', 'name to 2'], type: 'before', columnName: 'nameTo' }] // add their positions .map(n => ({ ...n, index: fiddler.getHeaderIndex(n.columnName) })) // validate .map(n => { if (n.index === -1 || (n.type !== 'before' && n.type !== 'after')) throw new Error(`invalid insertion setting(s) ${JSON.stringify(n)}`) return n }) // do this in ascending order .sort((a, b) => a.index - b.index) // do the work .forEach((n, i, a) => { const width = n.names.length const before = n.type === 'before'
// collect all the previous shifts const shifts = a.slice(0, i).reduce((pt, ct) => pt = ct.names.length, 0)
// do the insertion const insertionPoint = 1 n.index shifts targetSheet[before ? 'insertColumnsBefore' : 'insertColumnsAfter'](insertionPoint, width)
Here’s the new sheet with all the deletions and insertions completed
Creating formulas from scratch
Fiddler has a few more methods to help with this in addition to those covered in the previous articles on this subject (listed at the beginning of this article). Essentially we need to be able to get the a1 style address of any cell(s) given their row number and column name so that formulas can be constructed.
In this example we’ll repeat everything that’s gone before covering preserving formulas, and setting new values, then we’ll insert a new column (flux) which needs a new formula – (rate – average rate) / average rate
const frxn = () => {
const preserve = ['average rate']
// fiddle with the values, dump it out and preserve values let fiddler = bmPreFiddler .PreFiddler() .getFiddler({ id: '1hVB-54VyLrulcoO50Sa7_JL0sxh-QplvV1Zkwfdn4SY', sheetName: 'rxr' }) .needFormulas() .mapRows((row, { rowFormulas, rowOffset, fiddler }) => {
// change the amount row.amount = 100
// save any formulas marked for preservation preserve.forEach(f => row[f] = rowFormulas[f])
// replace the converted column with a formula const [amountA1, rateA1] = fiddler.getA1s({ columnNames: ['amount', 'rate'], options: { rowOffset, numberOfRows: 1 } })
// we can just pop thr formula in row.converted = `=${amountA1}*${rateA1}` return row }) // prettify the headings .setHeaderFormat({ backgrounds: 'black', fontColors: 'white', fontWeights: 'bold' })
const targetSheet = fiddler.getSheet().getParent().getSheetByName("Sheet5") // write it all out to another sheet fiddler.dumpValues(targetSheet)
// let's insert a new column - we can't use fiddler.insert, because we want the sheets API to readdress any ranges in formulas const after = fiddler.getHeaderIndex('average rate') 1 targetSheet.insertColumnsAfter(after, 1) targetSheet.getRange(1, after).offset(0, 1, 1, 1).setValue('flux')
// let's get the sheet back in again, and we'll need to preserve formulas // the fiddler constructor is exported from prefiddler for convenience // so you don't have to bother with the Fiddler library if you don't need it const { Fiddler } = bmPreFiddler.PreFiddler() fiddler = new Fiddler(targetSheet) .needFormulas()
// we can use this to see which columns have formulas already and preserve them const keepFormulas = fiddler.getColumnsWithFormulas()
// now lets set the new formula in the new column (rate - average rate)/average rate fiddler.mapRows((row, { rowFormulas, rowOffset }) => {
// preserve those that need preserved keepFormulas.forEach(f => row[f] = rowFormulas[f])
This introduces a couple of new handy methods and properties
Fiddler constructor export from bmPreFiddler
bmPreFiddler is a wrapper for bmFiddler. It’s just a shortcut so you can pass a few parameters to open or create a sheet and make a fiddler from it. The reason it exists is that bmFiddler is, like most of my libraries nowadays, dependency free – it doesn’t reference any Apps Script services. bmFiddler can be used standalone without even referencing any spreadsheets – it’s handy for manipulating any table like data in Apps Script.
bmPreFiddler does access Apps Script services, and if you are manipulating sheets and don’t mind the Spreadsheet app dependency, it’s handier than bmFiddler. However, you may still want to be able to create a fiddler directly without bothering to explicitly import the bmFiddler library. Here’s how
In previous examples, we provided a list of columns with formulas that needed to be preserved. More likely though, you’ll want to preserve all formulas by default. This method returns an array of column names that have any formulas present – so you can incorporate that into your mapRows process.
// we can use this to see which columns have formulas already and preserve them const keepFormulas = fiddler.getColumnsWithFormulas()
getColumsWithFormulas
Here’s how to use that list in mapRows
// now lets set the new formula in the new column (rate - average rate)/average rate fiddler.mapRows((row, { rowFormulas, rowOffset }) => {
// preserve those that need preserved keepFormulas.forEach(f => row[f] = rowFormulas[f])
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