SheetsCon 2020: Lessons Learned Running An Online Conference For 6,700 People

SheetsCon

The inaugural edition of SheetsCon — the world’s first dedicated, online conference for Google Sheets — happened on 11th & 12th March 2020.

It was the first time I’ve tried running an online event like this so I had no idea how it would turn out.

It was a big experiment…

…and I’m delighted (and relieved) that it went really well!

Over 6,700 registered for this online conference and we had thousands log in to watch the livestream presentations.

We had 11 world-class experts talk about how they craft solutions using Google Sheets and G Suite.

SheetsCon Replays are available here.

SheetsCon 2020 Online Conference

Online Conference Stats

  • 6,754 registered attendees
  • 3,830 registered attendees (57%) tuned in during the event
  • Day 1: between 800 and 1,500 watching the livestream
  • Day 2: between 650 and 1,000 watching the livestream
SheetsCon stats
Our event registration stats in the backend dashboard (click to enlarge)

What elements of an in-person conference did we have at SheetsCon?

To understand what the SheetsCon event looked like, watch this 2-minute intro video that we used to help people navigate the event:

The event platform we used recreates the elements of an in-person conference online, so we had a main stage, networking, roundtables, an expo hall and chat. The engagement was super high.

To get a fuller flavor of SheetsCon 2020, have a watch of the wrap-up presentation, which summarizes the event and key takeaways:

Presentations on the main stage

Watch replays of all the SheetsCon presentations here.

Day 1

Day 2

Polls

We ran a number of polls throughout the event, related to talks happening on the main stage. It was interesting to gauge audience in real-time and provided useful feedback.

SheetsCon Polls
(Click to enlarge in new tab)

Networking

One of the unique features of the Hopin platform we used was the networking feature.

Networking is one of the most important parts of going to an in-person conference for many people, so I was really excited to have this feature available for this online conference.

It’s a super interesting idea.

If you choose to participate, you get paired up with another random attendee and given a limited time to video chat, one-on-one. If it’s a productive conversation then you can click to exchange contact details to connect.

In practice, it received a mixed reception.

Many people did not want to connect with other random attendees and be on camera. Some experienced technical difficulties that prevented them getting a solid connection. Others tried but found the connections to be too random to be useful.

However, many tried it and loved it!

We could have improved the networking feature by having people network within groups (e.g. educators networking with other educators) and better educating people about how it works.

Some of the feedback we received for this part of the event was very positive:

+ One of my favorite parts of the whole conference! Great addition!

+ Very good. Talked to folks from all over the world. Made a work connection.

+ Loved getting to know people & loved how it forced conversations to be short.

Whereas for others it didn’t really work:

+ It was just ok. The experience felt very random and impersonal.

+ Wasn’t fond of it tbh. I didn’t like the randomness or how it cut off after speaking to someone.

+ Wasn’t comfortable for me, and I didn’t have a camera.

We have some areas to work on to improve the execution for SheetsCon 2021 but I think it will become a really valuable part of our online conferences in the future.

Roundtable Sessions

We ran three roundtable sessions on day two of SheetsCon, covering 1) Google Sheets for Educators, 2) Google Sheets Tips and Tricks and 3) the Apps Script Zone.

These roundtable discussions were open from 3pm to 4pm on day 2, between presentations on the main stage.

Each roundtable discussion had anywhere between 4 and 15 people speaking on the screen and another 80 – 100 listening and participating in the chat.

It was so cool to see these roundtable sessions come to life and happen simultaneously. The community came together and self-regulated these sessions, which were open to any attendees.

Online Conference roundtable discussion room
The Apps Script Zone roundtable discussion room at SheetsCon 2020 (click to enlarge)
Online Conference roundtable discussion room for educators
Google Sheets for Educators roundtable discussion room (click to enlarge)

There was a fourth roundtable session — Google Sheets for Digital Marketers — that didn’t gain any traction.

