How to Import XML to Google Sheets

Extensible Markup Language (XML) is an information exchange format that allows you to store and share data between differing computer systems or programming languages. By using a format like XML, you can ensure the accuracy of data between systems, allowing for data interchange between business information systems and verifiable data through an XML schema. However, if you want to view data, make changes, or otherwise utilize the power of cloud-based spreadsheet software like Google Sheets, you will need to import that XML data to Google Sheets before you can do so. 

Learn about the process of importing XML to Google Sheets, its advantages, some limitations, and some common troubleshooting tips. 

How do you import XML data to Google Sheets?

It’s important to note that you can’t import XML data directly into Google Sheets without preparing it first. In other software like Microsoft Excel, you can use an XML schema file (.xsd) to map your data elements to cells in the workbook, which allows the software to then map your XML data file (.xml) to those cells. 

With that in mind, you can still get your XML data, whether it's from a web page or your own XML file, into Google Sheets using one of the three methods below. 

Method 1: Use the IMPORTXML function.

If the data you want to import into Google Sheets is from a public web page, then you can use the IMPORTXML function to get that data into your sheet. You can use this function if it’s publicly available data in HTML, TSV, CSV, XML, or RSS formats.

To call the function in Google Sheets, go into any empty cell and type: =IMPORTXML. You’ll need the following two components of this function:

• URL: The URL is the target URL of the publicly available data you want to import.

• xpath_query: The xpath_query is the actual data you want to retrieve from that web page.

In this example, you will import box office data for the top 200 films of 2024 from Box Office Mojo. 

1. To start, open a blank Google Sheet and navigate to the web page. 

2. Next, call the IMPORTXML function in the first cell of your spreadsheet using =IMPORTXML.

3. Put in the URL you want to reference. In the example above, it's https://www.boxofficemojo.com/year/2024/?ref_=bo_yl_table_2. Ensure it's wrapped in double quotes and insert a comma after the quotes, which should look like: 

4. Now, to find the actual data you want on the web page, use your browser's “inspect” tool to find the data component in the HTML.

If your data is in a table, this will often be the <td> or <tr> tag. In this example, you want the <tr> tag. 

