DEV Community: Console

Using Google Apps Script to streamline our editorial process

David Mytton — Tue, 02 Feb 2021 14:08:23 +0000

We're always on the lookout for new and interesting tools and beta releases to consider for inclusion in the weekly Console newsletter. When we find something that might fit, we log it in Google Sheets.

We have one Google Sheet for beta programs and one for interesting tools. Every Wednesday, we review the sheets against our selection criteria and then pick the 4-6 items for inclusion in newsletter. This is managed in Mailchimp, so we have to copy the chosen content over to the email template. The email is sent on Thursday morning and we also tweet out the links over the following week.

The two Google Sheets containing the beta programs and interesting tools we track and consider for each newsletter.

Although this may change in the future, our goal as we launch in 2021 is to keep our tech stack as simple as possible. It's almost a cliche that a spreadsheet (often Excel) is the most common database / UI / management system / CRM / ERP, but relying on Google Sheets means we can focus on collecting the content rather than building an elaborate Content Management System (CMS). The main downside is that a spreadsheet isn't designed for writing. We're not writing essays but even so, writing inside a small cell isn't the best UX. It does make us think about keeping things short though.

Another downside is the disconnect between where we manage the content (Google Sheets) and where we distribute it (Mailchimp and Twitter). One solution to this is to manually copy the items to Mailchimp and Twitter. There isn't too much content, but this copy/paste could easily take 30-60 minutes and there is room for human error.

A better solution is to automate as much as possible. Mailchimp doesn't allow you to create email content via their API but our template is only HTML. This means if we can generate that HTML somewhere, we will only need a human to paste it into the editor. Same with Twitter. We can just create a one click Tweet link which can then be scheduled via the Twitter web UI.

I would like to have a complete API integration to automate everything, but this was a quick way to solve most of the problem - it is better than pasting content into different sections and dealing with formatting. It cuts down time spent on boring process from an hour (with multiple opportunities for human error), to several seconds (and just one opportunity for error). And if the copy/paste has a problem, it will be obvious.

Dropping the HTML directly in the Mailchimp template editor.

Using Google Apps Script

Google Sheets does a good job at hiding what is actually a very complex product. Like Office has VBA and Macros (now Office Scripts), Google has built Apps Script which allows you to use Google Workspace (Sheets, Docs, Slides, etc) as an application platform.

I built a simple app which adds a new menu item to the Sheets UI that will read the data in the sheet, find the items selected for the next newsletter, then output the HTML ready to be pasted into Mailchimp. Another option automatically generates links to compose the Tweets.

Custom menu in the Google Sheets UI.

The JS code to create this new menu is very simple:


function onOpen() {
  var ui = SpreadsheetApp.getUi();

  // Newsletter meny
  ui.createMenu('Newsletter')
    .addItem('Get MailChimp code', 'getMCCode')
    .addItem('Get Tweets', 'getTweets')
    .addToUi();
}

Clicking the menu option shows a modal dialog generated from an HTML template that loops through the selected content, generating the output that we need for the Mailchimp newsletter.

Modal dialog with generated HTML ready to paste into Mailchmp.

Both sheets have a similar structure so we take advantage of the ability to create and share Apps Script libraries. This packages common code in a library that is called by Apps Script bound to each Sheet. It includes a common method to get the date of next Thursday (for querying the content for inclusion in the next newsletter), and to get the data from the sheet itself.

Querying data from the sheet is core Apps Script functionality but the array is indexed by column position. We don't change the order of the columns often but there was an early bug because the fields were hard coded based on their expected position. I added a quick hack to loop through and find the right field index based on the heading column to protect against future order changes.


var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// https://developers.google.com/apps-script/reference/spreadsheet/sheet#getrangea1notation
var range = spreadsheet.getRange(range);
var values = range.getValues();

var data = [];

// Get the index for the field we want
// Assumes the first row is a header row
for (const [key, value] of Object.entries(values[0])) {
    if (value == scheduledForField) {
        var scheduledForIndex = key;
    }
}

