Skip to content

Instantly share code, notes, and snippets.

@AspenJames
Created February 24, 2020 21:22
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save AspenJames/f1857a899893cfdafe27bc705cccb497 to your computer and use it in GitHub Desktop.
Save AspenJames/f1857a899893cfdafe27bc705cccb497 to your computer and use it in GitHub Desktop.
function makeRangeAbsoluteReference() {
// This grabs the currently selected cells,
// applying _makeAbsolute() to each
var spreadsheet = SpreadsheetApp.getActive();
var range = spreadsheet.getActiveRange();
var numRows = range.getNumRows();
var numCols = range.getNumColumns();
for (var i = 1; i <= numRows; i++) {
for (var j = 1; j <= numCols; j++) {
var currentCell = range.getCell(i,j);
_makeAbsolute(currentCell);
}
}
};
function _makeAbsolute(cell) {
// This function accepts a reference to a cell which in turn
// references another cell relatively. The cell reference is updated
// to an absolute reference, ex. "C4" => "$C$4".
// Currently only works for direct cell references with no manipulation
// (won't work for calculated values such as "C4 + F2")
var formula = cell.getFormula();
var formArr = formula.match(/([A-Z]+)(\d+)/);
formArr.shift() // get rid of entire match, pieces remain
var absForm = formArr.map(n => `$${n}`).join('');
cell.clearContent();
cell.setFormula(absForm);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment