Mikä on GAS (Google Apps Script)?
Google Sheetsin automatisointi – johdanto

GAS on Googlen tapa suorittaa ohjelmia. Se käyttää samaa syntaksia kuin JavaScript ja ohjaa Google Sheetsin lisäksi Gmailia, Google Kalenteria, Google Drivea ja Google Lomakkeita.

Tarvitset vain Google-tilin. Se on ilmainen, eikä mitään tarvitse asentaa: avaa taulukko selaimessa ja ala kirjoittaa. Ohjelma toimii Googlen palvelimilla, ei omalla tietokoneellasi.

Google Sheetsissa on ”makroja”, jotka tallentavat toimintosi, jotta voit toistaa ne niin monta kertaa kuin haluat. Aloita tallennus valitsemalla ”Laajennukset” → ”Makrot” → ”Tallenna makro”, valitse ja kirjoita soluihin, niin toimintosi tallentuvat GAS-koodiksi.

/** @OnlyCurrentDoc */

function myFunction() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('B2').activate();
  spreadsheet.getCurrentCell().setValue('test');
  spreadsheet.getActiveRangeList().setFontWeight('bold');
};

Makro on siis GAS:ia. Vaikka antaisit makrolle nimen kuten ”Lihavointitesti”, koodin funktion nimi on myFunction. Valikossa näkyvä nimi ja funktion nimi tallennetaan erikseen.

Tallennuksen aikana valitset alareunan palkista ”Käytä absoluuttisia viittauksia” tai ”Käytä suhteellisia viittauksia”. Absoluuttiset viittaukset tallentuvat soluosoitteina kuten B2, joten makro kirjoittaa aina samaan paikkaan valitusta solusta riippumatta. Suhteelliset tallentuvat muodossa ”yksi rivi nykyisen solun alapuolella” ja lähtevät valitusta solusta.

Pelkkä tallennus ei riitä taulukoihin, joiden pituus vaihtelee, eikä vaiheisiin, jotka riippuvat ehdoista. Siitä eteenpäin luet tallennettua koodia ja täydennät sitä.

• Poistaa tyhjät rivit liitetyistä tiedoista ja muotoilla otsikot
• Katsoa summia tai päivämääriä ja kirjoittaa sarakkeeseen ”Iso” tai ”Myöhässä”
• Lisätä taulukkoon oma valikko ja ajaa tehtäviä kuin painikkeella
• Tehdä omia funktioita soluissa käytettäviksi (mukautetut funktiot)
• Lähettää sähköposteja Gmaililla taulukon perusteella
• Ajaa tehtävä kiinteään aikaan, esimerkiksi joka aamu kello 8 (käynnistimet)
• Kopioida Google Lomakkeiden vastaukset toiselle taulukolle heti niiden saapuessa

Käsin jokainen näistä vie muutamasta minuutista kymmeniin minuutteihin. Kun koodi on kerran kirjoitettu, riittää valikkokomennon napsautus – tai se toimii kokonaan itsestään.

1. Avaa taulukko ja valitse ”Laajennukset” → ”Apps Script”
2. Editori avautuu uuteen välilehteen: vasemmalla ”Koodi.gs”, oikealla function myFunction() { }
3. Kirjoita koodi { }-merkkien sisään ja tallenna (Ctrl+S, Macissa ⌘+S)
4. Napsauta yläreunasta ”▷ Suorita”. Tulos näkyy alhaalla ”Suoritusloki”-kohdassa

function myFunction() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const value = sheet.getRange('A1').getValue();
  Logger.log(value);
  sheet.getRange('B1').setValue('Tarkistettu');
}

Logger.log kirjoittaa arvon suorituslokiin. Luvut näkyvät desimaalipisteen kanssa, esimerkiksi 120.0 (console.log näyttää 120). Jos jokin ei toimi odotetusti, tarkista ensin arvot lokista.

Kun suoritat koodisi ensimmäistä kertaa, näkymä kysyy, saako skripti lukea ja muokata taulukkoasi. Moni jää tähän, joten käydään se läpi vaihe vaiheelta.

1. ”Valtuutus vaaditaan” → Tarkista käyttöoikeudet / OK
2. Valitse tili
3. ”Google ei ole vahvistanut tätä sovellusta” → vasemmasta alakulmasta ”Lisäasetukset”
4. ”Siirry kohteeseen (projektin nimi) (vaarallinen)”
5. Tarkista, mitä sallit, ja napsauta ”Salli” (jos valittavia kohtia on, valitse ne ja napsauta ”Jatka”)

