Superguia de Google Sheets: del congelar files a Apps Script

Aquesta guia està escrita per a qui obre un full de càlcul cada dia i mai ha tingut una hora per aprendre’l. No hi ha res d’avançat aquí: hi ha el 90 % del que fa que un full deixi de ser un patiment. Ho pots llegir seguit o anar a la secció que et faci falta.

Tot funciona igual a Google Sheets i, amb noms de menú diferents, a Excel. On canvia, ho dic.

1. Congelar files i columnes

És la primera cosa que s’ha de fer en qualsevol full amb més de 30 files, i la que més gent no fa.

Menú: Visualització → Congela → 1 fila. També pots arrossegar la barra grisa gruixuda que hi ha a la cantonada superior esquerra, just sobre el número de la fila 1.

Per congelar files i columnes alhora: selecciona la cel·la que quedarà just a sota i a la dreta del que vols fixar (per exemple B2) i fes Visualització → Congela → Fins a la columna B i Fins a la fila 1.

A partir d’aquí, quan baixis 400 files seguiràs veient què vol dir cada columna.

Dues coses que van juntes amb això:

• Ctrl + ↓ salta a l’última fila amb dades de la columna. Ctrl + Shift + ↓ selecciona des d’on ets fins allà. Deixaràs de fer scroll.
• Ctrl + Shift + V enganxa només el valor, sense format ni fórmules. És l’enganxat que hauries de fer servir per defecte.

2. Filtres, i per què no vols el filtre normal

Dades → Crea un filtre posa els embuts a la capçalera. El problema és que el filtre és compartit: si el fas servir en un full on treballa més gent, tothom veu el que tu has filtrat, i sovint algú edita pensant que hi ha 12 files quan n’hi ha 900.

La solució és Dades → Vistes de filtre → Crea una vista de filtre nova. Té la mateixa interfície, però:

• Només la veus tu.
• Se’n poden desar diverses amb nom (Pendents 2026, Clients sense NIF).
• L’URL canvia i inclou la vista: pots enviar un enllaç que obri el full ja filtrat.

Filtrar amb fórmula

Quan el filtre s’ha de recalcular sol, hi ha FILTER:

=FILTER(A2:E; D2:D="Pendent"; E2:E>1000)

Torna les files de A:E on la columna D és «Pendent» i la E passa de 1000. Cada condició nova és un argument més, i totes s’han de complir.

Per a condicions «o», se sumen amb +:

=FILTER(A2:E; (D2:D="Pendent")+(D2:D="Revisió"))

I si necessites ordenar, agrupar o comptar alhora, QUERY fa amb una línia el que amb fórmules són cinc:

=QUERY(A1:E; "select D, count(A), sum(E) where E > 0 group by D order by sum(E) desc label count(A) 'Casos'"; 1)

L’1 final diu que la primera fila és capçalera. La sintaxi és SQL retallat, i val la pena tenir-la a mà.

3. Eliminar duplicats sense perdre res

Abans d’esborrar res, mira quants n’hi ha. El menú esborra sense preguntar i no sempre és el que vols.

Per comptar quantes vegades apareix cada valor d’una columna, en una columna auxiliar:

=COUNTIF($B$2:$B; B2)

Tot el que surti amb 2 o més és un duplicat. Ordena per aquesta columna i mira’ls: sovint descobreixes que no són duplicats de veritat, sinó Acme SL i ACME S.L., que és un problema diferent i pitjor.

Quan ja saps què tens:

• Menú: Dades → Neteja de dades → Elimina els duplicats. Pots triar quines columnes compten per decidir si dues files són iguals. Si marques totes, només elimina files idèntiques; si marques només el NIF, elimina qualsevol repetició de NIF i es queda amb la primera aparició.
• Fórmula: =UNIQUE(A2:C) torna la llista sense repetits en un altre lloc, sense tocar l’original. És l’opció segura.

També hi ha Dades → Neteja de dades → Retalla els espais en blanc, que arregla el clàssic "Acme SL " amb un espai final que fa que dues coses iguals no ho semblin.

4. Buscar dades a una altra taula: VLOOKUP i XLOOKUP

Aquesta és la funció que separa un full que serveix d’un full que es copia a mà.

VLOOKUP, i el seu paranys

=VLOOKUP(A2; Clients!$A$2:$D$500; 3; FALSE)

«Busca el valor de A2 a la primera columna del rang Clients!A2:D500, i torna’m el que hi hagi a la tercera columna d’aquell rang.»

Tres coses que fallen sempre:

1. L’últim argument ha de ser FALSE. Si el deixes buit o hi poses TRUE, Sheets assumeix que les dades estan ordenades i torna la coincidència aproximada — resultats equivocats que semblen correctes.
2. El valor buscat ha de ser a la primera columna del rang. VLOOKUP no sap mirar cap a l’esquerra.
3. El 3 és una posició, no una columna. Si algú insereix una columna al mig de Clients, el 3 continua apuntant a la tercera posició, que ara és una altra cosa. Silenciosament.

Els $ del rang ($A$2:$D$500) fixen les referències perquè no es desplacin en arrossegar la fórmula cap avall. És la causa número u de VLOOKUPs que funcionen a la fila 2 i no a la 3.

XLOOKUP, que arregla els tres problemes

=XLOOKUP(A2; Clients!$A$2:$A$500; Clients!$C$2:$C$500; "No trobat")

«Busca A2 dins la columna A de Clients i torna’m el que hi hagi a la mateixa fila de la columna C. Si no el trobes, escriu No trobat.»

• Dius quina columna busques i quina columna tornes, per separat. No hi ha números de posició que es trenquin.
• La columna de resultat pot estar a l’esquerra de la de cerca.
• El quart argument és el valor per defecte: t’estalvies embolicar-ho tot amb IFERROR.
• Per defecte fa coincidència exacta. El comportament correcte és el predeterminat.

Si el tens disponible, fes servir sempre XLOOKUP. VLOOKUP només val la pena conèixer-lo perquè el trobaràs als fulls que ja existeixen.

Buscar per dues columnes alhora

Quan la clau no és una sola columna (per exemple, client i mes), es concatenen les dues bandes:

=XLOOKUP(A2&"|"&B2; Dades!$A$2:$A$999&"|"&Dades!$B$2:$B$999; Dades!$E$2:$E$999; "")

El "|" al mig evita que "AB" & "C" i "A" & "BC" acabin sent la mateixa clau.

5. Text: unir, partir i netejar

Unir amb &, que és més curt que CONCATENATE i fa el mateix:

=A2 & " " & B2

Quan són moltes peces i algunes poden estar buides, TEXTJOIN és millor perquè no deixa separadors orfes:

=TEXTJOIN(", "; TRUE; C2; D2; E2)

El TRUE vol dir «ignora les cel·les buides». Amb & acabaries amb Barcelona, , 08001.

Partir una columna en diverses:

=SPLIT(A2; ",")

O, sense fórmula, Dades → Divideix el text en columnes.

Netejar:

• =TRIM(A2) treu els espais de sobra, inclosos els dobles interiors.
• =PROPER(A2) posa majúscula inicial a cada paraula; UPPER i LOWER fan l’obvi.
• =REGEXEXTRACT(A2; "[0-9]{8}[A-Z]") treu un DNI d’enmig d’un text.
• =REGEXREPLACE(A2; "[^0-9]"; "") es queda només amb els dígits: perfecte per normalitzar telèfons.

Compte: les funcions REGEX* de Sheets fan servir RE2, que no admet lookahead ni lookbehind. Si has copiat una expressió d’Internet i et diu error, sol ser això.

6. Condicionals

IF amb una condició:

=IF(E2>1000; "Revisar"; "OK")

Quan hi ha diversos casos, IFS evita els IF niats il·legibles:

=IFS(E2>5000; "Alt"; E2>1000; "Mitjà"; TRUE; "Baix")

S’avaluen en ordre i guanya la primera que es compleix. El TRUE final és el «per a tota la resta».

IFERROR embolica qualsevol fórmula que pugui petar:

=IFERROR(VLOOKUP(A2; Clients!A:D; 3; FALSE); "Sense client")

I per comptar o sumar amb condicions, COUNTIFS i SUMIFS — noteu la S final, que permet més d’una condició:

=SUMIFS(E2:E; D2:D; "Pendent"; B2:B; ">="&DATE(2026;1;1))

7. ARRAYFORMULA: una fórmula per a tota la columna

Aquest és el canvi que fa que un full deixi de trencar-se. En comptes d’arrossegar la mateixa fórmula 800 files —i que la fila 801 es quedi sense—, se n’escriu una sola a la fila 2:

=ARRAYFORMULA(IF(A2:A=""; ""; B2:B * C2:C))

Llegeix-la així: «per a cada fila del rang, si la columna A és buida no escriguis res; si no, multiplica B per C». Quan algú afegeix una fila nova, el càlcul ja hi és.

L’IF(A2:A=""; "";...) no és opcional: sense això, la fórmula omple de zeros les 40.000 files buides del full.

8. Apps Script amb Gemini: la secció llarga

Aquí és on un full de càlcul es converteix en una eina. Apps Script és JavaScript que corre dins de Google i pot llegir i escriure el full, enviar correus, cridar APIs i executar-se sol a una hora fixa.