for (var row in values) {
  var scheduledFor = new Date(values[row][scheduledForIndex]);
  data.push(values[row])
}

The online script editor has recently been significantly improved, however Google provides a command line tool called clasp which means I can sync the code locally and do the development using my preferred IDE.

Conclusions

This is how we have built our own simple tooling around Google Sheets as a platform for managing our content. The code is minimal and we can easily extend it if our requirements become more complex. Eventually, we may build a custom system but I expect this to be a key part of the editorial workflow for a while.

This works well because it allows anyone in our team to build the weekly Mailchimp email template with little effort. It avoids us having to build a custom CMS and instead use something reliable and robust we are already used to: Google Sheets.

The Apps Script code we use is available on our GitHub account:

Porting a serverless chatbot from Python to Rust

David Mytton — Wed, 27 Jan 2021 09:03:59 +0000

Console is starting as a free weekly newsletter highlighting the best tools for developers. We want to know how our list is performing, which means keeping an eye on some key metrics like active subscribers and unsubscribe rates. We can log into Mailchimp to see this data, but I wanted to get a quick daily update rather than forming a habit of constantly logging in.

We default to asynchronous communication and use the chat room built into Basecamp for informal socialising or posting non-critical links and comments. I check it each morning, so it seemed like a good place to post a status update.

Like most chat products Basecamp has a bot API to send arbitrary messages into the chat. It is therefore easy to use the Mailchimp API to pull the stats and post them into Basecamp. This code just needs to run every morning.

Keeping our tech stack as simple as possible is a priority. We don't have a complex product and dealing with servers, OS updates, database replication, etc is something we will put off as long as possible! However, a chat bot has to run somewhere. This is the perfect use-case for a serverless function - just write the code and let the platform deal with the infrastructure maintenance.

Azure vs Google vs AWS

I have tried AWS Lambda, Google Cloud Functions, and Azure Functions, and have several personal utility functions running on both Google and Azure. Whilst I have been impressed with Google Cloud's database products, they are still behind when it comes to the breadth of functionality in their serverless functions product. Google doesn't support Rust and has no concept of custom runtimes like AWS and Azure do.

This meant it was down to AWS vs Azure. Both have excellent documentation, lots of language SDKs, and a large ecosystem of developers. However, I prefer the Azure web portal, like how they build in public on GitHub, and rate them best for environmental sustainability. AWS is the more popular platform and Lambda/Azure Functions are competitive. I just prefer Azure.

Creating a Python serverless bot

I have been writing Python for over 10 years, so it was the default choice to create a simple serverless bot. Azure has native Python support, the Basecamp API takes a simple HTTP POST, and Mailchimp has an official Python SDK.

This meant that within a few hours I had written a function, created the Azure Resource Manager template, and configured GitHub Actions to automatically deploy on push. The bot was running and delivering us stats each morning.

Python bot posting our daily Mailchimp stats into Basecamp.

Why port to Rust?

Over Christmas I usually leave London to visit my family, however the change in COVID-19 rules meant that I could no longer leave the city. With some extra time, particularly in the quiet period between Christmas and New Year, I ended up spending several days playing around with Rust.

Although it has been around since 2010, Rust has become particularly popular in the last few years. Stack Overflow have listed it as the most loved language since 2016.

I won't go into the standard Rust pitch of "better safety, concurrency, performance" - you can find detailed Rust vs Language X posts elsewhere - but I did want to write up my experience as a Rust newbie.

Reason 1: Fun and interesting

