/*
  taken from https://productforums.google.com/forum/#!topic/docs/ymxKs_QVEbs
  created by --Hyde
*/
/** 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);
}