CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Joshua Rosenberg
Joshua Rosenberg

Reputation: 4226

Javascript custom function to extract text string from formula Google Spreadsheets

I am trying to extract a text string from a formula in Google Spreadsheets. Specifically, I have a column with HYPERLINK formulas from which I'd like to use a custom function to create another column with the extracted text of the formula, so that calling the function on a cell with =HYPERLINK("https://twitter.com/jrosenberg6432/status/617013951184957440") would return https://twitter.com/jrosenberg6432/status/617013951184957440 in another cell.

I found this very helpful function from this help forum:

/** Extract a text string in double quotes from the formulas in selected cells
*/


function replaceFormulasWithFirstQuotedTextStringInFormula() {
  // Goes through all the cells in the active range (i.e., selected cells),
  // checks if a cell contains a formula, and if so, extracts the first
  // text  string in double quotes in the formula and stores it in the cell.
  // The formula in the cell is replaced with the text string.
  // see https://productforums.google.com/d/topic/docs/ymxKs_QVEbs/discussion

  // These regular expressions match the __"__ prefix and the
  // __"__ suffix. The search is case-insensitive ("i").
  // The backslash has to be doubled so it reaches RegExp correctly.
  // https://developer.mozilla.org/en-US/docs/JavaScript/Reference/Global_Objects/RegExp

  var prefix = '\\"';
  var suffix = '\\"';
  var prefixToSearchFor = new RegExp(prefix, "i");
  var suffixToSearchFor = new RegExp(suffix, "i");
  var prefixLength = 1; // counting just the double quote character (")

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var activeRange = ss.getActiveRange();
  var cell, cellValue, cellFormula, prefixFoundAt, suffixFoundAt, extractedTextString;

  // iterate through all cells in the active range
  for (var cellRow = 1; cellRow <= activeRange.getHeight(); cellRow++) {
    for (var cellColumn = 1; cellColumn <= activeRange.getWidth(); cellColumn++) {
      cell = activeRange.getCell(cellRow, cellColumn);
      cellFormula = cell.getFormula();

      // only proceed if the cell contains a formula
      // if the leftmost character is "=", it contains a formula
      // otherwise, the cell contains a constant and is ignored
      // does not work correctly with cells that start with '=
      if (cellFormula[0] == "=") {

        // find the prefix
        prefixFoundAt = cellFormula.search(prefixToSearchFor);
        if (prefixFoundAt >= 0) { // yes, this cell contains the prefix
          // remove everything up to and including the prefix
          extractedTextString = cellFormula.slice(prefixFoundAt + prefixLength);
          // find the suffix
          suffixFoundAt = extractedTextString.search(suffixToSearchFor);
          if (suffixFoundAt >= 0) { // yes, this cell contains the suffix
            // remove all text from and including the suffix
            extractedTextString = extractedTextString.slice(0, suffixFoundAt).trim();

            // store the plain hyperlink string in the cell, replacing the formula
            cell.setValue(extractedTextString);
          }
        }
      }
    }
  }
}


/** Add a custom menu to the active spreadsheet, containing a single menu item
*   for invoking the replaceFormulasWithFirstQuotedTextStringInFormula() function.
*   The onOpen() function is automatically run when the spreadsheet is opened.
*/


function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Replace formulas with text strings",
    functionName : "replaceFormulasWithFirstQuotedTextStringInFormula"
  }];
  ss.addMenu("Extract", entries);
}

But, this function replaces the original cells with the extracted text, rather than retaining what is in the original cells and returning the output in another column.

I tried to edit the code but am a Javascript novice so thought to ask despite this possibly being a minor edit to it.

Upvotes: 0

Views: 2011

Answers (2)

Misha
Misha

Reputation: 190

That post on the help forum is old. Here is a working solution. Tested it myself. Hope it helps. Feel free to message me for questions (I don't have enough reputation to comment yet lol).

function myFunction() {
  var formulas = SpreadsheetApp.getActiveRange().getFormulas();
  var toPut = SpreadsheetApp.getActiveRange().offset(0, 1, SpreadsheetApp.getActiveRange().getNumRows(), 1);
  var extracted = [];
  for(var index in formulas){
    var array = formulas[index];
    for(var formulaIndex in array){
      Logger.log("f:" + array[formulaIndex]);
      extracted.push([array[formulaIndex].substring(array[formulaIndex].indexOf('"')+1, array[formulaIndex].lastIndexOf('"'))]);
    }
  }

  toPut.setValues(extracted);
}

function onOpen(e){

    SpreadsheetApp.getUi().createMenu("Testing").addItem("myFunc", 'myFunction').addToUi();
}

Upvotes: 1

Swanky Coder
Swanky Coder

Reputation: 1018

You can try this:

cell = activeRange.getCell(cellRow, cellColumn);

var output_cell = activeRange.getCell(cellRow, (cellColumn + 1)); 
//Or put in the column number in which u want the output to be put

cellFormula = cell.getFormula();
.
.
.

//(Keep the rest of the code as it is)

Then, instead of writing cell.setValue(extractedTextString); in your function, do this:

output_cell.setValue(extractedTextString);

So, what I am trying to do here is to put the new values in the column next to your original column.

Hope it works :)

Upvotes: 1

Related Questions