I la bona notícia: ja no cal saber-lo escriure de memòria. Cal saber-lo demanar i saber-lo llegir.

On és

Extensions → Apps Script. S’obre un editor amb un fitxer Codi.gs. El botó ▷ executa, i el rellotge de l’esquerra (Activadors) és on es programa que s’executi sol.

Si el teu compte té Gemini a l’editor, hi trobaràs un botó d’ajuda per generar codi. Si no el tens, funciona igual de bé escriure a gemini.google.com i enganxar el resultat.

La part que decideix si el codi funciona: el prompt

El 90 % del codi que Gemini escriu malament és perquè no li han dit amb què treballa. Compara:

Prompt dolent:

Fes-me un script que enviï un correu quan hi hagi un client nou.

Rebràs codi versemblant que suposa noms de columnes inventats i no funcionarà.

Prompt bo:

Escriu una funció d’Apps Script per a Google Sheets.

Full: es diu Clients. La fila 1 és capçalera. Columnes: A = Data (data), B = Nom (text), C = Email (text), D = Import (número), E = Estat (text: Nou, Actiu, Baixa), F = Avisat (text, buit o sí).

Què ha de fer: recórrer totes les files amb dades. Per a cada fila on E sigui exactament Nou i F estigui buida, enviar un correu a avisos@example.com amb assumpte Client nou: <Nom> i cos amb el nom, l’email i l’import. Després escriure sí a la columna F d’aquella fila.

Requisits: llegir totes les dades d’una sola vegada amb getValues() i escriure els resultats d’una sola vegada amb setValues(), no cel·la a cel·la. Si el full no existeix, llançar un error amb missatge clar. Comenta el codi en català.

La diferència no és l’estil del prompt: és que el segon conté el contracte — noms exactes, tipus, valors possibles i condició de sortida. Aquesta informació no la pot endevinar ningú.

Una regla pràctica: si el que has escrit no permetria a un becari fer la feina sense preguntar-te res, tampoc en té prou Gemini.

Les cinc coses que has d’entendre del codi que et torni

No cal saber programar, però sí reconèixer aquestes peces:

function avisaClientsNous() {
  // 1. El full
  const full = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Clients');
  if (!full) throw new Error('No hi ha cap full que es digui "Clients".');

  // 2. Llegir-ho tot d'un sol cop
  const dades = full.getRange(2, 1, full.getLastRow() - 1, 6).getValues();

  // 3. Treballar en memòria
  const avisats = dades.map((fila) => {
    const [data, nom, email, import_, estat, avisat] = fila;
    if (estat !== 'Nou' || avisat) return [avisat];

    MailApp.sendEmail({
      to: 'avisos@example.com',
      subject: `Client nou: ${nom}`,
      body: `Nom: ${nom}\nEmail: ${email}\nImport: ${import_}`,
    });
    return ['sí'];
  });

  // 4. Escriure-ho tot d'un sol cop
  full.getRange(2, 6, avisats.length, 1).setValues(avisats);
}

1. SpreadsheetApp és la porta d’entrada. getSheetByName agafa una pestanya pel nom — si canvies el nom de la pestanya, l’script deixa de funcionar.
2. getRange(fila, columna, nombreDeFiles, nombreDeColumnes) — tot són números, i comencen per 1, no per 0. getRange(2, 1, 100, 6) vol dir «des de la fila 2, columna A, 100 files i 6 columnes».
3. getValues() torna una llista de llistes: dades[0] és la primera fila, dades[0][2] és la seva columna C.
4. setValues() escriu, i el bloc que li passis ha de tenir exactament la mida del rang. És l’error més freqüent.
5. throw new Error(...) atura l’script amb un missatge llegible en comptes de fallar a mitges.

La regla de rendiment que has d’exigir sempre

Cada getValue() o setValue() és una anada i tornada als servidors de Google. Dins d’un bucle, això és la diferència entre 0,5 segons i quatre minuts.

Malament (una crida per fila):

for (let i = 2; i <= full.getLastRow(); i++) {
  const estat = full.getRange(i, 5).getValue();   // ← crida
  if (estat === 'Nou') full.getRange(i, 6).setValue('sí');  // ← crida
}

Bé (dues crides en total):

const dades = full.getRange(2, 1, n, 6).getValues();
const sortida = dades.map((f) => (f[4] === 'Nou' ? ['sí'] : [f[5]]));
full.getRange(2, 6, sortida.length, 1).setValues(sortida);

Posa-ho al prompt literalment: «llegeix i escriu en bloc, mai cel·la a cel·la dins d’un bucle». Gemini ho fa bé quan li ho demanes i sovint no ho fa si no li ho demanes.

