3

I need to change the multiple links in a Google Sheets from normal sheet hyperlinks such as:

=HYPERLINK("https://traveltractions.com","Travel Tractions") 

into:

<a href="https://traveltractions.com">Travel Tractions</a>

How can I do this in bulk?

  • Thanks, I've played with this a little but admittedly I'm not very good at it. Do you know a script I could try or point me to a place where there are already made scripts for this? Surely it's a common problem, I looked for extensions and add-ons but nothing was definitive. – matthew Oct 14 '17 at 19:55
  • 1
    Can you give an idea of how they're laid out? Are you wanting to bulk change a list of just links, or are they scattered about in your spreadsheet? – David Metcalfe Oct 14 '17 at 22:11
  • Maybe an easier solution is to use Find/Replace. Just be sure to check the search inside formulas checkbox – Rubén Oct 15 '17 at 18:03
  • @pnuts suggested I change "Also search within formulae:. His formula didn't work but now at least I can do a search and replace. Thank you – matthew Oct 15 '17 at 18:51
1

Could be impractical if your links are scattered throughout a sheet, but should be viable if say all in a column (assumed A):

  • Replace all = in ColumnA with say # with Also search within formulae
  • Enter, say in B1, and copy down to suit:

    =substitute(trim("<a href="&mid(A1,12,find(",",A1,12)-12)&">"&mid(A1,find(",",A1)+2,len(A1))),char(34)&")","</>")
    
  • Copy ColumnB, Paste special, Paste values only over the top

  • Replace all # in ColumnA with = (or delete ColumnA?)
1

There are several ways to do this. Next, two of them:

  1. Using built-in FIND/REPLACE with the find insi

    1. Replace =HYPERLINK( by <a href=".
    2. Replace ," by >.
    3. Replace ") by </>
  2. Using Google Apps Script

    1. Use the getFormula(range) or getFormulas(range) Google Apps Script's methods to get the formula(s),
    2. Use replace(string) JavaScript method

For examples, checkout Extract the link text and URL from a hyperlinked cell

0

It's fairly rudimentary, but a Google Apps Script solution could be as follows:

function createHref(url, title){
  /* Create the URL and return it. */
 return '<a href=' + url + '>' + title + '</a>';
}

function flatten(arr) {
  /* Flatten n-dimensional array. */
  return arr.reduce(function(flat, toFlatten) {
    return flat.concat(
      Array.isArray(toFlatten) ? flatten(toFlatten) : toFlatten
    );
  }, []);
}

function setCellVal(row, col, val) {
  /* Function to make setValue() less annoying. */ 
  SpreadsheetApp.getActiveSheet()
    .getRange(row, col)
    .setValue(val);
}

function createLinks() {
  /* Main function */
  const sh = SpreadsheetApp.getActive().getActiveSheet(); // Active spreadsheet object.
  const linkCol = 1; // Column for links.
  const outputCol = 2; // Column for formatted link output.
  const numRows = sh.getLastRow();
  const myRegexp = /\("(.*?)".*?\"(.*?)"/g; 

  var linkList = sh.getRange("A2:A").getFormulas();
  linkList = flatten(linkList.filter(String));  
  var match = myRegexp.exec(linkList);

  var count = 2;
    while (count < numRows + 1) {
      setCellVal(count, outputCol, createHref(match[1], match[2]));
      match = myRegexp.exec(linkList);
      count++;
    }
}

Here's a link to a working example that you can copy and try for yourself.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.