Templated Export for Google Spreadsheets

Update 16/01/14: One of the services originally used in this project has been deprecated by Google. The code has been updated to fix this. If you are using an existing version of Templated Export and would like to upgrade keeping your existing settings please follow the instructions in this document (comments are open if any step is unclear).

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 ;)

63 Comments



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

    Reply

  2. 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

  3. 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. 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. 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

  4. 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

  5. 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

  6. 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. Sounds like the managed library isn’t registering properly. In the script editor when you open Resource > Managed Libraries … does it include TemplatedExport ?

      Reply

      1. 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

  7. 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

  8. 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


  9. Very nice tool. Thanks for doing the vids and blog post. I had a couple false starts, but i got it working!

    — jack lail

    Reply

  10. 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. 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

  11. 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. 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. 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

  12. 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. Hi Dar,

      Finally fixed this bug. If you take a fresh copy of the template and follow the instructions it should work

      Martin

      Reply

      1. 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

  13. 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

  14. 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

  15. 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. There are sometimes domain restrictions on Google Apps accounts. Is this possible in your scenario or is the main account another Google account?

        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. 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.


  16. 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

  17. Hi Martin and Thank You very much to have sharing this… Very very very usefull for me. Thank You again !

    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. 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

  20. Thank you very much for the brilliant work. I have successfully created a rss feed that can be used by feed readers. Upon importing the published url all the feeds populate the reader as expected. My problem is that the feed does not update and generate new feeds with the spreadsheet even if the spreadsheet is left open. I have tested this with several readers. Any thoughts would be greatly appreciated. Thank you.

    Reply

  21. Hi Martin.
    Many thanks to your great tool. It’s been my best co-worker for 2 years.
    Buy sadly Google has disabled the ScriptDB since last month. Do you have plan to migrate our belove tool to other technique to keep it alive ?

    Regard

    Reply

    1. ahh forgot about this project using ScriptDb. I had no idea anyone was using it. I’ll see if there is a quick fix. In the meantime I recommend exporting you templates

      Reply

      1. Just ran into the same problem, having only just discovered this great tool. I was hoping to publish spreadsheet updates as an RSS – do you know of any other methods to achieve the same result, or any clues as to how you were doing it?

        Many thanks, hope you manage to find an easy fix!

        Reply

      2. Could we just have a quick fix by using the manual method: save and load template from local/gdrive file ? I think it’s even better since now we know where our precious templates are stored.
        1 day without the tool make our life so hard, Martin ;((

        Reply

      3. Just update following your instruction and the tool works well again. I got “something went wrong” when try to restore the backup file and some old templates are missing. But that’s still okey.
        Thanks so much, Martin.

        Reply

  22. One of the services originally used in this project has been deprecated by Google. The code has been updated to fix this. If you are using an existing version of Templated Export and would like to upgrade keeping your existing settings please follow the instructions in this document (comments are open if any step is unclear).

    Reply

  23. Hi Martin,
    Thank you for the awesome tool! I’ve been using it to create KML files. It was working on Jan 18th, but I tried it today and I get the following error:

    ReferenceError: “ScriptDb” is not defined. (line 43, file “Code”)

    Is there anything I can do to get it working?

    Reply

    1. ScriptDb looks like it’s completely toast now. In the Script Editor comment out (by adding // at the start of the line so that it becomes //TemplatedExport.setDb(ScriptDb.getMyDb());) should fix it. If you didn’t export your templates I’m afraid you might have to redo them.

      Reply

  24. Hi Martin!
    Thank you very much for the great work. I’m trying to install for the first time the Templated Export but I also get the error [ReferenceError: “ScriptDb” is not defined]
    You say the code has been fixed to solve this issue, but the code I’m getting when I follow the link in your setup instructions looks to still have the issue. Is there an updated version?
    Thanks!

    Reply

    1. Ah looks like Google have completely removed ScriptDb now. You can fix by commenting out line 43 (by adding // at the start of the line) so that it becomes //TemplatedExport.setDb(ScriptDb.getMyDb()); or take a fresh copy of the script.
      Thanks – Martin

      Reply

  25. Hi Martin,
    commenting of the row
    //TemplatedExport.setDb(ScriptDb.getMyDb());

    doesn’t help. Still have the same error as other guys:
    ReferenceError: “ScriptDb” is not defined….

    Reply

  26. Excellent tool.. Thanks a lot

    Only one question, for whatever I am using this for it doesn’t recoginize the published link since it doesn’t have the file extension .XML

    please suggest a fix.

    Thanks

    Reply

    1. published urls don’t need extensions and the content type is included in the header information (the server requesting the url is told what the content type is). If exporting a file for download the correct extension is added

      Reply

  27. Hi Martin,

    I was looking for a tool to create a XML file with a specific structure from a Google Spreadsheet, I think your tool could be the perfect bet for this.

    Unfortunarely when I try to run the script, I get this message:

    “Your one shot authorisation state is not valid. The scripts must be reauthorised. The script’s reauthorisation can be triggered by calling ScriptApp.invalidateAuth() and rerunning the script.”

    I followed your instructions but it seems I’m missing something.

    Thanks

    Reply

  28. Fantastic and interesting article.
    I used the code to convert a google spreadsheet into an XML file. Like others, I found a bit of a learning curve, but reading through the comments helped me to debug. Thanks for the time you are taking to answer peoples questions and for the original share.

    FYI:
    I am using a google spreadsheet to manage (create, prioritize and visulize) windows 8 custom speech vocabularies and am using your script to export the custom WSR voice commands file. The spreadsheet allows me to consider how I am growing my computers vocabulary to increase recognition.
    Cheers!

    Reply

  29. Hello Martin thanks for sharing this! really.
    I’m getting the same error as Simeon and Thomas seems like there is a problem with the authorisation:

    svc.enable(svc.Restriction.ALL);

    Method Service.enable is deprecated.Collapse
    File: Code Line: 63
    The method has been marked as deprecated which means that the feature should be avoided and may be removed in the future. Consider using an alternative solution.

    Any help on this will be much appreciated.

    Thanks

    Reply

    1. Sorry, working now.. I just had to deauthorize
      ScriptApp.invalidateAuth();

      and authorize again.. and now everything working fine.

      Reply

      1. I’m running into the same issue you guys had… where did you put that snippet of code to make it work? I’ve tried everything I can think of, and I can get it to re-auth, but the same error keeps popping up. Thanks!

        Reply

        1. I’m in the same situation, I tried to insert ScriptApp.invalidateAuth(); in line 63 and comment the other function and I was able to deauthorize an authorize again the app but it still not working.

          Is anyone still able to use this script?

          Reply

Leave a Reply

Your email address will not be published. Required fields are marked *