Close
Simone Seagle

Simone Seagle

Independent Web and Educational Software Developer

Read More

Search

Prev Next
Turn your Google Spreadsheet into XML!
Blog

Turn your Google Spreadsheet into XML!

February 23, 2017 Simone Seagle

Now this is something that I had done about a year ago for my Element Factory project. Bresdin and I made a nice fancy google spreadsheet with all of the information about each element and each compound for the game. The idea was that it would be easy to update for the client. Even though the information on the internet is kind of patchy about this, YES - you can turn your Google Spreadsheet into an XML document without the use of any plugins. This is how you do it!

  1. You have to publish it to the web - if it contains sensitive information, just unpublish it as soon as you're done. Or figure out some other solution.

    Publish to the web

  2. Figure out your spreadsheet ID. You can find it in the URL while you're working:

    https://docs.google.com/spreadsheets/d/*THIS IS YOUR SPREADSHEET ID HERE*/edit#gid=0
  3. In order to get the content of your primary worksheet as an XML document, type this in your browser:

    https://spreadsheets.google.com/feeds/list/*SPREADSHEET-ID*/od6/public/values

    Now you'll see something like this: Publish to the web

    Go ahead and copy/paste all that into a nice new XML document.

    You're already done if you only have one worksheet in that spreadsheet, but if you have more, you need to do a couple more steps:

  4. Copy this into your browser:

    https://spreadsheets.google.com/feeds/worksheets/*SPREADSHEET-ID*/public/full

    You'll wind up with a really ugly XML document that has a list of all the sheets in that spreadsheet.

  5. Search the document for the name of your second sheet. When you find it, you'll see an "id" attribute tag right before your "title" tag. The "id" tag will look like this:

    <id>https://spreadsheets.google.com/feeds/worksheets/*SPREADSHEET-ID*/public/full/*SHEET-ID*</id>

    My sheet ID is 'ov13bg2'. Another one I did was 'o3j2em8'. They always look kind of like that. You get the idea.

  6. Now for all subsequent sheets, you type the following into your address bar:

    https://spreadsheets.google.com/feeds/list/*SPREADSHEET-ID*/*SHEET-ID*/public/values

Enjoy! Hope that helps someone.