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.
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
- Open Templated Export for Google Spreadsheets
- File > Make a copy of the script
- File > Manage Versions and enter a description and Save New Version
- Run > onInstall twice (first time to authorise, second to fun the function)
- 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 ;)
Last updated by at .
Loading...
Very interesting! Does this approach allows you to push the submissions from my google form to my local Excel sheet?
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…
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.
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.
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
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?
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.
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.
Sounds like the managed library isn’t registering properly. In the script editor when you open Resource > Managed Libraries … does it include TemplatedExport ?
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
Hi Martin ,
what is the solution for this one ??
“ReferenceError: “DOCNAME” is not defined. (line 54)”
I have the same error
¿ what am I missing ?
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).
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.
Very nice tool. Thanks for doing the vids and blog post. I had a couple false starts, but i got it working!
– jack lail
Can you explain how the “TemplatedExport” object works and where is it defined ?
Are you hiding it ? if So how did you do ?
It’s a managed library I wrote https://developers.google.com/apps-script/guide_libraries (using the script debugger you can set a breakpoint and step into the code. The code should be linked to in the post somewhere)
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 !
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
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