Custom formula in Google Apps Script (GAS) to extract the formula used in a cell (G Suite Sheets)

README.md

Get the formula in a cell, instead of the value for Google Sheets in Google Apps Script, G Script

(or whatever its name is this week, (Thanks, marketdroids) )

So, sometimes you want to show what formula was used to get to a value. this custom formula will extract it and show it as the new cell value:

Adapted from https://goo.gl/xd0449 (StackExchange)

Example of use of the custom function

Input:

x	A	B	C
1	FOO	=A2	=CELLFORMULA(B3)

Output:

x	A	B	C
1	FOO	FOO	=A2

Code.gs

/**
 * Extract formula from a cell
 *
 * @param {reference} a cell reference from which to extract the formula
 * @return a string representation of the formula in {reference} 
 * @customfunction
 */
  
function CELLFORMULA(reference) {
  var ss = SpreadsheetApp;
  var sheet = ss.getActiveSheet();
  var formula = ss.getActiveRange().getFormula();
  var args = formula.match(/=\w+\((.*)\)/i);
  try {
    var range = sheet.getRange(args[1]);
  }
  catch(e) {
    throw new Error(args[1] + ' is not a valid range');
  }
  return range.getFormula();
}