G Suite (formerly Google Apps for Work) is a suite of cloud based productivity and collaboration tools that are a competitor to Microsoft Office 365. The suite has a full programing interface and can be customised and automated in a variety of ways. This is a hands-on course that gives participants an opportunity to learn how to program and automate functions in G Suite.
If you are a Microsoft Office Macro programer and your company has moved to G Suite, fear not. You have access to the same sorts of functionality and this course provides an excellent conversion.
If you need to create a clever custom function for Google Sheets or would like to convert a function or macro that you had working in Microsoft Excel then this course will help you achieve that and give you plenty of ideas for other
If you have never automated an office task but feel that you could increase your productivity by doing so then this course is an excellent introduction to the G Suite Programing language and will help you get started in automating repetitive tasks.
This course will give you the advanced knowledge to create fully G Suite Applications which provide advanced user interaction via add-ons and dialogs, can pass data between the G Suite applications and these dialogs and return results when finished. It will show you how to integrate between the different applications and to call some of the more advanced Google APIs.
Cost: Price on application
Duration: 4 days
This course is not available as part of our public schedule but can be provided on a customised client specific basis.
Some programming knowledge, ideally Macros or VBA for Excel.
This lesson is an overview of the G Suite applications such as Google Documents, Google Sheets,
Opening the script editor
Is gs the same as js?
Basic syntax, creating a hello world function.
Calling the function.
Automating a task in Google Documents:
Find a problem to solve.
Create a function to solve it.
Various ways of getting the function to run.
Creating a custom function in Google Sheets:
Google Sheets has a wide variety of built in functions but they donít cover everything that you could possibly need. Eventually you may need to create your own function. Often you will end up with an incredibly complex formula that can be vastly simplified by having a custom function. This module will show you how to write your own custom functions
Creating a function:
Putting your custom function in a cell
Testing and debugging
Programming Spreadsheet tasks:
Working with ranges
Iterating through a range
Reading values from a cell
Writing values to a cell
Reading a formula from a cell
Writing a formula to a cell
Copying a row
Creating a new sheet
Copying data to the new sheet
Integrating Sheets with Docs and Mail:
Create a document from a script
Write to the document
Create a templated document from a script
Generate a series of documents from a range in a spreadsheet
Generate a series of emails from a spreadsheet
Generate a document and email it
Adding a HTML file to your project:
There are two types of programming resources you can add, scripts and html. A script is written in google script and runs on the server. A HTML resource runs on the client.
Opening a HTML resource
Adding an Alert
Adding a button
Adding an event
Closing a HTML resource
Creating a simple dialog in HTML:
Creating an object on a spreadsheet
Adding an action to it
Opening a HTML resource
Adding controls to your dialog
Closing the dialog
This lesson demonstrates how to include the very popular jQuery library in your html dialog. This will allow you to deliver more powerful functionality with less effort.
Creating a Sidebar Add-On:
G Suite has two user interactions modes, dialogs and add-ons. This lesson will demonstrate how to create a sidebar and use it to interact with your code.
Creating a sidebar HTML file
The Spreadsheet onOpen method
Adding a menu item for our add-on.
Opening the add-on.
Calling functions in the spreadsheet from the add-on.
Launching a dialog from the add-on.
Passing values to a dialog:
When we launch a dialog we often want to pass values to it. For example we might have a row selected in the spreadsheet and we launch a dialog to allow us to edit it. To do this we must read the values from the spreadsheet and pass them to the dialog.
Creating a HTML template.
Adding data to the HTML template.
Calling the evaluate() method.
Displaying the data in the dialog.
We can use the gmail service to send emails from a script.
Creating an email.
Sending an email.
Getting a list of contacts from the Contacts Service
Interacting with the calendar:
Google Calendar API
Reading from the Calendar
Creating an Event
Setting a Reminder