Category: Spreadsheets

How Do Array Formulas Work In Google Sheets?

Array Formulas have a fearsome reputation in the spreadsheet world (if you’ve even heard of them that is).

Array Formulas allow you to output a range of cells, rather than a single value. They also let you use non-array functions with arrays (think ranges) of data.

In this post I’m going to run through the basics of using array formulas, and you’ll see they’re really not that scary.

Hip, Hip Array!

Array formulas

What are array formulas in Google Sheets?

First of all, what are they?

To the uninitiated, they’re mysterious. Elusive. Difficult to understand. Yes, yes, and yes, but they are incredibly useful in the right situations.

Per the official definition, array formulas enable the display of values returned into multiple rows and/or columns and the use of non-array functions with arrays.

In a nutshell: whereas a normal formula outputs a single value, array formulas output a range of cells!

The easiest way to understand this is through an example.

Imagine we have this dataset, showing the quantity and item cost for four products:

Array formulas example data

and we want to calculate the total cost of all four products.

We could easily do this by adding a formula in column D that multiplies B and C, and then add a sum at the bottom of column D.

However, array formulas let us skip that step and get straight to the answer with a single formula.

Advanced Formulas in Google Sheets💡 Learn more
Learn more about working with Advanced Formulas in the Advanced Formulas in Google Sheets course

What’s the formula?

=ArrayFormula(SUM(B2:B5 * C2:C5))

How does this formula work?

Ordinarily, when we use the multiplication (*) operator in a Sheet, we give it two numbers or two cells to multiply together.

However, in this case we’re giving it two ranges, or two arrays, of data:

= B2:B5 * C2:C5

However, when we hit Enter this gives us a #VALUE! error as shown here:

Array error message

We need to tell Google Sheets we want this to be an Array Formula. We do this in two ways.

Either type in the word ArrayFormula and add an opening/closing brackets to wrap your formula, or, more easily, just hit Ctrl + Shift + Enter (Cmd + Shift + Enter on a Mac) and Google Sheets will add the ArrayFormula wrapper for us.

=ArrayFormula(B2:B5 * C2:C5)

Now it works, and Google Sheets will output an array with each cell corresponding to a row in the original arrays, as shown in the following image:

Effectively what’s happening is that for each row, Google does the calculation and includes that result in our output array (here showing the equivalent formulas):

Array formula explained

and another view, showing how the calculation is performed (just for the first and last row):

Array formula explained

Note: array formulas only work if the size of the two arrays match, in this case each one has 4 numbers, so each row multiplication can happen.

Finally, we simply include the SUM function to add the four numbers:

=ArrayFormula(SUM(B2:B5 * C2:C5))

as follows:

Array formula

Quick Aside:

This calculation could also be done with the SUMPRODUCT formula, which takes array values as inputs, multiplies them and adds them together:

=SUMPRODUCT(B2:B5 , C2:C5)

Another Array Formula Multiplication Example

In this example, we enter a single formula to multiply an array of row headings against an array of column headings.

This only works because the dimensions of the arrays are compatible, since the number of columns in the first matrix is equal to the number of rows in the second matrix:

Array formula

Array Formula With IF Function

This is an example of a non-array function being used with arrays (ranges). It works because we designate the IF formula as an Array Formula.

Consider a standard IF statement which checks whether a value in column A is over $2,000 or not:

=IF(A2>2000,"Yes","No")

This formula would then be copied into each row where we want to run the test.

We can change this to a single Array Formula at the top of the column and run the IF statement across all the rows at once. For example, suppose we had values in rows 2 to 10 then we create a single Array Formula like this:

=ArrayFormula(IF(A2:A10>2000,"Yes","No"))

This single formula, on row 2, will create an output array that fills rows 2 to 10 with “Yes”/”No” answers, as shown in the following image:

Array Formulas with IF function

Can I see the example worksheet?

Click here to make your own copy

Related Articles

