Google Sheets: Tech Fair Guide

Session Sign Ups

Google SheetsTech Fair Guide

Learn how we use Google Sheets to organize and communicate information when planning & faciliating our Tech Fair.

start

Index

Showcase & Session Posters

Pivot Tables

Random Name Picker

Dynamic Rosters

About Sheets

Additional Resources

Add-Ons

Functions

Email Reminders

About Sheets

Google Sheets

Your data workhorse

Data collected from Forms is sent to Sheets. From here, you can use different formulas and Add-ons to communicate with presenters and guests more efficiently.

link

Pivot Tables

Pivot Tables

Summarize your data

Use pivot tables to summarize and analyze your data. Try creating a pivot table to determine how many people have signed up for each session or time slot at your Tech Fair using a Google Form.

+info

A pivot table showing the number of attendees for each workshop

Dynamic Rosters

Create Dynamic Rosters

with VLOOKUP & FILTER

Select a session using a dropdown menu and display session information as well as attendees for that session.

+info

Click on the dropdown to select a session and view the roster

Showcase & Session Posters

STEP 1

STEP 3

Collect info from presenters via Google Form

Create a template for posters (Docs or Slides)

STEP 2

STEP 4

Send Form responses to Google Sheets

Use Autocrat to create posters

Random Name Picker

Random Name Picker

Easily select winners randomly from your attendees using a Google Form, Google Sheets, and Flippity.net

How to Create a Random Name Spinner for Tech Fair Attendees

Email Reminders

Session Reminders

Using the FormMule Add-on

To begin, you'll need to install the Form Mule add-on for Google Sheets. You may need to contact your tech department for permission.

+info

Email Reminders

Session Reminders

Using the FormMule Add-on

Next, you'll need to follow steps to create templates and use data from your sheet to determine recipients, email contents, and merge conditions.

+info

Email Reminders

Session Reminders

Using the FormMule Add-on

Preview and send your customized emails to remind your attendees of sessions they have signed up for and to provide additional information prior to your event.

+info

Google Sheets Add-ons

Copy Down

Crop Sheet

Form Mule

Autocrat

Here you will find a collection of add-ons for Google Sheets that were used to help organize our data and streamline our communication processes with presenters and attendees.

Functions

Functions for Google Sheets

Just a little spreadsheet magic

Search for a value in a column and return a corresponding value from a specified column in the same row.

VLOOKUP

Append one or more strings to one another.

CONCATENATE

Select and display data that meets specified criteria.

FILTER

Import data from one spreadsheet to another.

IMPORTRANGE

+info

Additional Resources

Google Sheets YouTube Playlist

Google Sheets for Educators

Get started with Google Sheets

A large collection of video tutorials from GCFLearnFree

Tutorials, resources, and training from the Google for Education Teacher Center

A comprehensive blog post and 50-minute webinar from Eric Curts at ControlAltAchieve.com

Creating a Template

Poster Template in Slides

This template was created in Google Slides and is set up to pull information from the Form/Sheet in order to create posters for each Showcase and Workshop Session. Your template can be created in Docs or Slides and should include Tags (<<Tag Name>>) for each field you would like to pull from your Sheet. Notes: Make sure to consider your text size and spacing when creating your template. You should use text boxes instead of tables to avoid errors.

Creating a Form

Tech Fair Presenters Info

This Google Form was used to collect details about different Showcases and Sessions that educators volunteered to present at our last Tech Fair. The data collected in this form can be used to create posters and can be added to a schedule and sign in form.

• Session title & description
• Name(s) of presenters
• Session format
• A scavenger hunt question
• Required resources or peripherals

Form Mule Add-on

Form Mule sends targeted, personalized emails from a Google Sheet. Run as either a manual email merge or -- more powerfully -- as a triggered merge that runs automatically on form submit or on a timer.

Here are a few features:

• Create up to 10 email templates for different conditions
• Easily build your emails with the template editor
• Preview emails before sending

Form Mule Add-on

Form Mule sends targeted, personalized emails from a Google Sheet. Run as either a manual email merge or -- more powerfully -- as a triggered merge that runs automatically on form submit or on a timer.

Here are a few features:

• Create up to 10 email templates for different conditions
• Easily build your emails with the template editor
• Preview emails before sending

Form Mule

Close

Close

Close

Email Merge Utility

Form Mule sends targeted, personalized emails from a Google Sheet. Run as either a manual email merge or -- more powerfully -- as a triggered merge that runs automatically on form submit or on a timer. Some features include:

• Create up to 10 different templates with unique send conditions
• Use the template builder to easily insert tags with a click
• Preview your templates while building and before sending to fix errors
• Capture form edit URL to use in merge

Tutorials

Watch

Automating Email with Form Mule

Watch

Use the rangetotable function

Watch

Creating Follow Ups with FormMule

Form Mule Add-on

Form Mule sends targeted, personalized emails from a Google Sheet. Run as either a manual email merge or -- more powerfully -- as a triggered merge that runs automatically on form submit or on a timer.

Here are a few features:

• Create up to 10 email templates for different conditions
• Easily build your emails with the template editor
• Preview emails before sending

FILTER Function

Help

IMPORTRANGE Function

Help

The Copy Down add-on for Google Sheets allows you to create formulas in a spreadsheet that is collecting Form responses and apply that formula to new submissions as they are collected. Watch this video from Ann Witherspoon to see an example of how Copy Down can be used to create leaderboards when gamifying content.

Creating an AutoCrat Job

Session Posters

Basic Setup

1. Create a dropdown from a range containing session titles
2. Enter VLOOKUP formulas in cells to pull presenter name, time, location, etc. based on the dropdown selection
3. Enter separate FILTER formulas in cells to pull first name, last name, email, and comments from the Google Form responses based on dropdown selection

Crop Sheet is a handy add-on from Eric Koleda that allows you to quickly remove extra rows and columns from a sheet. You can either crop your sheet to all available data, or crop to a specific selection.

Watch this tutorial from Eric Curts to learn more about how to create pivot tables in Google Sheets. This clip is from a longer (51 min) webinar on Google Sheets for Educators. Click the links below to access session resources or read his blog post from Control Alt Achieve.

VLOOKUP Function

Help

Autocrat

Merge spreadsheet data to shared documents

Autocrat is a multi-purpose document merge tool that allows you to take data from a spreadsheet and merge it into a document via a template. Tell Autocrat which fields to merge via <<merge tags>> and then let Autocrat mass-generate personalized documents. Optionally send the documents as email attahchments. Optionally tell Autocrat to run when new forms are submitted to created truly automated processes.

FAQs & Known Issues

AutoCrat Users Guide

Create Bulleted Lists

Using Checkbox Data

CONCATENATE Function

Help