Google表格-ImportXML函数.接收错误? [英] Google Sheets -- ImportXML function. Receiving Error?

问题描述

我正在尝试在Google表格上使用IMPORTXML函数.

I'm trying to use the IMPORTXML function on Google Sheets.

例如:

=IMPORTXML("https://www.tiktok.com/@charlidamelio?lang=en", XMLPATH) 

应返回"72.6M"

should return "72.6M"

我使用Chrome检查器复制了xpath，这给了我:

I used the Chrome inspector to copy the xpath, which gives me:

/html/body/div[1]/div/div[2]/div/div[1]/div/header/h2[1]/strong[2]

当我在Google表格中尝试此操作时，它返回错误:#N/A(导入内容为空).

When I try this in Google Sheets it returns an error: #N/A (Import Content is Empty).

P.S.我愿意采用其他方式将所需的数据获取到Google工作表中，而不必使用IMPORTXML函数.

P.S. I'm open to other ways to get the data I need into the google sheet, it doesn't have to use the IMPORTXML function.

不久前我问了这个问题，有人给了我以下解决方案:

I asked this question a while ago and someone gave me the following solution:

=REGEXEXTRACT(IMPORTXML(C2,"//script[@id='__NEXT_DATA__']"),"followerCount"":(\d+)")

这工作了好一阵子，但此后就停止了工作.

This worked great for a while but has since stopped working.

推荐答案

问题和解决方法:

我再次检查了提案.这样，我可以理解2020-07-21之后HTML数据已更改，因为当我在2020-07-21上发布此数据时，我可以确认xpath起作用.现在，通过此更改，我确认我的提案无法再使用.

Issue and workaround:

I checked my proposal again. By this, I could understand that the HTML data had been changed after 2020-07-21, because when I posted this at 2020-07-21, I could confirm the xpath worked. By this change, now, I confirmed that my this proposal cannot be used anymore.

因此，为了检索您期望的值，作为当前的解决方法，我建议使用Google Apps脚本.在当前阶段，似乎在使用Google Apps脚本时，可以检索该值.

So in order to retrieve the value you expect, as the current workaround, I would like to propose to use Google Apps Script. In the current stage, it seems that when Google Apps Script is used, the value can be retrieved.

1. 打开Goog​​le Spreadsheet的脚本编辑器.

1. Open the script editor of the Google Spreadsheet.

将以下脚本复制并粘贴到脚本编辑器中，然后保存脚本.

Copy and paste the following script to the script editor and save the script.

 function SAMPLE() {
   var url = "https://www.tiktok.com/@charlidamelio?lang=en";
   return UrlFetchApp
     .fetch(url)
     .getContentText()
     .match(/<meta name\="description"[\s\S\w]+?>/)[0]
     .match(/([.\w]+?) Fans/)[1];
 }

返回Google Spreadsheet，将=SAMPLE()放到单元格中.

Back to Google Spreadsheet, put =SAMPLE() to a cell.

• 此功能用作自定义功能.

通过此流程，将检索到值.

By this flow, the value is retrieved.

• 我不确定可以使用多长时间.更改HTML结构后，可能无法使用此解决方法.请注意这一点.
• 将此脚本用于其他URL时，可能无法使用此替代方法.请注意这一点.

• Google表格中的自定义功能
• UrlFetchApp类

• Custom Functions in Google Sheets
• Class UrlFetchApp

这篇关于Google表格-ImportXML函数.接收错误?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！