Array Formulas with Google FormsUse Array Formulas With Google Forms Data
See how to use Array Formulas with Google Forms data to automatically calculate running metrics on your data, like sum, average, % of total and others.
Data Best Practices Google Sheets18 best practices for working with data in Google Sheets
This article describes 18 best practices for working with data in Google Sheets, including examples and screenshots to illustrate each concept.
remove Duplicates in Google SheetsHow to Remove Duplicates in Google Sheets
Learn how to remove duplicates in Google Sheets with these five different techniques, using Add-ons, Formulas, Formatting, Pivot Tables or Apps Script.

Gmail Mail Merge For A Specific Label With Apps Script

Every Monday I send out a Google Sheets tip email and occasionally I’ll include a formula challenge.

I posted Formula Challenge #3 — to alphabetize a string of words separated by commas using a single formula — in January 2020 and had over 150 replies!

It would have been too time consuming to reply to all 150 responses manually from my inbox.

Since 95% of all my replies would be the same (a thank you and the formula solution) it was a perfect case for automation.

(The solution was essentially a mash up of this post on extracting email addresses in Gmail and this post on reply to Google Form solutions quickly with Apps Script.

Apps Script course including Google Sheets macros💡 Learn more
Learn more about Google Apps Script in this free, beginner Introduction To Apps Script course

Gmail Mail Merge Script Outline

  1. Make sure all of the emails are labeled correctly in Gmail (you can use a filter to do this).
  2. Then use Apps Script to extract the solution responses into a Sheet with names and emails addresses.
  3. Categorize each row of data (i.e. each email) into 3 or 4 different categories, e.g. “Correct”, “Correct but…” etc.
  4. Next, create a reply template for each of these categories, to say thank you for taking part and also sharing any feedback.
  5. Then use a simple VLOOKUP formula to add a reply to each row, based on the category.
  6. Following that, use Apps Script to create draft emails for everyone in the Sheet (the Gmail Mail Merge part).
  7. The last part is manual: a quick check of original email and response, add any customization and then press SEND.

Part 1: Extract Gmail Emails To Google Sheet With Apps Script

Assuming all your emails are labeled, so that they’re all together in a folder, you can use Apps Script to search for this label and extract the messages into a Google Sheet.

Search for the messages under this label with the search query method from the GmailApp service. This returns an array of Gmail threads matching this query.

Retrieve all the messages with the getMessagesForThreads() method.

From this array of messages, extract the From field and the body text.

The From field takes the form:

Ben Collins <test@example.com>

Parse this with a map function, which creates a new array out of the original array where a function has been applied to each element. In this case, the function parses the From field into a name and email address using regular expression.

Finally, this new array, containing the Name, Email Address and Message Body, is returned to whichever function called the extractEmails() function.

Here’s the code:

function extractEmails() {
  
  // define label
  var label = 'marketing-formula-challenge-#3';
  
  // get all email threads that match label from Sheet
  var threads = GmailApp.search("label:" + label);
  
  // get all the messages for the current batch of threads
  var messages = GmailApp.getMessagesForThreads(threads);
  
  var emailArray = [];
  
  // get array of email addresses
  messages.forEach(function(message) {
    message.forEach(function(d) {
      emailArray.push([d.getFrom(),d.getPlainBody()]);
    });
  });
  
  // parse the From field
  var parsedEmailArray = emailArray.map(function(el) {
    var name = "";
    var email = "";
    var matches = el[0].match(/\s*"?([^"]*)"?\s+<(.+)>/);
    
    if (matches) {
      name = matches[1]; 
      email = matches[2];
    }
    else {
      name = "N/k";
      email = el;
    }
    
    return [name,email,"'"+el[1]];
  });
  return parsedEmailArray;
}

To paste into the Google Sheet, I created this function, which actually calls the extractEmails() function on line 8 to retrieve the email data:

function pasteToSheet() {
  
  // get the spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();  
  
  // get email data
  var emailArray = extractEmails();
  
  // clear any old data
  sheet.getRange(2,1,sheet.getLastRow(),4).clearContent();
  
  // paste in new names and emails and sort by email address A - Z
  sheet.getRange(2,1,emailArray.length,3).setValues(emailArray);
  
}

