在Google表格上使用JSON提取网址 [英] FetchingURL Using JSON on Google Sheets

问题描述

我正在尝试使用JSON提取Spotify播放列表上的关注者.似乎各种播放列表具有不同的HTML数据-使其变得复杂.

这是我当前在Google Apps脚本中使用的代码.(由@Tanaike提供)

  function SAMPLE(url){const res = UrlFetchApp.fetch(url).getContentText();const v = res.replace(/&/g，&").match(/Spotify\.Entity \ =([\ s \ S \ w] +?);/);/返回v&&v.length == 2?JSON.parse(v [1] .trim()).followers.total:值不能为检索到."；} 

然后我只在单元格中使用了= SAMPLE(链接)

但是，我在某些播放列表链接上遇到此错误(我不知道为什么):SyntaxError:JSON输入意外结束(第4行).

工作链接示例:

注意:

• 该示例脚本适用于您所询问的URL.因此，当您针对其他URL测试它时，该脚本可能无法使用.并且，当在服务器端更改HTML的结构时，该脚本可能无法使用.所以请注意这一点.

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.replace(/&/g, "&").match(/Spotify\.Entity \=([\s\S\w]+?);/);
  return v && v.length == 2 ? JSON.parse(v[1].trim()).followers.total : "Value cannot be 
retrieved.";
}

Then I just used =SAMPLE(the link) in the cells

However, I am getting this error on some playlist links (I don't know why): SyntaxError: Unexpected end of JSON input (line 4).

Examples of working links: https://open.spotify.com/playlist/5aSO2lT7sVPKut6F9L6IAc https://open.spotify.com/playlist/7qvQVDnLe4asawpZqYhKMQ

Examples of nonworking links (shows the error stated above) https://open.spotify.com/playlist/2Um96ZbAH1fFTHmAcpO50n https://open.spotify.com/playlist/68jtRFcWtaFNHKO5wYLBsk

About 80% of the links work (I have over 400). Any guidance or help would be greatly appreciated.

Thank you everyone!

解决方案

I believe your goal as follows.

• You want to retrieve the number of followers from the following URLs.

    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
  

Modification points:

• I think that in the current stage, all JSON data is retrieved and the data is parsed, and then, the value is retrieved. In this case, I thought that the specific characters might be included and those characters might occur the issue.

So in this answer, I would like to retrieve the part of value from JSON data and parsed it and returned value. The modified script is as follows.

Modified script:

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.";
}

Result:

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

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.

这篇关于在Google表格上使用JSON提取网址的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！