Templated Export for Google Spreadsheets

Templated Export allows users to reshape and selectively publish data from Google Spreadsheets in custom formats. The tool is powered by Google App Script which allows integration with Google Spreadsheets using existing authenticated access. Example uses include: turning data from a spreadsheet into a RSS feed and publishing data from a spreadsheet in JSON format to be used by other tools/services. The framework for creating a custom wrapper for your data means there are many more opportunities.

Templated Export

Main features

  • Cloud based
  • Selective publication of data (down to individual cells)
  • Publication of data in different file types (text, atom, rss, xml, json)
  • Complete customisation of output file structure

Setup

  1. Open Templated Export for Google Spreadsheets
  2. File > Make a copy of the script
  3. File > Manage Versions and enter a description and Save New Version
  4. Run > onInstall twice (first time to authorise, second to fun the function)
  5. Publish > Deploy as web app... and click Update and open the 'Current web app URL' or click on the 'latest code' link if available. Bookmark this link (there are many tutorials for turning chrome bookmarks into applications)

Example usage

Generating a custom RSS Feed of data from a Google Spreadsheet

Publishing data from other sources like Guardian Datastore

Selectively publishing cell ranges as JSON

Some example templates

If you download this JSON file it contains the templates used in the demonstrations above. Use the Local Template: Backup/Restore button in Templated Export to install them.

Comments/suggestions welcome

I make the usual warranties that I'm not a professional programmer and I'm already aware of some niggly bugs, but comments and suggestions are always welcome. If there is enough interest in this tool I'm happy to put it on a community coding site or donate it to someone who actually knows what they are doing ;)

