Blog

Apps Script V8 Runtime Is Here! What Does That Mean?

In February 2020, Google announced the launch of the V8 runtime for Apps Script, which is the same runtime environment that powers Chrome. It allows us to take advantage of all the modern JavaScript features.

A runtime environment is the engine that interprets your code and executes the instructions.

Historically, Apps Script used a runtime environment called Rhino, which locked Apps Script to an older version of JavaScript that excluded modern JavaScript features.

But no more!

In this guide, we’ll explore the basics of the new V8 runtime, highlighting the features relevant for beginner-to-intermediate level Apps Script users.

Enabling The Apps Script V8 Runtime

When you open the Apps Script editor, you’ll see a yellow notification bar at the top of your editor window prompting you to enable V8:

Enable V8 runtime in Apps Script

If you don’t see this notification, you can select Run > Enable new Apps Script runtime powered by V8

Enable V8 runtime in Apps Script

Save your script to complete the enabling process.

If you need to return to the old version (in the unlikely scenario your script isn’t compatible with the new V8 runtime) then you can switch back to the old Rhino runtime editor.

Select Run > Disable new Apps Script powered by V8.


New Logging In The Apps Script V8 Runtime

The new V8 runtime logger shows both the Logger.log and console.log results for the most recent execution under the View > Logs menu.

Previously the console results where only accessible via the Stackdriver Logging service.

Here’s an example showing the Logger and console syntax (notice Logger is capitalized and console is not, it matters):

function loggerExample() {
  Logger.log("Hello, world from Logger.log!");
  console.log("Hello, world from console.log!")
}

The output in our logger window (accessed via View > Logs) shows both of these results:

Logger and console logs in V8


Modern JavaScript Features

There are a lot of exciting new features available with modern JavaScript. They look strange at first but don’t panic!

There’s no need to start using them all immediately.

Just keep doing what you’re doing, writing your scripts and when you get a chance, try out one of the new features. See if you can incorporate it in your code and you’ll gradually find ways to use them.

Here are the new V8 features in a vague order of ascending difficulty:

Multi-line comments

We can now create multi-line strings more easily by using a back tick syntax:

// new V8 method
var newString = `This is how we do 
multi-line strings now.`;

This is the same syntax as template literals and it greatly simplifies creating multi-line strings.

Previously each string was restricted to a single line. To make multi-line comments we had to use a plus-sign to join them together.

// old method
var oldString = 'This is how we used\n'
+ 'to do multi-line strings.'; 

Default Parameters

The Apps Script V8 runtime lets us now specify default values for parameters in the function definition.

In this example, the function addNumbers simply logs the value of x + y.

If we don’t tell the function what the values of x and y are, it uses the defaults we’ve set (so x is 1 and y is 2).

function addNumbers(x = 1, y = 2) {
  console.log(x + y);
}

When we run this function, the result in the Logger is 3.

What’s happening is that the function assigns the default values to x and y since we don’t specify values for x and y anywhere else in the function.

let Keyword

The let statement declares a variable that operates locally within a block.

Consider this fragment of code, which uses the let keyword to define x and assign it the variable of 1. Inside the block, denoted by the curly brackets {…}, x is redefined and re-assigned to the value of 2.

let x = 1;
  
{
  let x = 2;
  console.log(x); // output of 2 in the logs
}
  
console.log(x); // output of 1 in the logs

The output of this in the logs is the values 2 and 1, because the second console.log is outside the block, so x has the value of 1.

Note, compare this with using the var keyword:

var x = 1;
  
{
  var x = 2;
  console.log(x); // output of 2 in the logs
}
  
console.log(x); // output of 2 in the logs

Both log results give the output of 2, because the value of x is reassigned to 2 and this applies outside the block because we’re using the var keyword. (Variables declared with var keyword in non-strict mode do not have block scope.)

const Keyword

The const keyword declares a variable, called a constant, whose value can’t be changed. Constants are block scoped like the let variable example above.

For example, this code:

const x = 1;
x = 2; 
console.log(x);

gives an error when we run it because we’re not allowed to reassign the value of a constant once it’s been declared:

const keyword error

Similarly, we can’t declare a const keyword without also assigning it a value. So this code:

const x;

also gives an error when we try to save our script file:

Apps Script V8 runtime const error message

Spread syntax

Suppose we have the following array of data:

var arr = [[1,2],[3.4],[5,6]];

It’s an array of arrays, so it’s exactly the format of the data we get from our Sheets when we use the getRange().getValues() method.

Sometimes we want to flatten arrays, so we can loop over all the elements. Well, in V8, we can use the spread operator (three dots … ), like so:

var flatArr = [].concat(...arr);

This results in a new array: [1,2,3,4,5,6]

Template Literals

Template literals are a way to embed expressions into strings to create more complex statements.

One example of template literals is to embed expressions within normal strings like this:

let firstName = 'Ben';
let lastName = 'Collins';
console.log(`Full name is ${firstName} ${lastName}`);

The logs show “Full name is Ben Collins”

In this case, we embed a placeholder between the back ticks, denoted by the dollar sign with curly brackets ${ some_variable }, which gets passed to the function for evaluation.

The multi-line strings described above are another example of template literals.

Arrow Functions

Arrow functions provide a compact way of writing functions.

Arrow Function Example 1

Here’s a very simple example:

const double = x => x * 2;

This expression creates a function called double, which takes an input x and returns x multiplied by 2.

This is functionally equivalent to the long-hand function:

function double(x) {
  return x * 2;
}

If we call either of these examples and pass in the value 10, we’ll get the answer 20 back.

Arrow Function Example 2

In the same vein, here’s another arrow function, this time a little more advanced.

Firstly, define an array of numbers from 1 to 10:

const arr = [1,2,3,4,5,6,7,8,9.10];

This arrow function will create a new array, called evenArr, consisting of only the even numbers.

const evenArr = arr.filter(el => (el % 2 === 0));
console.log(evenArr);

The filter only returns values that pass the conditional test: (el % 2 === 0) which translates as remainder is 0 when dividing by 2 i.e. the even numbers.

The output in the logs is [2,4,6,8]:

Apps Script V8 runtime arrow function logs

Other Advanced Features

There are more advanced features in V8 that are not covered in this post, including:

I’m still exploring them and will create resources for them in the future.


Migrating Scripts To Apps Script V8 Runtime

The majority of scripts should run in the new V8 runtime environment without any problems. In all likelihood, the only adjustment you’ll make is to enable the new V8 runtime in the first place.

However, there are some incompatibilities that may cause your script to fail or behave differently.

But for beginner to intermediate Apps Scripters, writing relatively simple scripts to automate workflows in G Suite, it’s unlikely that you’ll have any problems.

You can read more about migrating scripts to the V8 runtime and incompatibilities in the detailed documentation from Google.


Other Apps Script V8 Runtime Resources

V8 Runtime Overview

ES 6 Features for Google Apps Script: Template Literals

ES6 Features for Google Apps Script: Arrow Functions

Here’s a good explanation of the V8 runtime from Digital Inspiration

The new V8 runtime offers significant performance improvements over the old Rhino editor. Your code will run much, much faster! Here’s a deep dive: Benchmark: Loop for Array Processing using Google Apps Script with V8

Unpivot In Google Sheets With Formulas, Or How To Turn Wide Data Into Tall Data

Unpivot in Google Sheets is a method to turn “wide” tables into “tall” tables, which are more convenient for analysis.

Suppose we have a wide table like this:

Wide Data Table

We want to transform that data — unpivot it — into the tall format that is the way databases store data:

Unpviot in Google Sheets

But how do we unpivot our data like that?

It turns out it’s quite hard.

Much harder than going the other direction, pivoting tall data into wide data tables.

This article looks at how to do it using formulas, which is challenging and obtuse.

The formulas are complex and difficult to read so it’s hard to recommend this method in a production setting.

But it’s a fascinating look at advanced formulas in Google Sheets and I’m certain you’ll learn something new along the way.

If you need to do this in a production setting, then you might want to consider using the Apps Script code or example sheet from the first answer of this Stack Overflow post.

But if you’re ready for some complex formulas, let’s dive in…

Unpivot in Google Sheets – Solution 1

We’ll use the wide dataset shown in the first image at the top of this post, in Sheet1 of our Google Sheet.

Remember, what we’re trying to do is transform the wide data table into the tall data table. The output of our formulas should look like the second image in this post.

In other words, we need to create 16 rows to account for the different pairings of Customer and Product, e.g. Customer 1 + Product 1, Customer 1 + Product 2, etc. all the way up to Customer 4 + Product 4.

Of course, we’ll employ the Onion Method to understand these formulas.

Template

Click here to open the Unpivot in Google Sheets template

Feel free to make your own copy (File > Make a copy…).

(If you can’t open the file, it’s likely because your G Suite account prohibits opening files from external sources. Talk to your G Suite administrator or try opening the file in an incognito browser.)

Customers Column

To start, create a second Sheet and add a simple header row in row 1, with “Customer”, “Product” and “Value” in cells A1, B1 and C1 respectively.

Let’s create an array formula to populate the customers column. In cell A2, enter this:

=COUNTA(Sheet1!$1:$1)

This formula gives the count of the number of columns — 4 — in our wide dataset (assuming cell A1 in our original dataset is empty, per the first image of this post).

QUICK NOTE: when copy-pasting these formulas into your own Google Sheets, paste them directly into the formula bar to avoid any issues.

Similarly, this next formula would give the count of the number of rows — 3 — in our wide dataset (again assuming cell A1 in our original dataset is empty, per the first image of this post).

=COUNTA(Sheet1!$A:$A)

Multiplying these two together gives us the number of values in our table — 12 — which corresponds to the number of rows we’ll need in our new tall data table:

=COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1)

So let’s create those 12 rows!

Wrap this with the SEQUENCE function, starting from 1:

=SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)

Now divide that by the count of rows:

=SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)/COUNTA(Sheet1!$A:$A)

Hmm, it gives an answer of 0.3333333 but we’ve lost our 12 rows…

…so turn it into an Array Formula silly!

=ArrayFormula(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)/COUNTA(Sheet1!$A:$A))

Ah, that’s better. “But how does it help us?” I hear you ask.

Let’s round all those decimals up to the nearest integer, like so:

=ArrayFormula(ROUNDUP(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)/COUNTA(Sheet1!$A:$A)))

Nice!

We now have the column vector 1,1,1,2,2,2,3,3,3,4,4,4 with repeating positions, which is exactly what we needed.

For the moment, leave this formula alone and let’s move to cell B2 to construct the next piece. We want to create a table of the column headings that we can “lookup” with those repeating positions. Don’t worry, it’ll make more sense in a moment!

Ok, so start with this formula in B2:

=ArrayFormula(COLUMN(Sheet1!$1:$1))

And try this formula in B3:

=ArrayFormula(Sheet1!$1:$1)

Can you see what we’re doing yet?

Let’s combine these in cell B2 as follows:

=ArrayFormula({COLUMN(Sheet1!$1:$1);Sheet1!$1:$1})

and delete the formula in cell B3.

The output should look the same, but it’s created with a single formula.

Now we can use the HLOOKUP function to lookup those positions into this data array we’ve created.

Change our formula in cell A2 to:

=ArrayFormula(HLOOKUP(ROUNDUP(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)/COUNTA(Sheet1!$A:$A)),{COLUMN(Sheet1!$1:$1);Sheet1!$1:$1},2))

It’s nearly right, but the answer is offset slightly. Hmm.

Ah ok, it’s that blank cell in A1 of the original data that we didn’t account for. Our repeating positions really start from 2. It’s a simple fix to just add 1 to them.

=ArrayFormula(HLOOKUP(ROUNDUP(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)/COUNTA(Sheet1!$A:$A))+1,{COLUMN(Sheet1!$1:$1);Sheet1!$1:$1},2))

That’s the customers populated in column 1.

What about the products in column 2?

Products Column

Well, it’s an almost identical formula, so I’ll just share it here and leave it to the reader to use the Onion Method to build it in steps.

Actually, no I won’t, that’s just me being lazy. Let’s walk through it together.

It’s a similar idea, but it looks a little different because we do a vertical lookup.

So, if you haven’t already, clear out cells B2 and B3.

Start with this SEQUENCE formula in cell B2:

=SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)

This time we want a sequence that looks like 1,2,3,1,2,3,1,2,3 etc. i.e. repeating. This calls for the MOD squad, I mean MOD function.

=MOD(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1),COUNTA(Sheet1!$A:$A))

Oops, make it an Array Formula:

=ArrayFormula(MOD(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1),COUNTA(Sheet1!$A:$A)))

