Google Sheetsistä Google Kalenteriin: tapahtumien luonti taulukosta ilman kaksoiskappaleita ja ”päivää liian aikaisin” -virhettä

Työvuorolista elää Google Sheets -taulukossa. Tiimi elää Google Kalenterissa. Joku on jo kaksi vuotta kirjoittanut joka perjantai toisen sisällön käsin toiseen, ja sillä yhdellä viikolla, kun hän oli lomalla, kaksi vuoroa jäi ilman tekijää. r/GoogleAppsScript-analyysissämme tämä on toinen kahdesta yleisimmästä automaatiokysymyksestä (otoksessa noin 250 keskustelua), ja noin kolmannes näistä keskusteluista ei koskaan saanut toimivaa vastausta. Syy ei ole se, että asia olisi vaikea. Syy on se, että pieleen menee neljä erillistä asiaa, ja jokainen ohje kuvaa vain yhden niistä.

Alla: skripti, jonka voi turvallisesti ajaa uudelleen, aikavyöhykeongelma, jonka vuoksi tapahtumat päätyvät päivää liian aikaisin, miten kaksoiskappaleet syntyvät ja miten suurin osa niistä estetään, ja milloin tätä ei pitäisi tehdä skriptillä lainkaan. Tarkistettu Google Kalenterin ja Apps Scriptin dokumentaatiota vastaan 9. syyskuuta 2026. Testaa taulukon kopiolla ja testikalenterilla, ennen kuin osoitat skriptin oikeisiin.

Mitä ”oikein” tarkoittaa

Skripti taulukosta kalenteriin on hyödyllinen vain, jos sen voi ajaa uudelleen. Se tarkoittaa, ettei se saa luoda samaa tapahtumaa kahdesti, sen on päivitettävä tapahtuma, kun rivi muuttuu, se ei saa hiljaa siirtää aikoja, ja sen on pysähdyttävä selkeään virheeseen, ei puolitiehen. Suurin osa foorumeilta kopioiduista skripteistä ei täytä kaikkia neljää kriteeriä. Toimiva ratkaisu on yksinkertainen: yksi taulukon sarake säilyttää kalenteritapahtuman ID:n, joka kirjoitetaan tapahtuman luontihetkellä, ei lopussa. Ei ID:tä – luodaan; on ID – päivitetään. Lukitus estää kahden ajon päällekkäisyyden, ja tilasarake hoitaa peruutukset.

Taulukon rakenne

Alun ja Lopun on oltava oikeita päivämäärä- ja aika-arvoja, ei tekstiä, joka näyttää päivämäärältä. Sarakkeen muotoilu ”Päivämäärä ja aika” -muotoon ei muunna olemassa olevaa tekstiä, joten tarkista solu kaavalla =ISDATE(B2): sen pitää palauttaa TRUE. Esimerkissä käytetään päivä.kuukausi.vuosi-järjestystä, joka on käytössä Suomen ja useimpien Euroopan maiden lokaaleissa; jos laskentataulukkosi lokaali on Yhdysvallat, kirjoita 09/14/2026 08:00. Alla oleva skripti ohittaa rivit, joiden päivämäärät eivät ole oikeita päivämääriä, ja raportoi niistä sen sijaan, että epäonnistuisi hiljaa.

Skripti

Avaa Extensions, Apps Script. Jos projektissa on jo koodia, älä korvaa sitä: lisää uusi skriptitiedosto (plusmerkki Files-kohdan vierestä) ja liitä alla oleva sinne. Täytä kolme vakiota ylhäällä: laskentataulukon ID (taulukon URL-osoitteesta), välilehden nimi ja tiimikalenterin ID (Calendar settings, Integrate calendar). Skripti osoittaa taulukkonsa ja kalenterinsa suoraan, ei ”aktiivista” taulukkoa, koska ajastetussa ajossa aktiivista taulukkoa ei ole.

const SPREADSHEET_ID = 'liita-id-taulukon-urlista';
const SHEET_NAME     = 'Roster';
const CALENDAR_ID    = '[email protected]';

