Cómo escribir en una celda de una hoja de cálculo con Google Apps Script

Artículo perteneciente a la introducción de Google Apps Script
Artículo referente a como usar Google Apps Script en spreadsheets

Hace poco explicamos como leer información de una hoja de cálculo con GAS pero de poco nos servirá saber leer si no sabemos escribir.

La forma de escribir en una celda con GAS  es obtener esa celda y cambiar el valor que contiene. Esta técnica sencilla permite no solo cambiar el contenido de una celda, sino cambiarle el color de fono, de la fuente , el borde, etc...

El código es muy sencillo:

function onOpen() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [
    { name : "Escribir",  functionName : "Escribir"}
   ];
  spreadsheet.addMenu("Lord Pakus Scripts", entries);
};

function Escribir()
{
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();
  
  sheet.getRange(1,1).setValue('Hola').setBackground('red');
  sheet.getRange(1,2).setValue('Mundo').setBackground('green');
}


Con estas pocas líneas ya tenéis la base para empezar a escribir vuestros documentos. 

Si no sabes muy bien como va esto de los Google Apps Scripts te recomiendo que empieces por el principio con un sencillo Hola Mundo y luego te pongas con este artículo.

Solo tenéis que tener en cuenta un pequeño detalle, la escritura es lenta y tarda un poco en refrescarse el contenido. Si veis que ejecutáis el script, que finaliza, pero que no ha hecho nada, refrescad la hoja de cálculo.

Ya para finalizar, recordad que no abusar de las escrituras en hojas de cálculo, no están pensadas para ser escritas masivamente y pueden ralentizar muchísimo la ejecución de los scripts. Si necesitáis escribir gran cantidad de información tal vez deberíais mirar este post.

Espero que os sirva

Nos vemos

