Como copiar datos de una hoja a otra con Google Apps Script


 Después de mucho escribir en este blog me acabo de dar cuenta que una de las cosas más básicas, copiar de una hoja a otra usando Google Apps Script, no lo había explicado todavía.

Por suerte es un tema tan sencillo que con unos pocos ejemplos os quedará superclaro. Todo lo que haremos será leer y escribir.

1.Para empezar haremos lo básico, obtener todos los datos de una hoja y copiarla en otra hoja del mismo spreadsheet.

function Copiar() 
{
   var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
   
   var values = spreadsheet.getSheetByName("Hoja 1").getDataRange().getValues();

   var out = spreadsheet.getSheetByName("Hoja 2");
   out.clear();

   out.getRange(1,1,values.length,values[0].length).setValues(values);
}


2. Si no queremos copiar todos los datos sino solo unas cuantas filas o columnas deberemos obtener el rango que nos interese.

function Copiar() 
{
   var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
   var values = spreadsheet.getSheetByName("Hoja 1").getRange(1,1,3,4).getValues();

   var out = spreadsheet.getSheetByName("Hoja 2");
   out.clear();

   out.getRange(1,1,values.length,values[0].length).setValues(values);
}


3. Otra cosa interesante es filtrar las filas que vamos a copiar para solo pintar lo que nos interesa.  A fin de hacer esto usaremos la funcionalidad filter. Aquí teneis un ejemplo en que solo copiaremos las filas que tienen un 1 en la primera columna.

function Copiar() 
{
   var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
   
   var values = spreadsheet.getSheetByName("Hoja 1").getDataRange().getValues();
   
   values = values.filter(row => row[0] == '1');

   var out = spreadsheet.getSheetByName("Hoja 2");
   out.clear();

   out.getRange(1,1,values.length,values[0].length).setValues(values);
}


4. Si lo complicamos un poco nos puede interesar copiar una hoja, eliminando alguna columna que no nos resulta útil. Hay múltiples formas de implementar esto. Creo que la más sencilla es obtener los diferentes rangos de valores que nos interesan , juntarlos en uno solo y entonces copiarlos en la hoja de destino.
function Copiar() 
{
   var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
   var sheet       = spreadsheet.getSheetByName("Hoja 1")
   var values1      = sheet.getRange(1,1,sheet.getLastRow(),2).getValues();
   var values2      = sheet.getRange(1,4,sheet.getLastRow(),2).getValues();
   
   for(var i = 0 ; i < values1.length ; ++i )
   {
     for(var j = 0 ; j < values2[i].length ; ++j)
     {
       values1[i].push(values2[i][j]);
     }
   }

   var out = spreadsheet.getSheetByName("Hoja 2");
   out.clear();

   out.getRange(1,1,values1.length,values1[0].length).setValues(values1);
}


5. También nos puede resultar muy útil copiar la información de una hoja de un spreadsheet, a otra hoja en OTRO spreadsheet. 
 
function Copiar() 
{
   var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
   var spreadsheet_out = SpreadsheetApp.openById("el-id-del-spreadsheet-donde-copiar");

   var values = spreadsheet.getSheetByName("Hoja 1").getDataRange().getValues();

   var out = spreadsheet_out.getSheetByName("Hoja 1");
   out.clear();

   out.getRange(1,1,values.length,values[0].length).setValues(values);
}

Con esto yo creo que tenemos todo lo básico sobre copiar información con Google Apps Script. Evidentemente hay tantas combinaciones como se quieran, pero con las que os he presentado deberíais ser capaces de montar el script que necesiteis para copiar información de una hoja a otra con Google Apps Script.

 No vacileis en hacerme llegar vuestras dudas. 

Nos vemos!

Como usar Google Drive como servidor de hosting


 

Uno de los mayores problemas cuando haces un pequeño juego web o implementas una web pequeña para un cliente es donde alojar los recursos (imágenes, videos y audios) necesarios para tu producto. 