function syncSheetToCalendar() {
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(30000)) { Logger.log('Toinen ajo on yha kesken; ohitetaan.'); return; }
  try {
    const sheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(SHEET_NAME);
    const cal   = CalendarApp.getCalendarById(CALENDAR_ID);
    if (!sheet) throw new Error('Valilehtea "' + SHEET_NAME + '" ei loytynyt');
    if (!cal)   throw new Error('Kalenteria ei loytynyt tai ei kayttooikeutta: ' + CALENDAR_ID);

    const lastRow = sheet.getLastRow();
    if (lastRow < 2) return;                                  // vain otsikkorivi
    const rows = sheet.getRange(2, 1, lastRow - 1, 6).getValues(); // A:F, yksi luku
    const problems = [];

    rows.forEach((row, i) => {
      const r = i + 2;                                          // rivin numero taulukossa
      const [title, start, end, desc, id, status] = row;

      if (String(status).trim().toLowerCase() === 'cancelled') {
        if (id) {
          try { const ev = cal.getEventById(id); if (ev) ev.deleteEvent(); }
          catch (e) { problems.push('Rivi ' + r + ': poisto epaonnistui: ' + e.message); return; }
          sheet.getRange(r, 5).setValue('');                    // tyhjentaa ID:n, rivi jaa peruutetuksi
        }
        return;
      }
      if (!title) return;                                       // tyhja rivi
      if (!(start instanceof Date) || !(end instanceof Date) || end <= start) {
        problems.push('Rivi ' + r + ': alku/loppu ei ole kelvollinen aikavali'); return;
      }
      try {
        let ev = id ? cal.getEventById(id) : null;
        if (ev) {
          ev.setTitle(title); ev.setTime(start, end); ev.setDescription(desc || '');
        } else {
          ev = cal.createEvent(title, start, end, { description: desc || '' });
          sheet.getRange(r, 5).setValue(ev.getId());            // kirjoittaa ID:n valittomasti
        }
      } catch (e) {
        problems.push('Rivi ' + r + ': ' + e.message);
      }
    });

    if (problems.length) {
      Logger.log(problems.join('\n'));
      // Valinnainen: MailApp.sendEmail('[email protected]', 'Vuorolistan synkronointiongelmat', problems.join('\n'));
    }
  } finally {
    lock.releaseLock();
  }
}

Aja se ensin kerran editorista (toistopainike kohdan syncSheetToCalendar vierestä) taulukon kopiolla ja testikalenterilla. Google pyytää valtuuttamaan pääsyn laskentataulukoihin ja kalenteriin. Koska ajat skriptiä, jonka itse juuri liitit omaan projektiisi, tämä valtuutus koskee omaa koodiasi; lue silti pyydettyjen oikeuksien luettelo, ja jos joskus näet ”unverified app” -varoituksen skriptille, jota et ole kirjoittanut, pysähdy ja selvitä, kenelle se kuuluu. Tarkista kalenteri, tarkista sitten, että E-sarake on täyttynyt ID:illä. Aja uudelleen: mikään ei saa kahdentua. Testaa sitten virhepolku: kirjoita Alku-soluun tekstiä ja varmista, että ajo päättyy loppuun, kirjaa lokiin ongelmarivin eikä koske muihin tapahtumiin.

Miksi ID kirjoitetaan jokaiselle riville erikseen

Monet foorumiskriptit keräävät ID:t taulukkoon muistissa ja kirjoittavat ne kaikki lopussa. Jos ajo keskeytyy puolitiessä (kiintiö, oikeusvirhe, 6 minuutin raja), siihen mennessä luodut tapahtumat ovat kalenterissa, mutta niiden ID:t eivät päädy taulukkoon, ja seuraava ajo luo ne uudelleen. Jokaisen ID:n kirjoittaminen heti createEvent-kutsun jälkeen maksaa yhden lisäkutsun jokaista uutta riviä kohti ja sulkee suurimman osan tästä aukosta. Se ei tee laskentataulukosta ja kalenterista yhtä transaktiota; virhe createEvent– ja setValue-kutsujen välissä on edelleen mahdollinen, vain harvinainen. Jos kaksoiskappale joskus silti ilmestyy, ID-sarake kertoo, minkä tapahtuman taulukko tuntee, ja toinen on orpo tapahtuma.

