5 Simple Steps to Trace Cell Precedents in Google Sheets Using Apps Scripts

Ever had a long formula with references to other sheets that was too confusing to follow?

I recently started a new job where I needed to ramp up quickly on a formula heavy model in Google Sheets. In the past, I relied on a custom Excel Add-On I built that helped me easily navigate through cell precedents and dependents using keyboard shortcuts.

Unfortunately, Google Sheet doesn’t have the same built-in tracing functionality like Excel which served as the foundation for my custom-built VBA scripts.

Luckily, Google Sheet is powered by Google Apps Script and with a bit of regular expression magic, one can build a Sidebar that can help list out all the cells that a formula of another cell is referencing.

Before we dive-in, hat tip to Oscar over at Get Digital Help for the regular expression; it saved me a good chunk of time. I simply made slight adjustments for it to fit Google Sheets and Google Apps Script, which if you’re not familiar with, you can get quickly get up to speed with here.

Step 1: A function to extract all cell references

The first step is to build-out the main function that will extract all the cell references within the formula of the active (selected) cell which you’re looking to explore.

The getFormula() function, when called on the getCurrentCell() function of the SpreadsheetApp, returns a string of the active cell’s formula (e.g., =SUM(A1:A2)).

var formula = SpreadsheetApp.getCurrentCell().getFormula();

Then we need to use a regular expression with a match() function to get an array of all the cell references. Match() returns an array with substrings of the formula string that match the pattern of the regular expression. In this case, it’s the structure of cell references in Google Sheets (e.g., SHEET NAME!A1 or A1:D5).

var regex = /('?[a-zA-Z0-9_\s]{1,99}'?!?)?(\$?[A-Z]{1,3}\$?[1-9]{1,7}(:\$?[A-Z]{1,3}\$?[1-9]{1,7})?|\$?[1-9]{1,7}\:\$?[1-9]{1,7}|\$?[A-Z]{1,3}\:\$?[A-Z]{1,3})/g;
var cell_references = formula.match(regex);

The logic of the expression is thoroughly detailed in Oscar’s post, so I’ll spare you from repeating it here.

Then we need to add the current sheet’s name to any cell references that are within the current sheet because we will need it later when we navigate between cell references. E.g., make C1 into THIS SHEET!C1.

if (cell_references) {
 cell_references = add_current_sheet(cell_references);
 return cell_references;
 }

We use an if condition to determine if the previous step (the match() function) has indeed returned an array of cell references. If the regular expression was matched on the formula string, an array would be returned and thus the variable cell_references would have a truthy value. If there were no cell references in the active cell, then cell_references would be null and thus falsy.

We will build the add_current_sheet function in the next step.

Once the alteration of the cell references is done, the function will return an array. Here’s the full-function:

function get_cell_reference() {
  var formula = SpreadsheetApp.getCurrentCell().getFormula();
  var regex = /('?[a-zA-Z0-9_\s]{1,99}'?!?)?(\$?[A-Z]{1,3}\$?[1-9]{1,7}(:\$?[A-Z]{1,3}\$?[1-9]{1,7})?|\$?[1-9]{1,7}\:\$?[1-9]{1,7}|\$?[A-Z]{1,3}\:\$?[A-Z]{1,3})/g;
  var cell_references = formula.match(regex);
  if (cell_references) {
    cell_references = add_current_sheet(cell_references);
    return cell_references;
  };
};

You’ll need to run this function first to set up permissions for the script to access the Google Sheet document. If you’re not familiar with what this means, I recommend you check out the section of my previous post that walks you through it.

Step 2: Add the current sheet name

A straight forward function that determines if we need to add the name of the current sheet:

function add_current_sheet(references){  
  for (var i =0;i<references.length;i++){
    if (!references[i].match(/!/))
      references[i] = SpreadsheetApp.getActiveSheet().getSheetName()  +'!' + references[i];
  };
  return references;
};

The function takes in the array of references and loops through it to see if the reference includes a ! which tells us whether it’s a stand-alone cell reference or a cell reference with a sheet name already in it. If not, we’ll leverage the getSheetName() function of the getActiveSheet() function from SpreadsheetApp to prefix it to the cell reference. Finally, we return the altered array.

Step 3: Function to navigate between cell references

Another straight forward function that takes in a reference and activates it in the Google Sheet window.

function navigate(cell_reference){
  var sheet_name = cell_reference.match(/^(.*)!/)[1];
  var cell_ref = cell_reference.match(/!(.*)$/)[1];
SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheet_name).setActiveSelection(cell_ref);
};

I chose to keep using a match function, but this could also be achieved with a split() function. Once we have the cell reference and the sheet name, we can pass the cell reference to the setActiveSelection() function using the getSheetByName() function to reference the cell’s sheet.

Step 4: Creating a templated HTML for the Sidebar

In order to be able to go back and forth in an easy way, I chose to list out the cell references in a Sidebar because it allows me to keep a view of the list while retaining full access to the sheet itself.

A templated HTML allows for dynamic content to be created by referencing functions from another Google App Script document. You’ll need to create a new HTML file from the App Script code editor:

Looking to keep the Sidebar simple, I used an unordered list to list out all the cell references and then an onclick() property for list items to trigger the navigation function from the previous step.

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <? var data = get_cell_reference(); 
       if (data) { 
         for (var i = 0; i < data.length; i++) { ?>
         <ul>
           <li onclick = "google.script.run.navigate(<?= data[i] ?>);"> <?= data[i] ?></li>
           <br>
         </ul>
        <? }; 
        } 
        else { ?>
         <h2> No formula </h2>
       <? }; ?>
  </body>
</html>

The <? ?> mark up is what makes this a templated HTML file rather than a simple HTML file and will thus need to be evaluated when we call it so the <? ?> portions will be executed before the HTML document is displayed on the Sidebar.

As the templated HTML is evaluated, it runs the function from Step 1 to store all the cell references from the cell currently highlighted into the data variable. Then it loops the data array to list the cell references one by one in as list items in an unordered list. At the same time, it also populates the onclick() call to the navigation function (Step 3) with the corresponding cell reference. If there are no cell references, then a big “No formula” message will be displayed in the Sidebar.

Step 5: Create a Macro to open the Sidebar

In order to have a keyboard shortcut that opens the Sidebar, we need to create a dummy macro and replace its code with the code to open a Sidebar and serve the evaluated templated HTML.

I like the Ctrl+Alt+Shift+0 shortcut, but any will work, of course.

We’ll replace the code in new macro function created with:

function open_sidebar() {
SpreadsheetApp.getUi().showSidebar(HtmlService
      .createTemplateFromFile('Sidebar')
      .evaluate());
};

The showSidebar() function of getUi() does the heavy lifting and takes in an HTML file which we’ll evaluate using the HtmlService’s createTemplateFromFile() function where Sidebar is the name of the HTML file from Step 4.

That’s it! Now, whenever you Ctrl+Alt+Shift+0 a Sidebar will open up, and if there is a formula with cell references they will show up listed.