Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- 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);
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement