Automatic Updated At Fields In Google Sheets

When working on Google Sheets, especially with others, it is often quite useful to have fields that capture when a cell, column, row, or even an entire sheet has been updated. In order to do that, you need to use Google Apps Scripts. Don’t worry if you haven’t really coded before; the sample script below is quite straightforward and can be easily altered to meet your needs.

Step 1: Open the Sheets script editor

In your Google Sheets, you first have to open the script editor. It is in Tools -> Script editor. That will start a new Google Apps Script project with a single script: Code.gs. Be sure to set a project title at the top of the page.

Step 2: Create the script

Replace the contents of Code.gs with the script below. It gets the selected cell on the sheet being viewed. If that cell is in a column after B and row after 1 and it is on Sheet1, then the current date is set in the first cell of the same column.

function onEditSheet1(e) {  
  var sheet = SpreadsheetApp.getActiveSheet();
  var cell = sheet.getActiveCell();
  // The edit is done in any column after B and row after 1 and the sheet name is Sheet1
  if (cell.getColumn() > 2 && cell.getRow() > 1 && sheet.getName() == "Sheet1") {
    sheet.getRange(1, cell.getColumn()).setValue(new Date()).setNumberFormat("yyyy-MM-dd");
  }
};

Step 3: Add a trigger

To execute the script on edits to your sheet, you need to attach it to a trigger. To view your project’s triggers, click the clock symbol on the script editor’s tool bar. That will open up the project’s triggers dashboard. Click the Add Trigger button at the bottom left of the page. Use the following settings:

  • Function to run: onEditSheet1
  • Deployment: Head
  • Event source: From spreadsheet
  • Event type: On edit

Click save. You will be prompted to grant permission to run the script with your Google account and informed that the app has not be verified. That is fine since you are the person that created it.

Now go back to your sheet and on Sheet1, try editing some cells. You will see that the cells at the top of the columns you edit get set to the current date.

Closing thoughts

Well now you know enough to be dangerous 🙂

Google Apps Scripts is incredibly powerful. Here, we are just doing simple cell updates but where things can get crazy is when you start making calls to 3rd party APIs. That means you can set triggers that will make calls to Webhooks. At that point, the possibilities with what you can do with these little scripts is limitless. The reference documentation for Google Apps Scripts specific to Google Sheets can be found here.

My First Imperfect Foods Box

I finally decided to give Imperfect Foods a try, a grocery delivery service with an interesting twist. Basically, I got tired of going to the store especially with the COVID crisis going on. The lines and social distancing have made getting groceries take so long. In fact, before the crisis, I was getting pretty annoyed having to deal with parking. Even with all that, I have been hesitant to use a delivery service but then I found Imperfect Foods.

What makes Imperfect Foods interesting is that by and large they sell the produce that grocery stores won’t. There isn’t anything wrong with their fruit and veg aside from them being a little misshaped or scarred. As it is all food that doesn’t make the cut for the shops, they can sell it at a really good price and if you spend over $60, easy enough to do for a week’s worth of groceries, you get free shipping.

I’m super happy with this first box I got from them and I’m really looking forward to seeing how they do with my next. It might just be good enough to keep me from going back to my grocery store.

Local Kubernetes using Docker Desktop

Kubernetes is really starting to be a de-facto standard in how we deploy things to the cloud. It provides a ton of functionality around docker images that it deploys while at the same time making optimal use of the hardware that it is running on. Having worked with traditional servers and then VMs, I have found Kubernetes to be quite refreshing. A lot of the tedious things that you had to deal with are just handled by default.

Running kubernetes locally is really quite simple. You only need to have docker desktop installed. That should be almost an automatic these days if you are developing software for the cloud. Now if you are someone that has docker desktop installed but hasn’t messed with kubernetes, don’t worry. A lot of folks do write apps and build docker images for them without needing to play with it. They are either leveraging a hosted service, like AWS ECS, to run and manage their containers or they have someone on their team that is hogging all the fun. Here’s a quick tutorial on how to start a container on kubernetes locally.