”Google ei ole vahvistanut tätä sovellusta” on varoitus skripteille, joita Google ei ole tarkastanut. Itse kirjoitettuja skriptejä ja tallennettuja makroja ei ole tarkastettu, joten tämä näkymä tulee aina. Jos olet tehnyt sen itse, voit jatkaa huoletta.

Jos näkymä taas tulee taulukossa, jonka olet saanut tuntemattomalta, valitse ”Takaisin turvaan”. Sallimisen jälkeen skripti voi käsitellä taulukoitasi ja sähköpostiasi tililläsi.

Jos kirjoitat koodin alkuun /** @OnlyCurrentDoc */, skripti saa koskea vain ”tähän taulukkoon” ja pyydetyt oikeudet ovat suppeammat. Tallennetuissa makroissa se on alusta asti.

Voit käydä samat vaiheet läpi harjoitusten tehtävässä 1.

Tässä on se, mitä taulukon kanssa käytetään useimmin. Rivien ja sarakkeiden numerot alkavat 1:stä, getValuesilla luetun taulukon indeksit 0:sta.

function judgeTargets() {
  const sheet = SpreadsheetApp.getActiveSheet();
  for (let row = 2; row <= sheet.getLastRow(); row++) {
    const sales = sheet.getRange(row, 2).getValue();
    const target = sheet.getRange(row, 3).getValue();
    if (sales >= target) {
      sheet.getRange(row, 4).setValue('Saavutettu');
    } else {
      sheet.getRange(row, 4).setValue('Ei saavutettu');
    }
  }
}

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Kuukauden päättäminen')
    .addItem('Merkitse isot kaupat', 'markLarge')
    .addToUi();
}

Funktio nimeltä onOpen käynnistyy automaattisesti, kun taulukko avataan. Jos suoritat sen editorin ”▷ Suorita”-painikkeella, tulee virhe Cannot call SpreadsheetApp.getUi() from this context., joten tallenna ja avaa taulukko uudelleen tarkistaaksesi.

GAS:sta haetaan useimmin sähköpostin lähettämistä ja käynnistimiä.

function sendReminders() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const rows = sheet.getRange(2, 1, sheet.getLastRow() - 1, 2).getValues();
  rows.forEach(([name, email]) => {
    MailApp.sendEmail(email, 'Muistutus määräajasta', name + ',\n\nmääräaika on tänä perjantaina.');
  });
}

Lähetyksillä on raja: ilmainen Google-tili voi lähettää enintään 100 sähköpostia päivässä (Google Workspace -tilit enemmän). Yksi suoritus saa kestää enintään 6 minuuttia; sen jälkeen se pysähtyy ilmoitukseen ”Exceeded maximum execution time”.

Ajastaaksesi napsauta editorin vasemmasta reunasta ”Käynnistimet” (kellokuvake) → ”Lisää käynnistin” ja valitse funktio sekä ”Aikaperusteinen”. Voit asettaa kellonajan joka päivä, viikko tai kuukausi.

Sähköpostit lähtevät oikeasti, vaikka vain kokeilisit. Testaa ensin taulukolla, jossa on vain oma osoitteesi.

Ajatukset ovat molemmissa samat: lue soluja, kirjoita, toista ja erottele ehtojen mukaan. Kun osaat toisen, osaat lukea toistakin. Excelin makroja käsitellään Excelin makrojen johdannossa.

Sivuston makro- ja GAS-simulaatiossa harjoittelet suorittamalla oikeaa koodia virtuaalisessa taulukossa ja Apps Script -editorissa. Täydennät koodin aukkoja, ja puhelimella valitset jokaisen aukon neljästä vaihtoehdosta.

1. Luku 1 Makrojen tallentaminen ja suorittaminen

   Suorita tallennettu makro ja tallenna oma. Absoluuttiset ja suhteelliset viittaukset, tallennetun koodin lukeminen ja korjaaminen.

2. Luku 2 Lue, toista, erottele

   Lue soluja getValuella, kirjoita setValuella ja tarkista arvot suorituslokista. Käy kaikki rivit läpi forilla ja erottele ifillä.

3. Luku 3 Tapaustutkimus: kuukauden myyntitaulukon siistiminen

   Poista tyhjät rivit, merkitse isot kaupat kerralla ja aja se omasta valikosta. Lopuksi oma funktio soluihin.

Aloita simulaatio (13 tehtävää) →

Hyvä alku on ”Tallenna makro” omassa taulukossa ja syntyneen koodin lukeminen. Silloin näkee helpommin, mitä kukin komento tekee.

Myös generatiivinen tekoäly kirjoittaa koodia. Mutta jotta voit arvioida, onko se oikein ja mitä pitää korjata, sinun pitää osata lukea vähintään tämän artikkelin ”yleinen koodi”.