Google Sheets IMPORTXML – Fix ‘Meet Google Drive’ and ‘#N/A’ Errors

google sheetsgoogle-driveimportxml

I am trying to import a simple xml file into a new Google Sheet, via the importxml function:

importxml("https://drive.google.com/uc?id=1HqnptNBNn3yyjvaEVDNOpEWjegchwMpU", "//")

Google Sheet processes the importxml function. However, content was not pulled from the xml file. The Sheet cell showed some sort of html landing page content, starting with the words "Meet Google Drive – One place for all your files".

The next day I opened the same sheet without changing the importxml function. The result changed from "Meet Google Drive…" to "#N/A" with the new error message "Could not fetch URL".

I've tried a few things:

Copying the URL into a browser correctly triggered the target xml file download, as expected.
Varying the xpath consistently gave the same unwanted result
Varying the target file ID consistently gave the same unwanted result
Copying the target xml file to a Shared Drive and a My Drive folder consistently gave the same unwanted result.
Copying the importxml call from a sheet currently showing the "#N/A" result to a new sheet showed the original result of "Meet Google Drive…"
Adding the "export" parameter with "view" & "download" options; same unwanted result.

With export parameter, the function call becomes:

=importxml("https://drive.google.com/uc?export=view&id=1HqnptNBNn3yyjvaEVDNOpEWjegchwMpU", "/")

=importxml("https://drive.google.com/uc?export=download&id=1HqnptNBNn3yyjvaEVDNOpEWjegchwMpU", "/")

I also checked file access permissions; they showed the containing Sheet and the target file were both owned by the same Google account.

The account is low usage and so I doubt any quota limits have been breached (although I am unclear how to confirm quota consumed vs quota limit).

The file ID points to a simple xml file, stored under the same Google account. The xml file was drawn from a W3 Schools example. Its contents are:

<?xml version="1.0" encoding="UTF-8"?>
<note>
  <to>Tove</to>
  <from>Jani</from>
  <heading>Reminder</heading>
  <body>Don't forget me this weekend!</body>
</note>

What do I need to change, so that importxml() imports the xml?

Best Answer

Workaound solution: Edit the permissions for the source XML file. Change permissions so "Everybody" can read the file. ImportXML then works as expected.

Not a good solution, as the XML file is now exposed.

Related Solutions

XPath – Get Non-Distinct Parent Node Data for Each Child Node

As stated in my comment, doing this with =importXML and XPath seems tricky.

But doing it programmatically, using the XmlService of Google Apps Script, looks easier.

I have written the following:

function parseTvRageXml(url) {
   var result = [];
   var xml = UrlFetchApp.fetch(url).getContentText();
   var document = XmlService.parse(xml);
   var root = document.getRootElement();

   var show = document.getRootElement();
   var episodeList = show.getChild("Episodelist");
   var seasons = episodeList.getChildren("Season");
   for (var i = 0; i < seasons.length; i++) {
     var season = seasons[i];
     var seasonNum = season.getAttribute("no").getValue();
     var episodes = season.getChildren("episode");
     for (var j = 0; j < episodes.length; j++) {
       var episode = episodes[j];
       var resultRow = [];
       resultRow.push(seasonNum);
       resultRow.push(episode.getChild("epnum").getText());
       resultRow.push(episode.getChild("seasonnum").getText());
       resultRow.push(episode.getChild("airdate").getText());
       resultRow.push(episode.getChild("link").getText());
       resultRow.push(episode.getChild("title").getText());
       result.push(resultRow);                
     }
   }
  return result;
 }

In effect, it fetches the content from the given URL, parses it as XML, and iterates through the elements, extracting values of the elements you wanted, which is stored in a 2-dimensional array (result), and returned.

To use this, you must install the script in your spreadsheet. Click the Tools menu → Script editor, and paste the code listed above. Save the script.

In the cell where you want the output, enter

=parseTvRageXml("http://services.tvrage.com/feeds/episode_list.php?sid=3183")

The result should be a list of episodes, with the following columns:

Season number | epnum | seasonnum | airdate | link | title

I have set up an example spreadsheet to demonstrate this, feel free to copy it.

Blogger – Cannot Link to an Image from Google Drive

That is the link to the Google Drive image viewer, rather than a link to the image itself. If you check the URL for the image when you visit that link, you get an URL like:

blob:https%3A//drive.google.com/32efcf38-d7e6-4656-9046-eede4c4880f9

That's not useful. You need to do a little URL manipulation of your original URL to be able to hotlink it. You need to take your original URL, which looks like this:

https://drive.google.com/file/d/{file-id}/view?usp=sharing

And change it to this:

https://drive.google.com/uc?export=view&id={file-id}

So, in your specific case:

https://drive.google.com/uc?export=view&id=0B-NH9wCC0oM4U2x2WTZ3ZkJ1a1k

And now you should be able to see your image:

Of course, the "sharing" setting for the image must be set to "public on the web". (Which you've already done.)

This URL manipulation is completely undocumented in the Google Support documentation. As such, you are duly warned that this method may stop working. You might want to find a more stable image storage solution. Imgur is purpose-built for this sort of thing, but there are plenty of other alternatives.

Credit where credit is due: I found this solution on the Google Operating System blog, which in turn found it at this question on Stack Overflow: Displaying files (e.g. images) stored in Google Drive on a website.

Best Answer

Related Solutions

XPath – Get Non-Distinct Parent Node Data for Each Child Node

Blogger – Cannot Link to an Image from Google Drive

Related Topic