Step 1: Create a docker image

You might already have an image kicking around for an app you are developing. Let’s assume that you are starting from scratch though. Create a file called Dockerfile with the following contents. alpine is a minimal linux distro and we are going to install HTTPie on it. The working directory is set to be /opt/. The image opens a shell using the command /bin/sh.

FROM alpine:edge

RUN apk add httpie

WORKDIR /opt/

CMD /bin/sh

Build the image using the following command:

docker build --no-cache -t hello-world:docker-desktop .

You can verify that the image is built and working as expected by run it in interactive tty mode. That will allow you to interact with the shell as you would through a console.

docker run -it hello-world:docker-desktop

In the shell, you can run httpie from the container. That basically proves that your simple docker image was built correctly.

http https://jsonplaceholder.typicode.com/todos/1

No need to clean up. As soon as you exit the shell the container will stop running.

Step 2: Create a kubernetes pod

Now that you have a simple docker image, let’s create a kubernetes pod for it. A pod is a group of one or more containers. In this tutorial, it is just going to be one container that has our small image with HTTPie installed.

Select the docker-desktop context from the docker desktop kubernetes drop down menu. That is the locally running kubernetes instance that you get with docker desktop. You can confirm that you have that context selected using this command:

kubectx

Let’s create a namespace in your context called hello-namespace. Namespaces let you group together pods. In fact, you can create multiple pods with the same name as long as they exist in different namespaces.

kubectl create namespace hello-namespace

That gives us the kubernetes infrastructure needed for deploying our pod. A pod is defined using manifest files like the one below. They can either be JSON or YAML but virtually everyone uses YAML. This manifest says to create a pod named hello-pod with a single container called hello-container. This container will use the hello-world image and runs the command sleep 15m which means it will run for 15 minutes before completing. Copy this manifest into a file named hello-manifest.yaml

---
apiVersion: v1
kind: Pod
metadata:
  name: hello-pod
  namespace: foo
spec:
  containers:
    - name: hello-container
      image: hello-world:docker-desktop
      imagePullPolicy: IfNotPresent
      workingDir: /opt/
      command: ["sleep"]
      args: ["15m"]

We can deploy the pod using this command:

kubectl apply -f hello-manifest.yaml

We can now see that our pod has successful been deployed using get pods.

kubectl get -n hello-namespace pods

Step 3: Shell into your pod

Let’s jump into that pod you just deployed. The command below should look very familiar as it is almost the same as the one we used to shell into your docker container from step 1.

kubectl -n hello-namespace exec -it hello-world /bin/sh

Just like in step 1, let’s run HTTPie.

http https://jsonplaceholder.typicode.com/todos/1

The pod will complete after 15 minutes but we should still clean up and remove it. Use the command below to delete the pod:

kubectl delete -f hello-word.yaml 

Closing thoughts

This tutorial has walked you through how to deploy a kubernetes pod with a docker image you built. The image that we used is really quite basic but you should be able to see that with a little effort, you can use it to deploy any image you are working on. There is more that you need to tinker with if you are going to setup a Web app or service but what we covered in this post is a good first step in that direction.

Nocking Point Coffee Club April Shipment

My Nocking Point Coffee Club shipment has arrived for the month and this time it has come with a lightweight backpack. As a coffee club member, you always get a lifestyle item with your monthly coffee. It’s a nice perk as the beans are already great.

I really like this backpack. As a runner, I’ve collected a bunch of them from races I’ve done. I use them all the time but they always have draw strings which I find super annoying. Fortunately this Nocking Point pack has zippers so I can be confident that it will stay closed as I am bopping around on the weekends.

Pre-order Quarantine Pinot Noir from Nocking Point

I’ve become a rather big fan of the Nocking Point winery. I’ve now made several purchases from them and have been quite happy with their wines and coffees. This month, they are putting out a pretty cool collaboration with The Kutcher Family, the Quarantine Pinot Noir. It is an Oregon based Williamette Valley wine. The important bit though is that all the profits go to charities, a combination of support for food banks and EMTs.

The wine is on pre-order currently with a ship date early in May. I’ve already reserved 3 bottles and I am tempted to get a few more. I am sure that the wine will be fantastic as it always is from Nocking Point but it is nice that with this one the money is going to a good place. I mean, I personally love putting my dollars into a local Washington business but it is great that they are able to do this fundraiser during the COVID crisis.

Still doing long run reverse splits

With the weather getting better, I am doing more running outdoors. That and my gym is still closed. I am following the social distancing guidelines so that means doing my runs first thing in the morning before there are too many walkers out and sticking to less crowded routes. It is kind of amazing how there appear to be a lot of “first time” walkers who don’t really understand how to share sidewalks or pathways. On my last time out, I literally had someone hold out his arms to try to block me from running on the path. I’m not sure what that was all about but at least I got a pretty good 9 mile time for myself breaking the 1:20 mark.

I’m continuing to do negative splits on my long runs and it is boiling down to a combination of me still not quite being used to running at my current low weight and … taking forever to warm up. My first mile times have been terrible lately. I don’t know if I can bring myself to do a warm up beyond just walking out to the main road but I guess that might have to be a thing for me. I’m also wondering if it will go away as it gets warmer in the mornings.

I can’t wait for us to get past the social distancing and have our lives return back to relative normal. I’m quite eager for a half marathon though there are a lot of discussions about how we are going to have new social norms to prevent the spread of viruses like COVID-19. I wonder how that is going to impact running events. We all certainly pack in pretty tightly into those starting corrals. I get it if that stops being how we start races but part of me will also be a little sad.

Homemade Puff Pastry and Palmiers

Since the social distancing went into effect basically world wide, I’ve joined the baking bandwagon. One of my favorite things to make from scratch is puff pastry. There is something that I find just so relaxing about making the lean dough where you slowly add water a tablespoon at a time to flour that you toss in the air. Pounding out the butter is fun too.

My go to recipe with puff pastry is palmiers. They are super simple to make but tricky to get their shape just right. Essentially, you make a log out of your pastry which you then cut into these cookies. You definitely need to use parchment paper to keep them from sliding around in the oven as the butter melts however, that isn’t enough to give them that perfect heart shape. There is something that I have yet to figure out about how to shape them just before popping them in to bake. As you can tell, it is still a work in progress for me. The whole family loves them just the same. It’s a shame you can’t make a healthy version of these cookies given all the butter. That really limits how often you can make them or in my case, work on getting that heart shape.

Anyone doing the One Punch Man workout?

Well, it’s been almost a month and I know that my gym, Orangetheory, has been closed because of COVID-19. Most of us are in that camp now where we are on our own for home workouts. Running wise, that hasn’t really impacted me. I still throw on my runners and head out on the trail. It is actually nice because I am seeing more runners and starting to see frequent faces. Exercise wise, however, I’ve had to change things up a lot.

I don’t have any weights at home. My gym memberships over the years gave me a good reason to give them up. Saving space at home and always having a great set of equipment to use, the gym equation just worked for me. Without the gym, that means I got to do body weight exercises. That has led me to doing a variant of the One Punch Man workout.

One Punch Man is a hilarious anime about a super hero that can defeat his opponents with a single punch. One episode, someone asked him how he got so strong and he shared his daily workout which is:

  • 100 Push-Ups
  • 100 Sit-Ups
  • 100 Squats
  • 10 km Running

The page I linked to earlier goes into way more detail than one should about how to work up to the One Punch Man routine and even talks about what you should eat… So on my off days, I’ve been doing this variant:

  • 100 Push-Ups
  • 100 Sit-Ups
  • 100 Side Crunches
  • 100 Squats
  • 100 Lunges
  • 1 Hour Walk

I’ve been doing 5 miles twice during the week and a 9 miler on the weekend. The weather has been hit or miss but if we end up getting a good stretch of sunny days, I’ll probably add another 5 miler. It isn’t Orangetheory and I do miss my gym buddies but this simple routine is working for me.

