How to get url from Google Spreadsheet cell with Google Apps Script?

Question

I've got a google spreadsheet copied from excel ANd I have some problem with that. This is my document https://docs.google.com/spreadsheets/d/1Ok_phu5OXtvKHLj3MLa7N3WV2qBdMWRz8dLHnTqjHrc/edit?usp=sharing

This is my code

function myFunction() {
  cel = SpreadsheetApp.getActiveSpreadsheet().getActiveCell()
  Logger.log(cel.getFormula())
  Logger.log(cel.getValue())
}

And this is my log:

[16-10-05 13:39:59:628 EEST] 
[16-10-05 13:39:59:629 EEST] RU: VTB Arena - Park and Hotel

And I need to get an URL (https://na3.salesforce.com/0065000000a1b8p) How can I do that?

Answer 1

At this time the Spreadsheet Service of Google Apps Script is not able to get the properties of cells rich text content, just the plain text by using getValue() method. Other methods like getFormula(), getNote() are able to get the corresponding cell property, but none of the them includes the URL of the link.

On Google Sheets the workaround is to get the URL manually. A programmatically alternative is, to use VBA on the Excel file to get the hyperlinks before converting the file to the Google Sheets format. See the answer to Can I use an excel formula to extract the link location of a hyperlink in a cell? for details.

Answer 2

I think it's best to publish the google spreadsheet to the web and then parse the html with BS4 to get the urls.

Answer 3

Here's a workaround I used to get links from cells. Apparently when you use ctrl+K to add links they are added as rich text rather than as formulas. I used the function below in a google sheet to turn these rich text fields with links into html. If there is a link in the field then it returns the html code for the link. If there isn't a link then it simply returns the text passed in. Of course this could be done more elegantly ... I used lots of variables to make it easier to follow what was going on.

//get link from a cell given by A1 notation
function getHtmlfromRichTextRange(theRange){
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var range = sheet.getRange(theRange);
  var cell = range; 
  var cellValue = cell.getRichTextValue();
  var cellText = cell.getValue();
  var theThing = cellValue.getLinkUrl();

  if (theThing == null){
    return cellText;
  } else {
    return '<a href="'+theThing+'">'+cellText+'</a>'; 
  }
}

In a spreadsheet you could do something like (see image 1):

1. In cell A1, enter "a link to apple"
2. With cell A1 highlighted, press ctrl+k to add the hyperlink to http://www.apple.com
3. In cell B1, enter A1
4. In Cell C1, enter the formula =getHtmlfromRichTextRange(B1)

As a result, you should see the value

<a href="http://www.apple.com/">a link to apple</a> in cell C1

A couple of things I noticed when working with this example in this way:

• If the function in C1 returned just the url and not the url with some String text around it, then Google sheets turned C1 into RichText, too. Since I was using this to create html emails, I had the function return the HTML code for the link.
• I could have simplified and passed the String "A1" in quotes the function. I was trying to find a way to have the formula in C1 update when the richtext link in A1 was updated. My thought was that if A1 changed (e.g. the URL changed), then the result of the function in C1 would automatically update. It doesn't. I didn't pursue it. What is written above did the trick for me.
• This code worked if the link was all the text in the cell. If the cell had some text in it that had the hyperlink and some text that did not (e.g. the hyperlink was only part of the text in the cell) then this code returned it as plain text and not as a URL.

I used the function as-is in AppScript to loop over a range that contained links. Inside the loop I passed the function the A1 notation of the cells, some of which had links. It worked beautifully and saved me loads of time!

I'm sure there's an easier way. I typically find that after doing it the harder way and thus don't need to do it anymore.

I hope this helps!

Answer 4

Thanks for asking this question! I've been looking for this myself! I copied a list of Facebook friends into a spreadsheet. They copied as Rich Text, meaning they contained a link, but it wasn't available in =Hypertext format.

I searched forever and it seemed certain there's no easy way around this.

Then I stumbled upon it.

Just select the cell containing the Rich Text (assuming you want to get the link it contains when you hover over it) and hit Command-K to Edit Link.

It will bring up a window where you can enter the Cell Text in the top field, and the URL in the bottom field.

No need to enter anything - it's there by default. So just hit [Enter] and BAM! It's now formatted as =HYPERLINK("URL","Text")

But I had HUNDREDS of these to convert and wanted to find an easier way.

And so I stumbled upon AppleScript (I've never used it til today - even though I'm transitioning away from Apple entirely).

I created a simple AppleScript:

tell application "Google Chrome" to activate delay 0.5 tell application "System Events" delay 0.5 tell process "Chrome" keystroke "k" using command down delay 0.2 keystroke return delay 0.2 keystroke return delay 0.2 keystroke return delay 0.2 end tell end tell

Select the cell you want to edit in your spreadsheet (assuming you're using Google Chrome) and then run this AppleScript.

I made several Scripts, all in US Currency amounts. So I have one that runs just once, and then a 5x, a 10x, a 20x, a 50x, and a 100x.

So for my list of 143 names, I just now ran the 100x script, then a 50x script. I sad back and took a sip of coffee while I watched my computer rapidly do the work for me.

Sorry if this is more info than people are looking for -- it's my first time answering a question here (that I recall) and, well, if I had stumbled across the answer I'm writing now -- it would have been a godsend earlier this year!

Happy coding!

Answer 5

Try this:

var url = /"(.*?)"/.exec(cel.getFormulaR1C1())[1];
Logger.log(url);

It pulled the URL when I added it as a link manually.

Note that I used the following i my test:

  var dataSheet = CentralSS.getSheetByName(CENTRAL_DATAFILES_SHEETNAME);
  var range = dataSheet.getRange("E2");
  var url = /"(.*?)"/.exec(range.getFormulaR1C1())[1];