Retrieve the formula in a cell as text using a custom function

Ever wanted to retrieve the formula from another cell as text?

No? Hmm…. maybe this only applies to people who are building tutorials for Google Sheets. In any case, it’s a pretty cool method and we can learn a lot about writing custom scripts for Google Sheets.

Method

Google Sheets does not have a built-in function for getting the formula from a cell, so we have to use Google App Scripts to create a custom function.

Creating the custom formula() function

To create a custom function, open the script editor by going to Tools -> Script Editor.

In the script editor, copy and paste the following function.

function formula(r,c) { 
  return SpreadsheetApp.getActiveSheet().getRange(r,c).getFormula(); 
}

This creates a custom function formula() that takes a row index and column index and returns the formula contained in the specified cell.

For example, we use the following function to get the formula from the cell B3 (row 3, column 2).

=formula(3,2)

Using the custom formula() function

Having to use the row and column indexes to reference a cell isn’t very convenient. We can’t reference a cell directly (e.g. B3) or copy the function to other cells and have it adjust. However, we can look up the cell’s indexes with the ROW() and COLUMN() functions.

We can now look up the formula in B3 by entering the following formula:

=formula(ROW(B3),COLUMN(B3))

Example Spreadsheet

A working example can be found here:  https://docs.google.com/spreadsheets/d/1r9Uixcacc0RdLY00IaTbYDdP3cWrKLvFf0X9YRUkJTM/edit?usp=sharing

If you don’t want to setup the custom function yourself, you can copy the example and modify it.

Example contents:

Script:

 

 

 

 

Add a Comment

Your email address will not be published. Required fields are marked *