Sensorica Knowledge

You can perform the following tasks from the Script Editor.

pening, deleting, renaming, and saving scripts

Cutting, copying, and pasting text

Find and replace

Setting a time zone

scripts with time-based triggers

Running functions

Viewing log messages

revision history

write pseudocode first

When you're planning a script

narrative version of what the script needs to do.

A particular script is associated with one and only one Google Spreadsheet.

If you make a copy of the Spreadsheet, the script is also copied.

A particular Spreadsheet can have multiple scripts associated with it.

use the onOpen event handler in more than one script associated with a particular Spreadsheet, all scripts begin to execute when you open the Spreadsheet and the order in which the scripts are executed is indeterminate.

event handler is a function executed when a particular event takes place.

see Running Scripts in Response to an Event.

A script cannot currently call or create another script and cannot call functions in another script.

If you want to store the results of a function, you must copy them into a spreadsheet cell.

You can trigger Apps Script events from links that are embedded in a Google Site. For information about how to do this, see Using Apps Scrip in Your Ssite.

You can insert a script into a Site as a gadget.

you must grant permission for the script to run as a service.

You also designate whether only you can invoke the service or whether all members of your domain can invoke the service.

you can assign functions within the script any arbitrary name.

The instructions in a function must be enclosed within curly braces.

event handler

when a spreadsheet is opened,

when a script is installed

when a spreadsheet is edited

at times you choose

menu item

Using a drawing or button embedded in a Spreadsheet

Using a custom function that is referenced as a Spreadsheet function

Clicking the Run button

object-oriented programming languages

Google Apps Script uses the JavaScript language.

Operations

are performed using the objects and methods described in the API documentation.

An API provides pre-packaged code for standard tasks you need to accomplish in scripts or programs.

API includes objects that you use to accomplish tasks such as sending email, creating calendar entries

A method describes the behavior of an object and is a function attached to an object.

MailApp

use to create and send email

To send email, you invoke the sendEmail method and provide values for the method arguments.

Google Apps Script can access or retrieve data in different formats in different ways.

A custom function

is called directly from a cell in a Spreadsheet using the syntax =myFunctionName()

they cannot set values outside the cells

have some restrictions not shared by other functions

cannot send email

cannot operate on a Google Site

cannot perform any operations that require user authorization

cannot perform any operations that require knowledge of who the user

onInstall function

onOpen function

Other functions run when you run them manually or when they are triggered by clicking

Custom functions and formulas in the spreadsheet execute any time the entire Spreadsheet is evaluated or when the data changes in the function or formula's cell.

share the Spreadsheet

publish the script to the Script Gallery

spreadsheet template

the color coding for that line will not be correct

A script with incorrect syntax or other errors does not run.

The Script Editor includes a debugger.

view the current state of variables and objects created by a script while that script runs.

step through the code line by line as it executes or set breakpoints

The debugger does not work with custom functions, onEdit functions, event triggers, or scripts running as a service.

use the debugger to find errors in scripts that are syntactically correct but still do not function correctly.

Functions ending in an underscore (_), for example, internalStuff_(), are treated differently from other functions. You do not see these function in the Run field in the Script Editor and they do not appear in the Script Manager in the Spreadsheet. You can use the underscore to indicate that users should not attempt to run the function and the function is available only to other functions.

from a Spreadsheet

from a Site

As a stand-alone servlet

Deciding Whether to Run a Script from a Spreadsheet or as a Service

The built-in code autocomplete functionality in the editor requires you to type the trailing period that follows app.

Plan the script. What tasks should the script accomplish?

Write down the specific information you want to display to or collect from your users.

Draw the user interface

Determine what the script and interface should do in response to any user input.

Determine the conditions for exiting the script.

you need a UiApp application object to contain the user interface elements. After you create the UiApp application object, you can add buttons, dialog boxes, panels, and other elements to the UiApp application object.

The general syntax for these operations is as follows:

To create a UiApp application object, use the syntax var your_application_object_name = UiApp.createApplication();

To create a user interface element and associate it with your UiApp application object, use the syntax var your_ui_element_name= your_application_object_name.createElement_Name();.

To add one user interface element to another

use the syntax your_ui_element_name1.add(your_ui_element_name2);

a button with the text Press Me on it:

creates a vertical panel.

other kinds of panels

pop-up panels, stack panels, focus panels, form panels, and so on.

code for displaying your button on the panel:

add the panel to the application:

nstruct Google Apps Script to display the interface elements:

You can create the user interface elements in any order.

the display order

Creating the elements and adding them to your application are separate steps requiring separate instructions.

a short script that does nothing but display a panel with a button on it.

You can chain together setter methods

sets its title

set the size of the object:

how to use Grid objects and the setWidget method to create a more complex layout and also how to create text boxes and label them.

To make a user interface useful, you need the ability to update a Spreadsheet with information a user enters from the interface.

a short script that responds to an action in the interface by updating the Spreadsheet.

looping structure in the script to keep the panel displayed and active

Server-side means that the actions are performed by a server

same script, with functions added that enable the form to be used multiple times before a user chooses to exit.

script collects some information from text fields on a panel and writes that information into the Spreadsheet.

You can make a script's user interface available to users from inside a Spreadsheet or Site or by running it separately as a service.

how to make the user interface as a service.

A script that provides a stand-alone user interface must invoke the doGet(e) function or the doPost(e) function for an HTML form submit.

A script that provides the user interface from the Spreadsheet invokes doc.show(app).

The doGet(e) function takes the argument e, passing in the arguments for the user interface, including the user name of the person invoking the script.

After you write the script, you publish it as a service. During the publishing process, you define who has access to the script.

In a Google Apps domain, you can publish the script so that only you have access or so that everyone in the domain has access.

In a Google consumer account, you can publish the script so that only you have access or so that everyone in the world has access.

Updating a Spreadsheet from the User Interface, the user interface is displayed from the Spreadsheet where the script is stored. The following code defines how the user interface is displayed:

Here's the skeleton code for displaying a user interface as a stand-alone service:

some aspects of the two ways to display a user interface.

let's consider the terminology we use for events

event triggers

triggers

in response

event handler

event

onInstall function

onOpen function.

onEdit function

the simple event handlers are restricted in what they are permitted to do:

The spreadsheet containing the script must be opened for editing

cannot determine the current user

cannot access any services that require authentication as that user

Calendar, Mail and Site are not anonymous and the simple event handlers cannot access those services.

can only modify the current spreadsheet. Access to other spreadsheets is forbidden.

see Understanding Permissions and Script Execution.

The onOpen function runs automatically when a user opens a spreadsheet.

add custom menu items to the spreadsheet's menu bar.

onEdit function runs automatically when any cell of the spreadsheet is edited.

record the last modified time in a comment on the cell that was edited.

The onInstall function is called when a script is installed from the Script Gallery.

setting up custom menus for the user.

the script can call onOpen from onInstall.

Installable event handlers are set on the Triggers menu within the Script Editor, and they're called triggers in this document.

When a specific time is reached

When a form is submitted

When a Spreadsheet is edited

When a Spreadsheet is opened.

They can potentially access all services available to the user who installed the handler.

are fully-capable scripts with none of the access limitations of simple event handlers

may not be able to determine which user triggered the event being handled

The spreadsheet containing the script does not have to be open for the event to be triggered and the script to run.

You can connect triggers to one or more functions in a script. Any function can have multiple triggers attached. In addition, you can add trigger attributes to a function to further refine how the trigger behaves.

When a script runs because of a trigger, the script runs using the identity of the person who installed the trigger, not the identity of the user whose action triggered the event. This is for security reasons.

Installing an event handler may prompt for authorization to access