My main reason for porting from Python to Rust was that it was fun and interesting! I like Python (which sits at #3 in the "most loved" 2020 ranking) because it is easy to start writing code and you can get things done quickly. It also enforces a particular coding style which means more consistent layout, spacing, indenting, etc.

Python doesn't make you think about concepts like static typing and memory management because it handles it for you. Rust is very different. It forces you to consider types, ensure all return cases are handled consistently, understand scoping, and many other things which are absent in Python. This is by design because it makes the resulting code much more robust.

Rust uses the concept of "correctness". It's not that Rust is "right" and Python is "wrong", but forcing you think about these concepts, and enforcing them before code will compile, helps to avoid errors:

almost all (92%) of the catastrophic system failures are the result of incorrect handling of non-fatal errors explicitly signaled in software.

Simple Testing Can Prevent Most Critical Failures: An Analysis of Production Failures in Distributed Data-intensive Systems

The number of times I have found bugs in my Python code due to silly mistakes like typos or not handling a particular error case makes me appreciate Rust's approach. Better to invest more time in development rather than finding bugs and errors once code has been shipped to production.

I still need to write more extensive unit tests and mock the external API calls but tests are a core language feature. Developers are encourged to write tests inside the source that is being tested, with integration tests in a "tests" directory.

Reason 2: Portability

Neither AWS Lambda nor Azure Functions have native support for Rust, but they both support Custom Runtimes/Handlers. This allows you to build a generic web endpoint which, if it can handle to standard HTTP requests and return a standard HTTP response, can be executed by the platform.

(This is true for an HTTP triggered function on Azure. Getting into more platform-specific function types, like a Timer or Queue trigger, means relying more on the platform features. This makes it more difficult to port away, but not impossible).

All languages have a range of frameworks that help building web endpoints, often with an embedded web server. Rust has Actix and Rocket, amongst others. By creating generic HTTP endpoints, the application can be run anywhere. AWS Lambda has some experimental Rust language bindings, but creating a set of generic web endpoints is better because it is not then tied to a specific platform.

This is not unique to Rust. Python has Flask (or Django, for a more "batteries included" approach). However, Rust differs because it is a statically compiled language. This means the output is a binary that runs entirely independently, only requiring the system libc implementation. If you compile it with a musl target it becomes 100% static and will therefore run on any Linux OS.

Statically compiled binaries have all their dependencies included executable. This means the binary should continue to run "forever", even if the platform changes. Indeed, because the application is completely isolated and separate from the platform, there is no reliance on specific system-installed packages. Both can be updated independent of each other. This can be partly achieved using Python Virtual Environments, but they are complex and have developer overhead (PEP-0582 aims to fix that). Static compilation is a more robust approach.

It is currently convenient to run this bot on Azure, but it would be trivial to move it to AWS or a generic web server. Using upx to compress the executable means the final binary with a full embedded web server is just 2.5MB. This application is so lightweight it could even run on a Raspberry Pi in the office - I'd just need to compile it for ARM.

The final 100% static, upx compressed binary. Only 2.5MB.

This is definitely overkill for a chatbot project. There is no tangible benefit to statically compiling a few hundred lines of Rust vs deploying the same length Python code to Azure Functions. There are only a couple of dependencies!

But this is a great test project to learn how it works (see Reason #1 above). Internal tools tend to be built quickly and with less polish than software you intend to ship to customers. Yet they often last the longest in production because of how widely used they become. In some organizations it can be hard to justify spending expensive developer time on such projects, so investing in maintainabilty upfront is worth it.

Reason 3: Developer tooling

The overall experience of writing Rust is made much more enjoyable because of the developer tooling. Everything revolves around Cargo which makes documentation, dependency management, linting, testing, and builds all part of the core language. Installing Rust using the standard rustup command also includes Cargo and there are several useful extras like Clippy and Audit.

This is a big win for developers because everything you need is available out of the box. Third-party packages can be found in the Crate registry. The equivalent in Python, PyPI and pip, does not feel as slick and wasn't even included with Python when I first started coding it (when setuptools/easy_install were the primary method of package management). You also have to be careful to use a Python virtual environment if you have a system managed installation of Python, otherwise you will end up installing system-level dependencies.

I'm a vim user so the official rust.vim plugin gives me autocomplete and in-editor checks, but the output from Cargo (warnings and errors) is very helpful and pinpointing problems. rust-clippy is fun, too.

Where Python has an advantage is the number of libraries. PyPI has over 280,000 packages whereas Cargo has 53,000. Quantity is not an indication of quality, and Python has been stable longer, but it is reasonable to expect vendors to provide official Python SDKs. That is less common with Rust.

Challenges learning Rust

I wrote the Python bot in a few hours but it took me several days to get the Rust port working. This would be hard to justify if it wasn't a learning project, but everyone gets faster with experience. I had to learn not just a new language but also how to properly cross-compile for a different platform. Here are my notes on some of those challenges:

Language challenges

There are several features which make Rust robust, safe, and correct, but require a shift in mindset if you're used to other languages. For example, the way scoping and ownership works means you can't just declare global variables and must consider when they go out of scope. Another example is the requirement to handle all return types/errors so that the program will never crash unexpectedly.

Both of these make sense, but being forced to think about memory and correctness upfront is new to me. This is an example of how Rust shifts effort, bunching it all in the development process rather than these things (maybe) appearing later as bugs. From the perspective of building quality programs, this is better, but it does slow down development and make it harder for newbies. No doubt this becomes faster as it becomes a normal way of thinking.

Web framework challenges

Rust is designed for system programming which means its really good at command line interfaces, programs that need to run robustly millions of times, or within memory/processing constrained environments like embedded systems. That doesn't mean it's not good for other use cases, but like most people don't write webapps in C maybe Rust isn't the best choice for building HTTP APIs?

There are several web frameworks for Rust: Rocket, Actix, Warp, Iron - but only Actix has released a stable 1.0 release, and there has been considerable controversy over how it uses unsafe Rust.

I started with Actix, which uses the relatively new support for asynchronous Rust programming, but couldn't get it working with the non-async Mailchimp crate. I tried writing my own basic calls using the built-in async HTTP client but that doesn't support HTTPS connections without a feature flag. There is example code but it states "As of actix-web 2.0.0, one must be very careful about setting up https communication" without explaining what that setup involves. I saw strange behaviour with initial requests timing out then subsequent requests succeeding, which I assumed was to do with async.

Unable to get it to work, I switched to Rocket. This is being updated to support the new Rust async features but for now is synchronous. I guessed it would solve my problem with the Mailchimp Crate not working properly with async code, and it did seem to solve the issue. This was too difficult to debug as a Rust newbie.

Rocket also requires Rust nightly. All this shows that Rust is not as mature as Python when it comes to building HTTP APIs. If I had built the chatbot as a CLI then I could have avoided the web framework question (and async problems), but then it would not fit into spec for running a serverless function on Azure.

Chatbot running the embedded web server framework, Rocket.

Build challenges

I run Manjaro Linux locally so compiling it for the same platform - x86_64-unknown-linux-gnu - was no problem. This is considered a Tier 1 Rust platform so it is officially supported and pretty much "guaranteed to work".

Azure also runs Linux but requires the compile target to be x86_64-unknown-linux-musl. This falls under Tier 2 which means "guaranteed to build". Note the subtle difference between "work" and "build". It seems safe to assume that Tier 2 platforms will work fine, but the Rust Book warns that may not always be the case because they do not run automated tests for these platforms.

I ran into problems cross-compiling for musl because of OpenSSL. The compiler searches for the relevant OpenSSL library headers to compile against, and by default it will probably discover the system OpenSSL. I'm not using musl libc locally so the system install is "standard" OpenSSL. This was also the case when I was compiling on a clean GitHub Action Ubuntu Linux environment.

The builds actually compiled and the web server launched (including when deployed to Azure). However, when an HTTPS request was made to the Mailchimp API, the executable segfaulted. Digging into this revealed the problem was with the OpenSSL library.

After a lot of searching I came across a Docker image which provides a clean environment for building Rust linked against musl libc. It includes curl, pq, sqlite3, and zlib, but the main one I needed was OpenSSL. This container image solved my problem and I can run using Docker locally as well as in the GitHub Action build workflow.

Compiling totorobot for Linux musl on GitHub Actions.

Conclusions

This was a fun project to port to Rust. I was able to learn not just a new language but also a new approach to deploying software. Coming from the world of dynamic languages like Python, this is a different way of shipping code. The chatbot was small enough to not take too much time, but also touched several key concepts like making external HTTPS requests, building an API server, and writing basic unit tests. It's cool to be able to package the entire executable in a tiny binary that can run on any system it is built for.

The bot code is available open source on GitHub and I will continue to work on improvements in my spare time. It needs more testing and probably some refactoring to avoid deliberate panics.

That said, I'm not convinced that Rust is (currently) the right language for writing web APIs. If I had already decided to use Rust, creating a CLI that could be called by a cronjob or as a systemd service would likely fit the language strengths better. Of course that would require a server to run it somewhere (even if it was just a Raspberry Pi). If I had to create an HTTP API, Go may be a better choice as a Python alternative. But if safety and low overhead were crucial, maybe Rust is the right choice.

This highlights that there is no one "right" way to do everything - the tool should fit the job rather than forcing the job to fit the tool.

The Console tech stack 2021

David Mytton — Mon, 18 Jan 2021 18:58:54 +0000

When I built the original version of Server Density, a server monitoring startup I started in 2009, I was using it as a project to learn Python. I also had to build a lot of the underlying infrastructure because there were only a few cloud products and they were not very mature.

This was a world of visiting the bank to get a merchant account, writing my own invoicing and subscription management system, renting dedicated servers, and figuring out which relational database to use.

Things are different in 2021. There are so many tools and products and projects - everything we need to run Console is available as a fully-managed, pay-as-as-you-go service. This means we have been able to focus on our core product, rather than building the infrastructure from scratch.

A serverless tech stack

Console is starting as a weekly newsletter for developers, which means the tech stack is minimal. This means Console v1 is entirely serverless, but we have written a few tools here and there.

This is our tech stack as we launch at the beginning of 2021. I intend to publish a review of our stack every year so it will be fun to see how things develop.

Basecamp

We run the company using Basecamp and follow their ethos of real-time sometimes, asynchronous most of the time.

We discuss things using longer-form messages in the Basecamp Message Board rather than internal email, and avoid group chat for anything important (I really dislike Slack). Chat has its uses, from posting fun links to coordinating incident management, but there should almost never be a need to monitor it in real-time. We do not expect an immediate response (to chat or messages) and we can choose when and whether to participate in any discussions.

Keeping an eye on how subscriber numbers are going is important, so I wrote a simple bot that queries the Mailchimp API to post our subscriber stats every morning. This is the closest we get to running servers, but it is actually an Azure Function originally written in Python, but now ported to Rust (blog coming soon).

I have been playing around with Azure recently because I have been impressed with Microsoft's environmental progress. The chatbot is currently running in the Azure Europe West region but as soon as the new 100% renewables with 24/7 hourly matching region in Sweden comes online later in 2021, I will move it there.

Mailchimp

There are lots of tools for people running newsletters (ConvertKit is interesting), but Mailchimp is industry-standard. The company has been around since 2001 and has revenues of +$500m/yr without any external funding. All we need is a way to reliably send emails to our subscribers, and Mailchimp has proven its ability to achieve this.

Console Mailchimp dashboard as of early Jan 2021.

Google Workspace

Microsoft has come a long way with Microsoft 365, but having used it to collaborate with my classmates when I was doing my MSc in 2019/2020, Google Docs is still the best for real-time collaboration & commenting. Setting up group Sharepoints and trying to work on the same Word documents is still pretty frustrating, especially when docs get out of sync and/or people save and email different versions around. I am also on Linux, and the Office web apps are well behind their desktop versions (which only exist for macOS or Windows).

I try to avoid Google's free products for my personal tools because of their approach to privacy, however they still offer the best suite of collaboration products for business. Paying with money rather than privacy, with contractual data guarantees when you do so, makes it easier for me to accept using Google Workspace to provide our email, calendar, drive, and docs/sheets. I don't like how there are no local files for Google Docs, but browser-only real-time collaboration avoids the merge-conflicts I regularly saw with Word.

We run our editorial process through Google Sheets, collecting the interesting tools and betas throughout the week ready for review. Once we have selected the content for the newsletter, a custom Google Apps Script generates the Mailchimp HTML and Twitter drafts ready to publish. I was pleasantly surprised at how powerful Apps Script is - we've used it to build a good editorial flow.

Beta programs Google Sheet with custom "Deploy" and "Newsletter" Apps Script.

Cloudflare

Being a newsletter, we need a way to subscribe people and provide somewhere to explain what we're doing. We wanted to have a very fast, static website, but also needed some scripting abilities without having to run our own backend servers.

We use Cloudflare Workers Sites to host our homepage because it makes it easy to deploy static content to a huge, global CDN, and manipulate the pages using serverless scripting. For example, our index page adjusts the referrer form field based on the query string. We will use this to track performance as we experiment with various marketing channels. The site is deployed using GitHub Actions and runs k6 performance testing against a test worker to ensure the site meets performance SLAs.

Our domains are also hosted with Cloudflare DNS because we can use Workers to direct routes for the additional pages we plan to build. I've been really impressed with the products Cloudflare offers for core infrastructure.

All our Cloudflare configuration is managed using Terraform, and Terraform Cloud.

The Console homepage Cloudflare Worker dashboard.

Plausible Analytics

We tried using Cloudflare Privacy First Analytics but the 1-week data retention limit of the UI was too low, so switched to Plausible Analytics. There is an upcoming blog post about this decision.

GitHub

GitHub continues to thrive under Microsoft and they are regularly releasing interesting and useful features. One I really appreciate is Dependabot, which helps us stay up to date with new library releases. This is particularly important given the risks of compromise up the software supply chain.

The small amount of code we have is all in GitHub and our deployments are managed by GitHub Actions, but I am keeping an eye on Source Hut which is growing as a credible alternative.

Console GitHub account.

Xero

All our receipts go into a Google Group which forwards them into the accounting product, Xero. We aren't paying for much at the moment so it is very lightweight, but every company needs to keep proper books. Xero has led the small business accounting market for over a decade now, and I used it extensively at my previous business. It makes accounting much easier.

Bills paid so far in Xero.

1Password

Keeping track of all those logins, especially managing password security, enforcing 2FA, and dealing with new/leaving team members is still a hassle. It's annoying that Single-Sign-On is an "enterprise" feature in most products, which means bumping the monthly fee far too high for a new business.

I have been using 1Password for over a decade now, and it is still my favourite. We're using 1Password Teams to manage shared (and private) logins at Console. I like pass as an open source alternative, but having my personal and company logins in the same product is smoother.

Just a few items in the Console 1Password Teams account.

Ghost

This blog is hosted by Ghost, a very lightweight blogging platform that is also available open source and self-hosted. I use WordPress for my personal blog, but we had heard good things about Ghost and wanted to try out a more modern product. So far I am impressed with the performance, and how clean the generated HTML is, but WordPress still has an advantage when it comes to the large ecosystem of plugins and themes.

Feedbin

One of the reasons we started Console was because it is difficult to keep up with all the new releases in the industry. We still trawl through Twitter, Reddit, Hacker News, forums, and many other sites, but we also use Feedbin to follow thousands of sources. Saved searches and tagging helps us separate vendors from individuals, and we also use it to receive lots of email newsletters. Feedbin is a core part of our content monitoring process.

Using Feedbin to track all the releases and announcements.

That's it!

10 vendors. I'm sure that will grow as Console develops, but we achieved our goal of running zero servers! I will write this post at the start of every year to see how things change over time.