Arrastrar fórmulas automáticamente en Google SHEETS con GAS (Sin ArrayFormula)
HTML-код
- Опубликовано: 19 авг 2024
- Podemos hacer un sencillo código que me permita arrastrar fórmulas automáticamente. Tambien sirve para reemplazar fórmulas por si alguien por error las borra o cambia
👍 Si te ha gustado este video, te encantará el curso gratis "Aprende Google Sheets en 2 horas". Aquí puedes obtener acceso juansguzman.co...
📋 Puedes encontrar el código y la plantilla de este video (y los más de 350 videos del canal) inscribiéndote en un plan mensual o anual en mi membresía JuanSGuzman - juansguzman.com
🎓 Allí mismo, encontrarás más de 75 cursos completos de Sheets, Forms y Apps Script.
☎️ Si necesitas asesoría personalizada, ofrezco algunas horas a la semana de este servicio. juansguzman.co...
🔔 Suscríbete al canal donde recibirás nuevos videos semanales de Sheets, Forms, Apps Script y más.
Aquí algunos canales donde me puedes seguir y apoyar:
📨 - Newsletter: juansguzman.co...
➤Patreon: juansguzman.co...
➤Telegram: juansguzman.co...
➤Instagram: juansguzman.co...
ⓕ - Facebook: juansguzman.co...
➤Tiktok: juansguzman.co...
𝕏 - (Twitter): juansguzman.co...
Cualquier sugerencia, pregunta o inquietud, te invito a comentar más abajo
📝 Listas de Reproducción relacionadas
▶️Videos Relacionados
🔍Tabla de Contenido
Podriamos profundizar mâs en el tema, ya que puede servir para proteger valores y formulas. ¿Ven alguna otra aplicación práctica?
Si, por supuesto, creo que es la posibilidad de, con código, poder crear una especie de tablas como las de excel, que es lo único que le hecho en falta a Sheet.
@@gaston5999 Es verdad. Las tablas de Excel es lo único que extraño, jaja
Saludos!
Muchas gracias necesitaba 1 de tus furmulas y me ayudo
Excelente! Gracias por comentarlo!
Excelente Juan, y copiar y pegar los datos como texto es una opción aún mejor.
Saludos 👍
Gracias, saludos!
Excelente súper aporte, mil gracias pero... Me pusiste a trabajar con esta idea ya que me tocó corregir un montón de marañas que hice dado que no conocía ests excelente solución
Jaja
Esa es la idea, sembrar la semilla de la curiosidad!
Felicidades por tus ganas de mejorar las cosas
Saludos!
Por finnn lo que tus nerds seguidores estabamos esperando.
Pero... Una recomendación para este video Guzmán, es que al haber mostrado este ejemplo muchos van a tener problemas porque cada vez que editen cualquier cosa en otras hojas siempre se les va a copiar y pegar la formula de C2.
Tal vez yo habría mostrado el vídeo con un ejemplo seteando específicamente la hoja y no la hoja activa. O de pronto tal vez, el onEdit con un IF verificando que este en la hoja específica que querramos hacer el arrayformula y hasta mejor aun en la hoja y columna específica. Te vas a ahorrar muchos comentarios pidiendo ayuda la verdad jaja. Y hasta va a ser algo mucho más ligero a la implementación del video ya que tu onEdit estaría copiando y pegando la formula por cada edición que se haga, en cambio al forzarlo a que solamente funcione en una hoja y columna específica no correria todo el tiempo, agilizando aún mas todo.
Muchas gracias por tu video juan, lo había visto en un foro overflow esta alternativa pero se me hacía tal vez algo pesado el onEdit para la hoja de calculo pero si tu dices que es mejor que el arrayformula entonces lo implementaremos. Saludos!
Buen comentario, si, de hecho a mi me pasó eso también de tener más de una hoja en el libro y si, se editan todas. Hay que especificar hoja. Saludos
Gracias por el comentario
Tienes toda la razón...le faltó un pedazo
Ya voy a preparar un video tipo "postdata" para este (o simplemente la parte 2)
Saludos!
Fue justo lo que pensé cuando se explicó se hacía mediante onEdit. Probablemente sea una buena técnica para hojas de cálculo que tengan una baja cantidad de calculo enlazados. Ya que básicamente, al editar hay que esperar por cálculo completo de las nuevas formulas.
Me gustaría saber como determinar la eficiencia entre usar un ArrayFormula vs Formulas individuales
@@jesam01 es mejor las fórmulas individuales le puedes poner muchas condiciones con if o hacer do whiles... Es mas para evitar reprocesamientos escribe que si la fila no está vacía no haga nada (es decir que no copie y pegue la formula) y listo. Se ejecutará el doble de rápido
@@jesam01 Buena pregunta
Yo alguna vez vi una herramienta de aufitoria de Sheets pero lo unico que miraba es el numero de ArrayFormulas
Depronto se podria buscar el numero de arrayformulas y mirar para cuantas filas aplicaría
Complejo pero interesante
Saludos!
ME salvaste de hacer cosas innecesarias. En realidad ya le perdi el respeto al script. MUCHAS GRACIAS
Excelente! Esa es la idea
Uno le tiene miedo, pero se puede, mas ahora con las herramientas que nos ayudan mas que nunca como ChatGPT
Saludos!
Super, voy a implementarlo con unas receipts de IFTTT o Zapier para Google sheets ..sería un buen tema para un vídeo profe !!
Gran sugerencia
Zapier es una maravilla. No he "jugado" tanto con IFTTT, pero la idea es la misma
Saludos!
buen tip el de poner las funciones en ingles. tenia errores por eso . Gracias
Hola
Excelente! Me alegra ser de utilidad.
Un gran saludo!
Fantástico como sempre Juan. Gracias
Siempre firme apoyando con tus comentarios Luciano
Mil gracias!
Hola Juan, me parece una excelente función, pero la puse en práctica y resulta que cuando se ejecuta, y después llego a tener menos datos en las filas, la formula no se borra de la columna, y por esto deja de funcionar mailMerge (Que también aprendí de tus videos) Saludos. pdta. Esperando la segunda parte jajaja
Intenta en vez de poner "", no poner nada. Algo asi como
ArrayFormula(SI(A1:A="";;el_resto)
Saludos!
Magnífico vídeo, Juan!!!
Gracias!!!
me sirvio mucho el video pero tengo un problema, en la linea 12 yo coloque E3 y ahora me borro la informacion delas otras hojas, como puedo especificar que esta solo aplique para una hoja en especifico
Algo asi
function arrastrarFormulaAutomaticamente() {
var miFormula = "=A2+B2"
var libro = SpreadsheetApp.getActiveSpreadsheet();
var hoja = libro.getActiveSheet();
if (hoja.getName() == "Mi hoja") {
hoja.getRange(2, 3, hoja.getLastRow() - 1).setFormula(miFormula)
}
}
Saludos!
Hola, gracias por este porte, y si se tiene varias hojas, como se hace que solo actue en una hoja especifica y libro especifico, muchas gracias
Hola
Aquí te dejo un video donde lo hago
ruclips.net/video/kg6TVFrDpqY/видео.html
Saludos!
Hola
Quisiera saber como evito que me ponga 0 (ceros) en las columnas inferiores que no hay datos.
Gracias
Algo asi
=ArrayFormula(SI(A1:A="";;BUSCARV....))
Saludos!
¿Hay alguna forma para que Sheets me devuelva la cantidad de filas que contiene la zona de filas y columnas inmovilizadas?. Sería interesante para tenerlas en cuenta en el código.
Interesante!
Nunca se me habia ocurrido
function filasCong(){
let hoja=libro.getSheetByName(NOMBRE_HOJA_VIDEOS)
Logger.log(hoja.getFrozenRows())
}
Saludos!
Molesto nuevamente y te agradezco tu pronta respuesta
=ARRAYFORMULA(B2:B*C2:C) esta es mi formula quiero que cuando el resultado = $0,00 no me ponga nada, o sea blanco
muchas gracias
=ARRAYFORMULA(SI(B2:B*C2:C=0;;B2:B*C2:C))
Saludos!
Tengo una consulta si si aplico esto al momento de yo abrir el archivo desde la app movil y yo agregar una nueva linea desde el movil, seguira arrastrandose las formulas?
Hola!
Debería
Saludos!
Excelente video, por alguna razón no me esta tomando la segunda formula que mostraste:
function arrastarFormulaAutomaticamente2() {
var libro=SpreadsheetApp.getActiveSpreadsheet();
var hoja=libro.getActiveSheet();
var miFormula2=hoja.getActiveRange("Q2").getFormula()
hoja.getRange(2,17,hoja.getLastRow()-1).setFormula(miFormula2)
}
No sé que estoy haciendo mal y cuando trata de cambiar la formula por:
function arrastarFormulaAutomaticamente2() {
function arrastarFormulaAutomaticamente() {
var miFormula="iferror((R6/F6)*1000;" ")"
var libro=SpreadsheetApp.getActiveSpreadsheet();
var hoja=libro.getActiveSheet();
hoja.getRange(2,17,hoja.getLastRow()-1).setFormula(miFormula)
}
¿Me podrías indicar que vez mal en la formula por favor?
Q error t sale?
Saludos!
@@JuanSGuzman Me sale esto,cuando pongo "iferror" Error de sintaxis: SyntaxError: Unexpected string, línea: 12, archivo: Código.gs
@@hyunkim1205 Ojo con las comillas. Intenta así
'IFERROR((R6/F6)*1000;" ")'
Saludos!
geniaal video, como saber a ciencia cierta si arrayformula hace pesada una base, solo en los mb que ocupe? una forma mas rigurosa de saber hay? suscritoo ya
Hola Juan mira que aplique tu metodo y todo perfecto pero en el caso que necesite cambiar un valor manual de la columna y que se quede así. ya que siempre se actailiza y me deja el valor de la formula se puede cambiar eso?
Hola juan como estas !! si tengo varias columnas con diferentes formulas en cada columna como seria el codigo
Hola!
Tengo ese video pendiente
Lo sigo anotando...
Saludos!
Sos un crack. ¿ si tengo varias columnas con fórmulas como lo podría hacer?. También sería bueno un video explicando cómo ocultar las fórmulas. 🙏🏾
Lo tengo para un siguiente video
Saludos!
@@JuanSGuzman Qué buen contenido Juan, ¿Cuándo subes el siguiente video cuando hay varias columnas con fórmulas?
@@johanchoren5884 Ha subido de ranking esta peitción, jaja
Espero en un par de meses
Saludos!
@@JuanSGuzman Yo vengo 6 meses después y me encantaría saber si ya existe ese video. Estoy haciendo un sistema de bitácora que usa muchas fórmulas y fórmulas cruzadas entre hojas, pero lo hice en excel y la empresa usa google drive. Me encantaría saber cómo trasladar todas las fórmulas y, sobre todo, recuperar la funcionalidad de las tablas.
@@JuanSGuzman Nos avisas por favor si se subió el video
estoy usando ArrayFormula y me funciona, necesito saber como hago para que cuando el resultado es = $0,00 me muestre ""
Hola!
Intenta asi
=ArrayFormula(SI(A1:A=0;B1:B;))
Saludos!
Como hacer eso en el celular?
Hmmmm
Buena pregunta...la verdad yo soy muy poco fan de usar Sheets en el cel, me parece que es demasiado limitado. Pero lo investigo
Saludos!
Hola estimado, me sale el siguiente error SpeadsheetsApp is not defined
Además quería consultar como ingreso la fórmula "=SI(ESBLANCO(AN2),"Inseguro","Seguro"), ya que al colocar " " en el argumento de la función SI, Apps Script me lo toma como un error. Saludos
SpreadsheetApp
Saludos!
1. Ten cuidado, las formulas deben estar en ingles =IF(ISBLANK(
2. Puedes jugar con las comillas simples y dobles, asi: '=IF(ISBLAANK(AN2),"","")'
Saludos!
Estoy guandome con tu viideo y aunque se demora en cargar los datos funciona el onedit.. Pero resulta que se me estan cargando varias filas de mas y eso me afecta en la tabla
Debes revisar el ultima fila o getLastRow
Saludos!
Gracias de antemano y una consulta , pasa que me funciona pero por alguna razon las filas que no tienen valor me devuelve 0 y esto me llena filas innecesarias
Hola JAvier, la razón es que tienes un ArrayFormula, o que tienes casillas de verificación
Para eso, reemplaza el .getLastRow con una función de encontrar última fila que te explico en este video
ruclips.net/video/ioiDbhN5MHk/видео.html
Saludos!
Juan! me gustaria ver un video de como agregar el mismo texto a una seleccion de celdas con GAS y saltando las casillas vacias
Muy interesante
Ya lo anoto
Saludos!
Excelente video ❤
Hola!
Mil gracias por las palabras!
Saludos!
Buenas Juan, excelente tu explicación, casi justo lo que necesitaba. Pero tengo una duda que tal vez me puedas aclarar. Cómo hago para que copie y pegue, no sólo una celda, sino varias. Ej: de C2:F2. Gracias.
Lo solucioné simplemente haciendo lo mismo para cada columna.
Pero bueno, si hay alguna forma de hacerlo en un sólo paso estaría muy bueno saberlo. Más que nada para cuando son muchas las celdas con fórmulas que se quieren copiar. En mi caso eran sólo cuatro, pero me imagino que si necesitás escribir código para 100 o 200 celdas que quieras copiar puede ser engorroso mi método.
Saludos nuevamente.
@@gaston5999 creo que la única forma es la que dices, creando variables miFormula, miFormula2,miFormula3... Copiando de varias columnas y pegandolas
@@oxpey4473 Gracias por tu respuesta
Hola
Podrías hacer un for
Saludos!
Juan, una consulta desde aquí, con Array las hojas se vuelven muy pescadas (como bien, has comentado), y tardan mucho en la ejecución, sucedería lo mismo con este modo??? Son muchas las fórmulas que incluye cada tabla, si esta nueva forma de proceder que has mostrado para el arrastre, mínima o anula el tiempo de ejecución, cojo el primer vuelo para Colombia y te invito a una mariscada. Un abrazo fuerte Juan, como siempre gracias!!!👋
Con este modo es más ligero, en otro comentario le he comentado a juan una forma para que el onEdit sea aún mucho más ligero. Luego chécalo tal vez te sirva
Jaja!
Ya no estoy en Colombia sino en Canadá, pero acepto la mariscada, jajaja
Lo bueno de este método es que tu controlas cuando se ejecuta, y no siempre como el AF. Como bien dice Oxpey habría que ponerle algunos limites, pero creo que a largo plazo es mejor...aunque el tiempo lo dirá
Saludos!
Excelente video sobre todo cuando se tienen muchos registros. Quisiera saber como puedo juntar los registros que tengo en varios libros de Google sheets en uno solo todos tienen un mismo encabezado, lo trate de realizar con varios importrange anidados pero se vuelve pesado ademas de que son rangos muy grandes se podría hacer lo mismo con código
Probaste con query(importrange? Seleccionando específicamente columnas que solamente necesites. Yo haría varios en distintas hojas y luego una hoja juntando la información necesaria
Hola Eduardo
Te dejo varios videos del tema
ruclips.net/video/Qhky_Q33mp8/видео.html
saludos!
Gracias por sus respuestas veré los videos y tomaré en cuenta los consejos, adicional me gustaría preguntar hay manera de poner bordes en una celda o un rango con una condición, saludos.
@@eduardorivera4720 Hola!
Asi es!
FOrmato condicional desde Scripts Espero hacer videos algun día
Saludos!
Hola Juan,
muchas gracias por tus tutoriales, están buenísimos! ahora una pregunta, estoy intentando utilizar el script para arrastrar fórmula CONCATENAR, y no me está funcionando. Tiene algunos límites? desde ya muchas gracias!
Efectivamente, no funciona con CONCATENAR
Aquí te dejo el video que te lo explica y te dice como solucionar
ruclips.net/video/sQud3mv3sbY/видео.html
Saludos!
Muchas gracias por las explicaciones, fueron de gran ayuda. En mi caso logré hacer el script usando BUSCARV, pero ¿Cómo podría proceder en el caso de que, apenas el script inserte las fórmulas, convierta el resultado en valores (pegar como valores), para que solo salga el valor? y el usuario puede cambiar este valor normalmente si lo desea. Sería algo con CopyPasteType, creo. ¿Podrías ayudarme en este caso?
En el caso que estoy desarrollando, no habrá problemas si el usuario cambia la columna. El script realiza la búsqueda con la fórmula, pero permite al usuario ingresar otro valor.
Hola
Algo asi
function protegerFormula() {
var hojaDatos=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Datos")
var formula="=MONTH(A2)"
var rangoAPegar=hojaDatos.getRange(2,6,hojaDatos.getLastRow()-1)
rangoAPegar.setFormula(formula)
rangoAPegar.setValues(rangoAPegar.getValues())
}
Saludos!
@@JuanSGuzman Gracias!!
Hola yo tengo esta formula:
'=IF($G2 = TRUE, "TURNO NO DISPONIBLE", "RESERVADO")' he insertado en el var mi Formula y cuando me la pasa a la otra hoja me da ERROR de Analisis de Formula traigo desde un hoja formulario, a otra hoja Datos y desde el formulario quiero pasar Celdas con Valores y Celdas con formulas. Saludos
Intenta así:
var miFormula="=IF($G2 = TRUE, \"TURNO NO DISPONIBLE\", \"RESERVADO\")"
@@johanchoren5884 por ahora lo he solucionado con otra hoja en la que combino un Query los datos y dos columnas con las formulas , vere de todas formas tu sugerencia. Gracias
Hola!
PAsa el true a minuscula "true"
o en funcion TRUE()
Me cuentas
Saludos!
Hola juan, Excelente tus aportes... Aplique lo explicado en una tabla de datos y se me sigue extendiendo la formula (es un buscarv) y debido a que son respuestas de formulario no necesito que se me siga extendiendo la formula hasta donde no hay datos
function arrastrarpagos() {
var libro=SpreadsheetApp.getActiveSpreadsheet();
var hoja=libro.getSheetByName("Reg Pagos");
var formulamaestra=hoja.getRange("F2").getFormula()
hoja.getRange(2,6,hoja.getLastRow()-1).setFormula(formulamaestra)
}
El problema esta en el getlastrow
cambiala por una formula que encuentre la ultima fila de la columna que tu quieras
Aquí te dejo el video
ruclips.net/video/ioiDbhN5MHk/видео.html
Saludos!
Hola Juan tengo una pregunta, como ejecutamos una función automáticamente cuando quien hace la modificación en la hoja de calculo no es un
"usuario" si no un servicio de automatización como integromat o Respond? hablo de cuando una aplicación externa es quien agrega información en una nueva fila? tomé en cuenta este video y funciona si es el usuario quien hace la modificación, pero cuando es un servicio externo no realiza la acción de arrastrar 🤔
Por cierto tu material es de excelente calidad 😎👍
Hola!
Tienes razón.
Ya estoy preparando un video.
Saludos!
@@JuanSGuzman Hola Juan! Llegaste a sacar ese video? Me pasa lo mismo :(
@@Rodrigo-hh7rp Si señor
Es este
ruclips.net/video/__HqmOhchGY/видео.html
Saludos!
5:02 Menos mal que me me fui por este video, ya que exactamente tengo el script donde se busca la ultima fila, aquí lo mencionas: ruclips.net/video/JCkPPh7TbHo/видео.html
Jaja, ni yo me acordaba. Mil gracias!
Juan buenas tardes, arme un script en base a este video copiando una formula y me sucede que se AGREGAN lineas automaticamente en cada hoja que lo utilizo.. lo hace solo .. yo creo que es algo del getlastrow.
Te paso? No se como deshacer eso. En algunos casos llega a agregar hasta 29000 lineas en segundos
function arrastrarFormulaColRpl() {
var miformula= "=Q3:Q/12"
var libro=SpreadsheetApp.getActiveSpreadsheet();
var hoja= libro.getSheetByName('nzapiola');
hoja.getRange(3,18,hoja.getLastRow()-1).setFormula(miformula)
}
Hola!
Reemplaza .getLastRow por una función ultimaFila
Aquí te dejo el video
ruclips.net/video/ioiDbhN5MHk/видео.html
Saludos!
Hola Juan, necesito tu ayuda por favor, donde podría contactarte. Gracias
Hola!
sebastian@juansguzman.com
Saludos!
Hola Juan Muy buenos tus video, por eso me atrebo a pedirte una funcion que me resuelva un problema que tengo para ordenar una tabla de datos: 100 participantes y cada uno con 3 marcas, como puedo hacer una formula para que me saque el mejor con la mejor marca, en caso de haber 2 mejores marcas iguales toma la segunda mejor marca de ellos para determinar el ganador y en caso de haber empate que tenga en cuenta la 3er marca, y ordenar lo 8 mejores.
Juan 3-3-4
Pedro 4-5-2
Luis 5-1-4
Jorge 4-3-2
Esteban 2-1-3
Marcelo 1-2-1
javier 3-2-0
Fabian 2-1-1
En este caso el ganador seria Pedro ya que su 3er marca es mejor (2) y Luis tiene su 3er marca menor (1).
Desde ya espero tu ayuda. Muchas gracias por adelantado.
Hola Hector
Jaja, esta complejo tu caso. Lo dejo para revisar y mirarlo con calma más adelante
Saludos!
@@JuanSGuzman Bueno Juan te agradezco que lo tengas en cuenta. Ya que me seria de mucha utilidad si es posible resolverlo.
Te mando un abrazo.