Regex find URL parameter

- Regex

Problem? I’ve got spaghetti code in an old script.

My Google App Script takes a Jira query ID and initiate a GET request for tempo worklogs.

This is my spaghetti pomodoro code. Let’s refactor.

function getQueryID(link) {

  var sheet = SpreadsheetApp.getActiveSheet();
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var args = formula.match(/=\w+\((.*)\)/i);
  var linkRef = args[1].split(",")[0]; // split even if only 1 arg. Split will ignore it.
  var range = sheet.getRange(linkRef);
  var formulas = range.getFormulas();
  var formula = formulas.toString();
  var filterStart = formula.indexOf("filter=")+7;
  var filterLength = formula.indexOf('","')-filterStart;
  var query =   formula.substr(filterStart,filterLength);
  return query;

Let’s have Regex do all the work on this pass instead of doing Javascript indexOf string searching.

Due to how Google Sheets works to get the referenced cell's URL you need to extract that cell's A1 notation from the active cell's formula, rather than accepting it as a function parameter.
Text: =getQueryID(F22,1)
Regex: \=getQueryID[(](.*)\,
Match Result: F22

Now that we have our cell. Let’s extract the Jira Query ID. I did a match for filter= and captured everything, (.*), until "," is matched.

Text: =HYPERLINK("","YETI_01")
Regex: filter=(.*)","
Match Result: 58333

That’s it.

# Custom Google App Script function for Google Sheets

function getQueryID(link){

  // Get A1 notation for the URL link.
  var sheet = SpreadsheetApp.getActiveSheet();
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var A1regExp = '\=getQueryID[(](.*)\,'
  var args = formula.match(A1regExp); 

 // Get Jira Query ID from the referenced cell's link
  var linkFormula = sheet.getRange(args[1]).getFormula();
  var linkRegExp = 'filter=(.*)","'
  var linkArgs = linkFormula.match(linkRegExp);

  return linkArgs[1]; // Jira query ID

And, here’s some useful links..


Regex Tester