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