Automating the Summary of Data

My first iteration using Query and Sheets to automate a solution for turning a collection of data into a regular newsletter.

This year I decided to create a monthly newsletter collecting together the updates and resources that I came upon during the previous month. As I have reflected elsewhere, I realised that there was an opportunity to automate some of this process. Although some spoke about Pinboard or generating a post via email, I was interested in sticking to GSuite. I therefore began with the aim of generating a solution within Sheets. So here is my first iteration of an automated solution for turning a collection of links into a summary.

Organising the Data

I remember being in a session with Jay Atwood a few years ago talk about the importance of considering the way you collect your data before anything else. As I looked at my databases, one for updates and the other for resources, I realised that the first thing that I needed to do was reorganise the way that I was storing information. This included restricting the options associated with type and application, as well as separating the link and title and then smashing them together using the HYPERLINK formula. Inspired by Ben Collins’ post on working with text, I also created a column summarising the information in each row into Markdown summary.

Reorganised Data w/ Formulas by mrkrndvs is licensed under CC BY-SA

From Lookups to Queries

Once I had my data organised, I then started explored sorting and shaping the data. I began with a VLOOKUP with a dynamic selector. This allowed me to filter it in different ways. However, I quickly realised that this was limited. I turned to QUERY.

I remember David Krevitt talking about QUERY, describing it as the, ” big kahuna of Sheets functions.” I think this initially put me off. This time I opened up a number of guides from Krevitt, Collins and Anand Varma and dived in. This lead me to rewrite my VLOOKUP as a QUERY.

Bit by bit I stretched the solution. I began with a dynamic selector to represent variables and explored the ability to define queries by date. I then created a prototype with a query for each application across the sheet.

1st Iteration by mrkrndvs is licensed under CC BY-SA

Once I had that working, I create a vertical set of queries. To allow for the variable of the unknown number of posts each month, I left 30 blank rows between each formula.

Filtering Results

To get rid of the spaces and the data headings, I used a FILTER formula and removed the spaces and column headings produced by the QUERY formulas. This left me with a choice, copy the MarkDown data and paste it without formatting, therefore removing the table/sheet that it was in, or using the add-on Sheets to Docs to copy the text to a Google Doc.

So that is the first step in my solution using Sheets to generate the text for a newsletter. My next challenge is transferring this to a Google Script. If you have any thoughts and advice about this, I would greatly appreciate it. Otherwise, as always feel free to leave a comment.

Automating the Summary of Data by Aaron Davis is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.