Google Sheets — ImportXML function. Receiving Error?

0
votes

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

For example:

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

should return "72.6M"

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]

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

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.

1
xmlgoogle-sheetsxml-parsinggoogle-sheets-importxml
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, I could understand that my this proposal cannot be used anymore. I deeply apologize the the HTML was changed. So, now, I have to delete my answer for this question. Because it is required to find the other method. For this, how about using Google Apps Script? When the script is used, it seems that the value can be retrieved. After I got your replying, I would like to prepare it. - Tanaike
I am definitely open to using a Google Apps Script, I just may need some insight into how to install it into the Google Sheet. Tanaike, I truly appreciate all of your help with this matter. You are an angel. - ConfuciusCat
Thank you for replying. From your replying, I proposed the method for using Google Apps Script as the current workaround. Could you please confirm it? If that was not the direction you expect, I apologize. - Tanaike
Did my answer show you the result what you want? Would you please tell me about it? That is also useful for me to study. If this works, other people who have the same issue with you can also base your question as a question which can be solved. If you have issues for my answer yet, I apologize. At that time, can I ask you about your current situation? I would like to study to solve your issues. - Tanaike

1 Answers

0
votes

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.

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.

Usage:

  1. Open the script editor of the Google Spreadsheet.

  2. 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];
 }

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

    • This function is used as the custom function.

By this flow, the value is retrieved.

Note:

  • I'm not sure about how long this can be used. When the HTML structure was changed, this workaround might not be able to be used. Please be careful this.
  • When you use this script for other URL, this workaround might not be able to be used. Please be also careful this.

References: