Some Love for RangeLists
Steve Bolman
I was not able to find much out there on RangeLists or how to use them. They are very useful for my use case, but they are a bit strange so I though I would share what I think I learned about them.
What the below script is focused on is to develop an array of "R1C1" addresses of cells that either:
- Include a formula
- Include a value (excluding cells with formulas).
Below is a testing stub function that calls the IIFE that I recommend saving in a separate script file.
function testStub(){
const sheetName = 'Worksheet';
const sheet = ss.getSheetByName(sheetName);
const contents = RangeListOfCells.content(sheetName );
const sheetRangeListValues = sheet.getRangeList(contents.cellsWithValues);
const sheetRangeListFormulas = sheet.getRangeList(contents.cellsWithFormulas);
console.log("With Values = " + sheetRangeListValues.getRanges().map(x => x.getA1Notation()));
console.log("With Formulas = " + sheetRangeListFormulas.getRanges().map(x => x.getA1Notation()));
}
The string array (of R1C1 cell addresses) is constructed by the IIFE below. To produce a RangeList Object only requires a call to the sheet.getRangeList(arrayOfAddresses).
Something strange thing about the object returned is that it apparently is not a list of ranges (which is what you might expect). In order to extract a list of ranges you need to call ‘getRanges()’ on the returned RangeList.
As I promised above the IIFE RangeListOfCells, is copied below I need to acknowledge that I ripped off Bruce M. on how I packaged this. If I got it wrong it is on me. I am not a professional developer and I will admit to having an idiosyncratic style (it works for me by apologies if not for you).
As shown in the testStub function you can past the script below in a script file of it’s own or in some utility file, and refer to it as shown above RangeListOfCells.content(sheetName) (again this will return an string Array of R1C1 Addresses).
To convert this string Array of R1C1 Addresses into a RangeList you call sheet.getRangeList(arrayOfAddresses). I am hoping this works for Y’all. If there is interest I will post on GitHub but wanted to float this with the community first and see if there is a duplicate that I was not aware of.
// COPY TO BOTTOM OF POST
var RangeListOfCells = (function (ns) {
ns.withValues = function (sheetDataRange) {
//const rangeValues = sheetDataRange.getValues();
const cellAddressesWithValues = sheetDataRange.getValues().reduce(function (rowAcc, rowAsArray, rowIndex) {
const rowReturn = rowAsArray.reduce(function (colAcc, value, colIndex) {
if (value != '') {
colAcc.push('R' + (rowIndex + 1) + 'C' + (colIndex + 1));
}
return colAcc
}, []);
if (rowReturn.length > 0) {
rowAcc.push(rowReturn);
}
return rowAcc;
}, []);
//console.log(cellAddressesWithValues);
//console.log([].concat(...cellAddressesWithValues))
return [].concat(...cellAddressesWithValues); // Flattens array
}
ns.withFormulas = function (sheetDataRange) {
//const rangeFormulas = sheetDataRange.getFormulas();
const cellAddressesWithFormulas = sheetDataRange.getFormulas().reduce(function (rowAcc, rowAsArray, rowIndex) {
const rowReturn = rowAsArray.reduce(function (colAcc, formula, colIndex) {
if (formula != '') {
colAcc.push('R' + (rowIndex + 1) + 'C' + (colIndex + 1));
}
return colAcc
}, []);
if (rowReturn.length > 0) {
rowAcc.push(rowReturn);
}
return rowAcc;
}, []);
//console.log(cellAddressesWithFormulas);
//console.log([].concat(...cellAddressesWithFormulas))
return [].concat(...cellAddressesWithFormulas); // Flattens array
}
ns.content = function (sheetName) {
const sheet = ss.getSheetByName(sheetName);
// Functionally equivalent to creating a Range bounded by A1 and (Sheet.getLastColumn(), Sheet.getLastRow())
const sheetDataRange = sheet.getDataRange();
const cellsWithFormulas = ns.withFormulas(sheetDataRange);
const cellsWithValues = ns.withValues(sheetDataRange).reduce(function (acc, address) {
const testIndex = cellsWithFormulas.indexOf(address);
if (testIndex == -1) {
acc.push(address);
}
return acc;
}, []);
//console.log(cellsWithFormulas)
//console.log(cellsWithValues)
return {'cellsWithFormulas': cellsWithFormulas, 'cellsWithValues': cellsWithValues};
}
return ns;
})(RangeListOfCells || {}); // BOTTOM Of IIFE RangeListOfCells()
Bruce Mcpherson
--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/e91b6084-c6c0-4efb-bdf8-1c5d95e5c011n%40googlegroups.com.