5. Place the tag in the xpath_query section of the function, ensuring double quotes and a slash slash. (“//tr”).

6. Now, your function should look like this:

7. Hit “Enter” and let the data import. You might be prompted with a message from Google to allow import from an external website. Click “Allow,” and it should look like this:

You have successfully imported XML data into Google Sheets via the IMPORTXML function. You can now perform data cleaning and analysis. 

Troubleshooting IMPORTXML

It is possible to run into some issues using IMPORTXML, as it expects two input types in order to work. Below are some basic troubleshooting tips that can help if you run into issues using this tool:

1. Check that you are inputting your entire URL, including https:// and http:// in the URL. Any mismatch and IMPORTXML will not be able to read the site. 

2. Ensure that both your URL and your provided Xpath are wrapped in double quotes. 

3. Use the inspect tool. This allows you to click on portions of the web page and reveal them in the HTML of the site. Click through aspects of the table until you find the tag that best describes the data you want to import. This may take some trial and error. 

Related tasks: Importing JSON data vs. XML

Like XML, JSON is a data interchange format that allows for information exchange between computer systems and applications. While Google Sheets does not have a built-in IMPORTJSON function, you can add it to your profile through an extension called ImportJSON, which is free to install. It works similarly to the IMPORTXML function and allows you to import:

- A URL

- A file in your drive that has “anyone can access” permissions for all

- Any JSON data already in the spreadsheet

- cURL requests 

You call the function in the same way as IMPORTXML by using =IMPORTJSON (“URL”).

Method 2: Upload your own XML file via a CSV.

IMPORTXML is an excellent function you can use to import data from a publicly available website, but it does not allow you to import your own XML file. To do that, one method is to convert your file to a format Google Sheets can import, which, in this example, is CSV. Follow the steps below to do this. In this example, you can use the “books.xml” data from Microsoft, found here. 

1. Gather your XML data and ensure it’s valid with no formatting errors so that it converts to CSV cleanly. You can do this in any text editor of your choice. 

2. Convert the XML data to a CSV using either an online converter like ConvertCSV.com or codebeautify.org/xml-to-csv-converter, software like Microsoft Excel, or a custom script that converts your XML to CSV. 

3. Open a new Google Sheet and select “Import” from the “File” menu. Navigate to the location of your new CSV file and click “Upload.”

4. You can now navigate to where you want to import your data by selecting “create new sheet,” “insert new sheet(s),” “replace spreadsheet,” “replace current sheet,” “append to current sheet,” or “replace data in selected cell.” You can keep the selector at “detect automatically” or “comma for CSV.” For this example, select “insert new sheet” to import into the current document.

That’s it! Confirm that your data imported correctly, and you can do any data validation, cleaning, and analysis in Google Sheets.

Troubleshooting the CSV method

The CSV method is a great workaround for the failings of IMPORTXML in handling uploaded XML files to Google Drive. However, it may have some errors depending on the structure of your XML. Explore these tips if you are having issues:

1. If you’re having trouble getting your data to display properly, open your CSV to check for any extra commas, missing headers, or otherwise incomplete sections. Clean up any errors that you find in your CSV. You can also try importing the CSV into software like Excel, LibreOffice, or OpenOffice to see if it is just an issue with Google Sheets. 

2. If data is completely missing or wrong, you may want to compare your CSV to your XML to ensure everything is properly formatted, as the problem could lie in the structure of your XML. 

3. If you’ve verified that your data structures are good and it won’t import or is loading for a long time, the file may be too large for Google Sheets to import. Try breaking your data into multiple CSVs and importing them separately into the same sheet. 

Method 3: Use Google Apps Script for custom XML parsing.

The third method for importing XML into Google Sheets is similar to the second method in that you get to import any XML file, but it bypasses the need to convert your XML to CSV. It does require some basic knowledge of JavaScript in the Google Apps Scripting extension, but the way to use this method is as follows:

1. Using the books.xml from Microsoft, or the XML file of your choosing, upload it into your Google Drive. This allows you to get a shareable link to your file. Make sure you also adjust the sharing parameters of the file by selecting “anyone with link” and set them to “view.” Copy that link to the clipboard. 

2. Open a new spreadsheet where you want to import the books.xml data. Paste the file link you copied into any cell. Select just the file ID from the link and copy it to the clipboard: https://drive.google.com/file/d/1CBMkENRqDUugE7NrlBXphElwFAIc3RAV/view?usp=sharing.

3. You can paste the file ID into an empty cell to save for the next steps. You no longer need the full URL, but it doesn’t affect anything to leave it there. 

• Note: You can skip steps 2 and 3 if you just grab the file ID when prompted to insert it into your script, but this ensures you don’t forget it. 

4. Navigate to the “Extensions” and select “Apps Script.” 

5. Create a new script and name it as you would like.

6. You can delete any existing code that is pre-formatted. If you have experience working with Google Scripts, you can follow their documentation for XML Service classes to create your own script. However, below is a pre-written script that will handle the job for you: 

function xmlParser() {

 const fileId = "Your File ID"; //this is your file ID found in the URL

 const data = DriveApp.getFileById(fileId).getBlob().getDataAsString();

 const document = XmlService.parse(data);

 const root = document.getRootElement();

 const node = "book" //this is the tag of the first entry in your XML

 const entries = root.getChildren(node);

 const list = [];

 let headers = [];

 if (entries.length > 0) {

 const firstnode = entries[0].getChildren();

 firstnode.forEach(function (field) {

 headers.push(field.getName());

 });

 headers.push("id"); // Remove code if your xml does not have an id attribute

 list.push(headers); // Add headers to list

 }

 entries.forEach(function (node) {

 const subList = [];

 const fields = node.getChildren();

 fields.forEach(function (field) {

 subList.push(field.getText());

 });

 const idAttr = node.getAttribute("id"); //Remove code if your xml does not have an id attribute

 subList.push(idAttr ? idAttr.getValue() : ""); //Remove code if your xml does not have an id attribute

 list.push(subList);

 });

 writeToSheet(list);

}

function writeToSheet(list) {

 const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); //this is the spreadsheet you called the Apps Script extension from

 sheet.clear(); //clears the active spreadsheet

 const numCols = list[0].length;

 const fixedList = list.map(row => {

 const newRow = row.slice(); // copy

 while (newRow.length < numCols) newRow.push("");

 if (newRow.length > numCols) newRow.length = numCols;

 return newRow;

 });

 const range = sheet.getRange(1, 1, fixedList.length, numCols);

 range.setValues(fixedList);

}

7. Input your File ID from the sheet in the part of the code that reads “Your File ID.” It should look as follows:

8. Input the name of the first tag after the root of your XML. In the books.xml example, this is “book”:

9. In the script menu, select “Run,” which will bring up a series of menus asking if you give Google Apps Scripts permission to run this script. After you authorize it through your Google Account, the script will finish. 

10. Navigate back to the tab with your Google Sheet, and you will see that the XML file is populated into the sheet. 

• Note: Ensure that it clears the sheet of any existing data, which is why keeping the file URL and link ID there before didn’t affect the process. You can prevent this by removing the sheet.clear() function.

Troubleshooting the App Scripts method

Troubleshooting App Scripts is most effective if you have an understanding of JavaScript, as that is the language being used. However, if you are trying to use the script, you will want to ensure the following:

1. You must use the file ID and not the URL, as Apps Scripts cannot get files from their URL while in your Google Drive. 

2. Make sure you replace the proper XML elements in the script with the XML elements from your file structure. Do this by replacing the const node = the first element in your XML structure; otherwise, the script will not execute. 

Summary of steps to import XML to Google Sheets

To import XML into Google Sheets, you can use one of the following methods:

1. Use the IMPORTXML function for any publicly available data set.

2. Convert your XML to a CSV file, then import the CSV.

3. Use an Apps Script to pull the data from an XML file in your Google Drive.

Learn more about data analysis with Coursera.

Importing XML data to Google Sheets allows you to clean, sort, and further analyze the data via the Google Drive platform. If you want to gain in-demand skills as a data analyst, you can try the IBM Data Analytics with Excel and R Professional Certificate or the Microsoft Power BI Data Analyst Professional Certificate, both on Coursera, where you can get experience in extracting insights for businesses from data.