Aikavyöhykevirhe: tapahtumat päivän tai tunnin pielessä

Tämä on se osa, joka maksaa ihmisille kokonaisen iltapäivän. Mukana on kolme aikavyöhykeasetusta, ja jos et tiedä, mitä kukin tekee, ne eivät täsmää:

1. Laskentataulukon aikavyöhyke. File, Settings, Time zone. Soluihin kirjoitetut päivämäärät tulkitaan tässä.
2. Skriptiprojektin aikavyöhyke. Apps Script -editorissa Project Settings, valitse Show appsscript.json; kenttä timeZone. Uusien projektien oletus on joskus America/New_York riippumatta siitä, missä olet.
3. Kalenterin aikavyöhyke. Kyseisen kalenterin asetuksissa.

Päivämäärä- ja aikasolu laskentataulukossa tallennetaan ajanhetkenä, joka tulkitaan taulukon vyöhykkeessä. ”14.09.2026 08:00” Europe/Helsinki-taulukossa on 05:00 UTC. Kalenteri tallentaa saman hetken ja näyttää sen kunkin katsojan vyöhykkeessä, joten kollega New Yorkissa näkee oikein 01:00; se ei ole virhe. Virheet ilmenevät, kun skripti muuntaa tekstiä päivämääriksi skriptin vyöhykettä käyttäen, kun arvo ilman aikaa (keskiyö taulukon vyöhykkeessä) luodaan ajallisena tapahtumana ja näytetään toisessa vyöhykkeessä edellisenä iltana, tai kun kalenterin oma vyöhyke poikkeaa ja koko päivän tapahtumat siirtyvät päivällä. Käytännössä: aseta laskentataulukolle ja skriptiprojektille yrityksen kotivyöhyke, pidä taulukon päivämäärät oikeina päivämääräarvoina ja testaa yksi tapahtuma kesä-/talviajan rajan yli, ennen kuin luotat siihen.

Koko päivän tapahtumiin (päivämäärä ilman aikaa) käytä cal.createAllDayEvent(title, date), ei createEvent. Se ottaa arvon kalenteripäivämäärän, joten varmista, että taulukon vyöhykkeessä arvo todella on tämä päivämäärä. Koko päivän tapahtuman myöhempi päivitys vaatii setAllDayDate, ei setTime; yllä oleva skripti käsittelee vain ajallisia tapahtumia.

Automaattinen ajo

Napsauta editorissa Triggers (kelloikoni), Add trigger, valitse syncSheetToCalendar, tapahtumalähde Time-driven, esimerkiksi tunnin välein tai päivittäinen ikkuna, kuten 6:00-7:00 (Google ajoittaa tunnin sisällä, ei minuutin tarkkuudella). Älä käytä tähän yksinkertaista onEdit-triggeriä: yksinkertaiset triggerit eivät pääse kalenteriin, koska ne toimivat ilman valtuutusta. Asennettava ”on edit” -triggeri toimii, mutta ajastettu synkronointi on rauhallisempi ja helpompi debugata, ja skriptin lukitus tekee päällekkäiset ajot harmittomiksi.

Kaksi järjestyssääntöä. Luo triggeri tililtä, joka on olemassa myös ensi vuonna, mieluiten erilliseltä IT:n hallinnoimalta automaatiokäyttäjältä, koska asennettavat triggerit pysähtyvät, kun niiden omistajan tili suljetaan, eikä kukaan muu näe niitä. Ja aseta triggerin virheilmoitukset arvoon ”immediately” osoitteeseen, jota ihmiset lukevat, jotta rikkoutuneesta synkronoinnista kuullaan ennen tiimiä.

Tapahtumien poistaminen ja siirtäminen

