Jump-start Google Sheets, Forms and Apps Script

Name: Jump-start Google Sheets, Forms and Apps Script
Rating: 2.5 (1 reviews)

Learn and practice while building your Productivity assistant. Build 2 real life projects and collaborate with your team

Created byA Rezika

Last updated 10/2023

English

What you'll learn

Use Google sheets to build and automate your work
Create 2 real life projects using Apps Script, forms and sheets prebuilt and custom functions
Send tasks to your team and collect feedback.
Send your forms and tasks via emails with a click.
Create visual conditional formatting to help you take decisions and follow up tasks
Log tasks feedback and send updates from within your spreadsheet.
Moreover, create your visual easy-to-use filters.

Course content

7 sections • 38 lectures • 9h 22m total length

Introduction4:17
This course will take you from wherever you are in your experience with Google Sheets, Forms, and Apps Script to automate your work on google sheets so it assists your personal or professional productivity. Even if you have never used google sheets before or you don't have a Google account, in few minutes you will create a jump-start. This course teaches you with practical examples that we shall build together in an easy and efficient way most of the secrets of the Forms so you can send tasks with full fledged information set and links to your friends or team. Then, you will be able to collect, store and process their feedback. Even you can send updates and replies without losing track of any the previous replies or updates. It is all like opening a magic toolbox full of potentials where all its tools will be easy for you to use and apply. Building your own productivity assistant stimulates your creativity to continuously update your work processes and do not allow premade apps to be your limits. If you were ever curious about how to create survey forms, receive, log and analyze the feedback. You will learn all these topics and more. You will learn how to create drop down selections and conditionally format your cells to create auto visual feedback.
What are the Google tools that you will master in this course? for new users4:48
IN this lecture we are going to set up our minds to understand the Google apps you are going to use to build your own task manager and to use it as a productivity assistant. What is Google Sheets, Apps Script & Forms? and how they fit in Google suite bundles?
If you are familiar with sheets built-in capabilities you can skip it and go directly to section 3 where you will start with Apps Script and the first project we shall built together.
How to follow up with this course?4:08
Links to start up Google Sheets and Apps Script experience:
Google Chrome Browser : https://www.google.com/chrome/
Google account : https://accounts.google.com/SignUp
Google Sheets : https://docs.google.com/spreadsheets/u/0/

Explore sheets and write the first macro8:38
This section is for exploring basic and advanced Google Sheets built in capabilities. You can skip it to the following section if you have a good experience of Google Sheets and you want to learn Apps Scripts mainly. There we shall start building our 2 real life projects using Apps Script, Sheets, Forms and Gmail
Built-in capabilities and styling5:55
Build a real life scenario to test the Sheets capabilities
Conditional Formatting and validation6:42
Learn 2 of Google Sheets tools that enhances visual representation of data
Filters and subtotals6:10
Explore Filters and creating subtotals of filtered data
Mixing functions together to create a complex intended result7:50
How to mix Google built in functions to create a more complex output in an easy way
Google Special functions and help: Sparkline4:25
Explore Google Sheets sparkline function to visually represent data with a range or value, configure chart types, maximum value, colors, borders, and backgrounds, and learn the syntax for its options.
Collaboration with others and versions6:31
One of the vital tools of Google Sheets is the ability to instantly share it with others and request their feedback and even work simultaneously on same sheet