41 thoughts on “Templated Export for Google Spreadsheets

  1. Pingback:

  2. Joris Steurs

    Very interesting! Does this approach allows you to push the submissions from my google form to my local Excel sheet?

    Reply
  3. Martin,

    Having a bit of trouble getting off the ground with this. I've tried on a couple of GApps domains, have flushed out any previous instance of the script and retried, but each time I get to run the script the second time and see this message "ReferenceError: "DOCNAME" is not defined. (line 54)"

    Is it something simple/idiotic? Please don't spend more than 10 seconds on this - I'm sure I'll figure it out eventually...

    Reply
  4. nheilmann

    Hey Martin,

    Thanks for taking us step by step through the setup.

    Sadly, I've run into the same problem as Mark above.

    Any help would be much appreciated.

    Reply
    1. nheilmann

      OK got it to work but my resulting feed won't validate:
      https://script.google.com/a/macros/isp.cz/s/AKfycbzMUGHawJaTS6_7bOhDjYuWD-UWJrO1pN1nh7dPVOpTdK0pHWW9/exec?template=S12767461300u12768614302&download=true

      http://validator.w3.org says "It looks like this is a web page, not a feed."

      When I put the link URL into the browser it looks like an RSS feed should but the validator shows something completely different. This feed doesn't work with any reader I have either.

      This is a bummer because I think this could be a really great tool with tons of uses.

      Reply
      1. Post author

        Hi - I tried your link but the response was it did not exist. One other thing I noticed was the url you used was an export url which forces html download. Try using the publish url instead which should validate as xml

        If it's still not working get mack in touch

        Martin

        Reply
  5. Jacob

    Hi, I believe the problem nheilmann was having (and the problem I'm having now) is related to authentication. My employer uses Google Apps for Education, and no matter what I do I can't seem to prevent the feed URL from redirecting to web login via Shib. This is why we are seeing a nice valid feed in the browser (already authenticated) but feed readers and validators just get redirected - not very useful behavior for a feed.

    I've tried changing the access settings in both the script and the spreadsheet, making everything as public as possible, but to no avail. Any ideas?

    Reply
  6. I've just tried for the first time, from scratch, and I got it working without needing login, from incognito window in Chrome and using private browsing in Firefox, so Jacob you must have done something wrong.

    However the feed doesn't use content-type text/xml, perhaps because Google have added more layers of security and redirection?

    I can get my feed working in Google Reader.

    Reply
  7. Joe

    I am following the setup directions exactly and get to the 2d run onintall on step 4 and get this error: ReferenceError: "TemplatedExport" is not defined. (line 43)

    This seems to be perfect for my needs, too bad I can't get past this error.

    This must just be for developers and not day to day users I guess.

    Reply
      1. Joe

        ah, okay, I see what I did wrong, the first time I copy/pasted the code, I reread the instructions to Make a copy and it looks like ti's there. But I get the same error as user below about docname

        Reply
  8. Hey there, I am also having a problem with the DOCNAME not being defined. Is there a solution for this?

    Great & useful script though (from what I can see).

    Reply
  9. JAM

    It appears the issue is that DOCNAME and TEMPLATENAME are meant to be strings that the user selects.
    For example, changing these to "mydocname" and "mytemplatename" (each in quotes) should get the app to run.

    Reply
  10. Pingback:

  11. Sam

    Can you explain how the "TemplatedExport" object works and where is it defined ?

    Are you hiding it ? if So how did you do ?

    Reply
      1. Sam

        Yes I've managed to figure out it was a managed library. Really cool stuff this Google App Script. And thanks for your quick answer. Your Doing amazing work here !

        Reply
  12. Trevor Cunningham (@chalkjockey)

    Great script, exactly the type of thing I'm looking for. I'm trying to generate an RSS feed from a Google Spreadsheet to load into Tagxedo to make a word cloud. Google seems to have discontinued the publish as RSS feature and this script fits the bill perfectly. I had a few snags along the way. The first repeats the most consistent error (DOCNAME) mentioned above. Changing to MYDOCNAME fixed that. It's really easy to set up, and if you understand html, the template is a breeze. But I am now stuck on the feed validating. I've made the spreadsheet public, and that doesn't make a difference. The publish URL function seems to default to Private...can't get Published to stick...is this an issue?

    Bottom line, I've got a feed ready to go, but need to know what to do in order for a reader to take it.

    https://script.google.com/a/macros/aisr.org/s/AKfycby2FRvkCU0Yy48-altmopXO0-itCjOXLypAijDGGMsx5yafG4z_/exec?template=S13608932225u13609060141&download=false

    Reply
    1. Post author

      Hi Trevor, I tried accessing the link and it wasn't found. When you published as a service (Tools > Script Editor then think publish menu) did you allow anyone to access even anonymously? Some apps domain configuration prevent publication outside of the org

      Reply
      1. Trevor Cunningham (@chalkjockey)

        Well, I'm back at it again and keep running into the same problem. I am using an organizational Google domain, but everything I'm doing is in house. No matter the combination, the published URL will not be recognized as a feed. Here's the most current effort: https://script.google.com/a/macros/aisr.org/s/AKfycbw6YAOG7VLzTwJdpJezpjyuApZxFPm9u_bmu6sVtc4a1OA0zq8l/exec?template=S316206015830u316186598647&download=false

        No idea why this isn't working.

        Reply
  13. Dar

    Hi Martin,

    I've watched your videos and read the documentation - this looks ideal for what I want to do and thanks for taking the time to write it all up.

    However, I cannot get past the same issue that many above have raised:

    ReferenceError: "MYDOCNAME" is not defined. (line 54, file "Code")

    Even if I change the MYDOCNAME to something else the error persists.

    Could you please address this issue? You've taken great care to walk us through the other steps that it's a shame something so trivial is prevent us from getting past this.

    Many thanks,
    Dar.

    Reply
      1. Dar

        Hello again Martin,

        That's fantastic, thank you so much for doing that. I'm up and running now. I'm carefully examining your mashActivityRSS file in your video now to see what I have to customise for my own spreadsheet. It's looking very promising though.

        Cheers,
        Dar.

        Reply
  14. Dar

    Hello Martin,

    I think I'm 99% there but there appears to be an issue with my feed. Here it is:

    https://script.google.com/macros/s/AKfycbzX9FOtXLjTH5_ZFPMPaSRDwDmGPJAJ2SkTYgRN_eVjtfbfy7yf/exec?template=S14069818285u14068754955&download=false

    The problem, I think, is the Content-Type, it should be application/xml, but I'm currently returning text/html. As such, my feed is not being processed by my reader.

    Is there somewhere I can modify this?

    Feel free at any time to tell me to go work this out for myself!

    Thanks,
    Dar.

    Reply
  15. Dar

    Hi Martin,

    FYI, here is a screenshot of my template: http://tinypic.com/view.php?pic=30bcpbq&s=5

    I've tried using both Google Reader and Feedly - both just return an empty page.
    (Ultimately I'll be sending this feed to the Terminal Four CMS, but these Readers are a quick way to check that its working.)

    Also, I notice on my mac that Chrome (v27) shows the file OK, but Firefox (v21) just shows the empty page.

    Someone told me the issue was to do with the content-type and that it should be application/xml. I've tried toggling between XML & RSS in your template, but no joy.

    I feel like I'm so close!

    Dar.

    Reply
  16. First off, fantastic tool! I looked for several way to export my google spreadsheet to XML or JSON and yours was the best I came across. One of the problems I needed to overcome was to customize how the spreadsheet data was formatted, and the templates, with the prefix, row template, row separator, and suffix were exactly the control I needed.

    My only small issue I had was with using the Load/Save Template. No matter what I do, I can't seem to load a template. I tried downloading your template and restoring it. I can see the templates in the drop-down, but selecting them doesn't load a template. I tried saving my own templates, and there's data in the .JSON template file if I download it, but again, when I restore the data and try to Load it by selecting it from the drop-down, nothing happens. Any ideas?

    Reply
    1. Interesting thing, is that I used another Google account that I have to access the published app. The other Google account was indeed able to load the templates.

      Reply
        1. I'm unsure. My Google accounts are the free ones, not a paid business Google App account. The 1st Google account which I used to deploy the app cannot load the templates, but the 2nd Google account that I have, which is in no way connected to the 1st account, can load the template.

          What I also tested, was to use that 2nd Google account to deploy the app. The Load/Save Template feature does work as it should, so there must be some issue with my 1st Google account.

          Reply
          1. Sorry, it feels like I'm spamming your comment section, but I have isolated the issue. It seems to be related to the browser. It works on Safari, but not on Chrome. So it's either an issue with Chrome itself or with my settings.

            The reason I had thought it was related to my Google accounts was because I use a different browser for each account.

            In any case, my chrome is the most up-to-date. I tried adjusting settings, but couldn't seem to do anything to make it work, but at least I now know it's a browser issue. Thanks for all your help in this!

          2. Post author

            Hi Greg - not at all your feedback is very useful. Odd it's not working in Chrome. The script uses a watered down version of GWT so usually it's fairly bullet proof. I don't have a mac so hard for me to chase. The interface may be in the need of a overhaul as the script services it uses appear to be at risk.

  17. Steve Magoulas

    Excellent information and what a great tool! Thanks so much Martin for making that public.
    In just 10 minutes I managed to publish my first (totaly customized) RSS feed.

    I have only one question, and that is about caching. My source of data is updated frequently and I want to get these updates almost in real time. I heard that Google Docs caches results for about 2 hours, but one can bypass that adding to the request a random string like ...?time=RandomString.

    Could I apply this technique using Templated Export or not?

    Thanks again for making this great script. It can have so many uses.

    Reply
  18. Thank you so much Martin - this has saved hours of work and opened up some interesting possibilities. Making sure all the permissions are granted seems to be the key to easy use so the first video was very useful.

    Reply
  19. James

    I need to output a literal curly brace immediately next to a cell value, but this results in an error.

    e.g. I need {{{cell['name']}}} -> {thisName}

    Is it possible to escape the leading brackets or something?

    Reply

Leave a Reply