Jos rivi vain poistetaan taulukosta, tapahtuma jää kalenteriin eikä mikään enää tiedä siitä, joten älä poista rivejä: aseta sen sijaan F-sarakkeeseen Cancelled. Skripti poistaa silloin tapahtuman, tyhjentää ID:n ja ohittaa tämän rivin seuraavissa ajoissa. Tapahtuman siirtämiseksi toiseen kalenteriin merkitse se peruutetuksi, anna ajon poistaa se, muuta sitten rivi takaisin ja osoita skripti uuteen kalenteriin (tai laajenna skriptiä kalenterisarakkeella, jos synkronoit useita).

Milloin tätä ei pitäisi tehdä skriptillä

• Asiakasvaraukset. Käytä Google Kalenterin sisäänrakennettuja varaussivuja; asiakas valitsee ajan, eikä taulukkoa tarvita.
• Vuorosuunnittelu, jossa ihmiset vaihtavat, pyytävät ja hyväksyvät. Vuorosuunnittelutyökalut vuoronvaihdoilla, hyväksynnöillä ja ilmoituksilla ovat olemassa syystä. Taulukko plus skripti muuttuu siksi, mitä kaikki syyttävät.
• Kaikki, missä kalenteri on totuuden lähde. Yllä oleva skripti on yksisuuntainen. Jos ihmiset muokkaavat tapahtumia kalenterissa ja odottavat taulukon seuraavan, tarvitaan myös synkronointi toiseen suuntaan, ja nyt sinulla on synkronointiongelma, joka on oikea ongelma.

Usein kysytyt kysymykset

Miksi Google Sheetsistä luodut Google Kalenterin tapahtumani syntyvät päivää liian aikaisin?

Yleensä arvo ilman aikaa (keskiyö laskentataulukon vyöhykkeessä) on luotu ajallisena tapahtumana ja näytetään toisessa vyöhykkeessä, tai tekstipäivämäärät on tulkittu skriptiprojektin vyöhykkeessä. Aseta laskentataulukolle ja skriptille yrityksen vyöhyke, pidä taulukossa oikeat päivämääräarvot ja käytä riveille ilman aikaa createAllDayEvent.

Miten estän skriptiä luomasta kaksoiskappaleita joka ajossa?

Tallenna tapahtuman ID sarakkeeseen heti kunkin tapahtuman luonnin jälkeen, päivitä seuraavissa ajoissa olemassa olevat tapahtumat sen sijaan, että loisit uusia, ja käytä lukitusta, jotta kaksi ajoa eivät voi mennä päällekkäin. Yllä oleva skripti tekee kaikki kolme. Se ei voi tehdä kaksoiskappaleita mahdottomiksi jokaisessa virhetilanteessa, mutta tekee ne harvinaisiksi ja helposti tunnistettaviksi.

Voiko skripti käynnistyä automaattisesti, kun taulukko muuttuu?

Kyllä, asennettavalla ”on edit” -triggerillä, mutta ajastettu triggeri (tunnin tai päivän välein) on ennustettavampi. Yksinkertaiset onEdit-triggerit eivät pääse kalenteriin.

Aiheeseen liittyvät artikkelit

• Google Sheets -automaatiosi lakkasi toimimasta: 6 minuutin raja, oikeusvirheet ja milloin luopua Apps Scriptistä
• Google Kalenterin varaussivut: anna asiakkaiden valita aika itse
• Hallinnoidut IT-palvelut yrityksille, jotka käyttävät Google Workspacea

Jos haluat mieluummin, että joku muu pystyttää ja ylläpitää tämän, yllä kuvatun kaltainen yksinkertainen yhden kalenterin synkronointi on meille lyhyt työ, ja sen ylläpito on osa hallinnoituja IT-palveluja.

Automaatio rikki, eikä kukaan vastaa siitä? Arvioimme työnkulun, sen triggerit ja ajotilit, ja sen jälkeen määritämme korjauksen laajuuden ja ylläpitosuunnitelman nimetyllä vastuuhenkilöllä. Varaa maksuton 30 minuutin auditointi tai soita +371 22 30 50 90.

Arvioi automaatio