Unable to extract link text from tooltip in Google Spreadsheet

2
votes

I'm optimistic this question has already been answered. But if not, I'm hopeful that one of you JavaScript and Google Script experts can lend some assistance.

Steps to Reproduce

  1. Go to www.nhl.com/stats/player
  2. Copy the top few rows in the table (see screenshot #1)
  3. Paste the contents into a new Google Spreadsheet
  4. Click on the name of a player in the "Player" column (see screenshot #2)
  5. Notice that the name itself is shown in the formula bar while the tooltip has a link to the player's profile on nhl.com

What I Want to Happen

I'd like to extract the link content that's shown in the toolip. (In screenshot #2 this is referring to the https://www.nh...layer/8471215 text.)

What Actually Happens

Nothing really. There's no way to see the tooltip content in the formula bar and, as far as I can tell, there's no function (e.g. hyperlink) to extract the tooltip content.

Screenshots

Screenshot #1

Copy and paste default player text, note that the player's name is also a link

Screenshot #2

Tooltip contains link text that I'd like to extract

Other Notes

I'm comfortable coding in JavaScript, but I have minimal experience in the script editor.

1
google-apps-scriptgoogle-sheets
This is probably the closest you are going to get stackoverflow.com/questions/13847007/… . In your case, the solution won't work, because you are copying Rich Text Format - Anton Dementiev
Thank you for the link, @AntonDementiev! It answered my question. (Apologies, I did search on SO before but I didn't see that question.) Unfortunately I think you're right. There doesn't appear to be any way to do what I'm trying. - Graham Kennedy
The ugly hack workaround I did was to export the spreadsheet as an HTML file. Then I parsed it using PHP where I was able to extract the link using the freely available simple_html_dom.php library. I then exported the results as a CSV file and re-imported it into Google Sheets. This is acceptable given that I'm working on archived data, but it's about as inefficient as I can get... and I'm a seriously inefficient coder. - Graham Kennedy

1 Answers

2
votes

In addition to what Anton has said, Apps Script has only support for A1 Notation and R1C1 formulas for now:

Returns the formula (A1 notation) for the top-left cell of the range, or an empty string if the cell is empty or doesn't contain a formula.

Returns the formula (R1C1 notation) for a given cell, or null if none.