My overall impression of the roundtables were that they were really popular but I need to have some more direction for next year. I think having a moderator to run each session would fix this.

The feedback on the roundtable sessions was overwhelmingly positive and basically just confirmed my thoughts above:

+ Lots of sharing information and ideas.

+ Very good. Great thoughts and feedback and discussion.

+ It was good, I was able to talk about my knowledge on getting google sheets addons approved in the g suite marketplace.

+ It was a bit confusing without having a moderator leading the discussion and topics, etc.

+ I enjoyed the roundtables – I was only an observer but this allowed me to pop in and out of rooms as the discussions progressed around topics I was interested in and at levels that were appropriate for my knowledge base

+ It was actually interesting. Intimidating but really cool. Very much like in person meeting people at a conference

Expo Hall

SheetsCon 2020 brought all the aspects of a conference online.

We had an expo hall (a page within our SheetsCon event) that showcased our sponsors.

Each sponsor had their own booth (page) with a dedicated chat window and the option to have a live video session (e.g. for product demos, answering customer questions etc.) or an on-demand promo video.

The vendors that ran live sessions saw significantly more engagement than the vendors that only had an on-demand YouTube video.

At one stage, I browsed around the Expo Hall and saw four of the vendors running live software demos simultaneously. Much as the roundtable discussions rooms came to life, it was another eye-opening moment for me as an organizer to see the community in action.

Vendor live demos at SheetsCon 2020
Vendor live demos at SheetsCon 2020, featuring Sheetgo, Tiller Money and Analytics Canvas (click to enlarge)

We got some great feedback on the vendor booths, almost all super positive with some constructive critique that will help us improve next year:

+ I am glad the platform had this option. It is great to learn about the apps and add ons.

+ Some were obviously better than others but I was impressed to see how the platform worked.

+ When there was a live person there, it was great. When it was just a video, not so much.

+ Amazing! My two favorites were interactive and applied to me best. SheetsGo were the most interactive, they were there 100% of the time during breaks, and even contributing in main stage chat!

+ It was fun and unique process.

+ I preferred the demo and interactive ones, rather than just watching a youtube video. it would be great if they could show you the video and also advertise what time they would be giving a demo or available to chat.

I think one obvious improvement for next year would be to give each vendor a time for their product demos, so that people could plan to visit the booth when it was live.

Thanks to the following companies that supported SheetsCon 2020:

SheetsCon 2020 online conference sponsors

Swag Bags

To increase engagement and strengthen the SheetsCon brand, we created swag bags to give away during the conference, consisting of t-shirts and stickers:

SheetsCon Swag Bags

To win one, attendees had to either visit at least 5 of the vendor booths or share something about SheetsCon on social media with the hashtag #SheetsCon2020.

We used a Google Form to collect entries. We had hundreds of entries but only had 25 swag bags to give away.

The t-shirts were so popular we created a quick pop-up shop for people who wanted to buy them (sadly only available in the US). You can still purchase the SheetsCon t-shirts here:

Green SheetsCon T-shirt

Blue SheetsCon T-shirt

Our Online Conference Technology Partner: Hopin

We partnered with the online conference platform Hopin to run this event.

From their website:

“Hopin is the first all-in-one live online events platform where attendees can learn, interact, and connect with people from anywhere in the world.”

That’s exactly what SheetsCon was all about, which is why Hopin were the perfect platform for the vision I had for SheetsCon.

Would I use Hopin again?

Absolutely! 100%. I’m super impressed with the platform.

It wasn’t perfect, but it’s a new category of software. The core concept is AMAZING! I have total faith that the team will continue to improve an already fantastic product.

Online Conference Timeline

3 – 6 months out (October – December 2019)

The idea for an online conference happened around 6 months prior to the event, in October 2019. I was watching a demo webinar from Hopin and was seriously impressed with the software.

I thought about my 2020 plans and really wanted to include more live elements to my training programs.