Running this pasteToSheet() function creates a Google Sheet with the Name, Email Address and Message Body in columns A, B and C:

Gmail Mail Merge Google Sheet

Now review each row and assign a category. You want to have enough categories to catch the main differences in responses but not too many that it becomes manual and tedious (which we’re trying to get away from!).

For example, in this formula challenge, I had these four categories:

Correct, Extra Transpose, Other, N/a

Part 2: Create Reply Templates In Google Sheets

In a different tab (which I called “Reply Templates”), create your reply templates. These are the boilerplate replies for each generic category.

Gmail Mail Merge Reply Templates

Then use a standard VLOOKUP to add one of these reply templates to each row, based on the category:

=VLOOKUP(D2,'Reply Templates'!$A$1:$B$6,2,FALSE)

The Sheet now looks like this (click to enlarge):

Gmail Mail Merge Vlookup

Part 3: Create Draft Replies For Gmail Mail Merge

The final step is to create draft Gmail replies for each email in your Sheet, and then send them after a quick review.

This function retrieves the extracted email data from the Sheet, then searches for them in the label folder. It creates a draft reply for each email with the reply template response from the Sheet data.

function createDraftReplies() {
  
  // grab the email addresses from Google Sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var data = sheet.getRange(2,1,sheet.getLastRow(),7).getValues();
    
  // loop over them, find mnost recent email under that label for that email address
  data.forEach(function(arr) {
    
    if (arr[6] === "") {
      var emailAddress = arr[1];
      var reply = arr[5];
      var threads = GmailApp.search('label:marketing-formula-challenge-#3 from:' + emailAddress)[0];
      var message = threads.getMessages()[0];
      message.createDraftReply(reply);
    }
    
  });
}

When the script has finished running, all of the emails in this label folder will have a draft reply.

Review them, customize them if needed and press Send! ?

Gmail Mail Merge Notes

1) I could have used the reply method of the GmailApp service to automatically send replies and skip the draft review process. This would be useful if reviewing each draft was too time consuming at scale.

2) I did not include any error handling in this script.

This was deliberate because I was creating a one-use-and-done solution so I wanted to move as quickly as possible. This is one of the strengths of Apps Script. You can use it to create quick and dirty type of solutions to fill little gaps in your workflow. If the problem is specific enough, and not intended to be used elsewhere, you don’t need to worry too much about error handling and edge cases.

3) Lastly, be aware of Apps Script quotas when sending emails automatically with Apps Script. It’s 100 for consumer plans and 1,500 for G Suite (Business and Education).

Formula Challenge #3: Alphabetize Comma-Separated Strings

This Formula Challenge originally appeared as Tip #85 in my weekly Google Sheets Tips newsletter, on 20 January 2020.

Sign up here so you don’t miss out on future Formula Challenges:

 

Find all the Formula Challenges archived here.

The Challenge

Start with a list of words in a single cell, separated by commas and not in alphabetical order, like so:

Epsilon,Alpha,Gamma,Delta,Beta

Formula Challenge 3

Your challenge is to create a single formula (i.e. in a single cell) that reorders this list into alphabetical order.

Step 1

Use the SPLIT function to separate the comma-delimited string into separate cells.

=SPLIT(A1,",")

(Split has two additional arguments and you have to be precise with your delimiter. In this simple example, we can omit the two additional arguments. See here for more info on the nuances of the SPLIT function.)

Step 2

Use the TRANSPOSE function to change from row orientation to a column orientation, so that we can sort in Step 3.

=TRANSPOSE(SPLIT(A1,","))

Step 3

Sort the data with the SORT function!

You don’t need to specify a column or direction, because we only have 1 column and we want ascending order, which is the default order. This keeps our formula brief.

=SORT(TRANSPOSE(SPLIT(A1,",")))

Step 4

Finally, join the column back together with the JOIN function, again using a comma as the delimiter.

There’s no need to use a second transpose because the JOIN function works with a column of data just as easily as a row of data!

=JOIN(",",SORT(TRANSPOSE(SPLIT(A1,","))))

Bingo!

