How do you get the formula from a cell instead of the value?

Question

How do you get the literal value (the formula) from a cell instead of the result value?

EXAMPLE DESIRED:

• A2: "Foo"
• B3: "=A2" - so it displays "Foo"
• C3: "=CELL("formula", B3)" displays "=A2"

Of course, CELL() doesn't support a "formula" parameter, that's just for demonstrating the intent. I've looked over the function list and nothing jumps out at me.

USE CASE:
I would like to make a reference to another row, and based on that reference get other cells from the row. Putting an explicit row number won't work (e.g. B3 = "2"), since it will not auto-correct when rows are modified. Specifically, I would like for multiple columns in one row to be relative to columns in another row, and be able to change the relative row in one place without having to edit each of those columns.

Normally, to make one row relative to another you would put column values like this: "=A2+5" and "=B2+10". This means that A column is "relative row value +5" and B column is "relative row value + 10". If you want to change the relative row (for example, to row 56), you need to change each of the columns to "=A56+5" and "=B56+10". How to accomplish this by editing just one field?

For a practical example, consider a sheet that is a list of tasks and each one may be marked as "following" another for purposes of computing end dates, but you would like to be able to change the reference task and to support a N:1 relationship between tasks.

[Update]

Actually, I do have a solution to the specific use case. But I am still curious about the original question: getting access to the formula behind the value in a cell.

SOLUTION 1: - A2: "=ROW()" - B2: "Foo" - C3: "=A2" displays "2" and auto-adjusts to maintain reference as rows are added/removed

SOLUTION 2:

Another solution would be to add a unique "id" column and store references by id, then find the row using LOOKUP().

Answer 1

Use getFormula() or getFormulaR1C1() methods to get the formula of a cell.

Example adaptated from https://webapps.stackexchange.com/a/92156/88163

The following custom function will return the formula of the referenced cell but if the reference is not valid, it will return an error message.

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();
}

Example of use of the above custom function

A2: FOO
B3: Formula =A2, value FOO
C3: Formula =CELLFORMULA(B3), value =A2

Answer 2

Use this:function =FORMULATEXT("cell")