FetchingURL Using JSON on Google Sheets (from Spotify)

user_15142423 0
Viewed 50

I am attempting to use JSON to extract followers on Spotify Playlists. It seems like various playlists have different HTML data - which makes it complicated.

Here's the code I have currently in Google Apps Scripts. (courtesy of @Tanaike)

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

This worked for about a year, but the HTML specification keeps changing. Can anyone help? Thank you!

google-apps-script google-sheets html json
1 Answers

When I saw the URL as follows.

https://open.spotify.com/playlist/5aSO2lT7sVPKut6F9L6IAc
https://open.spotify.com/playlist/7qvQVDnLe4asawpZqYhKMQ
https://open.spotify.com/playlist/2Um96ZbAH1fFTHmAcpO50n
https://open.spotify.com/playlist/68jtRFcWtaFNHKO5wYLBsk
https://open.spotify.com/playlist/2phIYXF2hAd5gTj00IwXbU
https://open.spotify.com/playlist/7MBKSFzpPLQ9ryPtydXVwf
https://open.spotify.com/playlist/148My4xA7WoEaNDmnl2A8Z
https://open.spotify.com/playlist/4zMaYNXz2pP1OPGz9SIJhX
https://open.spotify.com/playlist/2hBohCkXzjzTLh6jtd7gUZ

It seems that the object is converted to the base64 data. So in this case, how about the following modified script?

Modified script:

function SAMPLE(url) {
  const res = UrlFetchApp.fetch(url).getContentText();
  const data = res.match(/id\="initial-state">([\s\S\w]+?)<\/script/);
  if (!data || data.length == 0) return "Value cannot be retrieved.";
  const str = Utilities.newBlob(Utilities.base64Decode(data[1])).getDataAsString();
  const v = str.match(/"followers":({[\s\S\w]+?})/);
  return v && v.length == 2 ? JSON.parse(v[1].trim()).total : "Value cannot be retrieved.";
}

Result:

When this script is used for the above URLs, the following result is obtained.

enter image description here

Note:

  • This sample script is for the above URLs. So when you tested it for other URLs, the script might not be able to use. And, when the structure of HTML is changed at the server-side, the script might not be able to use. So please be careful about this.
user_71086530
Related