Welcome to Google Developers Hackademy.
This is the second part of the course developed
by Antonio Guzman, from GDG Androidtitlan Mexico.
If this is your first contact with the course,
I recommend you watch the first part
and do the exercises.
Welcome to lesson two
of the Google Apps Script introduction course.
I'm Antonio Guzman.
This time we will see
the six types of scripts there are
and two execution modes:
manual and time-triggered modes.
The scripts, apart from the differences among them
because of the implementation we give them,
we can also distinguish them
by the type they belong to.
Within Google Apps Script we can identify 6 types of them.
We will see which are the characteristics
and advantages of each one.
Stand alone.
This kind of scripts are accessible
from Google Drive.
These ones are recommended for one-time tasks
and we can run them manually.
The script we ran in the previous lesson
was a stand alone script.
Container-bound scripts.
This kind of scripts are accessible
from applications such as Google Forms,
Google Spreadsheet,
Google Document,
and are used to provide extra functionality
to these applications.
Their behavior can be considered
as that of the macros.
Spreadsheet custom function.
This kind of scripts are the ones used
from the spreadsheets' cells,
following the notation
"=nameOfTheFunction()."
Web apps.
This type of scripts allow us to expose our functionality
in the Net.
We expose it through an URL
where we're getting http requests.
In this script concept
we have to consider permission management
for execution modes
and for users with permission to call it.
This is the kind of script recommended
to operate with other systems beyond Google Apps.
Google Site Gadgets.
This is the kind of scripts we use
to publicly expose a functionality,
but in the context of a Google Site.
We build our script, we use it as a gadget
and we publish it on the site.
Container extension.
This type of scripts is used
to add elements to the user interface
of applications such as Document and Spreadsheet.
Imagine you create a menu
from which you can call your script's functionality.
These are the types of scripts
that we can find in Google Apps Script.
In this lesson we'll work with the Stand Alone type
and we will execute it in two modes:
manually and with a time trigger.
What we're going to do
is read information we have in a spreadsheet,
manipulate it,
and we will write the result on that same spreadsheet.
LET'S GET TO WORK
Now, in order to develop our exercise,
we'll enter Google Drive.
From there we will create a Spreadsheet-like document.
In it, we will put the aforementioned values,
the ones we will manipulate,
a group of products,
but well, first, we will name our spreadsheet
with the following name:
"LESSON 02"
So...
"LESSON 02."
And now our document has a name.
Column A will have our product names.
Let's start with... shoes.
Let's start with a pair of shoes.
Now, videogames.
That will be our second product.
We add that "o" we missed...
and we'll add fruits.
And then, medicine.
Each one of these products
will have a different tax rate and price.
We like 35 for the medicine,
fruit is cheaper, 4...
What would be fine? 70 for the videogame,
and we'll put 115 for the shoes.
Now, in column C we'll put the tax rate:
16...
20 for the videogame; 0 for the fruits;
and we'll leave 5 for the medicine.
We have the prices and the tax rates
for our products.
Now we go back to Google Drive
to create a script.
As I said last time,
you don't need to enter script.google.com anymore,
now you can do it directly from Drive.
This is yet another choice for the kind of documents
you can create.
We go back. It's loading our script.
Again we get the welcome screen...
Remember, this screen will give us options
such as templates, we will pick the first one,
because we're going to create something from scratch.
There we have the ones we created previously,
and we go with a blank one.
Let's name it.
We will call it "GAS 02."
"GAS 02."
We save and now our script has a name.
Now let's write our function.
What did we want? To get the values from those cells;
we need to reference our spreadsheet.
Let's start with a variable...
to which we'll assign
the spreadsheet we're going to use,
which in this case is the one called LESSON 02.
And we use the SpreadsheetApp,
from which we have a method called "openById".
What's the ID of our document?
How will we pass it?
As in a text chain.
Where do we get the ID from?
By opening our document, and on the browser bar,
it's that text chain from right after the "="
and before "#."
That's the key of our document.
We select it,
then we copy it
and we're going to paste it in our editor
as the element that our openById method needs.
We already have a reference to our document.
Let's continue.
Now we will extract the information
that we have in the spreadsheet.
The spreadsheet has
different sheets. In this case, it's just one,
but we will bring along all this arrangement.
sps.getSheet
and then we have the reference to the sheets contained
inside the spreadsheet.
We'll keep going, and now
we have to take the information from the cells.
Let's create a variable called "data."
With it we're going to store the arrangement we set,
which was the prices, the product numbers
and the tax rate.
Now let's reference that first sheet. How?
With the index "0,"
and then "getDataRange,"
and "getValues."
As we can see, it returns a bidimensional arrangement.
All the information we have in that spreadsheet will be there.
We're going to move the arrangement with the info
that we placed in the cells of our spreadsheet,
we're going to do it with a "for",
using the variable "i" we're going to move "data"
and associate the information registration by registration
in a variable called "row."
Once we're there,
you know, "data,"
and we will manage it with the index
that "i" has with each iteration.
And what do we do next?
We obtain the information from each of the registrations.
We have "price."
Price was on the second column, in other words,
"row" with index "1."
After that, we'll have to extract the other values,
which in this case would be the tax rate.
So we create the variable "rate,"
and again, using "row,"
we will access that information.
Row was in the other column, the next one,
so its index is "2."
Once we have this information,
we will be able to manage
the total tax, which, in this case,
how do we determine the tax amount?
By multiplying the rate by the price
and dividing that by 100, so we do:
"price*rate"
and divided by 100,
as we just said.
In this variable, "tax," we'll already have the amount
that we will place in the next cell.
Now,
what we're going to do is go on with the development
of our program and we will set the information
in the cell.
In order to know how to do that,
we need to reference the position, so,
we're going to use a "startRow" variable
that will also work as an index.
That's "1" plus the value it has there.
Here we add a "+" there,
we place it in front,
so an arithmetic operation occurs
and not a concatenation.
Let's remember the "+" is overloaded
and it is also to concatenate,
not only to add.
So now we insert the values,
in this case, the first one will be "tax."
Where will we insert them? In our sheet.
We reference it: "sheet0,"
and we'll bring a range. What is that range?
That range will allow us
to insert the information where we want it.
The row number goes first and then the column number.
What is the row number? "startRow", as we said,
and which column? Number four.
And we do the "setValue."
The method is already there, we use it,
and we pass it our variable where we have stored
the tax.
We assign it and insert ";". We finish our sentence,
and we repeat the process, but this time,
what we do is insert another variable:
the result of the other combination,
but it's the same, in the same sheet, "0,"
we bring the range on which we're going to operate,
this time it's also "startRow",
what's going to change is the column,
because we want it in column number 5.
So we use "startRow"
and we change the column number by putting "5,"
for the rest, it's the same: "setValue."
Now, what were we going to put there?
What we're finally paying for our product.
We already have the tax and the price,
what we need to do is add them up.
"price + tax."
This way we'd insert the value we're interested in.
We finish our sentence, semicolon,
and we're ready. What are we missing?
Saving and executing it.
Then we'd be running and testing our script,
but before that, we're going to use another similar method,
inside SpreadsheetApp,
called "flush."
It will refresh the cells
to make sure the information we're sending
is shown there and that there's no problem
with stream or anything.
So we do our flush...
";" and we finish our sentence.
Now we can save it,
and we're ready to run our application.
We hit "run..."
it tells us it's starting to run,
and, once again, it requests authorization.
Remember, each script we run requests authorization.
It says it will access our spreadsheets
in Google Drive, so we confirm.
The script has run.
Now let's go to our spreadsheet, LESSON,
and we see: column D and E
have the expected values.
"D" has the correspondent tax according the rate
to each one of the products,
and "E" has the total amount to be paid
considering the calculated tax.
So our program was able to carry out the action
as we expected.
This was a manual execution.
Let's delete the data we have in those cells,
because we said we were also going to practice
the time-triggered execution, and that's next,
and we want to see that the data is put there again
and that it's not the same from the manual execution.
So let's delete it,
now columns D and E are empty.
We go back to our script
and we're going to click on this icon
that lists the triggers associated to our script.
It says there's none.
So we're going to select one,
that's going to be associated to the function marked there
if we want to create a time-triggered one
and there are several options.
We can create it for a specific hour,
a day of the week, an hour, a minute,
and if we want to repeat it,
we can use the last column.
On that we can select how frequently we want it
to be repeating.
The concept is very similar to that of cron tasks,
if you've worked on Linux,
or any other Unix type system,
with cron tasks.
So now we select a specific time set,
a specific date and time.
There you have the YYYY-MM-DD and time format.
So we set it to run in a few minutes:
2013-, we're in the 9th month,
09-, 28th day...
spacebar, the hour...
it's... 12, no, it's not 12...
it's 13...
We'll set it to run within two minutes
and we're going to wait for that time to pass
and... we like 13:49, right?
We set it like that, then save it,
and now we have a time trigger.
And now we just have to wait for those 2 minutes to pass.
And after two minutes,
we go back and see what has happened
and there's the information: in columns D and E,
the script executed correctly
and we found the information we expected.
Now we go to the Manager
and we can add one extra option,
which is receiving notifications
when our script fails.
To what e-mail address?
To the account's e-mail address that's building the scripts,
in this case, mine,
and I ask it to notify me right away.
If the script happens to fail, I will receive an e-mail
letting me know.
That way we've built a script
executed by a time-trigger and that saves the information.
Remember this is a stand alone script,
that means that the information
is available from Google Drive.
We search it by name
and now it's loading our script.
There we have the results.
So we open it. It's the script we've just built.
It is opening in another tab.
See, it's the same script.
We got it, and that's it,
we have finished our exercise.
LET'S DO AN EXERCISE
To conclude and reaffirm what we learnt in this lesson,
I invite you to analyze this scenario
to do an exercise.
Imagine the financing area of a company,
that records day by day a seller's commissions
who offers the products.
There's a weekly cut-off
to know what's the amount of the cheque he will receive.
What could we do
so that, automatically,
that amount is calculated on Mondays
and the result is written on that same spreadsheet?
RECAPITULATION
Recapitulating,
in this lesson we covered types of scripts,
execution modes: manual and time-triggered.
And in the next lesson we'll see container-bound scripts.
This is it for now.
Keep on programming.