Skip to main content
Web Applications

Return to Revisions

3 of 4
added 27 characters in body
james-h
james-h
  • 31
  • 5

Google Sheets IMPORTXML shows ""Meet Google Drive" one day and "#N/A" the next

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?

james-h
  • 31
  • 5