2

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?

Improve this question
8

1 Answer 1

Reset to default
1

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.

Improve this answer
1
  • 1
    It's known that IMPORTXML is only able to get access to publicaly available files. If you keed to keep them private you could use UrlFetchApp Service from Google Apps Script.
    – Rubén - Volunteer Moderator - ♦
    Commented Oct 31, 2020 at 23:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.