La respuesta lógica es usar un servidor de hosting donde almacenar la web y los recursos necesarios, pero cuando haces un pequeño proyecto personal o directamente cuando hacemos una web con Google Apps Script el hosting deja de tener sentido. Precisamente una de las ventajas de hacer webs con Google Apps Script es que no necesitas pagar por un servidor para alojar tu web. Además, si es una web para un cliente, sabrá el como actualizar  los recursos del hosting?

La solución más fácil para este problema es usar Google Drive para almacenar todos los recursos que necesitemos y obtenerlos de "alguna manera" para usarlos en nuestra web. No es complicado pero es algo que la gente desconoce bastante: usando la API por url de Drive. Podemos pedirle a Drive que nos de todo el stream de datos de un ciertos recurso y cargarlo en nuestra web como el tipo que necesitemos. 

Por ejemplo, en HTML5, si queremos cargar un audio que tenemos en drive solo tendríamos que hacer algo de estilo de este código:

<script>
function play() {
  var audio = new Audio('https://docs.google.com/uc?export=download&id=1xiCimh6-rQvwtpdYytNGd5Y8v2V4cjTz');
  audio.play();
}
</script>

Si os fijáis solo tenemos que cargar el recurso como si fuera un audio normal y corriente pero en la ruta del fichero ponerle algo del estilo de : https://docs.google.com/uc?export=download&id="id del fichero de audio de drive". 

Con este sencillo truco podréis usar cualquier fichero de audio que tengáis en Google Drive y nadie que use tu web notará la diferencia con una web con hosting.

Si queréis usar este truco para imágenes también funciona de la misma manera:

<img src='https://docs.google.com/uc?export=download&id=19kKkZT6DaBUVn4_9b8A-ulAeR4e8ApFw'/>

Y de la misma forma podréis estar pintando imágenes que están alojadas en Google Drive. Esto nos otorga una potencia descomunal. Podemos hacer webs dinámicas en que controlamos totalmente el contenido y podemos permitir que quien sea necesario actualice los gráficos o sonidos sin problemas.

Te imaginas subir imágenes a través de un formulario que luego usas para mostrarlo a través de una web? Las posibilidades que se abren con este truco no tienen fin.

Si queréis ver un ejemplo de como queda en una web, aquí tenéis un post donde uso la técnica que os he explicado para poner un botón con audio, cuyos recursos están en mi Drive.

Nos vemos!



Como hacer un Quiz Educativo con Google Apps Script


 

Como ya sabéis estoy haciendo un blog para aprender japonés y me he visto con la necesidad de desarrollar una herramienta para facilitar el aprendizaje del alfabeto Hiragana. A fin de conseguir un aprendizaje ameno y sencillo he creído que lo más idóneo era desarrollar un Quiz con Google Apps Script.

El sistema esta planteado de manera que podáis ir poniendo todas las respuestas que queráis en un spreadsheet y el quiz ( que no será más que una web) ira recogiendo esas preguntas y poniéndoselas al usuario de forma aleatoria. A la vez que se ha de poner la respuesta correcta, también se le tienen que poner las respuestas incorrectas y una url que dará la explicación de que es lo que hemos hecho mal.


Evidentemente, el código lo podréis personalizar como queráis, yo solo os doy un ejemplo de Google Apps Script que funciona. La aplicación final que vosotros hagáis solo se verá limitada por vuestras necesidades e imaginación.

Por motivos obvios, para hacer este artículo he usado el post que hacía referencia a números aleatorios en Google Apps Script

Sin más, os dejo con el código:

index.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <script>
    var ok = 0;

    function Response(string)
    {
       SetAciertos();
       document.getElementById("container").innerHTML = string;
    }

    function SetAciertos()
    {
       if(ok)
       {
        document.getElementById("aciertos").innerHTML = "<p> Aciertos consecutivos: "+ ok + "</p>";
       }
       else
       {
         document.getElementById("aciertos").innerHTML = "<p></p>";
       }
    }
  
  
    function Start()
    {
       ok = 0;
       google.script.run.withSuccessHandler(Response).Question();
    }

    function Ok()
    {
       ok++;
       google.script.run.withSuccessHandler(Response).Question();
    }

    function Fail(link)
    {
      var html = "<h2> Test de Nivel de Hiragana </h2>";
      html += "<p><a href='" + link + "' target='_blank'> Haz click aquí para saber cual es tu fallo </a></p>"
      html += "<p><button onclick='Start()'> REINICIAR </button></p>";
      Response(html);
    }
    </script>

  </head>
  <body>
 <div id="aciertos"></div>
<div id="container">
    <h2> Test de Nivel de Hiragana </h2> 
    <p><button onclick="Start()"> INICIAR </button></p>
</div>

  </body>
</html>

Code.gs

function doGet()
{
    return HtmlService.createTemplateFromFile('index')
        .evaluate() // El evaluate siempre debe estar antes del FrameOptions
        .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

function GetRandom(min,max)
{
  return Math.floor(Math.random()*(max -(min -1))) + min;
}

function crand(elements)
{
  return GetRandom(0,elements-1);
}

//Aleatorizamos un vector
function RandVector(Initial)
{
  var Vector = new Array();

  while(Initial.length)
  {
    var i = crand(Initial.length);
    Vector.push(Initial[i]);
    Initial.splice(i,1); 
  }
 
  return Vector;
}

function Question()
{
  //Obtenemos la lista de conversiones de texto
  var values = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getDataRange().getDisplayValues();

  var out = "<div>";

  var index = crand(values.length-1) + 1;

  var row = values[index];

  var vector = new Array();
  for(var i = 1 ; i < 5; i++)
    vector.push(row[i]);

  vector = RandVector(vector);
 
  out += "<table>";
  out += "<tr>";
  out += "<td></td>";
  out += "<td>";
  out += "<H1>" + row[0] + "</H1>";
  out += "</td>";
  out += "<td></td>";
  out += "<td></td>";
  out += "</tr>";
  out += "<tr>";
  for(var i = 0 ; i < vector.length ; ++i)
  {
    out += "<td>";
    if (vector[i] == row[1] )
    {
      out += "<p><button onclick='Ok()'> " + vector[i] + " </button></p>";
    }
    else
    {
      out += "<p><button onclick='Fail(\""+row[5]+"\")'> " + vector[i] + " </button></p>";
    }
    out += "</td>";
  }
  out += "</tr>";
  out += "</table>";

  out += "</div>";
  return out;
}

Si queréis ver funcionando este ejemplo podéis ver este código en un iframe dentro de este Juego para Aprender Hiragana

Google Drive : Lenguajes de programación


 

Si te estas planteando como automatizar tareas en el ecosistema de Google Drive lo primero que te has de plantear es que lenguaje has de usar para hacerlo. Respuesta fácil: Google Apps Script :) , pero no sería correcto o exacto.

Personalmente mi opción preferida de lenguaje de programación para Google Drive es usar Google Apps Script ( es por ello que hago este blog ) dado que sería algo así como el lenguaje de programación nativo de Google Drive. Es el que viene con el sistema y es extremadamente fácil hacer que todo funcione. En pocos minutos puedes tener armado tu script que se encargue de automatizar tareas en Google Drive.

Si tienes una base de JavaScript, C/C++ o algún otro lenguaje parecido dar el salto a Google Apps Script es trivial. Si no tienes ni idea de programar te costará un poco más dado que tendrás que entender la lógica de programación, pero con esfuerzo en muy poco tiempo podrás empezar a hacer tus scripts. Por donde empezar? Te recomendaría empezar por este artículo para tener una idea general del lenguaje y este otro para empezar a programar en GAS

Ahora bien, Google Apps Script no es el único lenguaje de programación que puedes usar en Google Drive. Google creó una API que permite tener acceso a las funcionalidades de Drive desde una aplicación de escritorio por ejemplo o en un servidor web . Tiene sus limitaciones y a mi personalmente siempre me ha dado quebraderos de cabezas, pero para ciertas cosas puede ser una buena opción.

Básicamente se puede usar cualquier lenguaje que sea capaz de atacar a la API de Google así que podríamos decir que con (casi) cualquier lenguaje podríamos automatizar tareas de Google Drive. Evidentemente, no es lo mismo decir que es posible a decir que es fácil , que haya documentación y ejemplos o que no pueda haber más problemas.

Ejemplos de lenguajes de programación que se usan de vez en cuando en la programación de "scripts" de Google Drive y de los cuales se puede encontrar algún ejemplo podrían ser Python, PHP, Java, Ruby, Plataforma .NET, Perl, etc... En general todos son lenguajes bastante enfocados a la programación web.

A priori, también deberían de poder usarse lenguajes "menos web" como C/C++, pero realmente hay muy poca documentación, por no decir ninguna ,de como atacar a la API de Google Drive desde estos lenguajes, así que salvo que tengas un afán de investigación exacerbado no te recomendaría ir por este camino, mucho menos si estás empezando.

Resumiendo, si estas empezando y te estas preguntando que lenguaje de programación deberías usar para programar script de Google Drive, la respuesta debería ser Google Apps Script, es lo más fácil y sencillo. Si eres un ingeniero y te estás preguntando si el lenguaje que estás usando en tu proyecto sería capaz de "hacer algo" en Drive, la respuesta es que si, pero que eventualmente podrá costarte sangre, sudor y lágrimas.

Si optas por Google Apps Script este blog intentará echarte un cable, si optas por la otra opción...en fin.. buena suerte, la necesitarás :D

Nos vemos

Como formatear HTML con Google Apps Script


Una aplicación muy útil de Google Apps Script es formatear automáticamente texto en formato HTML. En mi caso particular he montado este sistema para escribir textos en japonés (nunca es fácil escribir esos símbolos endemoniados :D ), pero vosotros podéis modificarlo como necesitáis para formatear vuestros htmls como querais: introducir links, poner negritas, cambiar tamaños , aplicar cursivas, etc.. Con esta base podéis montar como queráis vuestro formateador html con Google Apps Script.

En nuestro caso completo hemos montado una web configurable en el que tenemos todos los símbolos japoneses con sus respectivos links, como os he dicho lo podéis adaptar a lo que necesitéis poniéndole más columnas para configurarle más condicionantes a vuestro formateo.

Formateo Html Google Apps Script

Dado que el producto final es una web que nos pedirá el texto que queremos formatear,  tenemos parte de html y parte de gs.  Aquí tenéis el código de ambos:

index.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    
    <script>
    function Response(string)
    {
       document.getElementById("converted").innerHTML = string;
    }
  
    function Convert()
    {
       var text = document.getElementById("text").value;
       google.script.run.withSuccessHandler(Response).Convert(text);
    }
    </script>

  </head>
  <body>
 
<div>
    <h2> Romaji -> Hiragana </h2> 
    <p><textarea id="text"> </textarea></p>
    <p><button onclick="Convert()"> CONVERTIR </button></p>
    <p><div id="converted"> </div></p>
</div>

  </body>
</html>

Code.gs

