How to use importXML function with a file from Google Drive?

Question

I have an .xml file stored in Drive and would like to extract information using importXML(). However, it doesn't work when it's a sharable link is from Drive.

Answer 1

I think that there are 2 patterns.

1. Read directly XML files from Google Drive

In this case, all XML files which are used on Spreadsheet have to be shared as ANYONE_WITH_LINK and VIEW. The URL of the shared file is https://drive.google.com/uc?id=### file id ###&export=download. So the formula is as follows.

=importXML("https://drive.google.com/uc?id=### file id ###&export=download","### XPath query ###")

2. Read indirectly XML files using Web Apps

In this case, the XML files which are used on Spreadsheet are not required to share, because Web Apps is deployed. But GAS is used for this case. The install of this method is as follows.

1. Copy and paste following script on your script editor.

function doGet(e) {
  var xml = DriveApp.getFileById(e.parameters.id).getBlob().getDataAsString();
  return ContentService.createTextOutput(xml).setMimeType(ContentService.MimeType.XML);
}

2. Deploy Web Apps

• On the Script Editor
  • File
  • -> Manage Versions
  • -> Save New Version
  • Publish
  • -> Deploy as Web App
  • -> At Execute the app as, select "your account"
  • -> At Who has access to the app, select "Anyone, even anonymous"
  • -> Click "Deploy"
  • -> Copy "Current web app URL"
  • -> Click "OK"

The copied Current web app URL is used for importXML(). So the formula on Spreadsheet is as follows.

=importXML("https://script.google.com/macros/s/#####/exec?id=### file id ###","### XPath query ###")

If these were not useful for you, I'm sorry.