Page Comparison

...

Google Forms and More

• Google Forms/Sheets
• How to password protect a Google Form - If you want to password protect a Google Form, there is no current functionality included with Google. But our team found a workaround through searching on Google that allows a user to password protect certain forms.
• Google Script – In many of Google’s apps, you can make scripts on the backend. This is done by using Google Script, Google’s own scripting language. However, it is essentially just Javascript with Google-specific modules added for integration with their apps. For example, you can create a free, lightweight database on Google Sheets (many other Google-compatible databases are available), and use the Script Editor to add background scripts. You could generate reports, change format, and rearrange data – amongst many other things – using Google Script. We recommend using this if you have very little to no budget and want an out-of-the-box solution that is familiar (Javascript is not far off from Java).  Google Script Home: https://www.google.com/script/start/ 

...

Excel Survey: for clients using the Microsoft stack but want the easy-to-use functionality of Google Forms.  Excel Survey will do this exactly.  Part of Office 365.

 Google Google Apps Script: superset of JavaScript developed by Google for the purpose of modifying or extending the behavior of popular GSuite products (Google Sheets, Google Docs, etc.). Integrates very nicely with said products, and comes with extensive documentation to help maximize its potential.

Differences between Google Sheets vs. Excel:

• You cannot link sheets together like you can in Excel
• Connecting “Form Responses” to other tables only can be done through queries
  • As a result, changes to the Form tend to have a compound effect on tables, queries, calculations, etc.
• General useful formulas
  • Ifs
    • What it does: Allows you to display different values based on a variety of conditions
  • Iferror
    • What it does: Allows you to display a clean value instead of an error message if one occurs
  • Averageif
    • What it does: Calculates an average based on specific conditions
  • If(exact)
    • What it does: A condition that checks that two cells have the same value
  • Concat
    • What it does: Combines two strings into one
  • To_text
    • What it does: Converts all contents of a cell into a string
  • Below is a link to an extensive Google Sheets function list:
    • https://support.google.com/docs/table/25273?hl=en

Below are some other helpful hacks we found useful.

• Query(‘Form Responses’!A:BZ, “select A,B,C where A=’yes’”,1)
  • What it does: This is a bare-bones query similar to how we created our tables. We queried from all columns in the Form Responses sheet & selected the specific columns we wanted to display in the new table based off of a specific condition
• The link below was very handy in helping us figure out how to update a drop-down list in the Google Form based off of values in the Form Responses “database”
  • http://wafflebytes.blogspot.com/2016/10/google-script-create-drop-down-list.html
  • It is important to remember to add a trigger so your code runs based off of a specific action (in our case, we used “on form submit”)
• This link helped us create pre-filled Google Form links that correspond to cell values in Sheets
  • https://alicekeeler.com/2018/03/19/google-forms-pre-fill-answer/

Google Sheets Script Editor

This is a very useful feature in Google Sheets that allows users to create their own coded scripts and functions (think about Visual Basic for Application in Microsoft Excel). The coding language is primarily JavaScript that is ran through Google’s servers. List of all Google Sheets functions can be found here: https://support.google.com/docs/table/25273

Basic Tutorials of Script Fundamentals: https://developers.google.com/apps-script/quickstart/fundamentals-codelabs

Accessing the Script Editor

1.Open a Google Sheets document

2.Click on the “Tools” dropdown in the top toolbar

3.Click on “Script editor” to open the script window

4.Create a new script with File > New > Script file

5.Code customized functions in JavaScript

Google Apps Script-Triggers

Google Apps Script is a dashboard where users can view all their projects that utilize the script editor functionality within Google Workplace (formerly G Suite) applications.

Apps Script Home: https://script.google.com/home

Here, users can also set up triggers which can be used to run a specified custom function depending on application specific events. Events can be time-based, on file open, on file edit, on file change, or on form submission. This functionality is very useful for setting up an automated email notification system. Google Apps Script will also provide data analytics on active triggers and send periodic reports.

More info on these types of triggers can be found here: https://developers.google.com/apps-script/guides/triggers/installableAccessing Apps Script Triggers from Google Sheets

1.Navigate to the script editor window

2.To see active triggers on this document, go to Edit > Current project’s triggers

3.Add new triggers with the blue Add Trigger button in the bottom right corner

Guide to Create an Email Notification Script: https://blog.gsmart.in/send-email-when-cell-value-changes-in-google-sheets/

*Groups will need to modify steps to meet their specific needs