Hacks: Sheets
These are a few of my favorite functions. If you don't know about them, you're missing out on what you can do.
=Concatenate(A2,” “,B2,”<”,C3,”>”)
=join(",",A1:A5)
=IFERROR(value, [value_if_error]) (See more)
=IMPORTRANGE (sheet_url, range_string)
=QUERY(NAMEDRANGE, "SELECT A WHERE B='Google Sites' ")
=COUNTIF (range, criterion)
=COUNTA (range, criterion)
=DetectLanguage(B2) with =GoogleTanslate(B2,C2,”en”)
Want an example on how to collect information then share it with others? You can find an example below. Get a copy of documents you need.
Have you explored Flippity.net?” Flippity takes data you have in your Google Sheet and then does cool stuff with it.
With each Sheet, you will must go to File->Publish the sheet. This make its available on the web for viewing. When you get it setup, you will copy the Flippity.net website address (URL) (shown in blue in the image below) then share that with students or staff.
Amazing Add-Ons
Wish you could do more with GoogleForms and GoogleSheets? Then check out these "You can't live without 'em!" add-ons to our favorite Google tools!
AutoCrat makes it easy to automate merging of data from a Google Form into a Google Doc or Adobe Portable Document Format (PDF). This is pretty easy, but it requires you to be aware of several components.
The creator of Form Mule has several other must-have tools you need to check out. Those include:
- Form Limiter: Automatically sets Google Forms to stop accepting responses after a maximum number of responses, at a specific date and time, or when a spreadsheet cell contains a specified value.
Form Publisher: This app lets you use Google Form responses to create documents that can be shared and signed in just a few clicks!
Need certificates? Although Autocrat works great, you can also use the following Google Workspace Marketplace add-on for Forms:
- Certify 'em: Use this add-on to create online certifications and email custom certificates. Certify'em comes with several professionally designed certificate templates, and allows you to specify your own as well (created in Google Slides).
Make Your Own App with Google Sheets
Glide is a service that lets you add information to a Google sheet, then quickly create an app from its contents, all without coding. Just pick one of your sheets and Glide assembles it into a polished app that you can customize, share with a link, and even publish to the app store.
Google Data Studio and Similar Tools
Mail Merge Tools for Email
Wish you had a way to send emails like you print labels? Then email merge is your tool of choice. Tools like Yet Another Mail Merge and FormMule make sending out bulk emails to your students, colleagues, friends, and family easy as compiling a list in Google Sheets.
Important Info To Keep in Mind
Free Gmail account: If you use a free Gmail account, you are limited to sending a maximum of 500 emails in a 24-hour period, and a maximum of 100 addresses per email.
Paid Google Workspace account: If you use a paid Google Workspace account, you are limited to sending a maximum of 2,000 emails in a 24-hour period.
FormMule
Tip #1 - Send out custom directions or information. Need to send out Home Learning Codes for Seesaw? Seesaw provides you with a comma-delimited file that you can use. Use bulk email for that.
Tip #2 - Create custom comments in English or Spanish. Put comments can be placed in different columns, then include them in the text/body of your email. Some examples of English comments can be seen in this blog from Scholastic and Spanish ones are available here.
Get Stuff You Need First
In Google Sheets, get the FormMule add-on
Prepare Your Mail Merge Data
In Sheets, create your Sheets with Name and Email columns of data, at minimum
In Sheets, work through FormMule wizard. You may want to compose your HTML email in a text editor to get it to look the way you want
Sending Bulk Email
Send total emails your Gmail account supports (500 or 2000) with FormMule
Copies of sent emails are saved in your Gmail Sent folder
Track job completion with your emailed message in Sheets
Yet Another Mail Merge (YAMM)
Get Stuff You Need First
In Google Sheets, get the YAMM add-on
Prepare Your Mail Merge Data
In Gmail, create a draft copy of your email and leave it in your Draft folder, unsent. Put {{ }} around merge tags, such as: {{FirstName}}
In Sheets, create your Sheets with Name and Email columns of data, at minimum
Sending Bulk Email
Send 50 to 100 emails using YAMM Add-On (see pricing for more)
Copies of sent emails are saved in your Gmail Sent folder
Track interaction with your emailed message in Sheets