Statistikuppgift med Apps Script

Denna uppgift är främst avsedd för att vara en kort introduktion till programmering i Google Sheet och tanken är att inspirera till programmering i miljöer som vi inte har programmerat i tidigare. Vi kommer bara skrapa på ytan men om ni vet att det fungerar och har gjort något litet så kanske det finns intresse av att grotta djupare i det på egen hand. Som alla andra programmeringsspråk så finns det guider och dokumentation att ta del av för den som vill. Här är Googles introduktion till Apps Scrips för utvecklare och som vanligt finns det ett helt gäng av tutorials på youtube också.

Del 1: Skapa en enkel tabell i Google Sheets

Skapa ett kalkylblad med namn och resultat på två delprov.

  1. Öppna ett tomt Google Sheet.

  2. I cell A1 till C1, skriv rubrikerna:

    • A1: Elevnamn
    • B1: Prov A
    • C1: Prov B
  3. Fyll i cirka 10 rader med fiktiva eller riktiga namn och resultat.
    • Exempel:
Elevnamn Prov A Prov B
Alice 15 18
Bob 12 14
... ... ...

Del 2: Summera provresultaten med Apps Script

Skapa ett skript som automatiskt summerar prov A + B för varje elev och skriver resultatet i kolumn D.

  1. Gå till Tillägg → Apps Script (eller Extensions → Apps Script om du har engelskt språk).
  2. Radera eventuell kod och klistra in detta skript:

Kodexempel: summera provresultaten

function summeraProvresultat() {

  // Hämta det aktiva kalkylbladet
  const blad = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // Skapa en rubrik i rad 1, kolumn 4
  blad.getRange(1,4).setValue("Totalt");
  // Gör cellens innehåll fetstilt
  blad.getRange(1,4).setFontWeight("bold");

  // Starta vid rad 2 (första elevraden)
  let rad = 2;

  // Loopa igenom rader så länge det finns värden i kolumn A (Elevnamn)
  while (blad.getRange(rad, 1).getValue() !== "") {
    // Hämta värden från kolumn B och C (Prov A och Prov B)
    const provA = blad.getRange(rad, 2).getValue();
    const provB = blad.getRange(rad, 3).getValue();

    // Om båda har värden (inte tomma)
    if (!isNaN(provA) && !isNaN(provB)) {
      // Summera och skriv resultat i kolumn D
      blad.getRange(rad, 4).setValue(provA + provB);
    } else {
      // Töm om data saknas
      blad.getRange(rad, 4).setValue("");
    }
    rad++; // Gå till nästa rad, rad++ ökar värdet på rad med 1
  }
}
  1. Klicka på Kör-knappen för att testa funktionen första gången.
    • Du måste auktorisera skriptet: Följ instruktionerna som visas, välj ditt Google-konto och godkänn behörigheten.
  2. Resultaten ska nu visas i kolumn D ("Totalt").

Varje programmeringsspråk har sina egna syntaxregler så om du för första gången kommer i kontakt med JavaScript, som är språket som gäller i Apps Script, så kan följande saker vara bra att hålla koll på.

  • { }: Klammrar används för kodblock i t.ex. funktioner och loopar

  • const och let: alla variabler deklareras när de skapas.

    • const \= konstant (värdet ändras inte)
    • let \= vanlig variabel (värdet kan ändras)
  • ;: Semikolon används i JavaScript för att avsluta en kodrad (inte nödvändigt men rekommenderat)

Del 3: Skapa en huvudfunktion och koppla den till en knapp

Samla funktioner i en huvudfunktion som kan köras med en knapp i Google Sheet.

1. Skapa en huvudfunktion som anropar dina funktioner

Lägg till följande funktion högst upp i skripteditorn. Se till att denna funktion inte hamnar inne i den andra funktionen utan innan samt att du sparar scriptet när du är färdig.

Kodexempel: huvudfunktionen

function huvudfunktion() {

  summeraProvresultat();

  // Här kan du lägga till fler funktionsanrop senare
  // t.ex. beraknaMedelvarde();
}

2. Skapa en knapp och koppla funktionen

  1. Gå tillbaka till ditt kalkylblad.
  2. Gå till Infoga → Teckning och skapa en knapp (t.ex. en rektangel med texten "Kör analys"). Enklast är att välja en Form, rita ut den och sedan dubbelklicka i formen för att kunna skriva text.
  3. Spara och infoga ritningen i bladet.
  4. Högerklicka på knappen och välj Tilldela skript genom att klicka på de tre punkterna.
  5. Skriv in exakt: huvudfunktion

Nu kan du klicka på knappen för att köra skriptet! Testa att ta bort kolumnen med “Totalt” och alla värden, då ser du att scriptet fungerar som det är tänkt. Lägg också till en person och resultat för att se att det fungerar som det är tänkt.

