Search code examples
xpathgoogle-apps-scriptgoogle-sheetscustom-functiongoogle-sheets-formula

Google Spreadsheet "Document Name" ImportXML XPath Query

I want to write an ImportXML function in a Google Spreadsheet to return the document name of the same spreadsheet. For example, my spreadsheet is titled "Kimchi". I want' to return that name in cell "A1" to automate a series of functions within the spreadsheet based on the document name. I'm too lazy to type the value into the cell for each of the hundred or so spreadsheets I'll copy from the original template and rename.

I can't seem to nail a correct query structure.

This bit of XML looked promising, but I can't seem to get the query to pull it:

<span class="docs-title" id="docs-title" role="button"><div class="docs-title-inner" id="docs-title-inner">kimchi</div></span>

I've tried so far...

=ImportXML("SOME URL HERE", "//div[@class=’docs-title-inner’]/@content")

It returns...

Error: Imported Xml content can not be parsed.

I've tried all kinds of variations, some probably equally poorly formed. Following is some of the XML structure that looked juicy:

<html>
  <head>
     <title>kimchi - Google Sheets</title>

But this XPath query within the ImportXML function didn't work either

=ImportXML("SOME URL HERE", "/html/head/title")

It returned...

Error: Import Internal Error.

I'm stumpted.

Here's the spreadsheet with variations.

PS This ended up working after I shared the document with the world:

=ImportXml("THE URL", "//meta[@itemprop='name']/@content")
Solution

  • You dont have to do any of that. Go to tools-> script editor -> blank project

    replace the contents of the edit window with the code below:

    function BookName() {
  return SpreadsheetApp.getActiveSpreadsheet().getName();
}

    Ctrl-S, put BookName in the name box, click ok, wait for the yellow "saving" bar to dissapear. Close the tab with the code editor.

    In your sheet you can now simply type =BookName() and the cell will display the workbook title.