30 comentarios:

  1. Hola gran aporte, te comento un poco por encima lo que quiero realizar ya que veo que entiendes bastante, yo tengo unas landing page con google adwords, hay un formulario lo que quiero es que en la pagina .php que procesa el formulario quiero escribir el nombre,telefono,correo y mensaje que ha introducido el usuario, esto lo he de mandar a google spreadsheet y no se como hacerlo si con php o js, ya que no quiero instalar ninguna libreria a no se que fuera necesario

    ResponderEliminar
  2. Yo lo haría con js, creo que no te haría falta instalar nada. Igualmente no soy experto en php asi que es posible que hubiera otras soluciones mejores.

    ResponderEliminar
  3. muy bueno, una pregunta descolgada, ando buscando como hacer en una macro de scripts GAS la funcion de autocompletar datos al arrastrar una celda , en el viejo excel era:
    Range("D1").Select
    Selection.AutoFill Destination:=Range("D1:D14"), Type:=xlFillDefault

    pero no encuentro la contrapartida para las hojas de calculo de drive
    ¿alguien tiene alguna idea?

    ResponderEliminar
    Respuestas
    1. Hola, Igual no te estoy entendiendo pero el autocompletar manualmente existe.... para que lo necesitas en forma de GAS? Dependiendo de para que lo necesites pueden existir diferentes soluciones... podrias concretar un poco mas?

      Eliminar
    2. si claro, se que existe, la uso manualmente, te explico el problema

      arme una hoja de calculo en drive con muchas paginas, y tengo un montón de funciones lógicas que van de una pagina a otra para automatiza mucho trabajo que se hacia a mano, tengo unos 15 usuarios que le tienen que meter los datos

      son planillas con datos para que se calcule la estadística sola

      pero mis usuarios son "especiales" y les cuesta pasar simples datos numéricos, como son muchos numeros se equivocan todo el tiempo, y copian, pegan celdas y columnas enteras , de lo que seria mi area de datos, que es lo unico que pueden modificar

      estos contratiempos que ellos tienen, hacen que las formulas que estan cruzadas de una pagina a otra cambien siguiendo las celdas que son copiadas y pegadas

      estos errores en las formulas que siguen celdas equivocadas hacen que la estadística sea incoherente, y que planillas enteras estén mal

      yo tengo que corregir las formulas, no es difícil simplemente hay que buscar una celda que tenga las formulas bien y estirar para que la función de autocompletar corrija el error en toda la planilla , ... pero tengo que hacerlo manualmente unas 200 veces mas o menos, cada vez que mis usuarios hacen de las suyas, por lo que una macro seria una solución

      si en secuencia de comandos existe la función de autocompletar para reajustar todas las paginas de mi hoja de calculo, no tendría que crear una función que especifique cada una de las formulas en cada una las planillas

      Eliminar
    3. Ahora he entendido el problema. Según que usuarios deberian hacer un examen para tener permiso para introducir datos :D. Todos nos hemos encontrado con esto. Si el problema es ese.... tal vez podrias montar un script que se ejecute cada cierto tiempo y que se asegure que todas las celdas tienen las formulas que toca. Sinceramente no lo he probado nunca pero estoy casi seguro que haciedo un setvalue de una celda con "=FORMULA(PARAMETROS)" debería funcionar, así que no sería demasiado complicado implementarlo. No se si me explicado o aún te he liado más. :D Si lo pruebas y te funciona publicalo para que podamos verlo, es un tema muy interesante.

      Eliminar
    4. si, algunos usuarios deberían volver al jardín de infantes a que les enseñen los números otra vez.
      como sea, tan vez no te entienda, se me ocurrió algo, pero me parece demasiado complicado y sigo pensando que debería existir una forma mas facil de hacerlo
      function myFunction() {

      var i=0

      do {
      i += 1

      a = "Hoja 1!A"
      b = i
      c= a.concat(b)

      d = "=SUM(A"
      e = i
      f =":B"
      k =")"
      g = d.concat(e, f, e, k)

      m = "Hoja 1!C"
      n = i
      o = m.concat(n)

      p = "Hoja 1!b"
      q = i
      r= p.concat(q)

      SpreadsheetApp.getActive().getRange(c).setValue(i)
      SpreadsheetApp.getActive().getRange(o).setValue(g)
      SpreadsheetApp.getActive().getRange(r).setValue(i)
      } while (i < 20)
      }

      pero esto no me es practico, tengo celdas con media carilla de formulas lógicas anilladas, la estructura seria gigante solo para tratar de nombrar todas las celdas involucradas

      armar esto con una función de concatenar pedazo por pedazo es de locos

      Eliminar
    5. caramba ... es exactamente lo que buscaba, sabia que tenia que ser algo bien sencillo, solo que no podía verlo,

      use

      var t = SpreadsheetApp.getActiveSheet();
      t.getRange('E1').copyTo(t.getRange('E2:E20'));

      y va como piña

      muchas gracias, en serio gracias, cuando uno se traba con una pavada puede estar semanas golpeándose la cabeza contra el teclado sin ver que lo que le falta es ...una coma

      Eliminar
  4. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  5. Buenas tardes, una consulta, es posible un script capaz de obtener los comentarios hechos en una celda y detallarlos en una en otra pestaña de la hoja de cálculo.

    Gracias

    ResponderEliminar
    Respuestas
    1. Si, se puede hacer, recuerdo haberlo hecho para un proyecto hace ya un tiempo. Tendrias que mirarte la documentación de GAS que ahora mismo no recuerdo como iba, pero creo que era una función de Range o Cell. A la que me dejen escribo algo sobre los comentarios que es un tema que he dejado siempre abandonado

      Eliminar
  6. estimado. esta genial el blog. me ha servido para empezar en esto de GAS y me encanta.
    quisiera hacerle una consulta

    Estoy buscando info, no logro escribir en un rango determinado, con apps
    script.
    Evidentemente no es el mismo modo que escribir en una celda.
    podria guiarme donde buscar info o como hacerlo?

    este es el codigo que uso para escribir en la ultima linea de una hoja
    determinada. necesito escribir en la ultima linea de un rango determinado.
    ...
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = spreadsheet.getSheetByName('Hoja 3');

    -...
    var range = SpreadsheetApp.getActiveSpreadsheet().getRangeByName('campos');

    var lastrow = sheet.getLastRow()+1;
    var string2 = range.getValues();

    sheet.getRange(lastrow,2,1,5).setValues(string2);



    quisiera escribir en un rango con nombre o en un rango establecido.
    gracias nuevamente

    ResponderEliminar
    Respuestas
    1. Hola Nicolas,

      La función para escribir en un rango es el setValues ( la que has usado ).

      Un "rango establecido" ,por ejemplo, es el sheet.getRange(lastrow,2,1,5)

      No acabo de entender cual es el problema.

      Dimelo y te intento ayudar.

      Gracias por el comentario.

      Nos vemos

      Francisco

      Eliminar
  7. Maestro veo que no fui claro en el mensaje anterior. pido disculpas.
    lo que no estoy logrando hacer obtener correctamente lastrow de un intervalo determinado (ya sea intervalo con nombre o coordenadas) y luego poder escribir en el. le paso una copia completa del codigo con sus aclaraciones //
    *********
    function Escribir2()
    {
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = spreadsheet.getSheetByName('Hoja 6'); //hoja activa

    // var rangeimpacto = spreadsheet.getRange("Hoja 6!B9:D12").activate();intervalo donde quiero escribir
    // var rangeimpacto = sheet.getRange("B9:F15").activate();// intervalo en donde quiero escribir

    var rangeimpacto = SpreadsheetApp.getActiveSpreadsheet().getRangeByName('campoimpacto').activate();// Tambien he probado con intervalo con nombre

    var range = SpreadsheetApp.getActiveSpreadsheet().getRangeByName('campos2');// campos de donde obtengo los datos

    var lastrow = rangeimpacto.getLastRow();//ultima linea del intervalo (no la obtiene como ultima linea con datos sino directamente va a la ultima linea)

    var now = new Date();
    var year = now.getFullYear();
    var month = now.getMonth() + 1;
    var day = now.getUTCDate();
    var hora = now.getHours();
    var minutos = now.getMinutes();
    var segundos = now.getSeconds();

    var string = day + "/" + month + "/" + year + " " + hora + ":" + minutos + ":"+ segundos ;

    var string2 = range.getValues();//obtengo los datos del campos2

    var poss = rangeimpacto.getLastRow();// uso esto para comprobar cual es la ultima linea que el obtiene
    var poss2 = rangeimpacto.getLastColumn();


    sheet.getRange('A1').setValue(poss);// imprimo la ultima linea que obtiene...
    sheet.getRange('B1').setValue(poss2);
    sheet.getRange('C1').setValue(lastrow);
    //rangeimpacto.setValues(string2);// use esto para ver si imprime en todo el intervalo...

    rangeimpacto.getRange(lastrow,1).setValue(string);
    rangeimpacto.getRange(lastrow,2,1,5).setValues(string2);// intento escribir dentro del intervalo en la ultima linea... me genera un error que no encuentra getrange en el rango...


    }

    ResponderEliminar
    Respuestas
    1. getRange dentro de un rango?? No me suena que eso pueda hacerse, al menos no lo he intentado nunca.

      El getRange pertenece a sheet no a Range.

      Creo q tienes ahi los conceptos confundidos.. ya me diras

      Eliminar
    2. PAko. tenes razon. tenia los conceptos confundidos. con esta explicación que me diste, me fije las referencias, estaba confundido sobre las class. ahora ya me ordene mejor. vere el modo de poder escribir tipo lastrow del sheet. es decir debajo de la ultima linea escrita dentro de un rango. y no directamente a la ultima linea del rango.
      te agradezco muchisimo el tiempo que dedicas a nuestras consultas.

      Eliminar
    3. Para eso estamos.

      cualquier cosa ya lo sabes

      nos vemos

      Eliminar
  8. Hola.
    La función que onEdit(e) se ejecuta una vez cambiado el valor de la celda en una Hoja de Cálculo.
    Necesito obtener el dato (wDato) al posicionar el cursor en una celda (wRo,wCo)
    Alguno podría decirme como hacerlo.
    function onEdit(e)
    {
    ss = SpreadsheetApp.getActiveSpreadsheet();
    ss = SpreadsheetApp.getActiveSheet();

    wRo = e.range.getRow();
    wCo = e.range.getColumn();
    wDato = String(ss.getRange(wRo,wCo).getValue());
    }
    Juan

    ResponderEliminar
    Respuestas
    1. Hola

      Hasta donde yo se (ojala alguien me pueda corregir) no existe ningun evento referente a seleciconar una celda.

      La unica solucion que le veo es que llames a una función teniendo una celda seleccionada, pero no es lo mismo

      espero haberte ayudado

      Nos vemos

      Eliminar
  9. Otra pregunta (y muchas gracias)
    Luego de hacer varios CAMBIOS en la hoja de cálculo, deseo PROTEGER algunas CELDAS.
    Esta es la función (CAMBIOS) que estoy utilizando; funciona pero me pide que le indique quienes son los
    usuarios habilitados.
    Lo que necesito es que quede PROTEGIDO para TODOS menos para el PROPIETARIO
    function CAMBIOS ()
    {
    ss = SpreadsheetApp.getActiveSpreadsheet();
    ss = SpreadsheetApp.getActiveSheet();

    wRango= ss.getRange("A1:W2");
    var protection = wRango.protect().canDomainEdit();
    }
    Juan

    ResponderEliminar
  10. Ufff.... Aquí si que tendrás que googlear, no te podré ayudar con esto.

    El tema de permisos, protecciones y cosas por el estilo es un verdadero infierno.

    Además google va cambiando su política cada cierto tiempo así que lo que te diga hoy tal vez no te sirva en unos meses.

    Será más fiable si googleas, lo siento por no poder ayudarte más.

    Nos vemos

    ResponderEliminar
  11. Hola ya me contestaste una pregunta anteriormente y quisiera que si puedes me ayudes con un nuevo reto, estoy grabando datos relacionales desde un formulario al spreadsheet si el dato ya esta guardado en formulario anterior no tengo problema, pero necesito guardarlos simultáneamente la hoja principal y luego la relacionada, pero no veo como puedo agregar el id que estoy asignando al momento de guardar los datos de la hoja principal para registrar los datos relacionados... espero me haya podido explicar....

    ResponderEliminar
    Respuestas
    1. Más o menos creo que te he entendido....

      Lo que explicas no es complicado a nivel de código pero es un infierno a nivel de organizar el código y las variables.

      Sin tener el caso delante se me hace imposible ayudarte, todo depende de como tengas la info organizada.

      Lo siento

      Eliminar
  12. Gracias Lord PAkus por su respuesta. Hace horas que ando googleando y pude avanzar bastante con un codigo que escribió. Gracias de nuevo.

    ResponderEliminar
    Respuestas
    1. Me alegra que te haya servido. Para eso estamos. Cualquier cosa que necesites, dilo.

      Eliminar
  13. Hola Lord Pakus, queria consultarte algo , estoy haciendo un script de envio de correos con unos condicionales, requiero que en uno de mis condicionales cuando envie el correo despues le inserte un valor a una celda especifica, antes de este condicional estoy haciendo una iteración en la hoja y creo un objeto con cada uno de los datos. mira te adjunto mi codigo.

    function enviarCorreos() {
    const libro = SpreadsheetApp.getActiveSpreadsheet();
    libro.setActiveSheet(libro.getSheetByName("solicitudes"));
    const hoja = SpreadsheetApp.getActiveSheet();
    const filas = hoja.getRange("A2:BH").getValues();


    for (indiceFila in filas) {
    var solicitud = crearSolicitud(filas[indiceFila]);
    enviarCorreo(solicitud);
    }
    }

    function crearSolicitud(datosFila) {
    const solicitud = {
    fecha: datosFila[0],
    pdv: datosFila[1],
    nombre:datosFila[4],
    cedula: datosFila[5],
    correo: datosFila[3],
    estado: datosFila[56],
    envio: datosFila[57],
    diasvencidos: datosFila[59],
    };
    return solicitud;
    }

    function enviarCorreo(solicitud) {
    if(solicitud.estado != "enviado"){
    if(solicitud.diasvencidos > 1){console.log("camilo.gonazalez@crepesywaffles.com", "esta solicutud esta atrasada",solicitud.pdv,solicitud.nombre)}
    }else if(solicitud.envio !== "Correo enviado"){
    console.log(solicitud.correo, "Su dotación a sido enviada a " , solicitud.pdv);
    aui necesito insetar el valor "Correo enviado" a una celda especifica para que cada vez que se ejecute mi script no me envie los correos que ya envie
    }

    te agradezco la ayuda, pensaba talvez que en el objeto solicitud podia traer de alguna manera el rango de la celda que necesito y asi SetValues() con un getRange podria ayudarme.
    }

    ResponderEliminar
  14. Estoy haciendo un examen en google forms lo cual al finalizar gracias a un componente se envie una boleta con sus puntuaciones el problema es que si 2 alumnos les dan enviar al mismo tiempo no ejecuta el script ya que lo que tomo

    var sheet = SpreadsheetApp.getActiveSheet();
    var lastRow = sheet.getLastRow();
    var row = sheet.getActiveRange().getRowIndex();

    y no esta respetando me gustaria como hacer para que no pase esto saludos

    ResponderEliminar

Tal vez te interese