Thus, SheetsCon was born.

I bought the SheetsCon.com domain on the 29th October and bought a WordPress hosting account with GoDaddy on 11th November 2019.

Through November, I planned out the event and made shortlists of possible speakers, session topics and vendors. I reached out to them in November to get people on board.

Here’s an example email I sent out to potential speakers to see if they were interested:

Hey [Name],

How are you?

I want to share a new project with you and hopefully get you interested in being part of it.

Early next year I’m running SheetsCon — a 2-day online conference for all things Google Sheets.

I’d love for you to be one of the speakers.

My vision is to create an online experience that brings together thousands of Google Sheets users from diverse industries, to learn from experts (like you!), network with other professionals and be inspired by the magic of Google Sheets.

It’s completely online and will happen on the 5 – 6th February 2020.

In addition to sessions on the main channel (which will be like a series of webinars), there’ll be networking rooms, sponsor rooms and panels.

I’d love to have you talk about [XYZ].

Are you interested in hearing more? I’d love to jump on a call and tell you more.

Thanks,
Ben

As you can see, I was planning on a February event date at this point in time.

However, I got sick for a week in December and decided that there was too much to do to meet the early February deadline.

2 months out (January 2020)

I made the decision to delay SheetsCon by a month and move it to 11th/12th March 2020.

So far I’d only confirmed speakers, so it was a relatively small number of people to check in with and thankfully they all agreed to the new dates.

This is the email I sent out to announce the date change:

Hi [Name],

Quick update on SheetsCon 2020, the online Google Sheets conference.

Unfortunately, I got sick at the end of the year so the site is not where it needs to be yet.

I’ve moved the date to the 11th/12th March. Are you still available?

I’ll be in touch soon with more updates. Thanks again for being involved!

Best wishes for 2020!

Ben

1 month out (February 2020)

Logo

I created a logo design contest on 99designs to create a better logo for SheetsCon. I was really pleased with how this turned out.

Here’s the story of how the logo came to be and here’s the final logo:

SheetsCon

Marketing

I announced SheetsCon publicly to my email list, on my website and on social media. I sent all the traffic to the SheetsCon.com from where people could sign up.

The vendors also began sharing with their lists and social channels and this really drove a lot of sign-ups.

The momentum stalled when my whole family got the flu for a week, so I lost a lot of time. Not a major setback though, as everything was still on track at this stage.

Throughout the month, I was onboarding new vendors and doing SheetsCon calls to show vendors how the platform works and what to expect.

I also did at least one practice call with each speaker to show them how the platform worked and what the workflow was like on the day. We also discussed session topics with and ironed out any issues (e.g. ensuring that Google Sheet formulas or code was large enough to be legible on the screen).

I continued to put out information onto Twitter (e.g. this thread) and occasionally on LinkedIn.

1 week to go (March 2nd week)

Disaster! I got sick again for 4 days, urgh!

I had to postpone a bunch of practice run-through calls and had still not prepared my keynote. This illness was a setback for sure, but thank goodness it didn’t come the following week during SheetsCon.

3 days to go

All hands on deck!

My wife joined me full-time this week as the SheetsCon assistant.

I spent sometime creating and practicing my opening keynote, as well as getting the final pieces in place for the event. I created several process docs (Google Docs) for tasks on the day, key info for speakers and vendors, swag bag plans, etc.

We worked from 8am to midnight during the SheetsCon week, so it was a pretty intense week!

This week was also the tipping point for the coronavirus here in the U.S., where public opinion changed, schools began to close, events were cancelled and life changed seemingly overnight.

Coronavirus wasn’t on anyone’s radar when I began planning an online conference, so although it looked prescient from the outside, I always envisioned SheetsCon as an online conference. It allowed us to run the event without any major concerns or changes to the program.

SheetsCon Event (11th/12th March 2020)

Everyone has a plan ’till they get punched in the mouth. – Mike Tyson

The event began with almost 1,500 people active on the SheetsCon event site and watching the opening keynote livestream. The energy was palpable. The chat was going pretty crazy!

I was somewhat insulated as the speaker as I focussed on my presentation.

Part way through my opening keynote, we suffered some technology issues and my livestream went down for about 15 mins. Not the best start and for a while I was oblivious! However, my wife was working with the Hopin team (who were on hand and super helpful) to get things up and running again. My wife was also handling all the comments in the sidebar chat and keeping things positive.

It was a really intense (and stressful) first hour. But we survived and everything was solid thereafter. There were no other major snafus at all.

The event ran for 2 days, which went by in a flash.

Post-event activities

The work never stops!

As the dust settled on the live event, I had a long checklist (around 30+ items) to work through to wrap up SheetsCon.

Surveys for feedback from attendees and sponsors. Downloading all the presentation recordings and putting them together into a replay package with the accompanying slides and templates. Preparing and delivering swag bags to winners.

And of course, planning for SheetsCon 2021 begins!

What went well?

  • More people than we expected
  • Really great community with lots of energy and enthusiasm throughout
  • 11 fantastic speakers on a variety of topics and skill levels
  • The full conference experience online thanks to our technology partner, Hopin
  • Amazing to see roundtable discussions happening simultaneously, with up to 15 speakers and hundreds watching in each room
  • Likewise, amazing to see live demos from at least 4 of the vendors happening simultaneously
  • 89.5% of the attendees plan to return (based on 330 post-event survey responses)
  • 10.0% said they will “maybe” return
  • Only 0.5% said they don’t plan to return (2 responses)

We had really fantastic and supportive feedback from attendees too:

+ Overall it was a great conference. Looking forward to seeing what you add for next year.

+ THANK YOU Ben, and the rest of your team who brought this to us. Events such as this are so extremely helpful!!! 🙂

+ This con was brilliant!! It really got the fire rolling in terms of ideas and applications of sheets. SO happy I can across it and will most definitely be attending next year and every year following!!

+ Thanks for all of your hard work! It was a great first time event. Can’t wait to see how it grows in the future.

+ Thanks for battling through the glitches at the beginning. It was awesome to be a part of the whole thing! I’m super stoked to see what next year’s event brings.

What we need to improve

For a first event I think it went extremely well, but of course there are plenty of things to fix for next year:

  • Better explanations of how the various features work
  • At least one other person to help in the build-up and during the event
  • Moderators for each of the roundtable discussion rooms to lead the conversations
  • Group topics into tracks and/or skill levels
  • Better communication about the exact start time (confusing with the time difference)
  • Potentially making templates and slides from the speakers available ahead of time
  • Better way of communicating with speakers whilst presentation is in progress (e.g. to alert speaker they forgot to share their screen). We used text messaging in the end.
  • T-shirts and merch available throughout the conference and globally, not just the U.S.
  • More structured marketing campaign and possibly paid ads
  • I’ll try not to get sick 3 times (!!) in the 3 months leading up to the event
  • Having more swag bags and merch for attendees 🙂

I’m already thinking about improvements for the next event and can’t wait to bring SheetsCon 2021 to life.

How much did SheetsCon cost?

Here’s a breakdown of the expenses for SheetsCon:

  • Domain name purchase – $156 (included several SheetsCon domain variations)
  • Web hosting for SheetsCon – $131
  • WordPress theme – $79
  • Logo design on 99designs – $599
  • Fee to use Hopin platform – Redacted*
  • Swag bag t-shirts – $535
  • Swag bag stickers – $110
  • Postage for swag bags – $250
  • TOTAL – $1,860*

* Does not include the fee we paid to Hopin (I’m not sharing this publicly as it varies based on your unique situation)

What about revenue?

SheetsCon was free to attend. (The plan is to make it free to attend next year too!)