Formula Challenge 3 Solution

Community Solutions

I had over 150 responses to this formula challenge, and most came up with this same formula. It confirmed what I thought that there’s no shorter way to do it.

If you want to see how I used Apps Script to help me reply to these 150 emails, check out this article: Gmail Mail Merge For A Specific Label

2019 In Review And A Look Forward To 2020

Best wishes to all of you for 2020!

Family hike
Photo from a recent family hike up our local mountain. A great way to finish 2019!

This is my 5th annual review post.

I’m proud and thankful that I get to write this post every year, because it means I’m still running my own show and my business is still going.

At this time of year I like to pause. Stop doing the busy work. Step off the treadmill and look back at 2019 to acknowledge what I achieved, what went well, and what can be improved. To look forward to 2020 and make plans for the year ahead.

2019 was my most successful year as an independent, small-business owner. It was great to hit some big milestones this year and see the previous years of hard work pay off.

I’m full of optimism for 2020 and think it’ll be another fantastic year, building on the success of 2019.

Did I Meet My 2019 Goals?

My goals for 2019 were:

  • Create a follow-up Apps Script course — Yes, I achieved this!
  • Create two other courses — 50% success. I launched one other new course
  • Attend the Google Next 19 conference — Yes, it was one of the highlights of the year
  • Continue to grow the community on this site and the online school — Yes (this was a rather meaningless goal without any metrics though!)
  • Hold more webinars in 2019 — This I completely failed at, although I was a guest on a number of webinars and podcasts
  • Deepen my digital analytics and marketing knowledge, and also continue experimenting with data science and Google Cloud topics — I did improve my knowledge of SEO this year, but didn’t really make any progress in other topics. However, I’ve come to realize that my expertise doesn’t lie in this area and my time is best spent on my core business. I do still want to dive deeper into Google Cloud this year though.

2019 Highlights

Online Courses

I launched two new courses:

The Collins School of Data continues to grow with over 20,000 students now! (Thank you all!)

Website

  • I published 17 new pieces of content on benlcollins.com (down on previous years and something I want to increase again in 2020). However, almost all of them are long-form tutorials.
  • The traffic to benlcollins.com continues to climb, reaching around 170k users/month for around 300k pageviews/month. Overall, the site has seen 3.4 million pageviews this year and 1.8 million users. Wow!
  • The traffic grew in the first quarter of the year and then largely plateaued for the rest of the year, so I have work to do to grow again.

My favorite posts of the year were:

Google Sheets Tips Newsletter

  • I sent out a Google Sheets tip every Monday of this year except two (once I was sick and once for Christmas week). I was really happy to achieve this consistency and I’ve seen great growth and engagement around this list.
  • Over 30k people are now signed up to my email list
  • Popular emails were the formula challenges and this long form email on the mind-blowing facts behind card shuffling

Travel & Conferences

This year I traveled three times for work:

  • In April, I attended the Google Next conference in San Francisco. It’s one of the highlights of the year for me because of the energy and people I spend time with. It’s such an inspiration for the rest of the year. Like 2018, I live blogged whilst I was there, covering anything related to Google Sheets. I’ll be there again in 2020. Holler if you’ll be there!
  • In August Google invited me to New York to give a presentation to the Google Sheets team about my work. This was a real honor! I enjoyed meeting the team responsible for this product that I love.
  • In December, I traveled to Copenhagen, Denmark to lead a 75-person workshop for the UN, teaching advanced Google Sheets techniques to help their team transition from Excel to Google Sheets.

Becoming a Google Developer Expert

2019 started in the best possible way!

I became a Google Developer Expert (GDE) in January. It’s been an honor to receive this award and be part of the program. The relationships I’ve developed with other GDEs and Googlers have been fantastic and inspirational. The G Suite GDE family are a great bunch!

I was sad to miss the 2019 GDE summit but hopefully I’ll be there in 2020!

Non-Work Highlights

  • Moving to a mountain town. I LOVE, LOVE, LOVE having the woods and mountains easily accessible. It’s made a huge difference to our lifestyle. (I wrote more about our move from a work perspective and an outdoors perspective.)
  • A huge benefit of living in the mountains are the weekly hikes (like this one up Loudon Heights on the A.T.)
  • A wonderful trip home to the UK to visit family. My brother came over from Australia with his family and it was a joy to see all the cousins play together. My eldest son Dominic, upon meeting his 1 year-old cousin Henry for the first time, exclaimed, “He’s a little bit fat isn’t he?”. It made us all laugh.
  • During this trip, my brother and I had a sublime day hiking in Snowdonia National Park.
  • On the three work trips I did, I explored each city on foot (San Francisco, New York City and Copenhagen).
  • Getting fit again with the weekly hiking, running and biking. It hasn’t all been plain sailing though with injuries and illnesses still impacting my year, but I’m in much better shape than I was in Florida last year.
  • The weekly brainstorming hike with my wife. This has rapidly become one of the highlights of my week.
  • Watching my boys grow. Teaching them about the world. Taking them on hikes. Laughing at their silly questions and funny actions. It’s both the hardest and most rewarding thing I’ve ever done.
  • I read 24 books this year, beating last year’s tally (my long term goal is still 52 books, so some way to go!). My highlights this year were:
    • Company of One – Paul Jarvis
    • Atomic Habits – James Clear
    • The Paris Architect: A Novel – Charles Belfoure
    • Kochland – Christopher Leonard
    • Shadow Divers – Robert Kurson
    • Seveneves – Neal Stephenson
    • The Goldfinch – Donna Tart
  • Other things I enjoyed in 2019:

Challenges In 2019

In recent years I’ve experienced a number of health challenges, and this year was no exception. A nasty chest infection in March turned into pneumonia and landed me in hospital again. I recovered completely but work and life was impacted (another course launch postponed, sigh).

I was pretty healthy through the summer with lots of time outdoors. The trip to the UK was tiring and took some time to recover from. Right at the end of 2019, in the week before Christmas, another chest infection completely knocked me off my feet and necessitated a trip to the ER. A dose of meds straightened me out but it was a rough week.

After years of slow decline in my fitness levels, I reversed the trend this year and am now fitter than I was at the start of the year. I can run and hike much stronger than my Florida self could.

The transition from flat road-running in Florida to mountain running in West Virginia came at a price though. I suffered shin splints for the first time in my life over the Summer and am still managing my running now. I’m confident I’ll be back to normal on that front soon though.

By far the biggest challenge of my life continues to be the balance of being a good husband and father whilst running my own business and keeping fit. It’s hard to not feel like you’re failing on all these fronts, despite going full tilt all the time. All one can do is keep trying!

Looking Forwards To 2020

New Initiatives

  • In January, I’ll be launching a new website, Excel to Sheets, to help organizations migrate from Microsoft Excel to Google Sheets. This is one of the big project areas I want to work on in 2020. Expect to see some blog posts tackling objections that Excel users have to Google Sheets.
  • In March, I’ll be running an online event for all things Google Sheets. It’s going to be huge and very exciting! More details coming soon!

2020 Work Goals

  • Publish more high-quality tutorials than in 2019 (target > 17)
  • Hit 50k newsletter subscribers and send out a tip every Monday
  • Update my existing Google Sheets courses
  • Create one new Google Sheets course
  • Run 10 in-person workshops
  • Re-brand my digital assets
  • Find a VA to help with the business
  • Live-blog Google Next 2020 again
  • Work through this book from last year that I haven’t started yet Data Science on the Google Cloud Platform

Other 2020 Goals

  • My overall number 1 goal for 2020 is to be healthy
  • Fitness goals: be active 5 times/week (a mix of spin classes, runs and at least 1 run/hike up the mountain)
  • Keep up the weekly brainstorming hike with my wife
  • Read 30 books

Thank You

Thank you for giving me this opportunity to share my experiences and knowledge, and carve out this teaching niche.

My mission remains to create a world-class resource for learning Google Sheets and data analysis. The work continues!

Finally, best wishes to all of you for 2020!

Cheers,
Ben

Previous years