Que s’executi sol

Hi ha dues maneres:

Activadors simples. Una funció que es digui exactament onEdit(e) s’executa cada cop que algú edita el full, sense configurar res:

function onEdit(e) {
  if (e.range.getSheet().getName() !== 'Clients') return;
  if (e.range.getColumn() !== 5) return;            // només la columna E
  if (e.value !== 'Baixa') return;
  e.range.offset(0, 1).setValue(new Date());        // data a la columna F
}

L’objecte e porta e.range (què s’ha editat), e.value (el valor nou) i e.oldValue. Les dues primeres línies són el filtre: sense elles, l’script s’executa a cada tecleig de tot el document.

Els activadors simples no poden enviar correus ni cridar APIs. Per a això calen els altres.

Activadors instal·lables. Al rellotge de l’esquerra: Afegeix un activador, tries la funció, i «basat en el temps» → cada hora, cada dia a les 8, el que sigui. La primera vegada et demanarà permisos. Aquí és on viuen els informes automàtics.

Fórmules pròpies

Amb un comentari especial, una funció es pot cridar des d’una cel·la com qualsevol altra fórmula:

/**
 * Treu el NIF d'un text lliure.
 * @param {string} text El text on buscar.
 * @return El NIF trobat, o buit.
 * @customfunction
 */
function NIF(text) {
  const m = String(text).match(/\d{8}[A-Z]/);
  return m ? m[0] : '';
}

A partir d’aquí, =NIF(A2) funciona al full. Les fórmules pròpies tenen un límit de 30 segons i no poden escriure a altres cel·les.

Cridar una API des del full

UrlFetchApp fa peticions HTTP. Serveix per portar dades d’un sistema extern al full sense copiar res:

function portaDades() {
  const resposta = UrlFetchApp.fetch('https://api.example.com/clients', {
    headers: { Authorization: 'Bearer ' + PropertiesService.getScriptProperties().getProperty('TOKEN') },
    muteHttpExceptions: true,
  });
  if (resposta.getResponseCode() !== 200) throw new Error('L\'API ha respost ' + resposta.getResponseCode());
  const dades = JSON.parse(resposta.getContentText());
  // ...
}

Dues coses importants d’aquí: el token no s’escriu al codi, sinó a Configuració del projecte → Propietats del script; i muteHttpExceptions: true et deixa llegir el codi d’error en comptes de petar amb un missatge inútil.

Els límits que has de conèixer

• Temps d’execució: 6 minuts per execució amb un compte gratuït, 30 amb Workspace. Si el teu script processa 50.000 files, s’ha de partir en trossos.
• Correus: 100 al dia amb compte gratuït, 1.500 amb Workspace.
• UrlFetchApp: 20.000 crides al dia.
• Fórmules pròpies: 30 segons, i es recalculen més sovint del que t’imagines.

Depurar

Logger.log(variable) o console.log(variable) escriuen al registre. El panell Execucions (icona de llista, a l’esquerra) mostra totes les execucions, incloses les automàtiques que han fallat de matinada sense que ningú se n’assabentés. Mira’l un cop al mes. És el lloc on es descobreix que aquell informe automàtic porta tres setmanes sense enviar-se.

Una advertència sobre les funcions d’IA dins del full

Google ha anat desplegant funcions de tipus =AI(...) que criden un model des d’una cel·la. Són còmodes per explorar, però no són deterministes: la mateixa cel·la pot tornar coses diferents en dos recàlculs, i no hi ha manera d’auditar per què. No les posis en cap columna que després es facturi, es declari o es doni per bona.

Per a això, el patró correcte és cridar el model des d’Apps Script, escriure el resultat com a valor fix amb setValues() i deixar constància de la data. Així el que hi ha al full deixa de canviar-te sota els peus.

9. Higiene general

Tres hàbits que estalvien més temps que qualsevol fórmula:

1. Una capçalera, una taula per pestanya, sense files buides al mig. Tot el que hi ha en aquesta guia deixa de funcionar bé si el full té dues taules apilades a la mateixa columna.
2. Les dades i els càlculs, separats. Una pestanya Dades on només s’enganxa, i una altra amb les fórmules. Quan cal refer-ho tot, no perds la feina.
3. Protegeix les columnes de fórmules. Dades → Protegeix fulls i rangs. No és desconfiança; és que un enganxat mal fet destrossa 200 fórmules en un segon i no te n’adones fins al cap d’un mes.

Si has arribat fins aquí, ja tens més eines que la majoria de gent que treballa amb fulls de càlcul cada dia. La següent passa —quan el full comença a ser massa gran o massa crític— és una altra conversa.