Spreadsheets

Scripting an Automated Solution

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.

Aaron Davis

I am an Australian educator supporting schools with the integration of technology and pedagogical innovation. I have an interest in how together we can work to make a better world.

Latest posts by Aaron Davis (see all)

Scripting an Automated Solution by Aaron Davis is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.

13 thoughts on “Scripting an Automated Solution

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

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

  2. Scripting an Automated Solution by Aaron Davis

    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.

    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…

    via johnjohnston.info

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

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

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

  4. John, I really like the sound of the AppleScript as a solution. I do have a Mac (which I inherited), but have never really got around to tinkering. I started storing posts this month in a sheet, which maybe I could game to be in the same format as a Pinboard Feed. I was hoping to push it through into a document like this. I came across an Add-on called Document Studio, but not sure if it is exactly what I am after. Will continue tinkering.

Mentions

  • Read Write Respond #022
  • Aaron Davis
  • Aaron Davis
  • john
  • john

Leave a Reply

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