I also waived sponsor fees this year. I worked with a select few vendors as an experiment with the expectation that they would promote the event through their marketing channels.

I have a lot of ideas for how to monetize next year’s event, SheetsCon 2021.

Whilst SheetsCon 2020 did not make any direct revenue, it was still tremendously beneficial to my business.

Benefits From Running An Online Conference

If I didn’t make any money from SheetsCon, what did I get from it?

A ton!

It was a lot of work, but it was a really fun and energizing two days.

I developed a lot of really good relationships with speakers, vendors and attendees as a result of this online conference.

It was a huge growth and learning experience for me too, as I’ve never put together an event like this.

It established the SheetsCon brand and laid the foundations for future, bigger, events.

It certainly raised my profile within the G Suite and Google Sheets communities.

And it resulted in about 3,500 new and high-quality leads. These are potential future customers for my online training business and future SheetsCon event attendees. For people on my mailing list I typically see a 1 – 2% conversion rate, so applying that here would look something like this: 2% * 3,500 = 70 new buyers @ $299 = $20,930 future revenue (this is a hypothetical illustration).

All in all, it was totally worth it.

See you at SheetsCon 2021!

SheetsCon

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

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

Add A Google Sheets Button To Run Scripts

Learn how to add a Google Sheets button to run your Google Apps Script functions.

Let’s see how this works with a simple example.

Imagine you have an invoice template you use on a regular basis, but it’s a pain to clear out all the values each time you need to start over. Well, you can add a button to Google Sheets so you can run scripts and clear your invoice with a single button click.

Google Sheets button

Let’s start by creating a basic invoice template with placeholders to hold information:

Add button to Google Sheets invoice example

The user can enter information into cells B5, B8, E5 and E6 (shown in yellow).

In the script editor, accessed through Tools > Script Editor, add a very simple script to clear these specific cells out:

function clearInvoice() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var invoiceNumber = sheet.getRange("B5").clearContent();
  var invoiceAmount = sheet.getRange("B8").clearContent();
  var invoiceTo = sheet.getRange("E5").clearContent();
  var invoiceFrom = sheet.getRange("E6").clearContent(); 
}

You can run this function from the script editor and it will clear out the contents of the invoice.

But that’s a pain.

You don’t want to have to open up the script editor every time. You want to do that directly from your Google Sheet.

To do that, add a Google Sheets button.

You add a button via the Insert > Drawing menu.

This brings up the drawing editor where you can easily add a box and style it to look like a button:

Google Sheets button drawing

When you click Save and Close, this drawing gets added to your Google Sheet. You can click on it to resize it or drag it around to reposition it.

To assign a script, click the three little dots in the top right of the drawing and select Assign Script:

Google Sheets button assign script

Then type in the name of the function you want to run from your Apps Script code. In this example, choose the clearInvoice function (i.e. like the code above!).

Now, when you click the button it will clear out the invoice for you!

Button with apps script in google sheets

Note: to edit or move the button after you’ve assigned it to a script, you now need to right-click on it.

See Create, insert & edit drawings in the Google Documentation for more info on the Drawing feature.

Google Apps Script: A Beginner’s Guide

What is Google Apps Script?

Google Apps Script is a cloud based scripting language for extending the functionality of Google Apps and building lightweight cloud-based applications.

What does this mean in practice?

It means you use Apps Script to write small programs that extend the standard features of Google Apps. I like to say it’s great for filling in the gaps in your workflows.

For example, I used to be overwhelmed with feedback from my courses and couldn’t respond to everyone. Now, when a student submits their feedback, my script creates a draft email in Gmail ready for me to review. It includes all the feedback so I can read it within Gmail and respond immediately.

It made a previously impossible task manageable.

With Apps Script, you can do cool stuff like automating repetitive tasks, creating documents, emailing people automatically and connecting your Google Sheets to other services you use.

Writing your first Google Script

In this Google Sheets script tutorial, we’re going to write a script that is bound to our Google Sheet. This is called a container-bound script.

(If you’re looking for more advanced examples and tutorials, check out the full list of Apps Script articles on my homepage.)

Hello World in Google Apps Script

Let’s write our first, extremely basic program, the classic “Hello world” program beloved of computer teaching departments the world over.

Begin by creating a new Google Sheet.

Then click the menu Tools > Script editor... to open a new tab with the code editor window.

This will open a new tab in your browser, which is the Google Apps Script editor window:

Google Apps Script editor window

By default, it’ll open with a single Google Script file (code.gs) and a default code block, myFunction():

function myFunction() {
  
}

In the code window, between the curly braces after the function myFunction() syntax, write the following line of code so you have this in your code window:

function myFunction() {
  Browser.msgBox("Hello World!");
}

Your code window should now look like this:

Google Sheets script tutorial editor menu

Google Apps Script Authorization

Google Scripts have robust security protections to reduce risk from unverified apps, so we go through the authorization workflow when we first authorize our own apps.

When you hit the run button (the black triangle) for the first time, you will be prompted to authorize the app to run:

Google Apps Script Authorization

Clicking Continue pops up another window in turn, showing what permissions your app needs to run. In this instance the app wants to view and manage your spreadsheets in Google Drive, so click Allow (otherwise your script won’t be able to interact with your spreadsheet or do anything):

Google Apps Script Authorization

❗️When your first run your apps script, you may see the “app isn’t verified” screen and warnings about whether you want to continue.

In our case, since we are the creator of the app, we know it’s safe so we do want to continue. Furthermore, the apps script projects in this post are not intended to be published publicly for other users, so we don’t need to submit it to Google for review (although if you want to do that, here’s more information).

Click the “Advanced” button in the bottom left of the review permissions pop-up, and then click the “Go to Starter Script Code (unsafe)” at the bottom of the next screen to continue. Then type in the words “Continue” on the next screen, click Next, and finally review the permissions and click “ALLOW”, as shown in this image (showing different script):

More information can be found in this detailed blog post from Google Developer Expert Martin Hawksey.

Running a function in Apps Script

Once you’ve authorized a Google App script, the function will run (or execute). You will see two status messages to tell you what’s happening.

First this one:

GAS execute script

And then this one:

GAS execute status 2

If anything goes wrong with your code, this is stage when you’d see a warning message (instead of the yellow message, you’ll get a red box with an error message in it).

Now, assuming you got those two yellow status messages and they’ve both automatically disappeared from view, then your program has run successfully. Click back on the browser tab with your spreadsheet (most likely the tab to the left of the one we’re in).

You should see the output of your program, a message box popup with the classic “Hello world!” message:

Google Apps Script output hello world

Click on Ok to dismiss.

Great job! You’ve now written your first apps script program.

Rename functions in Google Apps Script

We should rename our function to something more meaningful.

At present, it’s called myFunction which is the default, generic name generated by Google. Every time I want to call this function (i.e. run it to do something) I would write myFunction(). This isn’t very descriptive, so let’s rename it to helloWorld(), which gives us some context.

So change your code in line 1 from this:

function myFunction() {
  Browser.msgBox("Hello World!");
}

to this:

function helloWorld() {
  Browser.msgBox("Hello World!");
}

Note, it’s convention in Apps Script to use the CamelCase naming convention, starting with a lowercase letter. Hence, we name our function helloWorld, with a lowercase h at the start of hello and an uppercase W at the start of World.

Adding a custom menu in Google Apps Script

In its current form, our program is pretty useless for many reasons, not least because we can only run it from the script editor window and not from our spreadsheet.

Let’s fix that by adding a custom menu to the menu bar of our spreadsheet, so that a user can run the script within the spreadsheet without needing to open up the editor window.

This is actually surprisingly easy to do, requiring only a few lines of code. Add the following 6 lines of code into the editor window, above the helloWorld() function we created above, as shown here:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('My Custom Menu')
      .addItem('Say Hello', 'helloWorld')
      .addToUi();
}

function helloWorld() {
  Browser.msgBox("Hello World!");
}

If you look back at your spreadsheet tab in the browser now, nothing will have changed. You won’t have the custom menu there yet. We need to re-open our spreadsheet (refresh it) or run our onOpen() script first, for the menu to show up.

To run onOpen() from the editor window, first select the onOpen function as shown in this image:

Google Apps Script custom menu

Once you’ve selected the onOpen function, the small triangle button will change from light gray to black, meaning it can be clicked to run your chosen function:

Run function

Now, when you return to your spreadsheet you’ll see a new menu on the right side of the Help option, called My Custom Menu. Click on it and it’ll open up to show a choice to run your Hello World program:

Custom menu

Run functions from buttons in Google Sheets

An alternative way to run Google Scripts from your Sheets is to bind the function to a button in your Sheet.

For example, here’s an invoice template Sheet with a RESET button to clear out the contents:

Button with apps script in google sheets

For more information on how to do this, have a look at this post: Add A Google Sheets Button To Run Scripts

Google Apps Script Examples

Macros in Google Sheets

Another great way to get started with Google Scripts is by using Macros. Macros are small programs in your Google Sheets that you record so that you can re-use them (for example applying a standard formatting to a table). They use Apps Script under the hood so are a great way to get started in seeing what you can do.

Read more: The Complete Guide to Simple Automation using Google Sheets Macros

Custom function using Google Apps Script

Let’s create a custom function with Apps Script, and also demonstrate the use of the Maps Service. We’ll be creating a small custom function that calculates the driving distance between two points, based on Google Maps Service driving estimates.

The goal is to be able to have two place-names in our spreadsheet, and type the new function in a new cell to get the distance, as follows:

GAS custom function for maps

The solution should be:

GAS custom map function output

Copy the following code into the Apps Script editor window and save. First time, you’ll need to run the script once from the editor window and click “Allow” to ensure the script can interact with your spreadsheet.

function distanceBetweenPoints(start_point, end_point) {
  // get the directions
  var directions = Maps.newDirectionFinder()
     .setOrigin(start_point)
     .setDestination(end_point)
     .setMode(Maps.DirectionFinder.Mode.DRIVING)
     .getDirections();
  
  // get the first route and return the distance
  var route = directions.routes[0];
  var distance = route.legs[0].distance.text;
  return distance;
}

Saving data with Google Apps Script

Let’s take a look at another simple use case for this Google Sheets Apps Script tutorial.

Here, I’ve setup an importxml function to extract the number of followers a specific social media channel has (e.g. in this case a Reddit channel), and I want to save copy of that number at periodic intervals, like so:

save data in google sheet

In this script, I’ve created a custom menu (as we did above) to run my main function. The main function, saveData(), copies the top row of my spreadsheet (the live data) and pastes it to the next blank line below my current data range as text, thereby “saving” a snapshot in time.

The code for this example is:

// custom menu function
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
      .addItem('Save Data','saveData')
      .addToUi();
}

// function to save data
function saveData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var url = sheet.getRange('Sheet1!A1').getValue();
  var follower_count = sheet.getRange('Sheet1!B1').getValue();
  var date = sheet.getRange('Sheet1!C1').getValue();
  sheet.appendRow([url,follower_count,date]);
}

See this post: Saving Data in Google Sheets, for a step-by-step guide to creating and running this script.

Google Apps Script example in Google Docs

Google Apps Script is by no means confined to Sheets only, and is equally applicable in the Google Docs environment. Here’s a quick example of a script that inserts a specific symbol or text string into your Doc wherever your cursor is:

Google Docs Apps Script

We do this using Google App Scripts as follows:

1. Create a new Google Doc

2. Open script editor from the menu: Tools > Script editor...

3. Click on: Create script for > Blank Project

Google Apps script menu

4. In the newly opened Script tab, remove all of the boilerplate code (the `myFunction` code block)

5. Copy in the following code:

// code to add the custom menu
function onOpen() {
  var ui = DocumentApp.getUi();
  ui.createMenu('My Custom Menu')
      .addItem('Insert Symbol', 'insertSymbol')
      .addToUi();
};

// code to insert the symbol
function insertSymbol() {  
  // add symbol at the cursor position
  var cursor = DocumentApp.getActiveDocument().getCursor();
  var element = cursor.insertText('§§');
  
};

6. You can change the special character in this line

var element = cursor.insertText('§§');

to whatever you want it to be, e.g.

var element = cursor.insertText('( ͡° ͜ʖ ͡°)');

7. Click Save and give your script project a name (doesn’t affect the running so call it what you want e.g. Insert Symbol)

8. Run the script for the first time by clicking on the menu: Run > onOpen

9. Google will recognize the script is not yet authorized and ask you if you want to continue. Click Continue

10. Since this the first run of the script, Google Docs asks you to authorize the script (I called my script “test” which you can see below):

Docs Apps Script Auth

11. Click Allow

12. Return to your Google Doc now.

13. You’ll have a new menu option, so click on it:
My Custom Menu > Insert Symbol

14. Click on Insert Symbol and you should see the symbol inserted wherever your cursor is.

Google Apps Script Tip: Use the Logger class

Use the Logger class to output text messages to the log files, to help debug code.

The log files can be accessed after the program has finished running, by going to View > Show Logs (or Cmd + Enter, or Ctrl + Enter (on PC)).

The syntax in its most basic form is Logger.log(something in here). This records the value(s) of variable(s) at different steps of your program.

For example, add this script to a code file your editor window:

function logTimeRightNow() {
  var timestamp = new Date();
  Logger.log(timestamp);
}

Run the script in the editor window, then View > Show Logs and you should see:

logger output

Real world examples from my own work

I’ve only scratched the surface of what’s possible using G.A.S. to extend the Google Apps experience.

Here’s a couple of interesting projects I’ve worked on:

1) A Sheets/web-app consisting of a custom web form that feeds data into a Google Sheet (including uploading images to Drive and showing thumbnails in the spreadsheet), then creates a PDF copy of the data in the spreadsheet and automatically emails it to the users. And with all the data in a master Google Sheet, it’s possible to perform data analysis, build dashboards showing data in real-time and share/collaborate with other users.

2) A dashboard that connects to a Google Analytics account, pulls in social media data, checks the website status and emails a summary screenshot as a PDF at the end of each day.

Marketing dashboard using Google Apps Script

3) A marking template that can send scores/feedback to students via email and Slack, with a single click from within Google Sheets. Read more in this article: Save time with this custom Google Sheets, Slack & Email integration

Send data from Google Sheets to Slack

My own journey into Google Apps Script

My friend Julian, from Measure School, interviewed me in May 2017 about my journey into Apps Script and my thoughts on getting started:

Google Apps Script Resources

For further reading, I’ve created this list of resources for information and inspiration:

Course

Documentation

Official Google Documentation

G Suite Developers Blog

Communities

Google Apps Script Group

Stack Overflow GAS questions

Books

Going GAS book

Going GAS by Bruce Mcpherson is a newly published (i.e. bang up-to-date as of April 2016) book covering the entire GAS ecosystem, with a specific focus on making the transition from Office/VBA into Google Apps/GAS. Even if you don’t use Office or VBA much or at all, it’s still a very useful resource. It’s been a few years since I’ve done any serious VBA work, but I still found the book very helpful and a great overview of the GAS environment.


Imagination and patience to learn are the only limits to what you can do and where you can go with GAS. I hope you feel inspired to try extending your Sheets and Docs and automate those boring, repetitive tasks!

Related Articles