Update2:25
2023 Update remarks
Create the faceplate that will receive the data18:16
It is easier to have an interface which we call it here as faceplate to enter your data through it. It is easier than finding the last row in a sheet, adding a new serial number and then finding the correct columns for your data. Also, when this interface is clear and eye-catching it helps you enjoy the time you spend on the productivity assistant.
- Learn how to create an auto search using index and match functions
- Drop down lists
- Draw a button with text
Apps Script starting basics & how to link it to your sheet data19:51
- Generate a new script file
- Set the ground rules needed to collect and manipulate data between sheets and Apps Script
- What is var, const and different types of variables
- Meet your debugging assistant
- Understand different cell notations to call data
- The power of Named Ranges
Use Apps Script to add your task in position and give you alerts23:41
- collect data from the input template
- Automatic creation of a new serial number with each new task
- Automatic amending of the new task data to its position in the All tasks sheet
- getRow(), getColumn(), setValue(), if conditional
Add a confirmation popup and clear your input form18:35
- Use the UI (user interface to create a confirmation dialogue
- Use the UI to prevent adding tasks with missing certain important data after giving a warning
- Create a function to clear the input form after adding the task
- Use if error to keep your input form tidy
Add a button to clear the input form
Add Checkboxes and functions programmatically40:20
•Use Apps Script to add Checkboxes
•Use apps script to add Data Validation
• Create a function to auto update tasks status
•Loop Through Data
•Automate conclusion
•Reduce execution time
•Compare two dates
•Use getTime() to convert dates to numbers
Add an Attachment Field to the Input Template and modify scripts and sheets
Filters and their Pitfalls47:16
In this lecture we had how to program our own filters that are visually clear and easy to implement and remove using Apps Script. We had also seen how to apply time zone settings in different Google apps
- Add a button & use Apps Script to apply a general filter
- Apps Script & use Apps Script to turn-off any filter
- Apply your own custom filter from clear visual drop down lists of the available selections
- Switch between Legacy and new Apps Script editor
- Use Filter functions in the Apps Script
- Set a background color matching your button when activated

Send email10:16
- How to access the email app
- Options for setting email parameters
- Properly shape your subject, receipient & body
What are forms?12:54
- How to add a form from within the spread sheet
- The form responses sheet, Link, Unlink
- Create responses
- Set form options
- Share or embed the form in an HTML page
How to send form with task data and feedback fields?30:57
- How to create a form using Apps Script?
- Add different items and their settings
- collect form ID for further manipulation
- Send the form link via email
House Keeping9:25
- What happened to my appsscript.json file?
- How to to change the wrap text strategy from Apps Script?
Collect different form URL and Manipulate them17:47
- How to manipulate different URL's to get useful ones and share them
- View individual Responses
- Allow user to see previous responses beforehand
- Set form permissions
Relink form when needed25:02
- How to relink a form when needed?
- How to rename a sheet?
Loop through form responses29:40
- 2D array concept
- How to loop through form responses and select a specific one?
- set visual indication when form is updated to take action
Create a last response function with parameters and pass it arguments19:38
- How to create a function that takes parameters?
- How to call this function and pass to it arguments prepared by other function?
- Auto closure of the tasks based on the form feedback
- Integrate all forms last response collection with the task status update and conclusion setting
Find 2 ways to prevent the error due to a task created without setting due date

Triggers & protection18:06
- Different types of Data Range protection
- Triggers role in tasks automation
- Time based Triggers from minutes to Monthly
- Spreadsheet action based Triggers
- The concept of revisions
Limit the scope of the onEdit execution
Tricks to use Apps Scripts functions on mobile easily13:29
- How to create a menu? link for google developers example: https://developers.google.com/apps-script/guides/menus?hl=en
- Why Buttons are not clickable on the Goggle sheet mobile app?
- The easy work around to run Apps Script functions from the mobile

Outlie your automated stock taking project15:34
Create an outline for this project, what we want to do and what we shall achieve?
Create the first item of the stock taking form
Guide the User and Automate adding of Items14:40
Creating pop ups to the user interface
Convert stock sheet item into a form item
Loop through the form & Check for Duplicates6:40
How to get the form items?
What is the difference between item description and help text?
How to know if you are trying to add an existing item in the form?
Double Split and difference between double and triple equality check?9:14
Some tricks in extracting and converting parts of text
The difference between "==" and "==="
Delete an item and rebuild the complete list with a click14:50
Prepare the add code to be re-used for the delete
Tricks on deleting all item and adding them
User Interface pop ups to keep user informed
Send and receive stock taking7:02
How to reuse parts of previous scripts?
Prepare and set triggers for reminder email
email API authentication addition
Form Responses11:04
Form Responses as an array
Finding last response and logging some of its item responses
Record last response for all items in the stocks sheet11:33
Looping through the last response
Setting onSubmit trigger
Taking Decision based on submitted form response
Automate Creation of a purchase request10:39
Automate request Serial number generation
How to Reuse previous task follow up form script
Replace all instances of a variable
Tune Purchase Form Variables14:50
Tune the purchase form to the current shortage and send it via email
Prepare the form description and needed calculations
Add multiple choice item for purchase request status and comments section
Collect form ID, fill link and responses to purchase request summary sheet for follow up

Another dimension of using the Apps Script4:15
You can use the digital skills from this course in excelling APIs in the front end and backend scripts. This lecture is the intro to an API excelling course. that will show you a straightforward approach on how to use APIs in your front end or client side scripts and in your backend scripts. Actually it will leverage your skills in using JavaScript, Node.js, modules, endpoints creation, Apps Script, Google Cloud Platform and APIs , Authentication and Authorization
See you inside the course

Requirements

An internet access to google and chrome
We will show you how to start a Google free account to access free online Google Sheets and creative tools

Description

This course will take you from wherever you are in your experience with Google Sheets, Forms, and Apps Script to automate your work on google sheets so it assists your personal or professional productivity. Even if you have never used google sheets before or you don't have a Google account, in few minutes you will create a jump-start. This course teaches you with practical examples that we shall build together in an easy and efficient way most of the secrets of the Forms so you can send tasks with full fledged information set and links to your friends or team. Then, you will be able to collect, store and process their feedback. Even you can send updates and replies without losing track of any the previous replies or updates. It is all like opening a toolbox full of potentials where all its tools will be easy for you to use and apply. Building 2 real life stimulates your creativity to continuously update your work processes and do not allow premade apps to be your limits. If you were ever curious about how to create survey forms, receive, log and analyze the feedback. You will learn all these topics and more. You will learn how to create visual feedback of the recorded or auto collected data.

Who this course is for:

Anyone interested in boosting his productivity
Who wants to efficiently use Google Sheets
Who wants to efficiently use Google forms, make surveys and receive feedback
Who wants to efficiently use Scripts or Apps Scripts and triggers with Google sheets

Jump-start Google Sheets, Forms and Apps Script

What you'll learn

Explore related topics

Course content

Introduction3 lectures • 13min

Jump-start to Google sheets7 lectures • 46min

Apps Script Step-By-Step7 lectures • 2hr 50min

Share and collaborate8 lectures • 2hr 36min

Take it to the next level2 lectures • 32min

Build a stock tracking app10 lectures • 1hr 56min

What's more?1 lecture • 4min

Requirements

Description

Who this course is for: