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

Question

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

EXAMPLE OF DESIRE:

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

Of course, CELL () does not support the “formulas” parameter, which simply demonstrates intent. I looked through the list of functions and nothing jumps at me.

CUSTOM CASE:
I would like to make a link to another row and based on this link to get other cells from the row. Putting an explicit line number will not work (for example, B3 = "2"), because it will not be automatically corrected when changing lines. In particular, I would like several columns in one row to refer to columns in another row and be able to change the relative row in one place without having to edit each of these columns.

Typically, to make one row relative to another, you would set the column values ​​as follows: "= A2 + 5" and "= B2 + 10". This means that the column "relative row value +5" and column "B" - "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 do this by editing only one field?

For a practical example, consider a worksheet that is a list of tasks, and each of them can be marked as “next” for end date purposes, but you would like to be able to change the reference task and maintain the N: 1 relationship between tasks.

[Update]

In fact, I have a solution for a specific use case. But I'm still curious to know the original question: access the formula behind the value in the cell.

SOLUTION 1: - A2: "= ROW ()" - B2: "Foo" - C3: "= A2" displays "2" and automatically adjusts to maintain the link when lines are added / deleted.

SOLUTION 2:

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

Answer 1

EDIT: this answer is intended to be used in describing the problem, with the requirement to do this only using its own functions (without scripts).

Based on AdamL's comments on the question, the answer is that you cannot get the formula from the cell (instead of the value).

However, for the actual use case that I was trying to solve, = ROW (A42) can be used to get a link to a specific line, which will be automatically updated with changes in the lines.

Answer 2

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

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

The following user-defined function will return the link cell formula, but if the link 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(); } 

An example of using the specified user-defined function

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

Answer 3

I had the same problem and tried to use the formula that Ruben created, but had a limitation. I could not use this formula inside another (I tried to make mid ()). Thus, a different approach was developed:

 function CELLFORMULA(reference) { var ss = SpreadsheetApp; var sheet = ss.getActiveSheet(); var formula = ss.getActiveRange().getFormula(); re = /cellformula\((.*)\);/g; args = re.exec(formula); try { var range = sheet.getRange(args[1]); } catch(e) { throw new Error(args + ' is not a valid range'); } return range.getFormula(); } 

Answer 4

I tried the modified cell formula of Luis Paulo Alberto Magalles in a different formula and it did not work for me. How can i fix this?

Answer 5

Try using =formula(2,3) where the coordinates are (line #, col #)

Sample page on Google: https://docs.google.com/spreadsheets/d/1A1l0qdnNSHlJB-5DARGKDeIsbuCCLGuoYWm8sR29UTA/edit?usp=sharing

Formulas shown ...

And here is how it does ...

Note Sometimes you need to download a formula during which you get some kind of error saying "Loading data ..." or something like that.

Also note: I have no idea why this works, since formula not a listed function for google sheets, but I use it in a spreadsheet. Maybe the idea to try it came from Excel or something else, I don’t remember.

Answer 6

Use this: function =FORMULATEXT("cell")