Introducción a Google Apps Script – Lección 2 (Spanish)

Introducción a Google Apps Script – Lección 2 (Spanish)

  02 Oct 2019   , , , , ,


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.

29 thoughts on “Introducción a Google Apps Script – Lección 2 (Spanish)

  1. Buenos días, Antonio me gustaría saber donde se encuentra en api de google Apps Scripts, donde encuentro las librerías que puedo usar y que paginas me recomiendas para poder encontrar información adicional y de ayuda para poder aprender y realizar mas funcionalidades.

  2. Hola , Alguien me puede explicar una parte del codigo: 
      la parte del "for" entiendo que es un conteo para que vaya fila por fila, pero no entiendo la forma en que lo hace como el programa sabe que i es 1 , entiendo que tiene que ver con , getDataRange(), que valor da ese comando y que valor da el comando getValues()??

  3. Hola. Antes que nada, muchas gracias por el tutorial y espero tengan mas para aprender. El Id del documento para obtener la cadena de texto, abro el documento y en la barra del navegador donde se supone debo copiar la cadena después del signo =,…..Este signo igual no me aparece en la cadena que debo copiar. Cual seria la solución? Muchas gracias de antemano.

  4. @Google Developers hola sus tutoriales me parecieron, muy solo que al momento de de copiar la URL, me marca error en la línea 2 y ya cheque todos los código de los algoritmos y están bien. Y no puedo hacer nada me marca error, gracias y me respondan pronto 

  5. no entiendo, si el nombre está en la columna 0, el precio en la 1, la tasa en la 2, y los resultados los ponemos en la 4 y 5 y no ha quedado ninguna celda vacía en medio, ¿qué ha pasado con la columna 3?
    Gracias por el tuto.

  6. hola estoy practicando, pero los ejemplos me indica que copie la key de mi hoja de calculo solo que en la URL no muestra la key como lo indicas en el vídeo. Gracias espero puedan ayudarme con eso.  

  7. Hola tu explicación es bastante fluida y explicita. Me encanto el tutorial.. Me funciono a la perfección. Saludos

  8. el link que me aparece ami es muy diferente al de ustedes miren………… https://docs.google.com/spreadsheets/d/1NclDB8o4HxpS3mvQnPpuA2XsT2vPho_ih5Ck4ZtVovc/edit#gid=0

  9. Hola como puedo activar un complemento con un boton, que formula me activa el nombre del complemento especifico. Gracias

  10. Impresionante.. muchas gracias 🙂
    ¿Sabríais decirme donde puedo conseguir un curso con más profundidad? aunque sea en inglés

  11. Gracias por el tutorial.
    Llevaba un tiempo buscando uno que empezara de verdad y explicándolo todo paso a paso y parece que al fin lo he encontrado. Gracias de verdad por la ayuda.

  12. MUCAS GRACIAS POR EL APORTE ANTONIO , TENGO UNA DUDA ESPERO ME PUEDAS AYUDAR. NECESITO AGREGARLE A ESTE SCRIPT ALGO PARA QUE UNA VEZ COPIADA Y PEGADA LA INFORMACION SE BORRE LO QUE SE COPIO, ES DECIR QUE LOS DATOS QUE ESCRIBI INICIALMENTE SE BORREN LUEGO DE SER COPIADOS Y PEGADOS EN LA OTRA HOJA.

    function launchPredefinedImports()
    {
    var importRanges = [
    {
    destinySheetName : "COPIAPEGA1",
    fromFileKey : "1X_ZoDcmmtR44CBqTC0W9b2cYoxKQnCYtwOwOufkuhG4",
    fromSheet : "COPIAPEGA",
    fromRange : "A:G"
    },
    ];

    launchImportsByArray( importRanges );
    }

    function launchImportsByArray( importRanges )
    {

    for (var i=0;i<importRanges.length;i++) {
    importExternalData(importRanges[i].destinySheetName,importRanges[i].fromFileKey,importRanges[i].fromSheet,importRanges[i].fromRange);
    }

    }

    function importExternalData(destinySheetName,fromFileKey,fromSheet,fromRange)
    {

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName(destinySheetName);
    var ultimafila = sheet.getLastRow()+1;
    if (sheet == null )
    {
    ss.insertSheet(destinySheetName);
    sheet = ss.getSheetByName(destinySheetName);
    }

    var data = SpreadsheetApp.openById(fromFileKey).getSheetByName(fromSheet).getRange(fromRange).getValues();

    if ( data[0] ) {
    sheet.getRange(ultimafila, 1, data.length, data[0].length).setValues( data );
    }
    }

  13. Hola, copiando igual las instrucciones que ustedes generan, hasta ahora no ha corrido el script ni el de la primera leccion ni el de la segunda

    en la linea 2 donde se coloca la direccion de la hoja para que lo lea, a mi no me aparece despues del signo de pregunta, te envio lo que me aparece, :https://docs.google.com/spreadsheets/d/1Co5gHUvwPYWZwwUXCqPb-psu6krZrQBpeRCjsLY2zaI/edit#gid=0

    yo copio a partir de: 1Co5gHUvwPYWZwwUXCqPb-psu6krZrQBpeRCjsLY2zaI/edit

    pero no me funciona

    gracias Saludos

  14. me aparece este mensaje "TypeError: No se puede llamar al método "getDataRange" de undefined. (línea 4, archivo "Código")", alguna solución?

  15. hola !! saludos…
    tengo una preguna.. como podria actualizar una celda con una función automáticamente cada cierto tiempo

  16. Cuando se genera un id distinto pueden usar la opción SpreadsheetApp.openByUrl("AQUI SE COPIA TODO EL URL")
    a cambio de usar SpreadsheetApp.openByid, les generará el mismo resultado

  17. No me funciono el código, pero logre que funcionara cambiandolo
    function myFunction() {

    var sps = SpreadsheetApp.openById("1bMSVSBpoYHnJ_hO88_QAbWS46sbn2ExkSeuKENfIyTc");

    var sheet = sps.getSheets()[0];

    var data = sheet.getDataRange().getValue();

    var cantidadDeFilas = sheet.getLastRow();

    for(var i=0; i<cantidadDeFilas; i++){

    var row = i;

    var startRow = 1 + +i;

    var precio = sheet.getRange(startRow, 2).getValue();

    var tasa =sheet.getRange(startRow, 3).getValue();

    var impuesto = (precio*tasa)/100;

    Logger.log(startRow);

    sheet.getRange(startRow, 4).setValue(impuesto);

    sheet.getRange(startRow, 5).setValue(precio + impuesto);

    }

    SpreadsheetApp.flush();

    }

Leave a Reply

Your email address will not be published. Required fields are marked *