In this tutorial, you’ll learn how to merge cells in Google Sheets, when to use merged cells in Google Sheets, the pros and cons of using merged cells, and finally, how to identify them with Apps Script.
Continue reading How To Merge Cells In Google Sheets And When To Be Careful
Tag: Apps Script
Sentiment Analysis For Google Tables Using Apps Script
In this post we’ll use Google Cloud’s Natural Language API to do sentiment analysis on tickets submitted to a Google Tables Support Issue Tracker.
We’ll use Google Tables as the platform for our Support Tracker and Apps Script to connect to the Cloud Natural Language API.
Here’s a GIF showing a portion of the Issue tracker to illustrate what happens when an issue ticket is submitted:
The description (which someone fills out when they submit a support ticket) is sent to the Natural Language API for sentiment analysis, to determine if it’s positive (i.e. good) or negative (i.e. bad) feedback.
This information is returned to our Google Tables table and displayed as a tag alongside each row, so that we can group tickets by sentiment.
For example, we may want to prioritize tickets that are either extremely positive or extremely negative, as these are probably the most important items to double down on or fix!
Sentiment Analysis Google Tables Overview
Here’s the architecture of the system:
In words:
- User submits support ticket
- New row of data added to our table
- Bot is triggered and sends data to our Apps Script webhook
- The Apps Script receives data and parses it
- Apps Script sends data to Natural Language API
- And receives the sentiment scores back from API
- Apps Script parses score to create sentiment tag
- And updates the original row of data in our table
This all happens in a under a second, so it feels almost simultaneous.
Inspiration for this idea came from the excellent sentiment analysis in Google Sheets post originally published by Alicia Williams.
Note: Since I’ve set this up using a webhook triggered by each new row, the API is called once for every new row. If we were building a high volume ticket system, we’d want to consider a different set up where we send the data through in batches and have the Apps Script running on a timer trigger instead.
Sentiment Analysis Google Tables Set Up
For this example, I’m starting with the default “New ? Support Ticket Queue” template from the Google Tables team.
1. Open Google Tables, login and select Templates > New ? Support Ticket Queue
2. Add three new columns: 1) Sentiment Score (number), 2) Sentiment Magnitude (number) and 3) Sentiment Tag (Tags).
3. Edit the new Sentiment Tag column and add the following tags: Super happy!, Happy, Satisfied, No opinion, Frustrated, Angry, Super angry!
(You can edit these categories to whatever you want, but you’ll need them to match the tags in your Apps Script.)
Sentiment Analysis Apps Script Set Up
4. Create a blank Apps Script file in Drive or through the Apps Script dashboard
5. Clear out the boiler plate code and add the following code to declare the two global variables we need for this project (we’ll fill them in soon):
/** * global variables */ const API_KEY = ''; // <-- enter google cloud project API key const TABLE_NAME = ''; // <-- enter google tables table ID
Since we just created our Table, let’s copy in the Table ID.
6. We find our table’s ID, by looking at the URL and copying the string right after /table/.
Table URLs can take the following form:
https://tables.area120.google.com/u/0/workspace/abcdefghijklmnop/table/TABLE_ID
https://tables.area120.google.com/u/0/table/TABLE_ID
https://tables.area120.google.com/u/0/table/TABLE_ID/view/abcedfghijk
Look for the string represented by the TABLE_ID in these fictional examples.
7. Paste this ID string into our Apps Script project, between the quotes in the line where we declare the variable TABLE_NAME:
const TABLE_NAME = ''; // <-- enter google tables table ID
Sentiment Analysis Google Cloud Set Up
This is probably the most difficult part of this whole project! 😉
For this to work, we need a Google Cloud account with billing set up. Don’t worry though, the Natural Language API is free for the first 5k “units” we use (each unit is worth 1000 characters). This is way more than we need to set this project up and test it out.
The full details of how to set up Google Cloud Natural Language API can be found in the documentation.
The steps to take are:
8. If you don’t already have a Cloud account, register for a Google Cloud account and set up billing.
9. Create a project in the Google Cloud account to use for this project.
10. Enable the Cloud Natural Language API, by clicking the link half-way down this page.
11. Create credentials for the Cloud Natural Language API. From the Cloud console choose the project we created in step 5 and navigate to APIs & Services > Credentials
Generate a new API Key through +CREATE CREDENTIALS > API key
12. Restrict the API key we generated to the Cloud Natural Language API
13. Copy the API key and paste it into our Apps Script file, between the quotes in the line where we declare the variable API_KEY (see code above):
const API_KEY = ''; // <-- enter google cloud project API key
Sentiment Analysis Google Tables Code
Staying in the Apps Script file, let’s add the webhook listener and main control function code for our program.
We use a special function called doPost(e) { } so that we can (eventually) publish our script as a web app. The doPost function sits there listening for a ping from our Google Tables bot (which we’ll set up later).
When a new row is added to our Google Table by a Form submission, the bot is triggered and sends the data we need through to our webhook.
This doPost function receives that data, parses it and sends it to the Natural Language API for sentiment analysis.
The returned sentiment data is parsed and sent back to our table to update the new row, using the patch method of the Area120Tables service.
14. Add the following doPost code:
/**
* doPost webhook to catch data from Google Tables
*/
function doPost(e) {
if (typeof e !== 'undefined') {
// parse data
const data = JSON.parse(e.postData.contents);
// get the id and description
const rowId = data.id
const description = data.description;
// analyze sentiment
const sentiment = analyzeFeedback(description); // [nlScore,nlMagnitude,emotion]
// combine arrays
const sentimentArray = [rowId,description].concat(sentiment);
// send score back to Google Tables
const rowName = 'tables/' + TABLE_NAME + '/rows/' + rowId;
const sentimentValues = {
'Sentiment Score': sentiment[0],
'Sentiment Magnitude': sentiment[1],
'Sentiment Tag': sentiment[2]
};
Area120Tables.Tables.Rows.patch({values: sentimentValues}, rowName);
return null;
}
}
In the code above, we call a function called analyzeFeedback, so we had better declare it.
This function handles the logic around the NL scores and how to interpret them as human readable tags. Feel free to play around with the boundaries. The sentiment score is bounded between -1 (max negative) and 1 (max positive), but the magnitude only has a lower bound of 0, so can be any positive number.
For more about the interpretation of the sentiment scoring, have a read of this page in the documentation.
15. Add the following code to our Apps Script file to analyze the sentiment scores:
/**
* Get each new row of form data and retrieve the sentiment
* scores from the NL API for text in the feedback column.
*/
function analyzeFeedback(description) {
if (description !== '') {
// call the NL API
const nlData = retrieveSentiment(description);
nlMagnitude = nlData.documentSentiment.magnitude ? nlData.documentSentiment.magnitude : 0; // set to 0 if nothing returned by api
nlScore = nlData.documentSentiment.score ? nlData.documentSentiment.score : 0; // set to 0 if nothing returned by api
//console.log(nlMagnitude);
//console.log(nlScore);
}
else {
// set to zero if the description is blank
nlMagnitude = 0;
nlScore = 0;
}
// turn sentiment numbers into tags
let emotion = '';
// happy
if (nlScore > 0.5) {
if (nlMagnitude > 2) { emotion = 'Super happy!'; } // higher magnitude gets higher emotion tag
else { emotion = 'Happy'; }
}
// satisfied
else if (nlScore > 0) { emotion = 'Satisfied'; }
// frustrated
else if (nlScore < 0 && nlScore >= -0.5) { emotion = 'Frustrated'; }
// angry
else if (nlScore < -0.5) {
if (nlMagnitude > 2) { emotion = 'Super angry!'; } // higher magnitude gets higher emotion tag
else { emotion = 'Angry'; }
}
// if score is 0
else { emotion = 'No opinion' }
return [nlScore,nlMagnitude,emotion];
}
Finally we need to declare the function called retrieveSentiment to actually call the API.
16. Add the code to call the NL API:
/**
* Calls Google Cloud Natural Language API with string from Tables
*/
function retrieveSentiment(description) {
//console.log(description);
const apiEndpoint = 'https://language.googleapis.com/v1/documents:analyzeSentiment?key=' + API_KEY;
// Create our json request, w/ text, language, type & encoding
const nlData = {
document: {
language: 'en-us',
type: 'PLAIN_TEXT',
content: description
},
encodingType: 'UTF8'
};
// Package all of the options and the data together for the call
const nlOptions = {
method : 'post',
contentType: 'application/json',
payload : JSON.stringify(nlData)
};
// Try fetching the natural language api
try {
// return the parsed JSON data if successful
const response = UrlFetchApp.fetch(apiEndpoint, nlOptions);
return JSON.parse(response);
} catch(e) {
// log the error message and return null if not successful
console.log("Error fetching the Natural Language API: " + e);
return null;
}
}
Press save!
The full code is available here on GitHub.
Since we’re using the Area 120 Tables Apps Script service, we need to enable it for this project.
17. Go to Resources > Advanced Google services… and switch on Area120 Tables API:
Publish As Web App
18. Publish this file as a web app via the menu: Publish > Deploy as a web app…
19. Set the access to Anyone, even anonymous, as shown in this image:
We’ll be prompted to review permissions:
followed by a review of the project scopes:
Click Allow.
This is a one-time step the first time we publish to the web or run our script (unless we add additional services in the future).
20. Copy the URL of the web app so we can paste that into our Tables bot, which we’ll create next!
Sentiment Analysis Tables Bot Set Up
The final piece of the puzzle is the bot in Google Tables.
When the issue tracker form is submitted it creates a new row of data in our table, which triggers the bot. The bot sends the data to the webhook (i.e. the code above) that handles the rest.
21. Create a new bot with the following specification:
Trigger: row added
Action: Send to webhook
Webhook URL: Our Apps Script web app URL from step 20
Webhook format: POST with JSON
Request parameters:
id : [[record_id]]
description : {{description}}
Visually, this is the bot:
The red arrow indicates where we paste the Apps Script web app URL.
Test The Sentiment Analysis Google Tables Tool
Finally, we’re ready to submit the form.
22. From the Google Table, click on the Support Ticket Form to open it in a new tab:
23. Submit it with a strong positive or negative sentiment in the description field (which is the one we send to the Natural Language API) to test out the Natural Language scores.
You’ll see the row of data arrive when we submit the form and then, a few moments later, the sentiment analysis columns get automatically populated too!
That’s it! Let me know how you get on in the comments.
The Complete Guide to Simple Automation using Google Sheets Macros
💡 Learn moreLearn how to write Apps Script and turbocharge your Google Workspace experience with the new Beginner Apps Script courseGoogle Sheets Macros are small programs you create inside of Google Sheets without needing to write any code.
They’re used to automate repeatable tasks. They work by recording your actions as you do something and saving these actions as a “recipe” that you can re-use again with a single click.
For example, you might apply the same formatting to your charts and tables. It’s tedious to do this manually each time. Instead you record a macro to apply the formatting at the click of a button.
In this article, you’ll learn how to use them, discover their limitations and also see how they’re a great segue into the wonderful world of Apps Script coding!
Contents
- What are Google Sheets macros?
- Why should you use macros?
- How to create your first macro
- Other options
- Best Practices for Google Sheets Macros
- Limitations of Google Sheets Macros
- A peek under the hood of Google Sheets Macros
- Example of Google Sheets Macros
- Resources
1. What are Google Sheets macros?
Think of a typical day at work with Google Sheets open. There are probably some tasks you perform repeatedly, such as formatting reports to look a certain way, or adding the same chart to new sales data, or creating that special formula unique to your business.
They all take time, right?
They’re repetitive. Boring too probably. You’re just going through the same motions as yesterday, or last week, or last month. And anything that’s repetitive is a great contender for automating.
This is where Google Sheets macros come in, and this is how they work:
- Click a button to start recording a macro
- Do your stuff
- Click the button to stop recording the macro
- Redo the process whenever you want at the click of a button
They really are that simple.
2. Why should you use macros in Google Sheets?
There’s the obvious reason that macros in Google Sheets can save you heaps of time, allowing you to focus on higher value activity.
But there’s a host of other less obvious reasons like: avoiding mistakes, ensuring consistency in your work, decreased boredom at work (corollary: increased motivation!) and lastly, they’re a great doorway into the wonderful world of Apps Script coding, where you can really turbocharge your spreadsheets and Google Workspace work.
3. Steps to record your first macro
Let’s run through the process of creating a super basic macro, in steps:
1) Open a new Google Sheet (pro-tip 1: type sheets.new into your browser to create a new Sheet instantly, or pro-tip 2: in your Drive folder hit Shift + s to create a new Sheet in that folder instantly).
Type some words in cell A1.
2) Go to the macro menu: Tools > Macros > Record macro
3) You have a choice between Absolute or Relative references. For this first example, let’s choose relative references:
Absolute references apply the formatting to the same range of cells each time (if you select A1:D10 for example, it’ll always apply the macro to these cells). It’s useful if you want to apply steps to a new batch of data each time, and it’s in the same range location each time.
Relative references apply the formatting based on where your cursor is (if you record your macro applied to cell A1, but then re-run the macro when you’ve selected cell D5, the macro steps will be applied to D5 now). It’s useful for things like formulas that you want to apply to different cells.
4) Apply some formatting to the text in cell A1 (e.g. make it bold, make it bigger, change the color, etc.). You’ll notice the macro recorder logging each step:
5) When you’ve finished, click SAVE and give your Macro a name:
(You can also add a shortcut key to allow quick access to run your macro in the future.)
Click SAVE again and Google Sheets will save your macro.
6) Your macro is now available to use and is accessed through the Tools > Macros menu:
7) The first time you run the macro, you’ll be prompted to grant it permission to run. This is a security measure to ensure you’re happy to run the code in the background. Since you’ve created it, it’s safe to proceed.
First, you’ll click Continue on the Authorization popup:
Then select your Google account:
Finally, review the permissions, and click Allow:
8) The macro then runs and repeats the actions you recorded on the new cell you’ve selected!
You’ll see the following yellow status messages flash across the top of your Google Sheet:
and then you’ll see the result:
Woohoo!
Congratulations on your first Google Sheets macro! You see, it was easy!
Here’s a quick GIF showing the macro recording process in full:
And here’s what it looks like when you run it:
4. Other options
4.1 Macro Shortcuts
This is an optional feature when you save your macro in Google Sheets. They can also be added later via the Tools > Macros > Manage macros menu.
Shortcuts allow you to run your macros by pressing the specific combination of keys you’ve set, which saves you further time by not having to click through the menus.
Any macro shortcut keys must be unique and you’re limited to a maximum of 10 macro shortcut keys per Google Sheet.
In the above example, I could run this macro by pressing:
⌘ + option + shift + 1
keys at the same time (takes practice ?). Will be a different key combo on PC/Chromebooks.
4.2 Deleting macros
You can remove Google Sheets macros from your Sheet through the manage macros menu: Tools > Macros > Manage macros
Under the list of your macros, find the one you want to delete. Click the three vertical dots on right side of macro and then choose Remove macro:
4.3 Importing other macros
Lastly, you can add any functions you’ve created in your Apps Script file to the Macro menu, so you can run them without having to go to the script editor window. This is a more advanced option for users who are more comfortable with writing Apps Script code.
This option is only available if you have functions in your Apps Script file that are not already in the macro menu. Otherwise it will be greyed out.
5. Best Practices for Google Sheets Macros
Use the minimum number of actions you can when you record your macros to keep them as performant as possible.
For macros that make changes to a single cell, you can apply those same changes to a range of cells by highlighting the range first and then running the macro. So it’s often not necessary to highlight entire ranges when you’re recording your macros.
6. Limitations of Google Sheets Macros
Macros are bound to the Google Sheet in which they’re created and can’t be used outside of that Sheet. Similarly, macros written in standalone Apps Script files are simply ignored.
Macros are not available for other Google Workspace tools like Google Docs, Slides, etc. (At least, not yet.)
You can’t distribute macros as libraries or define them in Sheets Add-ons. I hope the distribution of macros is improved in the future, so you can create a catalog of macros that is available across any Sheets in your Drive folder.
7. A peek under the hood of Google Sheets Macros
Behind the scenes, macros in Google Sheets converts your actions into Apps Script code, which is just a version of Javascript run in the Google Cloud.
If you’re new to Apps Script, you may want to check out my Google Apps Script: A Beginner’s Guide.
If you want to take a look at this code, you can see it by opening the script editor (Tools > Script editor or Tools > Macros > Manage macros).
You’ll see an Apps Script file with code similar to this:
/** @OnlyCurrentDoc */
function FormatText() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getActiveRangeList().setFontWeight('bold')
.setFontStyle('italic')
.setFontColor('#ff0000')
.setFontSize(18)
.setFontFamily('Montserrat');
};
Essentially, this code grabs the spreadsheet and then grabs the active range of cells I’ve selected.
The macro then makes this selection bold (line 5), italic (line 6), red (line 7, specified as a hex color), font size 18 (line 8), and finally changes the font family to Montserrat (line 9).
The video at the top of this page goes into a lot more detail about this Apps Script, what it means and how to modify it.
Macros in Google Sheets are a great first step into the world of Apps Script, so I’d encourage you to open up the editor for your different macros and check out what they look like.
(In case you’re wondering, the line /** @OnlyCurrentDoc */ ensures that the authorization procedure only asks for access to the current file where your macro lives.)
8. Examples of Google Sheets Macros
8.1 Formatting tables
Record the steps as you format your reporting tables, so that you can quickly apply those same formatting steps to other tables. You’ll want to use Relative references so that you can apply the formatting wherever your table range is (if you used absolute then it will always apply the formatting to the same range of cells).
Check out the video at the top of the page to see this example in detail, including how to modify the Apps Script code to adjust for different sized tables.
8.2 Creating charts
If you find yourself creating the same chart over and over, say for new datasets each week, then maybe it’s time to encapsulate that in a macro.
Record your steps as you create the chart your first time so you have it for future use.
The video at the top of the page shows an example in detail.
The following macros are intended to be copied into your Script Editor and then imported to the macro menu and run from there.
8.3 Convert all formulas to values on current Sheet
Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:
// convert all formulas to values in the active sheet
function formulasToValuesActiveSheet() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getDataRange();
range.copyValuesToRange(sheet, 1, range.getLastColumn(), 1, range.getLastRow());
};
Back in your Google Sheet, use the Macro Import option to import this function as a macro.
When you run it, it will convert any formulas in the current sheet to values.
8.4 Convert all formulas to values in entire Google Sheet
Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:
// convert all formulas to values in every sheet of the Google Sheet
function formulasToValuesGlobal() {
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
sheets.forEach(function(sheet) {
var range = sheet.getDataRange();
range.copyValuesToRange(sheet, 1, range.getLastColumn(), 1, range.getLastRow());
});
};
Back in your Google Sheet, use the Macro Import option to import this function as a macro.
When you run it, it will convert all the formulas in every sheet of your Google Sheet into values.
8.5 Sort all your sheets in a Google Sheet alphabetically
Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:
// sort sheets alphabetically
function sortSheets() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheets = spreadsheet.getSheets();
var sheetNames = [];
sheets.forEach(function(sheet,i) {
sheetNames.push(sheet.getName());
});
sheetNames.sort().forEach(function(sheet,i) {
spreadsheet.getSheetByName(sheet).activate();
spreadsheet.moveActiveSheet(i + 1);
});
};
Back in your Google Sheet, use the Macro Import option to import this function as a macro.
When you run it, it will sort all your sheets in a Google Sheet alphabetically.
8.6 Unhide all rows and columns in the current Sheet
Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:
// unhide all rows and columns in current Sheet data range
function unhideRowsColumnsActiveSheet() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getDataRange();
sheet.unhideRow(range);
sheet.unhideColumn(range);
}
Back in your Google Sheet, use the Macro Import option to import this function as a macro.
When you run it, it will unhide any hidden rows and columns within the data range. (If you have hidden rows/columns outside of the data range, they will not be affected.)
8.7 Unhide all rows and columns in entire Google Sheet
Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:
// unhide all rows and columns in data ranges of entire Google Sheet
function unhideRowsColumnsGlobal() {
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
sheets.forEach(function(sheet) {
var range = sheet.getDataRange();
sheet.unhideRow(range);
sheet.unhideColumn(range);
});
};
Back in your Google Sheet, use the Macro Import option to import this function as a macro.
When you run it, it will unhide any hidden rows and columns within the data range in each sheet of your entire Google Sheet.
8.8 Set all Sheets to have a specific tab color
Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:
// set all Sheets tabs to red
function setTabColor() {
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
sheets.forEach(function(sheet) {
sheet.setTabColor("ff0000");
});
};
Back in your Google Sheet, use the Macro Import option to import this function as a macro.
When you run it, it will set all of the tab colors to red.
Want a different color? Just change the hex code on line 5 to whatever you want, e.g. cornflower blue would be 6495ed
Use this handy guide to find the hex values you want.
8.9 Remove any tab coloring from all Sheets
Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:
// remove all Sheets tabs color
function resetTabColor() {
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
sheets.forEach(function(sheet) {
sheet.setTabColor(null);
});
};
Back in your Google Sheet, use the Macro Import option to import this function as a macro.
When you run it, it will remove all of the tab colors from your Sheet (it sets them back to null, i.e. no value).
Here’s a GIF showing the tab colors being added and removed via Macros (check the bottom of the image):
8.10 Hide all sheets apart from the active one
Copy and paste this code into your script editor and import the function into your Macro menu:
function hideAllSheetsExceptActive() {
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
sheets.forEach(function(sheet) {
if (sheet.getName() != SpreadsheetApp.getActiveSheet().getName())
sheet.hideSheet();
});
};
Running this macro will hide all the Sheets in your Google Sheet, except for the one you have selected (the active sheet).
8.11 Unhide all Sheets in your Sheet in one go
Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:
function unhideAllSheets() {
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
sheets.forEach(function(sheet) {
sheet.showSheet();
});
};
Back in your Google Sheet, use the Macro Import option to import this function as a macro.
When you run it, it will show any hidden Sheets in your Sheet, to save you having to do it 1-by-1.
Here’s a GIF showing how the hide and unhide macros work:
You can see how Sheet6, the active Sheet, is the only one that isn’t hidden when the first macro is run.
8.12 Resetting Filters
Ok, saving the best to last, this is one of my favorite macros! 🙂
I use filters on my data tables all the time, and find it mildly annoying that there’s no way to clear all your filters in one go. You have to manually reset each filter in turn (time consuming, and sometimes hard to see which columns have filters when you have really big datasets) OR you can completely remove the filter and re-add from the menu.
Let’s create a macro in Google Sheets to do that! Then we can be super efficient by running it with a single menu click or even better, from a shortcut.
Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:
// reset all filters for a data range on current Sheet
function resetFilter() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getDataRange();
range.getFilter().remove();
range.createFilter();
}
Back in your Google Sheet, use the Macro Import option to import this function as a macro.
When you run it, it will remove and then re-add filters to your data range in one go.
Here’s a GIF showing the problem and macro solution:
9. Resources
If you’re interested in taking things further, check out the following resources for getting started with Apps Script:
💡 Learn moreLearn how to write Apps Script and turbocharge your Google Workspace experience with the new Beginner Apps Script courseMacro reference guide in Google Docs help
Macro reference guide in the Google Developer documentation
And if you want to really start digging into the Apps Script code, you’ll want to bookmark the Google documentation for the Spreadsheet Service.
Finally, all of this macro code is available here on GitHub.
SheetsCon 2020: Lessons Learned Running An Online Conference For 6,700 People
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
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
- Opening Keynote – Ben Collins
- Pivot Tables in Google Sheets – Adam Steinfurth
- No Code Apps & Google Sheets – Cleo Espiritu
- Behind The Sheets – Andy Rudd
- You Will LOVE Google Sheets – Alice Keeler
- Voice First: Developing Conversational Interfaces In Google Sheets – Martin Hawksey
Day 2
- Using Google Sheets To Master Your Money – Alex Wilson
- Automation With Apps Script – Laura Taylor
- Moving On Up From Google Sheets To BigQuery – David Krevitt
- How To Be A Freelance Google Sheets Developer – Andy Conlin
- How To Build Google Sheets Add-Ons With Apps Script – Steve Webster
- Conference Wrap Up – Ben Collins
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.
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.
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.
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:
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:
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:
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:
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!
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:
If you don’t see this notification, you can select Run > Enable new Apps Script runtime powered by V8
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 were 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):
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:
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 const 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 const 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:
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:
Spread syntax
Suppose we have the following array of data:
const 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:
const 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]:
Other Advanced Features
There are more advanced features in V8 that are not covered in this post, including:
- Improved function detection
- Calling object methods and class static methods from triggers and callbacks
- Destructuring assignment that allows us to unpack values from arrays or objects, into distinct variables.
- Classes, which are a means to organize code with inheritance. Think of this as creating a blueprint from which copies can be made.
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
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