Ah, that’s better. But it gives 1,2,0,1,2,0 etc. so it’s not quite right. Fix the ordering by subtracting 1 from the dividend of the MOD function:

=ArrayFormula(MOD(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)-1,COUNTA(Sheet1!$A:$A)))

Now we have 0,1,2,0,1,2 etc.

Add 2 to this to get the repeating positions we want 2,3,4,2,3,4 (again, we start from 2 to account for the blank cell in A1 of our original dataset).

=ArrayFormula(MOD(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)-1,COUNTA(Sheet1!$A:$A))+2)

Leave this formula sitting pretty for a moment, and begin a new one in cell C2. Build an array for the vertical lookup with this formula (feel free to build in steps, I’m jumping straight to the array version):

=ArrayFormula({ROW(Sheet1!$A:$A),Sheet1!$A:$A})

Now we can combine this into the formula in cell B2, using a VLOOKUP:

=ArrayFormula(VLOOKUP(MOD(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)-1,COUNTA(Sheet1!$A:$A))+2,{ROW(Sheet1!$A:$A),Sheet1!$A:$A},2))

Woohoo!

There’s our products in repeating order and paired correctly with the customer column.

That leaves the values associated with each pair.

Values Column

Thankfully this is much simpler, using a standard INDEX / MATCH / MATCH construction to look up each pair.

The row offset in the INDEX function is found by matching the product with the product categories in column A of our original data, i.e.

=MATCH(B2,Sheet1!$A:$A,0)

The column offset is found by matching the customers, i.e.

=MATCH(A2,Sheet1!$1:$1,0)

Plug these both into the INDEX function:

=INDEX(Sheet1!$1:$1000,MATCH(B2,Sheet1!$A:$A,0),MATCH(A2,Sheet1!$1:$1,0))

which gives the value of 61 for the first pair, Customer 1 and Product 1.

Drag this formula down the column to fill in all the rows.

“Wait, what? Where’s the array formula? Can’t I just wrap this INDEX / MATCH / MATCH with an array formula wrapper?”

No bueno, I’m afraid.

The INDEX function does not play well with the Array Formula, so this option cannot be turned into an array formula.

Be patient, in solution 2 we’ll generalize this to use an array formula, but we have to approach it in a different, more verbose way.

Unpivot in Google Sheets – Solution 2

Leaving the Customer and Product array formula columns well alone, let’s focus purely on the Values column.

We left solution 1 with a somewhat unsatisfactory INDEX / MATCH / MATCH formula for the values that, ahem, had to be dragged down the column — oh the horror! — because it wasn’t an array formula.

Gasp! We don’t like such manual work.

So let’s create an array formula to grab the values we need.

Think of the standard VLOOKUP:

=VLOOKUP( search_key, data, column_index, false )

The search_key is the repeating array 2,3,4,2,3,4,2,3,4 etc. created using the same formula construction as the first part of the Products formula from Solution 1.

The column_index is the repeating array 2,2,2,3,3,3,4,4,4, etc. created using the same formula construction as the first part of the Customers formula from Solution 1.

When you plug these into the VLOOKUP, you’re searching for 2 and returning column 2, then searching for 3 returning column 2, searching 4 returning column 2, then searching 2 returning column 3, etc.

In other words, traversing the array of values and grabbing each one in turn.

The data needs to be setup by adding a search column at the front, which is done using the curly brackets array literal construction, like so:

=ArrayFormula({ROW(Sheet1!$A$2:$A),Sheet1!$B$2:$1000})

All that’s left is to combine them into the VLOOKUP, like so:

=ArrayFormula(VLOOKUP(MOD(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1,2)-2,COUNTA(Sheet1!$A:$A))+2,{ROW(Sheet1!$A$2:$A),Sheet1!$B$2:$1000},ROUNDUP(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)/COUNTA(Sheet1!$A:$A))+1))

Voila! Clear as mud, huh?

Unpivot in Google Sheets – Solution 3

In this solution, all we do is combine the three columns together into a single, giant array formula, using the curly bracket array literal construction.

Starting with the three columns combined:

= ArrayFormula({ Customer_Formula, Product_Formula, Values_Formula })

Next, we’ll wrap it with a QUERY function to remove null values:

= ArrayFormula( QUERY( { Customer_Formula, Product_Formula, Values_Formula } , "SELECT * WHERE Col3 IS NOT NULL" ))

The full array construction, with a static header row added, is:

=ArrayFormula( {"Customer","Product","Value";
QUERY( { Customer_Formula , Product_Formula , Values_Formula } , "SELECT * WHERE Col3 IS NOT NULL" )})

We can then simply plug in the Customer_Formula, Product_Formula and Values_Formula to create a one-stop shop for unpivoting our data:

=ArrayFormula({"Customer","Product","Value"; QUERY({HLOOKUP(ROUNDUP(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)/COUNTA(Sheet1!$A:$A))+1,{COLUMN(Sheet1!$1:$1);Sheet1!$1:$1},2),
VLOOKUP(MOD(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)-1,COUNTA(Sheet1!$A:$A))+2,{ROW(Sheet1!$A:$A),Sheet1!$A:$A},2),
VLOOKUP(MOD(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1,2)-2,COUNTA(Sheet1!$A:$A))+2,{ROW(Sheet1!$A$2:$A),Sheet1!$B$2:$1000},ROUNDUP(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)/COUNTA(Sheet1!$A:$A))+1)
},"SELECT * WHERE Col3 IS NOT NULL")})

Unpivot in Google Sheets – Solution 4

This one really blew my mind when I first saw it and picked it apart.

Kudos to this person on Stack Overflow for the original amazing answer.

I’ve modified it slightly, but have merely contributed a minor update to an ingenious and original solution.

Here it is, in all it’s mysterious detail:

=ArrayFormula({"Customer","Product","Value";
QUERY(IFERROR(SPLIT(TRIM(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&"🐠"&Sheet1!B1:1&"🐠"&Sheet1!B2:Z&"🌢", )), , 500000)), , 500000)),"🌢"))),"🐠"),""),"SELECT Col2, Col1, Col3 ORDER BY Col2 OFFSET 1",0)})

First off, what on earth are those fish 🐠 and chili peppers 🌢 doing in this formula? Is this some kind of joke?

No, no, my friend. Read on and you’ll find out!

But before we do that, let me show you the amazing trick with the QUERY function that is key to this formula.

Taking our dataset again:

Unpivot in Google Sheets wide table

Try this formula in cell H1:

=QUERY(A1:E4,"SELECT A",4)

See what it does?

Query headers trick

It joins the values in column A into a single string, because we’ve told the QUERY function to treat all 4 rows as headers. Crazy!

Even better, you can skip the SELECT statement altogether, like this:

=QUERY(A1:E4,,4)

which results in all of the columns being concatenated:

Query headers trick

Now that is interesting!

And it’s at the heart of how this crazy formula works.

Let’s build it up in steps, following the Onion Method.

The innermost IF function is (note the value_if_false argument is empty):

=ArrayFormula(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&"🐠"&Sheet1!B1:1&"🐠"&Sheet1!B2:Z&"🌢", ))

which gives the following output:

Array If formula

For every row of data, the formula joins them such that each cell has a unique combination of product, customer and value.

Next we transpose this array and join using the funky QUERY-header row trick above:

=ArrayFormula(QUERY(TRANSPOSE(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&"🐠"&Sheet1!B1:1&"🐠"&Sheet1!B2:Z&"🌢", )),,500000))

This gives a #REF! error, with the message “Result was not automatically expanded, please insert more columns (699).”

The array output is too wide for our current Sheet.

Wrap it with a transpose function to fix this and get all the data in a single column:

=ArrayFormula(TRANSPOSE(QUERY(TRANSPOSE(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&"🐠"&Sheet1!B1:1&"🐠"&Sheet1!B2:Z&"🌢", )),,500000)))

Use a second QUERY function with this headers trick to bring these values together:

=ArrayFormula(TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&"🐠"&Sheet1!B1:1&"🐠"&Sheet1!B2:Z&"🌢", )),,500000)),,500000)))

Now we basically just split this up based on the fish “🐠” and chili pepper “🌢” symbols that we used to separate the data packets.

Here’s the first split and transpose:

=ArrayFormula(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&"🐠"&Sheet1!B1:1&"🐠"&Sheet1!B2:Z&"🌢", )),,500000)),,500000)),"🌢")))

By now, our data looks like this, which is getting closer:

Unpivot in Google Sheets

