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

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

Goal Seek in Google Sheets

Goal Seek for Sheets is an Add-On for Google Sheets for doing Goal Seek type data analysis.

In October 2019, Google launched an official Add-On, called “Goal Seek for Sheets”, and it is that Add-On that this tutorial references.

1. What is Goal Seek?

It’s a tremendously powerful and useful technique in data analysis. It’s a process where you set an output you want to achieve (e.g. break even, sell 10k units, save $1m) and let the computer find the input value that will get you there (e.g. 500 attendees, $100k capital lump sum, save $8k/year).

There are three components: 1) the unknown input variable, 2) the equation or calculation that is performed on the input variables to get the output, and 3) the known output.

The Goal Seek algorithm performs a series of “what-if” calculations by plugging in different input values. Each guess (hopefully) gets closer and closer to the solution.

For example, a classic use case of Goal Seek is to determine the number of sales required to break even, given other variables like fixed costs etc.

2. How do you use Goal Seek in Google Sheets?

Imagine Jennifer runs an annual conference for Google Sheet developers called “Sheet Freakz 🀟”.

She has a great venue picked out with room for 500 and she’s confident she can fill it. She knows what her costs are — the rental fee for the room, the cost of catering, the cost of promoting the conference — and she has agreed a $1,500 fee with 15 Google Sheets experts to come and talk about the latest and greatest in Sheets developments.

(Editor note: I wish this was a real conference!! πŸ˜„)

What price must she charge to cover her costs?

This is a classic break even cost analysis example that the Goal Seek Add-On is ideally suited for solving.

Setting up the Sheet

The first step is to simply add all of the known variables into a sheet, like so:

Goal Seek Variables in Google Sheet

These are the variables that Jennifer knows at the start of her problem.

Next, add a line for the registration fee per attendee, but set it to 0 for now. I’ve highlighted the cell yellow to indicate that it’s the solution cell that I want Goal Seek to solve for:

Goal Seek Variables

Finally, add a profit line, which is my revenue (# of attendees * registration fee) less expenses (fixed costs + (# of speakers * speaker fee)):

= ( B7 * B8 ) - ( B4 + ( B5 * B6 ) )

Goal Seek setup

Of course, initially, my profit is -$47,500 because I have no attendees and hence $0 revenue.

It’s time to use Goal Seek and let it find the break even registration fee for us.

How do you add Goal Seek in Google Sheets?

Goal Seek is an Add-On, which means you need to add it to your Google Sheet before you can use it.

Search for “Goal Seek” in the Add-Ons marketplace, found under the menu Add-ons > Get add-ons

The official Google Add-On information page will appear.

Click to install. That’s all it takes to add it to your Sheets.

Goal Seek for Sheets

You can also find it in the G Suite marketplace directly by clicking here.

Using Goal Seek

Open the Goal Seek sidebar: Add-ons > Goal Seek > Open

There are three pieces of information you need to enter:

i) Set Cell

Jennifer wants to know what price to charge to break even. In other words, what’s the minimum ticket price to ensure her profit is $0 and she doesn’t lose any money on the conference.

The “Set Cell” is the one we want to specify a value for. It’s the target we’re aiming for.

It’s the cell with the calculation formula in.

** With the Goal Seek sidebar open, click on the cell with the formula, which is cell B10 in this case (1). Add that reference to the Goal Seek solver by clicking on the grid icon next to the Set Cell box (2). This will auto-populate with the cell B10 reference. **

Select cells in Goal Seek for Sheets

ii) To Value

Next, type in the value of the output you want to achieve in the “Set Cell” box.

In this example, we want to set the profit value to 0, so we simply type 0 into this input box.

iii) By Changing Cell

What input variable do we want to vary to solve our equation?

In this case it’s the registration fee. It’s the variable that Jennifer is trying to find, such that her profit is $0 and she breaks even.

Select the cell that holds this variable and then click on the grid icon next to the input field to auto-populate it.

Read it out loud to understand what you’re asking the application to do: “Set Cell X To Value Y By Changing Cell Z”.

In our case, “Set Cell Profit To $0 By Changing Cell Registration Fee” or even cleaner “Set Profit To $0 By Changing Registration Fee”.

When you have all three inputs filled in for the Goal Seek application, the Solve button will turn blue and become active.

Press it.

The registration fee value will start jumping around all over the place as the computer tries different guesses to see what brings the profit value closer to 0.

Eventually it’ll find a solution and notify you that it’s done!

Goal Seek to determine break even cost

In this example, it’s found that the break even registration fee is $94.99999 dollars, or $95. Great!

Click here to open a Google Sheet template with all the examples from this tutorial >>

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

If the file won’t open without permission, please open in an incognito window and copy from there.)

Manual Checks

It’s always a good idea to check the final solution that the Add-On finds, and not just trust it blindly.

In our example, it’s very simple to check that the two sides of the equation balance.

Jennifer’s expenses to run the conference are:

Expenses
$25,000 fixed costs + ( 15 speakers @ $1,500 each ) = 25,000 + ( 15 * 1,500 ) = $47,500

And her revenue on the other side of the equation will be:

Revenue
500 attendees * $94.99 registration fee (Goal Seek solution) = $47,499.99

The difference is simply a rounding error.

This is good.

The result from the Goal Seek is indeed a solution that gets Jennifer the break even registration price.

Another way to look at how the Goal Seek solver works is to visualize it. A very simplified version might look something like this:

Attempt 1

The computer has no idea what the solution is, so it makes a guess. For example, it might overestimate the result:

Guess 1: Overestimate

Attempt 2

The computer makes another guess. This time it might underestimate the result:

Guess 2: Underestimate

(It won’t always be a neat over/under/over/under guess. For example, if it guesses low, it might take many guesses before the first “over” guess happens. So this over/under flow is just to illustrate the concept.)

Attempt 3

With each additional “guess” the computer gets more accurate, because it uses the information from prior guesses to get closer to the solution. It might still overestimate, as shown here, but it’s getting closer to the solution:

Guess 3: Overestimate

Attempts 4 onwards

So on and so forth, as the computer makes guesses that get closer and closer, under, over, under, over, under, over, etc. until the solution is found:

Converging on solution

The program converges on the solution.

3. Other features of the Goal Seek Add-On

These features are all found in the sidebar underneath the input section for the Goal Seek variables.

Options

Under the Options menu, you can adjust the default settings for the Goal Seek solver.

You can change the 1) max number of iterations, 2) the tolerance (how accurate you need to be) and 3) the maximum time limit for the process, in seconds.

Goal Seek Settings

I’d suggest that the default settings will suffice for the majority of scenarios, but it’s good to know that you can make these changes should you need to.

Solve Status

The Solve Status box displays helpful information about the current Goal Seek solution.

It lets you know when the algorithm is finished, what the final status is, how many iterations it required and how long it took.

Solve Status in Goal Seek for Sheets

History

Access previous runs of the Goal Seek solver under the History menu.

Use the drop down menu to choose a prior solution based on a timestamp.

Goal Seek History

Error Messages

Occasionally the Goal Seek solver fails to find a solution.

One reason might be that the computer guesses get successively further away from the actual solution. They diverge away from the solution.

Should this happen, you’ll see an error message like this:

Goal Seek error message

4. More Goal Seek examples

Conference Break Even Example For Attendee Numbers

In the conference example above, instead of knowing how many people would attend, suppose Jennifer knew the registration fee.

She charges $299 for the registration fee and wants to know how many attendees she requires to break even?

The “changing cell” in this case becomes the number of attendees, rather than the registration fee.

The setup would be:

Goal Seek conference Example 2

The formula in cell B10 does not change from the original example. It’s still:

= ( B7 * B8 ) - ( B4 + ( B5 * B6 ))

The Goal Seek settings are:

Settings for conference example 2

Run Goal Seek and the answer comes back as 158.86.

In other words Jennifer needs 159 attendees paying a registration fee of $299 to break even.

Mortgage Calculation Example

Suppose you’re looking to buy a new house. You have an upper limit for your monthly payments of $1,500.

The other known variables in this case are: it’s a 30 year term with an annual interest rate of 4.5%.

What’s the maximum amount you can borrow?

Goal Seek can solve this for you.

Firstly, setup the sheet with the known variables in your Sheet:

Mortgage Goal Seek Example

The payment equation uses the PMT function in Google Sheets in cell B8:

= -PMT( B6 / 12 , B5 , B7 )

In Goal Seek, set the “Set Cell” to be this equation in cell B8.

Set the “To Value” to $1,500 (the maximum monthly payment that can be tolerated).

Set the “By Changing Cell” to the Amount Borrowed in cell B7 (currently $0.00).

Click Solve and let Goal Seek find your solution.

The algorithm will churn through the possible solutions until it settles on one that satisfies your tolerance (accuracy) setting.

In this case, the maximum amount you could borrow is $296,041.75.

Mortgage Solver Solution

Retirement Calculation Example

Suppose you want to retire with a pot of $1.5m in 40 years time. You’re confident of getting a 5% return on your investments.

What’s the annual contribution you need to make each year to hit this target?

Let’s use Goal Seek to find out.

Firstly, setup the sheet with the known variables in your Sheet:

Retirement Goal Seek Example

The calculation of the retirement pot value uses the Future Value function, the FV function, in Google Sheets in cell B7:

= FV( B5 , B4 , -B6 , 0 , 0 )

In Goal Seek, set the “Set Cell” to be this equation in cell B7.

Set the “To Value” to $1,500,000 (your target retirement pot).

Set the “By Changing Cell” to the Annual Contribution in cell B6 (currently $0.00).

Click Solve and let Goal Seek find your solution.

In this case, you need to contribute $12,417 each year to hit your retirement pot target of $1.5m.

Retirement Solution

Click here to open a Google Sheet template with all the examples from this tutorial >>

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

If the file won’t open without permission, please open in an incognito window and copy from there.)

Resources

Google Documentation on the Goal Seek feature.

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.