Del 4: Skapa en statistiktabell med min, max och medelvärde

Nu skall vi visa sammanställning med min-, max- och medelvärden för varje kolumn (Prov A, Prov B, Totalt).

Lägg till detta i Apps Script:

Kodexempel: beräkna min-, max- och medelvärde

function beraknaSammanstallning() {
  const blad = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // Hämta alla värden från kolumn B till D, från rad 2 till sista raden med innehåll
  const data = blad.getRange("B2:D" + blad.getLastRow()).getValues();

  // Rubriker för raderna
  const statistik = ["Min", "Max", "Medel"];
  const kolumner = [0, 1, 2]; // Index för Prov A, B och Totalt
  const resultat = [];

  // Gå igenom varje kolumn (Prov A, Prov B, Totalt) och beräkna statistik
  kolumner.forEach((i) => {
    const kolumnData = data.map(row => row[i]).filter(val => !isNaN(val));

    // Math.min(...kolumnData) använder "spread operator" (...) för att skicka alla värden som separata argument
    const min = Math.min(...kolumnData);
    const max = Math.max(...kolumnData);
    // reduce() summerar alla värden i listan, börjar på 0 och adderar varje värde
    const medel = kolumnData.reduce((a, b) => a + b, 0) / kolumnData.length;

    // Avrunda medelvärde till 1 decimal
    const avrundatMedel = Math.round(medel * 10) / 10;

    // Lägg till i resultatlistan
    resultat.push([min, max, avrundatMedel]);
  });

  // Skriv rubriker i F1 till I1
  blad.getRange("F1").setValue("Statistik");
  blad.getRange("G1").setValue("Prov A");
  blad.getRange("H1").setValue("Prov B");
  blad.getRange("I1").setValue("Totalt");

  // Fyll i rader med statistikvärden
  for (let i = 0; i < 3; i++) {
    blad.getRange(2 + i, 6).setValue(statistik[i]); // Sätt radrubrik (Min, Max, Medel)
    for (let j = 0; j < 3; j++) {
      blad.getRange(2 + i, 7 + j).setValue(resultat[j][i]);
    }
  }

  // Formatering av tabellen
  // Rubrikrad
  blad.getRange("F1:I1").setFontWeight("bold").setBackground("#dddddd");
  // Vänsterspalt
  blad.getRange("F2:F4").setFontWeight("bold").setBackground("#eeeeee");
  // Ram runt hela
  blad.getRange("F1:I4").setBorder(true, true, true, true, true, true); 
}

Se nu till att anropa funktionen beraknaSammanstallning i vår huvudfunktion.

Kodexempel: huvudfunktionen

function huvudfunktion() {

  summeraProvresultat();
  beraknaSammanstallning();

}

Spara nu och kör scriptet via knappen i kalkylarket.

Del 5: Färgmarkera elever över medelvärdet

Syfte: Markera de elever som har en totalpoäng (kolumn D) som är högre än medelvärdet.

Lägg till detta i Apps Script:

Kodexempel: färgmarkera elvever

function markeraEleverOverMedel() {

  const blad = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const totalData = blad.getRange("D2:D" + blad.getLastRow()).getValues().flat().filter(val => !isNaN(val));

  // Beräkna medelvärde
  const medel = totalData.reduce((a, b) => a + b, 0) / totalData.length;
  let rad = 2;

  while (blad.getRange(rad, 1).getValue() !== "") {
    const total = blad.getRange(rad, 4).getValue();
    if (!isNaN(total) && total > medel) {
      // Markera raden med grön bakgrund för elever över medel
      blad.getRange(rad, 1, 1, 4).setBackground("#ccffcc");
    } else {
      // Återställ bakgrundsfärg för övriga
      blad.getRange(rad, 1, 1, 4).setBackground(null);
    }
    rad++;
  }
}

Uppdatera huvudfunktionen så här:

Kodexempel: utskrifter och escapetecken

function huvudfunktion() {

  summeraProvresultat();
  beraknaSammanstallning();
  markeraEleverOverMedel();

}

Spara nu och kör scriptet via knappen i kalkylarket.

Fortsättning

Nu har vi kikat på lite olika sätt att styra vårt kalkylark via olika funktioner i Apps Scripts. Allt vi kodade går att göra med inbyggda funktioner och villkorsberoende formatering. Vill vi göra enkla saker i Google Sheets så kanske det är onödigt att programmera men om vi vill automatisera större processer så är det smidigt. Vi kan ju också hämta data från olika kalkylblad eller andra kalkylark om vi har behov av detta.

Om du vill utforska vidare så är generativ AI generellt duktig på denna typ av programmering och det kan ju kännas som fusk att låta någon annan programmera men att vara tydlig och förklara för tex. ChatGPT vad du vill att den skall programmera (pseudokod) och även att validera att det som tas fram är korrekt är typiska saker som programmerare behöver behärska.