function doGet()
{
    return HtmlService.createTemplateFromFile('index')
        .evaluate() // El evaluate siempre debe estar antes del FrameOptions
        .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

function Convert(txt)
{
  txt = String(txt.trim());

  //Obtenemos la lista de conversiones de texto
  var values = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getDataRange().getDisplayValues();

  var out = "<p>";

  //Miramos de todas las posibles conversiones cual cuadra con nuestro texto.
  for(var i = 1 ; i < values.length ; i++)
  {
    var tag = values[i][0].trim();
    var len = tag.length;
    var sub = txt.substring(0,len).trim();

    if ( tag == sub )
    {
        var char =  values[i][1];
        var url  =  values[i][2];
        
        //Este código es especfico del ejemplo. En vuestro caso concreto puede necesitar cambios.
        if( char.length < 2 )
        {
          out += "<a target='_blank' href='"+ url +"'>" + char + "</a>";
        }
        else
        {
          out += "<a target='_blank' href='"+ url +"'>" + char.charAt(0) + "<small>" + char.charAt(1) + "</small>" + "</a>";
        }

        //Nos quedamos con el texto todavía no  procesado
        txt = txt.substring(len);

        //Volvemos a mirar desde el principio
        i=1; 
    }
  }
  out += "</p>";

  return out;
}


Si queréis verlo funcionando lo tengo implementado en esta web de japonés

Cualquier duda , no vaciléis en hacerme llegar. Este proyecto es un poco más complejo de lo que os tengo acostumbrados.

Espero que os haya servido.

Nos vemos






















Como convertir número a texto con Google Apps Script


Necesitas usar un número como si fuera un string cuando estas trabajando con Google Apps Script?. En muchas ocasiones nos puede resultar útil convertir un dato de tipo numérico a tipo string con Google Apps Script. Generalmente se usa este tipo de conversiones para obtener un identificador de forma automática o un código de producto por ejemplo , de forma que también nos puede resultar útil si aseguramos que el string va a tener un tamaño mínimo (rellanado con ceros por la izquierda, por ejemplo).

El código que os presentaré hoy hace justamente eso, convierte un número a un string usando Google Apps Script y opcionalmente permite rellenar con ceros a la izquierda si es necesario hasta el tamaño de string que tu le digas. 

function NumberToText(number,min_size) 
{
  var text = "";
  
  while(number > 0)
  {
     var char = Math.floor(number%10);
     text = String(char) + text;
     number -= char;   
     number = Math.floor(number/10);
  }

  while ( text.length < min_size )
  {
    text = "0" + text;
  }

  return text;
}


La gracia de este código es que tanto se puede usar en nuestros programas Google Apps Script como usarlo directamente como una fórmula en nuestras hojas de Google Spreadsheets:


Como podéis ver el código es extremadamente sencillo y fácil de usar. Lo podéis adaptar como querías a vuestras necesidades con muy pocos cambios.

Cualquier cosa, no dudéis en hacérmelo saber.

Nos vemos

Como obtener el nombre de un fichero con Google Apps Script


 

Hay ocasiones en las que nos puede resultar útil obtener el nombre de un fichero de Drive del cual solo tenemos el id. La forma de solucionar este problema es extremadamente fácil, obtendremos el fichero gracias al id y la funcionalidad de DriveApp y entonces le preguntaremos el nombre.

Dado que esto tiene utilidad en listas grandes de ids, ya he preparado el código para que lea los ids de la primera columna y escriba el nombre en la segunda columna tal y como se ve en esta imagen:

Nombre de archivo de Drive con Google Apps Script

 Es fácil de adaptar a lo que necesitéis. Os adjunto el código, ya viereis que más sencillo no puede ser. Si tenéis cualquier duda, hacédmela llegar sin problemas.

function onOpen()
{
  SpreadsheetApp.getUi().createMenu("SCRIPT").addItem("Get Name","GetName").addToUi();
}

function GetNameFromId(id)
{
  var result = "NOT FOUND";
  var file = DriveApp.getFileById(id);

  if(file != null)
  {
    result = file.getName();
  }
  
  return result;
}

function GetName() 
{
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var values = sheet.getDataRange().getDisplayValues();
  
  for( var i = 1 ; i < values.length ; ++i )
  {
    var str = values[i][0];
    if(str != "")
    {
      sheet.getRange(i+1,2).setValue(GetNameFromId(str));
    }
  }
}

Solo como nota final, os comento que no es factible usar esta funcionalidad dentro de una formula ( como quería hacer al principio de redactar este post ) dado que las formulas se ejecutan sin permisos y esta funcionalidad necesita si o si de acceso a DriveApp ( como es lógico ). Es por ello que lo he montado en forma de ejecución batch de toda la lista.

Cualquier sugerencia siempre es bien recibida.

Nos vemos

P.D: Tal vez te interesa saber el paso contrario, como obtener el id de un  fichero con Google Apps Script








Como cambiar permisos de forma recursiva con Google Apps Script


 En muchas ocasiones nos puede resultar útil cambiar los permisos de un árbol de directorios de Drive , de forma que con una sola llamada a un script nos aseguremos que todos los ficheros y carpetas que cuelgan de la carpeta origen tienen los permisos que nosotros queremos.

Hace un tiempo ya hice un artículo sobre como cambiar permisos de una carpeta de Drive con Google Apps Script, así que tomando ese código de base, ya veréis que es muy fácil implementarlo para que sea recursivo en todo el árbol de carpetas.

Todo lo que hemos de hacer para conseguir recursividad  es llamar a la misma función que estamos creando para cada una de las subcarpetas de la carpeta actual. Dicho así, parece complejo, pero viendo el código Google Apps Script lo tendréis clarísimo:

function SetPermisosRecursivos(folder) 
{
    //Seteamos permisos a la carpeta actual
    folder.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);

    //Inspeccionamos todas las carpetas hijas
    var folders = folder.getFolders();
    while(folders.hasNext())
    {
       SetPermisosRecursivos(folders.next());
    }

    //Seteamos permisos a todos los ficheros de la carpeta actual
    var files = folder.getFiles();
    while(files.hasNext())  
    {
       files.next().setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
    }
}

En este ejemplo todas las carpetas y ficheros que dependan de la carpeta que le pasemos a la función se compartirán con cualquier que tenga el link. Evidentemente podéis cambiar esa linea de código para que los permisos se apliquen como lo necesites en tu caso.

Cualquier duda que tengáis, no vaciléis en hacérmela llegar.

Nos vemos


Cómo saber el usuario actual con Google Apps Script


 Cuando implementamos webs con Google Apps Script o en general cuando queremos comunicar javascript con Google Apps Script siempre podemos tener problemas en saber cual es el usuario real que está usando nuestro servicios.

Generalmente cuando distribuimos una web implementada con Google Apps Script lo hacemos con los permisos de ejecución del desarrollador. Es decir. la web accede a los datos a los que el desarrollador tiene permiso, y el usuario de la web  puede ver el html, pero no puede llegar a los documentos que hay detrás dado que el mismo no tiene permisos. Acostumbra a ser una muy buena manera de recolectar información de diferentes fuentes y poderlas  mostrar a los usuarios de una forma controlada y sin tener que dar permisos para nada a nadie.

Ahora bien, ¿Qué pasaría si necesitáramos saber cual es el usuario que accede a nuestra web? Imaginemos que la web ha de mostrar información diferente en función de que usuario se está conectando (una web con diferentes niveles de permisos, por ejemplo) . Google Apps Script nos permite saber sin lugar a dudas cual es el usuario que se conecta a nuestra web y con que usuario se está ejecutando la web, de forma que podremos mostrar información diferente sin ningún tipo de problemas.

Con este ejemplo veréis facilísimo cual es la diferencia entre usuarios conectados a una web:

function doGet()
{
  var active_user     = Session.getActiveUser().getEmail();
  var effective_user  = Session.getEffectiveUser().getEmail();
  var user            = Session.getUser().getEmail();

  var output = "<p>Active: " + active_user + "</p>";
  output += "<p>Effective: " + effective_user + "</p>";
  output += "<p>User: " + user + "</p>";
  return HtmlService.createHtmlOutput(output);
}

Si ejecutáis la web con vuestro usuario veréis que los 3 campos son iguales:

Usuarios con Google Apps Script
Si habéis habilitado que la web se puede ejecutar sin permisos de ninguno tipo, veréis que solo se llena el campo effective mientras que los otros dos quedan vacíos. 

Si ejecutas la web con otro usuario veras que el campo active y el campo effective difieren.

Conclusión:
- Session.getEffectiveUser() Nos devuelve con que permisos se esta ejecutando la web (generalmente el desarrollador del código que ha publicado la web)
- Session.getActiveUser(): Nos dice que usuario se ha conectado a la web. Este es el que tendremos que usar para saber que usuario esta usando nuestro web.

Con esta información podréis tener controlados que usuarios se conectan a vuestra web, pudiendo denegar servicio, o cambiar lo que vais a mostrar en función del tipo de usuario.

Ya veis que es muy sencillo saber que usuario se conecta a nuestro Google Apps Script y actuar en consecuencia.

No dudéis en hacerme llegar vuestras preguntas.

Nos vemos


Tal vez te interese