Use the TRIM function to fix those unsightly spacing issues.

Next, split it again across the tropical fish:

=ArrayFormula(SPLIT(TRIM(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&"🐠"&Sheet1!B1:1&"🐠"&Sheet1!B2:Z&"🌢", )),,500000)),,500000)),"🌢"))),"🐠"))

Unpivot in Google Sheets

Nearly there now!

Remove the #VALUE! error with an IFERROR wrapper function. Use a QUERY wrapper to re-order the rows and columns as required. The OFFSET removes a blank row from showing up in the table. The formula now looks like this:

=ArrayFormula(QUERY(IFERROR(SPLIT(TRIM(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&"🐠"&Sheet1!B1:1&"🐠"&Sheet1!B2:Z&"🌢", )),,500000)),,500000)),"🌢"))),"🐠"),""),"SELECT Col2, Col1, Col3 ORDER BY Col2 OFFSET 1"))

And the output like this:

Split function Google Sheets

The final step is borrowed from Solution 3 above, namely combining a static header row with array literals.

=ArrayFormula({"Customer","Product","Value"; MAIN_FORMULA })

Now we can insert our formula into this construction, in place of the MAIN_FORMULA placeholder:

=ArrayFormula({"Customer","Product","Value";
QUERY(IFERROR(SPLIT(TRIM(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&"🐠"&Sheet1!B1:1&"🐠"&Sheet1!B2:Z&"🌢", )), , 500000)), , 500000)),"🌢"))),"🐠"),""),"SELECT Col2, Col1, Col3 ORDER BY Col2 OFFSET 1",0)})

Crazy formula in Google Sheets

Further Reading

For more information on the shape of datasets, have a read of Spreadsheet Thinking vs. Database Thinking.

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.

And Apps Script is designed for automation in G Suite.

(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.

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

Don’t Miss Future Formula Challenge!

There are more formula challenges in the pipeline.

If you want to get involved and up your Google Sheets game whilst you exercise the ‘ole grey matter, consider signing up to my Google Sheets Tips newsletter.

Google Sheets Sort By Color And Google Sheets Filter By Color, With Apps Script

Excel has a handy feature that lets you sort and filter datasets by the cell color. Unfortunately, there is no native Google Sheets sort by color or filter by color.

However, with a few simple lines of Apps Script, we can implement our own version.

Filter By Color in Google Sheets

This article will show you how to implement that same feature in Google Sheets.

It’s a pretty basic idea.

We need to know the background color of the cell we want to sort or filter with (user input 1). Then we need to know which column to use to do the sorting or filtering (user input 2). Finally we need to do the sort or filter.

So step one is to to prompt the user to input the cell and columns.

I’ve implemented this Google Sheets sort by color using a modeless dialog box, which allows the user to click on cells in the Google Sheet independent of the prompt box. When the user has selected the cell or column, we store this using the Properties Service for retrieval when we come to sort or filter the data.

Google Sheets Sort By Color

At a high level, our program has the following components:

  1. Custom menu to run the Google Sheets sort by color program
  2. Prompt to ask user for the color cell
  3. Save the color cell using the Properties Service
  4. Second prompt to ask the user for the sort/filter column
  5. Save the sort/filter column using the Properties Service
  6. Show the color and column choices and confirm
  7. Retrieve the background colors of the sort/filter column
  8. Add helper column to data in Sheet with these background colors
  9. Sort/Filter this helper column, based on the color cell
  10. Clear out the values in the document Properties store

Let’s look at each of these sections in turn.

Add A Custom Menu (1)

This is simply boilerplate Apps Script code to add a custom menu to your Google Sheet:

/**
 * Create custom menu
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Color Tool')
        .addItem('Sort by color...', 'sortByColorSetupUi')
        .addItem('Clear Ranges','clearProperties')
        .addToUi();
}

Prompt The User For Cell And Column Choices (2, 4 and 6 above)

I use modeless dialog boxes for the prompts, which allows the user to still interact with the Sheet and click directly on the cells they want to select.

/**
 * Sort By Color Setup Program Flow
 * Check whether color cell and sort columnn have been selected
 * If both selected, move to sort the data by color
 */
function sortByColorSetupUi() {
  
  var colorProperties = PropertiesService.getDocumentProperties();
  var colorCellRange = colorProperties.getProperty('colorCellRange');
  var sortColumnLetter = colorProperties.getProperty('sortColumnLetter');
  var title='No Title';
  var msg = 'No Text';
  
  //if !colorCellRange
  if(!colorCellRange)  {
    title = 'Select Color Cell';
    msg = '<p>Please click on cell with the background color you want to sort on and then click OK</p>';
    msg += '<input type="button" value="OK" onclick="google.script.run.sortByColorHelper(1); google.script.host.close();" />';
    dispStatus(title, msg);
  }
  
  //if colorCellRange and !sortColumnLetter
  if (colorCellRange && !sortColumnLetter) {
      
      title = 'Select Sort Column';
      msg = '<p>Please highlight the column you want to sort on, or click on a cell in that column. Click OK when you are ready.</p>';
      msg += '<input type="button" value="OK" onclick="google.script.run.sortByColorHelper(2); google.script.host.close();" />';
      dispStatus(title, msg);
  }
  
  // both color cell and sort column selected
  if(colorCellRange && sortColumnLetter) {
    
    title= 'Displaying Color Cell and Sort Column Ranges';
    msg = '<p>Confirm ranges before sorting:</p>';
    msg += 'Color Cell Range: ' + colorCellRange + '<br />Sort Column: ' + sortColumnLetter + '<br />';
    msg += '<br /><input type="button" value="Sort By Color" onclick="google.script.run.sortData(); google.script.host.close();" />';
    msg += '<br /><br /><input type="button" value="Clear Choices and Exit" onclick="google.script.run.clearProperties(); google.script.host.close();" />';
    dispStatus(title,msg);
    
  }
}

/**
 * display the modeless dialog box
 */
function dispStatus(title,html) {
  
  var title = typeof(title) !== 'undefined' ? title : 'No Title Provided';
  var html = typeof(html) !== 'undefined' ? html : '<p>No html provided.</p>';
  var htmlOutput = HtmlService
     .createHtmlOutput(html)
     .setWidth(350)
     .setHeight(200);
 
  SpreadsheetApp.getUi().showModelessDialog(htmlOutput, title);

}

/**
 * helper function to switch between dialog box 1 (to select color cell) and 2 (to select sort column)
 */
function sortByColorHelper(mode) {
  
  var mode = (typeof(mode) !== 'undefined')? mode : 0;
  switch(mode)
  {
    case 1:
      setColorCell();
      sortByColorSetupUi();
      break;
    case 2:
      setSortColumn();
      sortByColorSetupUi();
      break;
    default:
      clearProperties();
  }
}

The buttons on the dialog boxes use the client-side google.script.run API to call server-side Apps Script functions.

Following this, the google.script.host.close() is also a client-side JavaScript API that closes the current dialog box.

Save The Cell And Column Choices In The Property Store (3 and 5)

These two functions save the cell and column ranges that the user highlights into the Sheet’s property store:

/** 
 * saves the color cell range to properties
 */
function setColorCell() {
  
  var sheet = SpreadsheetApp.getActiveSheet();
  var colorCell = SpreadsheetApp.getActiveRange().getA1Notation();
  var colorProperties = PropertiesService.getDocumentProperties();
  colorProperties.setProperty('colorCellRange', colorCell);

}

/**
 * saves the sort column range in properties
 */
function setSortColumn() {
  
  var sheet = SpreadsheetApp.getActiveSheet();
  var sortColumn = SpreadsheetApp.getActiveRange().getA1Notation();
  var sortColumnLetter = sortColumn.split(':')[0].replace(/\d/g,'').toUpperCase(); // find the column letter
  var colorProperties = PropertiesService.getDocumentProperties();
  colorProperties.setProperty('sortColumnLetter', sortColumnLetter);
  
}

As a result of running these functions, we have the color cell address (in A1 notation) and the sort/filter column letter saved in the Property store for future access.

Sorting The Data (7, 8 and 9 above)

Once we’ve selected both the color cell and sort column, the program flow directs us to actually go ahead and sort the data. This is the button in the third dialog box, which, when clicked, runs this call google.script.run.sortData();.

The sortData function is defined as follows:

/** 
 * sort the data based on color cell and chosen column
 */
function sortData() {
  
  // get the properties
  var colorProperties = PropertiesService.getDocumentProperties();
  var colorCell = colorProperties.getProperty('colorCellRange');
  var sortColumnLetter = colorProperties.getProperty('sortColumnLetter');

  // extracts column letter from whatever range has been highlighted for the sort column
  
  // get the sheet
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();
  
  // get an array of background colors from the sort column
  var sortColBackgrounds = sheet.getRange(sortColumnLetter + 2 + ":" + sortColumnLetter + lastRow).getBackgrounds(); // assumes header in row 1
  
  // get the background color of the sort cell
  var sortColor = sheet.getRange(colorCell).getBackground();
  
  // map background colors to 1 if they match the sort cell color, 2 otherwise
  var sortCodes = sortColBackgrounds.map(function(val) {
    return (val[0] === sortColor) ? [1] : [2];
  });
  
  // add a column heading to the array of background colors
  sortCodes.unshift(['Sort Column']);
  
  // paste the background colors array as a helper column on right side of data
  sheet.getRange(1,lastCol+1,lastRow,1).setValues(sortCodes);
  sheet.getRange(1,lastCol+1,1,1).setHorizontalAlignment('center').setFontWeight('bold').setWrap(true);
  
  // sort the data
  var dataRange = sheet.getRange(2,1,lastRow,lastCol+1);  
  dataRange.sort(lastCol+1);
  
  // add new filter across whole data table
  sheet.getDataRange().createFilter();

  // clear out the properties so it's ready to run again
  clearProperties();
}

And finally, we want a way to clear the properties store so we can start over.

Clear The Property Store (10 above)

This simple function will delete all the key/value pairs stored in the Sheet’s property store:

/**
 * clear the properties
 */
function clearProperties() {
  PropertiesService.getDocumentProperties().deleteAllProperties();
}

Run The Google Sheets Sort By Color Script

If you put all these code snippets together in your Code.gs file, you should be able to run onOpen, authorize your script and then run the sort by color tool from the new custom menu.

Here’s the sort by color tool in action in Google Sheets:

Google Sheets sort by color

You can see how all of the green shaded rows are sorted to the top of my dataset.

Note that this sort by color feature is setup to work with datasets that begin in cell A1 (because it relies on the getDataRange() method, which does the same).

Some improvements would be to make it more generalized (or prompt the user to highlight the dataset initially). I also have not included any error handling, intentionally to keep the script as simple as possible to aid understanding. However, this is something you’d want to consider if you want to make this solution more robust.

Google Sheets Sort By Color Template

Here’s the Google Sheet template for you to copy.

(If you’re prompted for permission to open this, it’s because my G Suite domain, benlcollins.com, is not whitelisted with your organization. You can talk to your G Suite administrator about that. Alternatively, if you open this link in incognito mode, you’ll be able to view the Sheet and copy the script direct from the Script Editor.)

If GitHub is your thing, here’s the sort by color code in my Apps Script repo on GitHub.

Google Sheets Filter By Color

The program flow is virtually identical, except that we filter the data rather than sort it. The code is almost exactly the same too, other than variable names being different and implementing a filter instead of a sort.

Rather than sorting the data, we create and add a filter to the dataset to show only the rows shaded with the matching colors:

Filter By Color in Google Sheets

The filter portion of the code looks like this:

// remove existing filter to the data range
if (sheet.getFilter() !== null) {
  sheet.getFilter().remove();
}

// add new filter across whole data table
var newFilter = sheet.getDataRange().createFilter();

// create new filter criteria
var filterCriteria = SpreadsheetApp.newFilterCriteria();
filterCriteria.whenTextEqualTo(filterColor);

// apply the filter color as the filter value
newFilter.setColumnFilterCriteria(lastCol + 1, filterCriteria);

If you want a challenge, see if you can modify the sort code to work with the filter example.

Google Sheets Filter By Color Template

Feel free to copy the Google Sheets filter by color template here.

(If you’re prompted for permission to open this, it’s because my G Suite domain, benlcollins.com, is not whitelisted with your organization. You can talk to your G Suite administrator about that. Alternatively, if you open this link in incognito mode, you’ll be able to view the Sheet and copy the script direct from the Script Editor.)

Or pull the code directly from the GitHub repo here.

Add-On

Lastly, there is an add-on, called Sort Range Plus, which allows you to do a sort by color. I have not used it however.