An event is passed to every event handler as the argument (e). You can add attributes to the (e) argument that further define how the trigger works or that capture information about how the script was triggered.

an example of a function that sends email to a designated individual containing information captured by a Spreadsheet when a form is submitted.

With Google Apps, forms have the option to automatically record the submitter's username, and this is available to the script as e.namedValues["Username"]. Note: e.namedValues are only available for Google Apps domains and not for consumer Google accounts.

The available attributes for triggers are described in the following tables.

you will want the custom menu to be displayed as soon as the Spreadsheet is opened. To do this,include an onOpen function in your script

array of objects

An array is a collection of elements that works something like a Spreadsheet. Each element in an array can be identified by its location in the array, called an index point.

Arrays

are zero-based

Arrays are a convenient way to store and manipulate data within a computer program or script.

Each menu item is placed inside curly braces, and this syntax defines each name-function name pair as an object.

After the definition of the menu entries comes the definition of the menu itself:

Here's generalized code for creating menu items and a menu.

The following code pops up a message box when a user selects a menu item on the new menu:

the generalized code

run directly on Google servers in order to provide direct access to the products they control.

can also use Google Apps Script from Google Sites

Google Apps Script Template Gallery

Google Apps Script Blog

guide contains the information you need to use Google Apps Script, a server-side scripting language, based on JavaScript, that runs on Google's servers alongside Google Apps

enable varying degrees of interactivity among the applications

easy enough to use that you don't have to be a programmer to create scripts.

use it to automate complex tasks within Google Apps

You don't have to be a programmer to use Google Apps Script

A script is a series of instructions you write in a computer language to accomplish a particular task. You type in the instructions and save them as a script. The script runs only under circumstances you define.

The Google Apps Script API provides a set of objects. You can use these objects and their associates methods to access Google Docs and Spreadsheets, Gmail, Google Finance, and other Google applications.

To run a script, you must first add the script to a Google Spreadsheet or Google Site using the Script Editor.

You can retrieve information from a wide selection of Google Apps and Services and from external sources, including web pages and XML sources. You can use Google Apps Script to create email, spreadsheets, pages on Google Sites, and files in the Google Docs Document List.

The instructions in a script are grouped into functions.

objects

methods

for such tasks

Create pages on a Google Site

Customize a Spreadsheet

Send email based on information in a Spreadsheet

You can manipulate

numeric

financial

string

an XML document

controlling data in the following applications

Spreadsheets

Google Document List

Contacts

Calendar

Sites

Google Maps

create and display interactive user interface elements

interact with relational database management systems

create folders, subfolders, and files in the Google Docs document list

access to user, session, and browser information

access to web services

extract data from XML documents and then manipulate that data

obtain translations of text from one language to another

send email

UrlFetch services

encode and decode strings and format dates

store properties on a per-script and per-user basis

create, delete and update contact information for individuals and for groups in Google Contacts

Install

Close

Choose a script and click Run

The onOpen function adds the Finance menu and the menu item Get Stock to the Spreadsheet

the onOpen() function

code function onOpen() declares the function and its name

code between the curly braces is the code that performs actions when you run the onOpen() function

the SpreadsheetApp.getActiveSpreadsheet method to obtain a spreadsheet object representing the currently-active spreadsheet.

invoking methods on ss, the script can manipulate the currently-active spreadsheet.

method is code that is associate exclusively with a class and performs a particular task on objects of that class

insert the column headers into the corresponding cells

the getRange() method

setValue() method

identify

arguments

You use arguments to pass data to a method.

String data

is in quotation marks

line var row = 2; creates a variable row with an initial value of 2

a counter

while (true) loop

if loop

if (!symbol) break; causes the script to exit from the while loop when there is no stock symbol in the variable symbol

The exclamation point states a negative (not) condition

continues to run, incrementing the row each time until it reaches an empty row

the break causes the script to exit from the loop and finish.

To comment out the code, put two forward slashes at the beginning of each line: