API Authentication Apps Script Reference

This post is a summary of common authentication protocols for API authentication with Apps Script.

I got fed up digging around in my Drive folder for old scripts to refresh my memory on the syntax, so I created this reference.

It’s not a comprehensive post on how to connect to APIs, instead, it’s a short summary of common protocols for easy reference.(If you’re new to APIs, start with my Apps Script API tutorial for beginners.)

This post looks at four different API authentication methods:

  1. No API authentication
  2. Requires API key in query string
  3. Requires API key in header
  4. OAuth2: Secure API authentication

Continue reading API Authentication Apps Script Reference

How To Wrap Text In Google Sheets

In this post, we’ll look at how to wrap text in Google Sheets so that long strings fit inside cells and can be read easily.

Method 1: Via Format Menu

Select a range of data and go to the menu: Format > Wrapping > Wrap

Wrap Text In Google Sheets Format Menu

Next to the wrap text option, you’ll find the clip option (show on one line and don’t allow any overflow) and overflow option (show on one line and allow to spill into adjacent cells).

Continue reading How To Wrap Text In Google Sheets

How To Build An Automated ConvertKit Report In Google Sheets Using Apps Script

In this post, you’ll learn how to build an automated ConvertKit Report tool in Google Sheets.

Every morning I have this report waiting for me in my inbox:

Automated Daily Dashboard Email

Opening the PDF attachment shows me the key growth metrics for my email list:

ConvertKit Email List Analysis Report

There’s also a link in the email to go direct to the Google Sheet, in case I want to ever make changes.

In this post you’ll see how to:

  • Set up this template for yourself
  • Connect to the ConvertKit API with Apps Script
  • Retrieve list data into your Sheet
  • Make projections about list growth
  • Create a report that is emailed to you daily

Continue reading How To Build An Automated ConvertKit Report In Google Sheets Using Apps Script

Repeatable Task: To Script Or Not To Script, That Is The Question

Script

This post was inspired by a question from a reader, who recently asked:

“When should I go to the trouble of writing a script versus doing the quick and easy approach?”

This is absolutely a valid question that you should ask before diving into your code editor.

Let’s create a framework for answering this question.

Question 1: Is It A Repeatable Task?

If you’re debating whether to create a script to automate a task, or simply do the task manually, the crucial question to ask is:

1) Is it a repeatable task?

If you answer YES, then ask yourself: 

2) Do I have time right now to write a script or record a macro?

If you answer YES to this, then ask yourself: 

3) Do I know how to do it?

If you answer YES, then go for it!

If you answer NO to any of those questions, it’s probably going to be quicker and easier to do it the usual (manual) way.

But I encourage you to learn a little Apps Script on the side, so you can answer YES next time!

Here’s a flow chart to help you:

Script

Question 2: How Long Does It Take?

The next crucial question to ask is: how long does it take to do the task?

The time it takes to do the task manually is how much time you’ll save each time if you can simply click a button to run a script.

So, if the answer is “the task takes lots of time”, then you should definitely consider automation.

Over the long run, it’s beneficial to turn repeatable tasks into automated scripts so the task can be done with a few button clicks.

Upfront Cost

Of course, there’s an upfront cost in the additional time it takes to record a macro or write a script. It will take longer than just doing the task manually.

Consider the following chart showing the time taken for a particular task:

Chart

The first time – when you write the script to do a task automatically – takes longer than doing it manually, but subsequent tasks are much, much quicker.

Eventually, the cumulative time doing the task manually will far surpass the cumulative time of using the script (assuming it doesn’t require any major updates).

Once the macro or script has been created, it takes minimal time (and effort) to perform future tasks, so the cumulative time effectively flat lines.

However, that manual process always takes about the same amount of time, so the cumulative time keeps increasing in a linear fashion…up, up, and up.

Repeatable Task versus Manual process

There’s an inflection point where the script starts saving you time. And it’s probably sooner than you think.

Consider a 30-minute repeatable task that you automate. Assuming it takes you 2 hours to create the script the first time, that’s only 4 repetitions until you “break-even” on time.

XKCD has a helpful matrix to show you how much time you should allow to automate a task:

Automation Matrix

Question 3: How Complex Is The Repeatable Task?

Don’t underestimate the time it takes to automate a task.

Sometimes automation looks more like this:

Automation gone wrong
(Source XKCD)

And you find yourself in this situation:

The best way to avoid this is to automate tasks that take a long time but are relatively simple.

For example, a set of data transformations is a good contender. An approvals pipeline involving 3 different people is not a good contender because the process is likely to be complex.

Benefits Of Automating A Repeatable Task

Saving Time

Years ago, I faced a dilemma:

Each month the client would send me their data. It took me a couple of hours to wrangle that data into the correct format, with formulas and pivot tables, so I could use it to update the dashboard.

Realizing this was a tedious and repeatable task, I wrote a script to automate the data transformation steps.

Now I click a button and it automatically does the task.

This frees me up to work on higher-value tasks, like thinking strategically about what insights the data shows or how to improve the model.

Avoiding Mistakes

When you keep doing a repeatable task manually, you stop learning. When you stop learning, you stop being engaged with the work.

After the 3rd or 4th repeat, you’re running on autopilot. You lose focus, get distracted, and make mistakes.

A script never gets bored, distracted, or tired!

Radio Buttons in Google Sheets: Only One Checkbox Checked

In this article, we’ll see how to make checkboxes in Google Sheets behave like radio buttons. In other words, we’ll ensure that only one can be checked at a time.

Since it’s impossible to create true radio buttons with checkboxes and formulas alone (although we can mimic radio button behavior with formulas), we use Apps Script to uncheck boxes as required.

Here are the radio buttons in Google Sheets in action:

Radio Button In Google Sheets

You can see that when I check a new checkbox, any other checkboxes on that row are unchecked.

It takes a split second: you can see the row turns orange when the checked checkbox count is briefly 2, but this is simply the script working in the background.

Let’s see how to implement this with Apps Script.

Continue reading Radio Buttons in Google Sheets: Only One Checkbox Checked