Extract link details from cell in Google Sheet-CodePudding

I was wondering whether it is possible to extract the link details from a cell when hovering the mouse over it?

For example the instagram followers of Kim Kardashian account as follows:

CodePudding user response：

I do not think you could obtain values from the social media preview of the link, directly via the popover, easily.

The content seen in the social media preview are embedded under the meta tags within the HTML of the relevant webpage and the content can be scrapped by parsing the relevant HTTP response.

Solution 1

You could use Google Sheet's IMPORTXML() and the suitable XPath, which helps in pointing to the data you want. The below is to be used as a cell value.

=IMPORTXML([Relevant cell id; Example: A1],"/html/head/meta[14]/@content]")

However, this does produces an error: "Could not fetch url", on my end, despite using the valid example URL that you gave.

Solution 2

You could leverage on a Google sheet bounded Google Apps Script with the below code and suitable constraints:

function fetchContent(url) {
    const htmlResponse = UrlFetchApp.fetch(url).getContentText();
    return Parser.data(htmlResponse).from("<meta content=\"").to("\"").build(); }

The relevant cell value to be:

=fetchContent([Relevant cell id])

However, when using the example URL, this solution also causes an error:"Exception: Request failed for https://www.instagram.com... returned code 429 ..", alluding that there has been too many HTTP request made to Instagram from the relevant IP address. See this SO Q&A regarding IP addresses associated with App script's UrlFetchApp()and this SO Q&A regarding Instagram blocking IP addresses.

In theory, the above solutions should work for other websites which do not block IP addresses.

Obtaining specific parts of the content via Google Apps script should be straightforward, but for within Google Sheets you could check out this SO Q&A and the related ones.

If you need to obtain data specifically from Instagram, you could check out Instagram's Graph API.