title: Google Spreadsheet: ImportXML from Google Drive .txt
body: <p>So, I'm a bit of a noob with this things, but I really need to get this working to reduce my daily workload.</p>
  
  <p><strong>Goal:</strong></p>
  
  <p>In google spreadsheet, extract the content of <code>.txt</code> files uploaded on a Google Drive folder into a cell.</p>
  
  <p>I found an add-on for Google Spreadsheet that extracts the file and share link of all the files in a given Google Drive folder. It works fine and it should be the first step to automatize it.</p>
  
  <p>With the URL, now I need to use <code>=ImportXML</code> to draw the data from the link into a cell.
  It is a simple .txt file, here is a sample:</p>
  
  <p><a href="https://drive.google.com/file/d/1aS-AGcfs6nQYX3Mq6arm8K8BzuimAAES/view" rel="nofollow noreferrer">https://drive.google.com/file/d/1aS-AGcfs6nQYX3Mq6arm8K8BzuimAAES/view</a>
  Here is the element on the inspection view:
  <a href="https://i.stack.imgur.com/YSLqe.png" rel="nofollow noreferrer">drive-viewer-text-page</a>
  So far, the formula I tried is:</p>
  
  <pre><code>=importxml("https://drive.google.com/file/d/1aS-AGcfs6nQYX3Mq6arm8K8BzuimAAES/view?usp=sharing", "//body[text()='drive-viewer-text-page']/@*")
  </code></pre>
  
  <p>I also tried with <code>//div</code> and <code>//pre</code> instead of body, as well as inputting the whole path manually (the 10 divs included using a single "/").</p>
  
  <p>So far, I keep getting the empty content message with "#N/A" on the cell.
  I also tried with <code>//body[contains(text(), 'drive')]/@href</code> and <code>//body[contains(text(), 'drive')]/@*</code> with no positive results.</p>
  
  <p>Any help is welcome, maybe I should start with a simpler task haha.</p>
  
  <p>Thanks in advance.</p>
  
  <p>Best regards,
  Lucas.</p>
  
  <p><strong>Edit:</strong>
  I am pretty sure the content of the file must be getting retrieved from some other URL, the viewer is not the direct URL to the file, but I can't seem to find it. Any help with that is also welcomed... and I'll need to automatize it too.</p>