⌙ Featured , Google , Google Apps Script , Google Sheets , Templated Export

Templated Export for Google Spreadsheets

Martin Hawksey

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 😉

chevron_left
chevron_right

Join the conversation

comment 66 comments

  • Templated Export for Google Spreadsheets using Google Apps Script | Google Apps Script | Scoop.it

    […] 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.   Check out the post for installation instructions and demonstration videos   […]

  • Joris Steurs

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

  • Mark Allen

    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…

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

  • 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?

  • Ian Weatherhogg

    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.

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

    • Martin Hawksey

      Sounds like the managed library isn’t registering properly. In the script editor when you open Resource > Managed Libraries … does it include TemplatedExport ?

      • 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

  • Sam

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

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

  • Comment spammer Twitter

    […] IFTTT ne sait pas encore utiliser des tableaux Google Drive pour déclencher des actions. Il faut transformer le tableau en fil RSS. Je n’ai pas trouvé comment créer un fil propre directement avec Google, j’ai utilisé une WebApp : Templated Export for Google Spreadsheets. […]

  • jack lail

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

  • Sam

    Can you explain how the “TemplatedExport” object works and where is it defined ?
    Are you hiding it ? if So how did you do ?

      • 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 !

  • 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

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

    • Martin Hawksey

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

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

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

    • Martin Hawksey

      Hi Dar, What reader are you using?

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

  • Greg Lam

    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?

    • Martin Hawksey

      Hi – do any messages display when you try and load a template?

    • Greg Lam

      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.

      • Martin Hawksey

        There are sometimes domain restrictions on Google Apps accounts. Is this possible in your scenario or is the main account another Google account?

        • Greg Lam

          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.

          • Greg Lam

            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!

          • Martin Hawksey

            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.

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

  • Philippe Reaud

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

  • Simon Thompson

    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.

  • 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?

  • Stev

    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.

  • Huy Tran Quang

    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

    • Martin Hawksey

      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

      • Andi Northrop

        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!

      • Huy Tran Quang

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

      • Huy Tran Quang

        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.

  • Martin Hawksey

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

  • Paul

    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?

    • Martin Hawksey

      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.

  • Eduard

    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!

    • Martin Hawksey

      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

  • Samuel

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

    • Martin Hawksey

      Hi – try deleting it (apps script reads commented out code)

  • Zubair

    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

    • Martin Hawksey

      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

  • Simeon

    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

    • Tomas

      Hello,
      I have the same problem.
      Thanks

  • dave

    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!

  • Hazael Diaz

    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

    • Hazael Diaz

      Sorry, working now.. I just had to deauthorize
      ScriptApp.invalidateAuth();
      and authorize again.. and now everything working fine.

      • Chris Kelly

        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!

        • Tobia Donati

          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?

          • Cameron

            Same.

          • Martin Hawksey

            … this is a project I sorely need to revisit particularly with Google introducing Add-ons for Sheets

  • Tim

    I for one would love to see this updated. Being able to publish sheets as RSS is such an obvious need! Tried to get this going and have tons of trouble getting a valid public feed. Publish URL never seems to stay as public and keeps reverting to private.
    Grrr.

Comments are closed.

css.php