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
  • The URL is incomplete. See Inline image attachment on Gmail from GDrive, Insert image from Google Drive into Google Sheets
    – Rubén ♦
    Oct 5, 2020 at 22:10
  • Thanks Ruben. I tried adding the export parameter and saw the same problem. I tried a couple of variants. Here's the amended URLs I tried: =importxml("drive.google.com/…", "/") and =importxml("drive.google.com/…", "/")
    – james-h
    Oct 6, 2020 at 10:16
  • This hasn't formatted too well! I'll amend the original post.
    – james-h
    Oct 6, 2020 at 10:17
  • Is the file shared to anyone with the link?
    – Rubén ♦
    Oct 6, 2020 at 12:47
  • 1
    Interesting. When I opened up the access controls to "anybody can read", the importxml starts to work as expected. Even though target xml file & the sheet were both owned by the same account and living in the same Shared Drive folder.
    – james-h
    Oct 6, 2020 at 16:18

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 ♦
    Oct 31, 2020 at 23:04

Your Answer

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

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