A plan for an automated monthly newsletter produced from Google Sheets. The intention is to develop data in a way that it can be used in a number of ways.
I recently wrote a post reflecting on the Digital Technologies curriculum. One of things that I realised through the process is that I often wait to the end to discuss my projects. Although this can be useful in providing an overview of learning and achievements, it does not necessarily allow others a means to provide feedback early on. I usually ask questions online, but this often lacks context. So this post is an attempt to plan out a new project, with the hope that others might be able to provide advice and guidance.
This year I started a monthly newsletter associated with Google. With GSuite the chosen learning and teaching platform in my organisation, I thought it would be useful to summarise the various resources for others. I started with a Google Doc, organising the various links under headings associated with the featured application, as well as a section documenting the overall updates.
This has ebbed and evolved as the year has gone on, with a clear order of applications to correspond with a range of modules. However, the question that has arisen is whether there is a better way of recording the various links and updates so that they are easily searchable.
Currently, you can go back through the various posts and look for resources, but this is both cumbersome and tedious. It therefore had me think about storing the links in a Google Sheet and possibly generating the monthly summary/newsletter from that.
I know that I could probably do this with a social bookmarking platform or even a blog, but I feel that putting the information into a spreadsheet provides more operability. It would mean that the data would be in a format with which I could present it a number of ways. It also means the links could be recorded using something as simple as Google Forms.
I am therefore thinking of creating a script in Sheets that collates all the links for the month in a Google Doc. To be honest, Google Apps Script is all still new to me, but I am wondering about the possibility of creating a template with merge fields. I remember Autocrat doing something similar. I could then use this to post in WordPress.
I am left with a number of questions, such as how should I action the script? Would it need some sort of selector or could it be done automatically? How customisable are templates? Could I generate a markdown version for the purpose of posting?
Maybe you have an idea or a post that you would recommend checking out before beginning or just a tip of where to start. As always, comments welcome.
If you enjoy what you read here, feel free to sign up for my monthly newsletter to catch up on all things learning, edtech and storytelling.
Scripting an Automated Solution by Aaron Davis is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.
As always you keep raising the bar Aaron. I can’t help you or suggest anything because you have left me far behind. Haven’t had time to explore this. Am only just using Google classroom. Hoping to flip some lessons if time allows.
Thank you for your comment Anne. I agree, it requires a lot of time and space to get your head around some of these things. Think for example your own collection of resources associated with EAL/D. You could record them in a sheet which acts as a database and then create some sort of tool which you could use to search them (that is what I did with Awesome Tables). I am also interested in creating these solutions for those who may not know.
In regards to Classroom, check out this recent post from Richard Byrne for an example of the way in which scripts can help us go even further.
This sounds really interesting. I’ve not done much with sheet scripting other than copy the odd script. But I am wondering if you could still use a social bookmarking service, say pinboard, then use IMPORTFEED in your sheet to grab all the links, and descriptions . This might be easier than opening a google form every time you want to add a link? Or not YMMV.
But you could then automate the creation of the newsletter as you outline above. Pinboard would be goods as you can get a feed for a tag (newsletter).
Scripting an Automated Solution by Aaron Davis
This sounds really interesting. I’ve not done much with sheet scripting other than copy the odd script. But I am wondering if you could still use a social bookmarking service, say pinboard, then use IMPORTFEED in your sheet to grab all the links, and descriptions . This might be easier than opening a google form every time you want to add a link? Or not YMMV.
But you could then automate the creation of the newsletter as you outline above. Pinboard would be goods as you can get a feed for a tag (newsletter).
Like this:
Like Loading…
Thank you John. I agree that Pinboard could be an option, but I must admit that I am trying to stick to just using Sheets at this point in time. IMPORTFEED will also require me to smash links and titles together, right?
Hi Aaron,
Here is an example of what you get with IMPORTFEED
https://docs.google.com/spreadsheets/d/1fAMmJvWpN95sGf-iY59EzbXBDDY1fUAeL0e8XFFbMD8
I would expect you would ha e more in your description 😉
Reading Tom’s comment this might be a way to collect links to later push to doc?
You could push the content to a Google Doc, easiest way I can think of would be to have a column (hidden or not) in there that records the month/year of the entry and loop the script through the records that match the current month. You could push that to a GDoc if you wanted but if your goal is a blog post, you could just push it into a gmail and use the jetpack post by email function to create your post.
You can set timers for Google Scripts to run (there’s a little timer icon in there) and here are more details about building your own if you wanted.
Given the raw content you have in Google Sheets you could pretty much do whatever you want w/in the bounds of Gmail’s HTML restrictions and jetpack’s email rules(if any?).
Another path would be to be fancier in Google sheets and write a formula that grabs all the month’s elements with whatever HTML/markdown wrappers you want and then make that sub-page public on the web and set a cron task and php page to grab it every month and create a post. That’s pretty much what I do with my pinboard weekly posts – details here.
Lots of options I think. If you go down a path and get stuck let me know.
Thank you Tom. The whole post to gmail path has me thinking. I wonder whether I could automate the sharing of links and resources to social bookmarking using this method? As the blog is with Edublogs/Global2, I will need to explore whether I can post via Jetpack (the Google Docs WordPress Add-on is blocked). This also means that cron might not be possible as well.
I’ll let you know if I get too stuck.
This Article was mentioned on collect.readwriterespond.com