ngrok: Accessible Self Hosting Simplified

I was trying to come up with a descriptive title for what ngrok does and that was the best I could do. Let me explain what I really mean.

Whether you are developing a micro service or Web app on your laptop, it is pretty common to want to give someone access to it over the Internet. That can be a bit of a pain as you need to look up your IP; make sure your router does the appropriate port forwarding; and confirm that you don’t have any firewalls that could get in the way. All that just to demo to someone what you are working on. Anyone that has done any amount of remote work has had to deal with this scenario.

Ngrok simplifies this whole process. It is a command line utility that sets up the necessary secure tunnels to expose the app you are running locally and registers it with an ngrok URL to make it easy to share. Let’s give it a go.

Step 1: Setup a simple Web app / service

Any app will do here. You can use one that you are currently developing or just have sitting around. In case you don’t have one, let’s spin up an instance of Elasticsearch using docker. First download the image we want to run.

docker pull docker.elastic.co/elasticsearch/elasticsearch:7.5.2

Now start it up in single node mode, a.k.a. dev mode.

docker run -p 9200:9200 -p 9300:9300 -e "discovery.type=single-node" docker.elastic.co/elasticsearch/elasticsearch:7.5.2

Let’s check that it is running correctly by sending it a request. I am using HTTPie to do that.

http localhost:9200

You should get a successful response with a 200 status code and some information about your Elasticsearch instance.

Step 2: Install ngrok

This install is pretty painless. Download the binary from the ngrok Website.

Create an account with ngrok. Don’t worry, it’s free. You need an account to get an auth token that you will use to setup the binary that you just downloaded.

./ngrok authtoken <YOUR_AUTH_TOKEN>

If you are looking for your auth token, it should be on your ngrok dashboard.

Step 3: Expose your Elasticsearch instance

Now for the fun stuff. Let’s use ngrok to make your instance of Elasticsearch accessible over the Internet. The command below creates an HTTP tunnel on port 9200 which is the same one that our docker container is using for Elasticsearch.

./ngrok http 9200

The tunnel should start up pretty quickly and you’ll see on the console the ngrok URLs that have been assigned to it. You should get one HTTP and one HTTPS.

Account                       Chris Jordan (Plan: Free)                                                                                                                                                                                  
Version                       2.3.35                                                                                                                                                                                                     
Region                        United States (us)                                                                                                                                                                                         
Web Interface                 http://127.0.0.1:4040                                                                                                                                                                                      
Forwarding                    http://fed1a36e.ngrok.io -> http://localhost:9200                                                                                                                                                          
Forwarding                    https://fed1a36e.ngrok.io -> http://localhost:9200

Let’s send a request to one of those URLs.

http https://fed1a36e.ngrok.io

That’s it. You’ve exposed your locally running Elasticsearch on the Internet securely.

As well as the tunnel that just got setup, ngrok has a locally running dashboard that displays all sorts of useful information about requests and connections being made to your app. You can access it here: http://localhost:4040/

Closing Thoughts

Ngrok is an amazing utility especially given the current climate that many of us find ourselves in with respects to COVID-19. A lot of us are now working from home and I know it can be a bit of a struggle to be as efficient and effective as we normally are when at the office. Calling a co-worker or friend over and giving them a demo of what you are doing is just so simple and natural to do in person. Ngrok doesn’t completely replace that but it is pretty close. Probably as close as we are going to get while we are all remote. Still not the same but it does make it easy to demo what you are doing.

Be safe everyone. We’ll get through it.

Noom Friendly Instant Pot Split Lentil Soup

Another green soup just in time for St. Paddy’s Day. I am not actually a big St. Paddy’s Day fan. It just happens that my favorite soups are green. This weekend, I am at my parents’s place so I decided to make this Instant Pot lentil one for them.

This soup is great because it is a great source of veggie protein while at the same time being very Noom friendly at only 240 calories per serving. My recipe makes about 4. On top of that, it’s a throw it all in the Instant Pot and click go recipe. It can’t get any easier than that.