1 of 55

Session #171944

​

Using G Suite Apps Scripting

In Intro Comp Sci

Brian Gray

St. Stephen’s Episcopal School

Google for Education Certified Trainer

for Education

​

BrianGray.org

2 of 55

What is it? What can it do?

Why use it?

Who can use it?

When in class should it be used?

Where is it?

How does it work?

Online Assistance

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

3 of 55

WWWWWHOA!

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

4 of 55

  • Sheets
  • Docs
  • Forms
  • Sites (Classic only)
  • Drive
  • URL Shortener
  • Gmail
  • Calendar
  • Contacts
  • Groups
  • Maps
  • Translate

What is it?

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

5 of 55

  • Custom Spreadsheet functions
  • New menus of new commands
  • Add-ons
  • Combine several Apps together

What does it do?

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

6 of 55

  • Different language (maybe), but reinforce same concepts
  • Different environment, but reinforce same concepts
  • Students are familiar with the end products
  • Authentic results
  • Platform independent - Mac or Windows
  • No software or driver installation

Why use it in class?

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

7 of 55

  • Learn to read & apply examples
  • Learn to read API documents
  • Learn to navigate nested data structures
  • Practice good habits:
    • modular design
    • stepwise refinement
    • reusable code

Why use it in class?

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

8 of 55

  • Learn to use the built-in Debugger
    • trace through code
    • examine data structures
  • See results in a familiar environment (Sheets, Gmail, etc.)

Why use it in class?

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

9 of 55

In my school

  • Intro Comp Sci students
    • Previously completed Geometry
    • No previous experience in programming required
    • Usually Juniors - some Seniors & Sophomores

Who?

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

10 of 55

  • Course sequence:
    • Scratch
    • Processing ( Java )
    • G Suite App Scripting
  • Approximately 12 weeks, starting in mid-February

When?

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

11 of 55

  • Spreadsheet Custom Function

Where do we start?

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

12 of 55

Why Write Custom Functions for a Spreadsheet?

​

Educational:

  • Easy first assignment
  • Students can test the process for creating and submitting an assignment
  • Teachers can test the process for grading an assignment

​

Practical:

  • If you build the same expression using spreadsheet functions and copy to 1000 rows, you have to change 1000 rows
  • If you build a custom function, you have to change 1 script
  • Easier to write complex logic into the custom function (loops, etc.)

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

13 of 55

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

14 of 55

/**

* Multiplies the input value by 2.

*

* @param {number} input The value to multiply.

* @return The input multiplied by 2.

* @customfunction

*/

function DOUBLE(input) {

return input * 2;

}

​

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

15 of 55

Again: Why Write Custom Functions?

​

Educational:

  • Easy first assignment
  • See the power of scripts
  • See how to use the documentation

​

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

16 of 55

Where? How?

An example...

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

17 of 55

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

18 of 55

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

19 of 55

function onOpen() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var entries = [

{ name : "Sort by First Name, Last Name",

functionName : "fNLn"

},

​

{ name : "Sort by Last Name, First Name",

functionName : "lNFn"

},

];

ss.addMenu("Sorting", entries);

};

Names of functions with no arguments

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

20 of 55

1

3

2

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

21 of 55

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

22 of 55

var FN = 1;

var LN = 2;

​

// sort by last name, first name

function lNfN(){

var sheet = SpreadsheetApp.getActiveSheet();

​

var data = sheet.getDataRange();

data.sort([{column: LN, ascending: true},

{column: FN, ascending: true}

]);

}

​

1-Relative Column References

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

23 of 55

Spreadsheet

  • References to columns and rows on a Sheet Range object are 1-relative
  • References to columns and rows in a Javascript array read from the sheet are 0-relative

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

24 of 55

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

25 of 55

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

26 of 55

JavaScript is case-sensitive!

​

The function name must EXACTLY match the entry in the menu created by onOpen()

  • Fix the case errors
  • Save the script
  • Switch to the spreadsheet tab
  • Run the script

​

function onOpen() {

// ...

var entries = [

// ...

{ name : "Sort by Last Name, First Name",

functionName : "lNFn"},

];

​

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

27 of 55

1

2

3

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

28 of 55

The range that we sorted included row 1 - the header row.

​

The range must exclude that row.

​

Click the Undo button to revert to the unsorted list.

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

29 of 55

Get a range that does not include row 1.

Context-based auto-complete will make this easier.

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

30 of 55

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

31 of 55

  • Click the line number to set a break-point
  • Click the method menu to select the method
  • Click the Debug button

Symbolic Debugger

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

32 of 55

  • Buttons at top:
    • Continue
    • Pause
    • Stop
    • Step into method
    • Step over method
    • Step out of method

​

  • The highlighted row is the NEXT one to be executed

​

​

  • Lower panel shows variables, types, values
  • Variables are undefined until initialized

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

33 of 55

Row count is 15 (as expected)

​

Start at row 2 and include (15 - 1) rows

​

​

Try again!

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

34 of 55

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

35 of 55

TEP-TATA

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

36 of 55

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

37 of 55

In the Sheet class, method getRange() with three arguments:

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

38 of 55

Get a Range with the required number of rows and columns.

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

39 of 55

Works as expected!

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

40 of 55

Make the same changes to the other sort method.

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

41 of 55

​

Online Assistance

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

42 of 55

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

43 of 55

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

44 of 55

  • stackoverflow.com

Online Assistance

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

45 of 55

  • Spreadsheet custom function
  • Spreadsheet Charts
  • Spreadsheet & Gmail - send one email
  • Spreadsheet & Gmail - send multiple emails
  • Spreadsheet & Gmail - send personalized emails

​

What Projects?

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

46 of 55

  • Gmail - attachments
  • Gmail - formatted with HTML
  • Gmail - inline image

​

What Projects?

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

47 of 55

  • Geocoding
  • Dynamic map URL
  • Static map URL
  • Spreadsheet Hyperlink - geocode, get maps, add URLs

​

What Projects?

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

48 of 55

  • Spreadsheet, Gmail, Maps
    • Read start & end addresses
    • Get directions
    • Build map image with route
    • Send custom email with directions, map, and URL

​

What Projects?

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

49 of 55

  • Calendars
    • Add, update, remove events
    • Email a list of events (daily agenda)
  • Drive
    • List file & label (folder) info
    • Recursion

​

What Projects?

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

50 of 55

  • goo.gl URL shortener/expander
  • Doc
    • Create a Doc
    • Add text to a doc
    • Find and format text in a Doc

​

What Projects?

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

51 of 55

WWWWWHOA!

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

52 of 55

  • A Script will run only if the user has Edit access
  • A Script can be edited only if the user has Edit access

​

When students submit files through Classroom, they lose Edit access.

  • Make a copy of the submitted file to see the script

Warning

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

53 of 55

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

54 of 55

WWWWWHOA!

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org

55 of 55

Session Evaluation in the TCEA Convention App

Session #171944

​

www.BrianGray.org

(links to slides and spreadsheet)

​

Brian@BrianGray.org

G-Suite App Scripting in Intro Comp Sci

Google for Education Certified Trainer

for Education

​

BrianGray.org