Como modificar formularios con google apps scripts


Los formularios de Google son una herramienta de entrada de datos tremendamente poderosa pero son tediosos de mantener, es por ello que los scripts de Google pueden ser de una tremenda ayuda para automatizar el proceso de mantenimiento de los formularios, permitiéndonos ahorrar tiempo y dinero.

El código para acceder a los datos de un formulario es sencillo pero requiere de práctica. Te recomiendo que con la base de este código vayas investigando para aprender a modificar de nuevas maneras los formularios de google.

El código que te mostraré aquí (solo es un ejemplo) se encarga de actualizar un campo en concreto para que sea un desplegable que tenga como opciones los nombres de las subcarpetas que existan en una cierta carpeta. Esto se puede modificar para que actualice en base a la información de ciertos ficheros o de cualquier otra información que se desee.

Aquí tenéis el código:

function onOpen()
{
    GetListOfFolders();
}

function GetListOfFolders()
{
   var folders = DriveApp.getFolderById("CARPETA CONTENEDORA DE SUBCARPETA").getFolders();

   var form = FormApp.getActiveForm();
   var items = form.getItems();

   var list = items[0].asListItem();
 
   var values = new Array();
   while ( folders.hasNext() )
   {
      var f = folders.next();
      var name = f.getName();
      values.push(name);
   }

   list.setChoiceValues(values);
}

NOTA: Si te interesa crear formularios con Google Apps Script mirate este link

