1

My colleague has a spreadsheet full of links. She copy/pasted them from a variety of different sources.

For a number of reasons, I want to convert these from just plain copy/pasted links to HYPERLINK formula cells.

Is there any way to do this?

2

Here's how I approached this problem. It requires some HTML/JS related knowledge, but I will try to post every step as detailed as possible.

Google Spreadsheet files can be downloaded as HTML pages.

  1. Copy the column/cells with the rich text links to a new sheet (for readability).

  2. From File > Download as > Web page (.html, zipped).

  3. Unzip the archive and you will get an .html file for every sheet you have in that document.
  4. Choose the sheet you have newly created in first step and open it in Google Chrome*.
  5. Use Ctrl + Shift + C on Windows or Cmd+Opt+C on Mac, to open the DevTools window.

  6. When the DevTools Window is opened look for the Console tab and paste the following:


let cells = document.getElementsByTagName('td');

for (let i = 0; i < cells.length; i++) {
    let links = cells[i].getElementsByTagName('a');
    //we are looping through all the links 
    for (let k = 0; k < links.length; k++) {
        //This is to make sure a link exists in the cell
        if (typeof links[k] !== 'undefined') {
            //We are going to add every link location and text as a new cell in that row
            let newCellLocation = cells[i].parentNode.insertCell(2);
            let newCellText = cells[i].parentNode.insertCell(3);
            newCellLocation.innerHTML = links[k].href;
            newCellText.innerHTML = links[k].text;
        }
    } 
}


let thead = document.getElementsByTagName('thead')[0];
thead.remove();

let table = document.getElementsByTagName('table')[0];
table.style.tableLayout = 'auto';

This will extend the current table by adding two cells (one for the link location and one for the link name) for each link that was in the original cell.

After that you can copy pate the newly created columns and perform your modification of the data: convert it to hyperlink or whatever.


*I have only tested this in Google Chrome, but this is javascript, so it should be correctly executed in Firefox's Developers Console, too.

  • Worked perfectly for a project we were doing. Had over 850+ records we needed to extract. – HPWD Aug 18 at 21:53
  • @HPWD I'm glad it was helpful to you. Were the steps easy to follow? – Ivanka Todorova Aug 19 at 11:39
  • They were prefect. – HPWD Aug 19 at 11:40
  • I am a web developer so I’m quite comfortable working in Chrome’s Dev Tools. :) – HPWD Aug 19 at 11:40
  • 1
    Here it is a six weeks later and I'm back to run this code snippet again! Very thankful for this! Pure brilliance. – HPWD Oct 20 at 14:45
1

This can only be done manually.

When a link is pasted into a spreadsheet cell as a rich text, neither spreadsheet formulas nor script functions can access its URL. One can only get the link text, for example using =A1&"" in the spreadsheet, or getValues in a script.

For this reason, pasting links into spreadsheet cells as rich text should be strongly discouraged.

(Tangentially related: Extract the link text and URL from a hyperlinked cell).

  • See Ivanka Todorova's answer. – HPWD Aug 18 at 21:54

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.