ImportXLM Using IfError on Google Sheets

user_15142423 0
Viewed 47

I wanted to thank everyone for being so helpful on this site - it means a lot!

I am trying to import the likes/followers from a Spotify playlist to Google Sheets. It seems like various playlists have a different XPath.

I can extract a majority(most work) of the likes/followers using this code: (B24 is the URL)

=INDEX(REGEXEXTRACT(IFERROR(QUERY(ARRAY_CONSTRAIN(IMPORTDATA(B24), 500, 5), "select Col5 where Col4 contains 'followers'", 0), QUERY(ARRAY_CONSTRAIN(IMPORTDATA(B24), 500, 7), "select Col7 where Col6 contains 'followers'", 0)), "\d+")*1)

However, some playlist links come up with an empty output. Example: https://open.spotify.com/playlist/5aSO2lT7sVPKut6F9L6IAc Example of a working one: https://open.spotify.com/playlist/7qvQVDnLe4asawpZqYhKMQ

I'm honestly not sure how to add a third argument, and I have been blindly changing the col numbers to see what works - no luck. Any idea on how to figure out what col #'s to change to/any guidance would be extremely helpful.

Thank you!!

google-sheets-formula google-apps-script google-sheets
1 Answers

Issue and workaround:

When I saw the HTML from both URLs, I thought that in this case, the value, you want to retrieve, can be retrieved from the JSON data included in the HTML. But unfortunately, the JSON data is large. So when IMPORTXML is used, an error occurs because of the data size. So in this answer, I would like to propose to use a custom function using Google Apps Script.

Sample script:

Please copy and paste the following Google Apps Script to the script editor of Google Spreadsheet. And, please put =SAMPLE("###url###") to a cell. By this, the value of followers is returned.

function SAMPLE(url) {
  const res = UrlFetchApp.fetch(url).getContentText();
  const v = res.replace(/&/g, "&").match(/Spotify\.Entity \=([\s\S\w]+?);/);
  return v && v.length == 2 ? JSON.parse(v[1].trim()).followers.total : "Value cannot be retrieved.";
}

Result:

When above script is used for your 2 URLs, the following result is obtained. In this case, the following custom formulas are put to the cells "A1" and "A2", respectively.

=SAMPLE("https://open.spotify.com/playlist/5aSO2lT7sVPKut6F9L6IAc")

=SAMPLE("https://open.spotify.com/playlist/7qvQVDnLe4asawpZqYhKMQ")

enter image description here

Note:

  • This sample script is for the URLs in your question. So when you tested it for other URLs, the script might not be able to used. And, when the structure of HTML is changed at the server side, the script might not be able to used. So please be careful this.

References:

user_71086530
Related