41 comentarios:

  1. Me da error esta linea, es posible?


    var items = form.getItems();

    ResponderEliminar
    Respuestas
    1. Buenas,

      Creo que se por que te falla.
      Esto debería haberlo explicado un poco mejor. El código lo has puesto dentro de un formulario o de un spreadsheet??
      var form = FormApp.getActiveForm(); Solo te va a devolver un elemento válido si estás dentro de un formulario, si estás dentro de un spreadsheet te va a devolver null y evidentemente no puede hacer getitems de null.

      Es fallo mio que no lo expliqué bien... a la que tenga un momento lo modifico.

      Gracias por verlo.

      Eliminar
    2. Buenos dias, estoy recien aprendiendo a usar GAS, tienes algún canal de youtube donde pueda aprender mejor?. Por ahora estoy tratando de usar scripts para crear un formulario con nuevas funciones yabque tenia uno hecho en google forms pero no sabia cómo embeberle o enriquecerlo usando script. Si pudieras capacitarme acordamos si es necesario un pago, pero deseo aprender mejor esto. Desde ya muchas gracias!

      Eliminar
    3. Hola,

      El canal de youtube es algo que hace tiempo que me planteo pero nunca me he arrancado a montarlo.

      En cuando lo haga os aviso.

      En cuanto a clases a distancia, no lo hecho nunca pero si, se podría hacer sin problemas. Mandame un correo aquí: software.developer.bordas@gmail.com y hablamos sobre como podemos manejarlo.

      Nos vemos

      Eliminar
  2. Buenas tardes.
    Soy Ferran Castro, y aplico GAS en diversos proyectos de una escuela.
    En primer lugar, quisiera felicitarte (y agradecerte) por la labor que haces con la difusión de este lenguaje de programación.

    En estos momentos, me estoy encontrando con el siguiente problema:
    Tengo unos formularios, que recogen una información en unas hojas de cálculo. Estas hojas, estan configuradas con unas fórmulas, de manera que, cuando se acumulan unos valores, los va contando y los anota en una columna.
    Un script de GAS, lee la hoja de cálculo y, cuando se ha acumulado un valor determinado, envia un correo a dos personas, dando un aviso de terminado (espero que se entienda mi explicación).
    El problema que tengo es que, cuando se introducen datos nuevos en el formulario, este inserta una fila nueva y desplaza hacia abajo las ya existentes, dejando esta nueva fila sin fórmulas y,por tanto, invalidando el funcionameniento del sistema. De momento, entro y lo soluciono de manera manual.
    He intentado crear un script que "repare" la hoja de cálculo de manera automática, ascribiendo la fórmula. El problema es que, cuando guardo la fórmula en una variable, esta contiene comillas y paréntesis, y el scrip lo considera parte de la sintaxis del lenguaje, con lo que me da error al guardarlo o ejecutarlo.
    ¿Como se podria hacer para que no de este error?
    El código es el diguiente:

    function reparar(){

    var sps = SpreadsheetApp.openByUrl("https://docs.google.com/***");
    var sheet = sps.getSheets();
    var formula1="==IFERROR(INDEX($B$2:$B$251;MATCH(0;INDEX(COUNTIF($D$1:D1;$B$2:$B$251););));"")";
    var formula2="==IFERROR(INDEX($B$3:$B$251;MATCH(0;INDEX(COUNTIF($D$1:D1;$B$3:$B$251););));"")";
    var formula3="==IFERROR(INDEX($B$4:$B$251;MATCH(0;INDEX(COUNTIF($D$1:D1;$B$4:$B$251););));"")";
    var formula4="==IFERROR(INDEX($B$5:$B$251;MATCH(0;INDEX(COUNTIF($D$1:D1;$B$5:$B$251););));"")";
    var formula5="==IFERROR(INDEX($B$6:$B$251;MATCH(0;INDEX(COUNTIF($D$1:D1;$B$6:$B$251););));"")";


    sheet[0].getRange(2, 4).setValue(formula1);
    sheet[0].getRange(3, 4).setValue(formula2);
    sheet[0].getRange(4, 4).setValue(formula3);
    sheet[0].getRange(5, 4).setValue(formula4);
    sheet[0].getRange(6, 4).setValue(formula5);
    }

    ResponderEliminar
  3. Bona nit Ferran,

    Esto es una cosa que pasa en muchas ocasiones. Se empieza un proyecto con formulas, se tiene que seguir con script , se mezcla todo y acaba en desastre. No eres el primero ni serás el último al que le pase, tranquilo.

    Soluciones:
    1. Pasarlo todo a script. Te olvidas de las formulas y lo picas en código. Para mi es la mejor solución, pero evidentmente es la que más trabajo genera. Si te decantas por esta opción, si tienes dudas te puedo ayudar gratis. Si quieres que lo haga yo todo, algo te debería cobrar :)(https://www.fiverr.com/franciscobordas/write-a-simple-custom-script-for-your-google-sheets), aunque siendo un cole ya te digo que no pasaría de los 5$.

    2.Parchear para que las formulas no se rompan. Es la opción por la que es optado tu. A priori, no es lo aconsejable, por que es como una casa sin cimientos que cada cierto tiempo alguien quiere amplia el edificio poniendole más plantas. Acaba en desastre, pero entiendo los motivos por lo que lo has hecho. Solo quiero que seas consciente que no es una opción mantenible en el tiempo.

    En cuanto a tu pregunta, tienes dos fallos.
    1. Te sobran las comillas que hay justo antes del cierre de parentesis
    2. Has de utilizar setFormula en vez de setValue

    Te deberia quedar algo como esto:
    var formula1="==IFERROR(INDEX($B$2:$B$251;MATCH(0;INDEX(COUNTIF($D$1:D1;$B$2:$B$251););));)";
    var formula2="==IFERROR(INDEX($B$3:$B$251;MATCH(0;INDEX(COUNTIF($D$1:D1;$B$3:$B$251););));)";
    var formula3="==IFERROR(INDEX($B$4:$B$251;MATCH(0;INDEX(COUNTIF($D$1:D1;$B$4:$B$251););));)";
    var formula4="==IFERROR(INDEX($B$5:$B$251;MATCH(0;INDEX(COUNTIF($D$1:D1;$B$5:$B$251););));)";
    var formula5="==IFERROR(INDEX($B$6:$B$251;MATCH(0;INDEX(COUNTIF($D$1:D1;$B$6:$B$251););));)";

    sheet[0].getRange(2, 4).setFormula(formula1);
    sheet[0].getRange(3, 4).setFormula(formula2);
    sheet[0].getRange(4, 4).setFormula(formula3);
    sheet[0].getRange(5, 4).setFormula(formula4);
    sheet[0].getRange(6, 4).setFormula(formula5);

    Espero que te haya servido.

    Nos vemos!

    ResponderEliminar
  4. Hola Pako.
    Gracias por la rapidísima respuesta.
    La opción de hacerlo con código, la habia pensado, pero, tal y como dices, da mas trabajo y quería probarlo por esta vía. En un principio, creo que lo podré hacer yo (si no lo consigo, recurriré e tí;-)). Me gusta pelearme con el código. He comprobado que es la mejor manera de aprender (y me queda muchíiiisimo!!).
    Las comillas que me comentas, en principio forman parte de la fórmula. Probaré si ellas a ver.
    Y, vaya fallo mas tonto lo de setFormula.
    Por cierto, he leido por ahí que te gusta el rol (El señor de los anillos). Me ha llamado la atención por que yo soy máster des de hace muchos años (cuando salió la primera edición del libro de reglas), y todavía sigo haciendo partidas de vez en cuando. De hecho, se lo he contagiado a mi hija y a mi cuñado (jeje).
    En fin, saludos y, lo dicho, muchas gracias por la ayuda.

    ResponderEliminar
    Respuestas
    1. Buenas,

      Ciertamente la única manera de aprender es pelearse con el problema.

      Si las comillas te hacen falta ( la verdad, he ido rápido y no me he fijado) deberías ponerle delante de cada comilla un barra hacia la izquierda \" :
      var formula1="==IFERROR(INDEX($B$2:$B$251;MATCH(0;INDEX(COUNTIF($D$1:D1;$B$2:$B$251););));\"\")";
      var formula2="==IFERROR(INDEX($B$3:$B$251;MATCH(0;INDEX(COUNTIF($D$1:D1;$B$3:$B$251););));\"\")";
      var formula3="==IFERROR(INDEX($B$4:$B$251;MATCH(0;INDEX(COUNTIF($D$1:D1;$B$4:$B$251););));\"\")";
      var formula4="==IFERROR(INDEX($B$5:$B$251;MATCH(0;INDEX(COUNTIF($D$1:D1;$B$5:$B$251););));\"\")";
      var formula5="==IFERROR(INDEX($B$6:$B$251;MATCH(0;INDEX(COUNTIF($D$1:D1;$B$6:$B$251););));\"\")";

      Y si, durante muchos años jugué a rol y le debo mucho ( de hecho conocí a mi mujer en un club de rol :) ), pero ahora mismo soy rolero no practicante. No tengo el tiempo ni el circulo de amistades para seguir haciendolo, aunque lo añoro la verdad.

      Ya me contarás como te va.

      Eliminar
  5. Lo acabo de probar, y, sin las comillas del final, me da error. Y con las comillas, no compila correctamente (uff...)

    ResponderEliminar
  6. Hola, veo que eres mas conocedor de las google apps scripts, tengo una situación que me tiene atorada, apenas estoy empezando a utilizar estos servicios de google entonces no tengo el conocimiento suficiente para solucionar mi problema, la situacion es que quiero lograr una forma de acceder a las respuestas que se dan en el formulario pero "en tiempo real", o sea, antes de que el usuario mande la respuesta, porque yo no quiero trabajar con la hoja de calculo que genera, quiero trabajar directamente con el form mediante el script, y no se si exista una manera de acceder a esos datos, agradecería que me ayudaras, un saludo.

    ResponderEliminar
    Respuestas
    1. Hasta donde yo se, no existe la manera.

      Toda la info de los formularios esta en "el aire" hasta que no se guarda en el spreadsheet.

      Sorry

      Eliminar
    2. Muchas gracias por tu respuesta, entonces podrías responder cómo trabaja TextValidation? Es la función que de alguna manera quiero descomponer, ésta si obtiene ese valor.

      Eliminar
    3. Hola,

      No acabo de entenderte, me pasas algún link a algún ejemplo?

      Nos vemos

      Eliminar
  7. Hola, estoy haciendo un formulario donde se recoge información de una actividad realizada, si la actividad esta "en proceso" debe llegar un correo electrónico a la persona que lo llenó con el enlace para editarla cuando termine, si por el contrario ya esta "terminada" llega a un superior que la revisa, y de la misma manera llega al superior al momento que se edita y pasa de "en proceso" a "terminada"

    a veces me envía bien los correos la primera vez y luego me aparece el siguiente error y no envía el correo, (esto siempre me pasa cuando se pasa de "en proceso" a "terminada" )

    TypeError: No se puede llamar al método "getEditResponseUrl" de undefined. at emailOnFormSubmit

    saben por qué puede estar pasando y como puedo solucionarlo?

    ResponderEliminar
    Respuestas
    1. Así sin ver el código, complicado.

      Generalmente cuando te diga undefined es que esa variable en concreto no existe.

      Entiendo que llamas a getEditResponseUrl de un objeto que no existe, pero ya te digo, es complicado.

      Si quieres, copia algo del código y te lo miro.

      Nos vemos

      Eliminar
    2. function emailOnFormSubmit (e) {
      var row = e.range.getRow();
      var responseSheet = e.range.getSheet();
      var googleFormUrl = responseSheet.getFormUrl();
      var googleForm = FormApp.openByUrl(googleFormUrl);
      var date = e.values[0].split(' ')[0]
      var time = e.values[0].split(' ')[1]
      var splittedDate = date.split('/')
      var timestamp = new Date(splittedDate[1] + '/' + splittedDate[0] + '/' + splittedDate[2] + ' ' + time);
      var formResponse = googleForm.getResponses(timestamp).pop();
      var responseUrl = formResponse.getEditResponseUrl();
      responseSheet.getRange(row, 60).setValue(responseUrl);
      var responses = googleForm.getResponses();
      }

      Eliminar
  8. he visto que a veces me guarda el enlace para editar en la hoja de respuestas y otras no, sin ese enlace no se envía el correo, otras veces se demora en cargar el enlace, pero carga y el activador se ejecuta antes de que la información cargue y queda igual en error

    ResponderEliminar
    Respuestas
    1. Has probado con un SpreadsheetApp.Flush() despues de hacer el setValue(responseUri) ?

      Tal vez te ayudaria.

      La otra manera seria que el sistema que se encarga de enviar el correo gestione correctamente esta posibilidad

      Ya me diras como te va

      Nos vemos

      Eliminar
    2. Este fue el código que use para solucionar mi problema

      function responseURL(e) {
      var row = e.range.getRow();
      var responseSheet = e.range.getSheet();
      var form = FormApp.openById('id del formulario');
      var formResponses = form.getResponses();
      for (var i = 0; i < formResponses.length; i++) {
      var formResponse = formResponses[i];
      responseSheet.getRange(row, 60).setValue(formResponse.getEditResponseUrl());
      }
      }

      me guarda el enlace en la hoja de calculo, y desde esta use el complemento formMule para enviar el correo, se pueden usar hasta 15 plantillas para enviar correos con algún tipo de condición.

      Muchas gracias por la ayuda.

      Eliminar
    3. Me alegro haberte podido ayudar.

      Nos vemos

      Eliminar
  9. hola me gustaria preguntar si es posible
    tengo 3 vendedores los cuales manejan clientes cada uno 30 no siempre son los mismos
    para eso cree un formulario de google por cada vendedor, al entrar al formulario el vendedor puede seleccionar su nombre en una lista desplegable luego el formulario lo lleva a unas preguntas especificas por cliente lo tedioso es que tiene que agrgar los datos del cliente cada ves que ingresa esa información y estos clientes cambias cada 15 dias lo que me lleva a no ser viable crear una lista desplegable de clientes pues tendria que modificar a cada rato el formulario mi pregunta es existe alguna formula o comando que me puedan actualizar automáticamente esta lista desplegable? me esplico mejor es posible ingresar los clientes una ves en una opción del formulario para luego aparezcan en una lista despegable del mismo formulario ? y asi cada ves que entres los vendedores escogen su nombre le dan siguiente y luego aparesca 2 opciones la primera opcion ingresar nuevo cliente la segunda gestionar clientes y dentro de esta ultima aparesca la lista desplegable :) si lo que digo es muy tonto discúlpenme si no se puede también, gracias

    ResponderEliminar
    Respuestas
    1. Hola buenas,

      Lo que pides no tiene nada de tonto, pero tampoco tiene nada de fácil.

      Se podría intentar hacer con formularios (aunque sería complicado) pero realmente la mejor manera sería implementar una web dinamica con google apps scripts: http://googleappscriptsweb.blogspot.com/2015/03/como-crear-una-web-dinamica-con-google.html

      Si quieres intentar hacerlo aquí estoy para irte ayudando, si quieres que te lo haga deberiamos mirar una opción más profesional: https://www.fiverr.com/franciscobordas?public_mode=true

      Nos vemos

      Eliminar
  10. Buenos días. Una pregunta, he creado un formulario de encuestas y he hecho un script para que ve llegue un correo cuando llenen las encuestas pero solo de algunas preguntas. El correo sí me llega con las respuestas que quise, pero ahora lo que quiero es esas preguntas y respuestas que seleccioné me lleguen dentro de un cuadro o una tabla, personalizado. Es decir, en la columna de la izquierda todas las preguntas, dentro de una tabla, y en la columna de la derecha todas las respuestas, también dentro de una tablas.
    Espero haberme explicado bien.
    Gracias por tu ayuda

    ResponderEliminar
    Respuestas
    1. Hola,
      Las tablas en el correo las puedes hacer con html.
      Las preguntas las puedes pillar de la primera fila de las respuestas

      A partir de aqui, si tienes dudas con el código compartelo y te comento.

      Si quieres que lo haga yo deberiamos mirar de establecer un proyecto dado que no es trivial.

      Nos vemos

      Eliminar
  11. Hola:

    Estoy intentado hacer un formulario para tomar decisiones de enviar un tipo u otro de respuesta según las respuestas enviadas.

    ResponderEliminar
  12. var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[0];

    // This represents ALL the data
    var range = sheet.getDataRange();
    var values = range.getValues();

    // This logs the spreadsheet in CSV format with a trailing comma
    for (var i = 0; i < values.length; i++) {
    var row = "";
    for (var j = 0; j < values[i].length; j++) {
    if (values[i][j]) {
    row = row + values[i][j];
    }
    row = row + ",";
    }
    Logger.log(row);
    }


    ResponderEliminar
  13. Buenas noches a todos, tengo una duda y si me pudierais ayudar os lo agradecería un montón.
    Estoy trabajando con GAS y necesito cambiar:
    var timestamp = e.values [0] que me da fecha y hora, por un formato que solamente me dé la fecha.
    Muchísimas gracias.

    ResponderEliminar
    Respuestas
    1. Hola,

      Le veo dos caminos.

      Si esa fecha y hora te viene en forma de string, tal vez lo más facil es parsear ese string y quedarte solo con lo que te interesa.

      Sino, otra manera es que utilices la funcionalidad de javascript new Date y con un objeto Date escojas como quiere mostrar la información ( hay muchisimos tutoriales si lo buscas como javascript)

      Espero haberte ayudado

      Nos vemos

      Eliminar
  14. Hola Amigo, que tal todo? un favor, tengo un formulario el cual será usado constantemente por varias personas, este Form en uno de los campos debe llevar una lista desplegable la cual debe actualizarse con base a otra Hoja de Sheets, tendras algun consejo para poder realizar esta función? soy nuevo en esto, agradezco cualquier ayuda. Gracias

    ResponderEliminar
    Respuestas
    1. Mi consejo sería no hacerlo :D

      Dame un par de dias que pueda revisarmelo, ahora mismo estoy a full con un proyecto y te digo algo

      Nos vemos

      Eliminar
  15. Hola LordPakus, siguiendo con la consulta anterior... yo hice el codigo para que actualice una lista desplegable... pero el activador abrir, se ejecuta cuando abro el formulario para editar... hay alguna manera de hacerlo cuando se abre para cargar una nuevo registro.. . muchas gracias

    ResponderEliminar
    Respuestas
    1. Ponme un poco de código a ver si lo veo más claro.

      Con la explicación que me has puesto creo que te medio-entiendo pero tengas mis dudas.

      Eliminar
    2. function GetListOfsheet()
      {
      var planilla = SpreadsheetApp.openById("1EV-G1u0pJoBZIQboE_Qx92pXRTZ2vgjXmEzM2KB-Hv8").getSheetByName("Hoja1");
      var form = FormApp.getActiveForm();

      var items = form.getItems();

      var listvendedor = items[0].asListItem();
      var values = new Array();

      // var numvendedor = planilla.getLastRow();// esta linea no me funciona

      for(j = 2; j < 10;){


      var zzz = planilla.getRange(j,1).getValues();

      values.push(zzz);


      j++

      }

      listvendedor.setChoiceValues(values);


      }


      necesito que se ejecute cuando se abre un formulario para la carga, asi actualiza los datos de la columna 1 de la hoja.

      con el activador... se ejecuta cuando se abre para editar.

      no se si fui claro.
      muchas gracias

      Eliminar
    3. Hasta donde yo se, si es que te he entendido bien, lo que quieres no puede hacerse.

      Deberias plantearte otras opciones como hacer formularios en HTML y servir la web con GAS: https://googleappscriptsweb.blogspot.com/2015/03/como-crear-una-web-dinamica-con-google.html

      Siento no dar mejores noticias

      Eliminar
    4. Buenas noches LordP..solo para tu informacion, cambie el activador para que en lugar de ejecutarse al abrir el form... se ejecute por tiempo... por ahora probe cada 10 minutos y funciona.. probare despues cada menos tiempo (creo que vi que se puede cada 1 minuto)... tengo pendiente hacer el form en html... muchas gracias nuevamente

      Eliminar
    5. Tienes toda la razón. Por tal como tienes el código lo puedes montar con un trigger de tiempo, no caí, mis disculpas.

      En general no podrias por que el trigger te da información, pero tal y como lo tienes montado esta separado de la información del trigger por lo que es totalmente factible.

      No te recomiendo que uses el trigger de cada minuto. Acostumbra a dar problemas de ejecución cuando pones los triggers tan seguidos ( hay un tiempo de ejecución máximo por hora y por dia)

      Gracias por la aportación!

      Eliminar
  16. Buenas noches, tengo un formulario que quisiera restringir las consultas como máximo 5 veces, es decir que un usuario solo pueda realizar hasta 5 consultas oor día, agradezco mucho la ayuda.

    ResponderEliminar
    Respuestas
    1. Perdoname, pero el concepto de formulario y consulta no me cuadra.... a que te refieres?

      Eliminar
  17. Buenas noches, tengo un formulario y necesito que al cargar datos del usuario de una db. Puede ser de la misma hoja de calculo

    ResponderEliminar
    Respuestas
    1. Si tienes código hecho con dudas concretas, ponlo aquí he intentaré responderte, si lo que quieres es que alguien trabaje para ti, te remito a mi pagina de contacto para contratación de proyectos: https://googleappscriptsweb.blogspot.com/p/como-contratar-mis-servicios-de.html

      Nos vemos!

      Eliminar

Tal vez te interese

Entradas populares