Google’i arvutustabelist Google’i kalendrisse: sündmuste loomine tabelist ilma duplikaatide ja “päev liiga vara” veata

Töögraafik elab Google’i arvutustabelis. Meeskond elab Google’i kalendris. Keegi kirjutab juba kaks aastat igal reedel ühe teise sisse ümber, ja sel ühel nädalal, kui ta puhkusel oli, jäid kaks vahetust katmata. Meie r/GoogleAppsScript analüüsis on see üks kahest sagedasemast automatiseerimisküsimusest (valimis umbes 250 arutelu), ja ligikaudu kolmandik neist aruteludest ei saanudki töötavat vastust. Põhjus ei ole selles, et see oleks raske. Põhjus on selles, et valesti läheb neli eraldi asja, ja iga juhend kirjeldab ainult üht neist.

Allpool: skript, mida võib turvaliselt korduvalt käivitada, ajavööndi probleem, mille tõttu sündmused satuvad päev liiga vara, kuidas tekivad duplikaadid ja kuidas enamikku neist vältida, ning millal seda ei peaks üldse skriptiga tegema. Kontrollitud Google’i kalendri ja Apps Scripti dokumentatsiooni järgi 9. septembril 2026. Testige seda tabeli koopia ja testkalendriga, enne kui suunate päris kalendrile.

Mida tähendab “õigesti”

Skript tabelist kalendrisse on kasulik ainult siis, kui seda saab uuesti käivitada. See tähendab, et see ei tohi luua sama sündmust kaks korda, see peab sündmust uuendama, kui rida muutub, see ei tohi aegu vaikselt nihutada, ja see peab peatuma selge veaga, mitte poolel teel. Enamik foorumitest kopeeritud skripte ei vasta kõigile neljale kriteeriumile. Toimiv lahendus on lihtne: üks tabeli veerg hoiab kalendrisündmuse ID-d, mis kirjutatakse sündmuse loomise hetkel, mitte lõpus. ID-d pole – luuakse; ID on – uuendatakse. Lukustus ei lase kahel käivitusel kattuda, ja olekuveerg tegeleb tühistamistega.

Tabeli ülesehitus

Algus ja Lõpp peavad olema päris kuupäeva- ja ajaväärtused, mitte tekst, mis näeb välja nagu kuupäev. Veeru vormindamine kujule “Kuupäev ja kellaaeg” olemasolevat teksti ei teisenda, seega kontrollige lahtrit valemiga =ISDATE(B2): see peab tagastama TRUE. Näites on kasutatud päev.kuu.aasta järjekorda, nagu Eesti ja enamiku Euroopa lokaatide puhul; kui teie arvutustabeli lokaat on USA, kirjutage 09/14/2026 08:00. Allpool toodud skript jätab vahele read, mille kuupäevad ei ole päris kuupäevad, ja annab neist teada, mitte ei ebaõnnestu vaikselt.

Skript

Avage Extensions, Apps Script. Kui projektis on juba kood, ärge kirjutage seda üle: lisage uus skriptifail (plussmärk Files juures) ja kleepige allolev sinna. Täitke kolm konstanti üleval: arvutustabeli ID (tabeli URL-ist), lehe (vahekaardi) nimi ja meeskonnakalendri ID (Calendar settings, Integrate calendar). Skript viitab oma tabelile ja kalendrile otse, mitte “sellele, mis on aktiivne”, sest plaanitud käivitusel aktiivset tabelit ei ole.

const SPREADSHEET_ID = 'kleepige-id-tabeli-urlist';
const SHEET_NAME     = 'Roster';
const CALENDAR_ID    = 'team@yourdomain.com';

function syncSheetToCalendar() {
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(30000)) { Logger.log('Teine kaivitus veel kestab; jatame vahele.'); return; }
  try {
    const sheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(SHEET_NAME);
    const cal   = CalendarApp.getCalendarById(CALENDAR_ID);
    if (!sheet) throw new Error('Lehte "' + SHEET_NAME + '" ei leitud');
    if (!cal)   throw new Error('Kalendrit ei leitud voi puudub ligipaas: ' + CALENDAR_ID);

    const lastRow = sheet.getLastRow();
    if (lastRow < 2) return;                                  // ainult paisrida
    const rows = sheet.getRange(2, 1, lastRow - 1, 6).getValues(); // A:F, uks lugemine
    const problems = [];

    rows.forEach((row, i) => {
      const r = i + 2;                                          // rea number tabelis
      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('Rida ' + r + ': kustutamine ebaonnestus: ' + e.message); return; }
          sheet.getRange(r, 5).setValue('');                    // tuhjendab ID, rida jaab tuhistatuks
        }
        return;
      }
      if (!title) return;                                       // tuhi rida
      if (!(start instanceof Date) || !(end instanceof Date) || end <= start) {
        problems.push('Rida ' + r + ': algus/lopp ei ole kehtiv ajavahemik'); 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());            // kirjutab ID kohe
        }
      } catch (e) {
        problems.push('Rida ' + r + ': ' + e.message);
      }
    });

    if (problems.length) {
      Logger.log(problems.join('\n'));
      // Valikuline: MailApp.sendEmail('it@yourdomain.com', 'Graafiku sunkroonimise probleemid', problems.join('\n'));
    }
  } finally {
    lock.releaseLock();
  }
}

Käivitage see kõigepealt üks kord redaktorist (esitusnupp syncSheetToCalendar juures) tabeli koopia ja testkalendriga. Google küsib luba ligipääsuks arvutustabelitele ja kalendrile. Kuna käivitate skripti, mille ise just oma projekti kleepisite, on see luba teie enda koodile; lugege siiski küsitud õiguste loetelu läbi, ja kui näete kunagi hoiatust “unverified app” skripti kohta, mida te ei ole kirjutanud, peatuge ja selgitage välja, kellele see kuulub. Kontrollige kalendrit, seejärel kontrollige, kas veerg E on ID-dega täitunud. Käivitage uuesti: miski ei tohi dubleeruda. Siis testige vearada: kirjutage Alguse lahtrisse tekst ja veenduge, et käivitus jõuab lõpuni, kirjutab logisse probleemse rea ja ei puutu teisi sündmusi.

Miks ID kirjutatakse iga rea kohta eraldi

Paljud foorumiskriptid koguvad ID-d massiivi ja kirjutavad need kõik lõpus. Kui käivitus katkeb poolel teel (kvoot, õiguste viga, 6 minuti piirang), on seni loodud sündmused kalendris olemas, aga nende ID-d tabelisse ei jõua, ja järgmine käivitus loob need uuesti. Iga ID kirjutamine kohe pärast createEvent-i maksab ühe lisapäringu iga uue rea kohta ja sulgeb enamiku sellest aknast. See ei tee arvutustabelist ja kalendrist üht tehingut; viga createEvent-i ja setValue vahel on endiselt võimalik, ainult haruldane. Kui duplikaat kunagi siiski tekib, ütleb ID-veerg, millist sündmust tabel tunneb, ja teine on orvuks jäänud sündmus.

Ajavööndi viga: sündmused on päeva või tunni võrra nihkes

See on see osa, mis inimestele terve pärastlõuna maksma läheb. Mängus on kolm ajavööndi seadistust, ja kui te ei tea, mida igaüks teeb, ei lange need kokku:

1. Arvutustabeli ajavöönd. File, Settings, Time zone. Lahtritesse kirjutatud kuupäevi tõlgendatakse siin.
2. Skriptiprojekti ajavöönd. Apps Scripti redaktoris Project Settings, märkige Show appsscript.json; väli timeZone. Uute projektide vaikeväärtus on mõnikord America/New_York, olenemata sellest, kus te asute.
3. Kalendri ajavöönd. Konkreetse kalendri seadetes.

Kuupäeva ja kellaaja lahtrit hoitakse arvutustabelis ajahetkena, mida tõlgendatakse tabeli vööndis. “14.09.2026 08:00” Europe/Tallinn tabelis on 05:00 UTC. Kalender hoiab sama hetke ja näitab seda iga vaataja vööndis, seega New Yorgis asuv kolleeg näeb õigesti 01:00; see ei ole viga. Vead tekivad siis, kui skript teisendab teksti kuupäevadeks skripti vööndit kasutades, kui ajata väärtus (kesköö tabeli vööndis) luuakse ajaga sündmusena ja kuvatakse teises vööndis eelmise õhtuna, või kui kalendri enda vöönd erineb ja kogu päeva sündmused nihkuvad päeva võrra. Praktikas: seadistage arvutustabelile ja skriptiprojektile ettevõtte koduvöönd, hoidke tabelis kuupäevi päris kuupäevaväärtustena ja testige enne usaldamist üht sündmust üle suve-/talveaja piiri.

Kogu päeva sündmuste jaoks (kuupäev ajata) kasutage cal.createAllDayEvent(title, date), mitte createEvent. See võtab väärtuse kalendrikuupäeva, seega veenduge, et tabeli vööndis on väärtus tõesti see kuupäev. Kogu päeva sündmuse hilisemaks uuendamiseks on vaja setAllDayDate, mitte setTime; ülal toodud skript käsitleb ainult ajaga sündmusi.

Automaatne käivitamine

Klõpsake redaktoris Triggers (kellaikoon), Add trigger, valige syncSheetToCalendar, sündmuse allikas Time-driven, näiteks iga tund või päevane aken, näiteks 6:00 kuni 7:00 (Google plaanib tunni sees, mitte minuti täpsusega). Ärge kasutage selleks lihtsat onEdit päästikut: lihtsad päästikud ei pääse kalendrile ligi, sest need töötavad ilma loata. Installitav “on edit” päästik töötab, aga ajastatud sünkroonimine on rahulikum ja lihtsamini silutav, ja skripti lukustus teeb kattuvad käivitused ohutuks.

Kaks korrareeglit. Looge päästik kontolt, mis on olemas ka järgmisel aastal, ideaalis eraldi IT hallatavalt automatiseerimiskasutajalt, sest installitavad päästikud peatuvad, kui nende omaniku konto peatatakse, ja keegi teine neid ei näe. Ja seadistage päästiku tõrketeated väärtusele “immediately” aadressile, mida inimesed loevad, et katkisest sünkroonimisest saaks teada enne meeskonda.

Sündmuste kustutamine ja teisaldamine

Kui rida tabelist lihtsalt kustutatakse, jääb sündmus kalendrisse ja miski sellest enam ei tea, seega ärge ridu kustutage: selle asemel pange veergu F Cancelled. Skript kustutab siis sündmuse, tühjendab ID ja jätab selle rea järgmistel käivitustel vahele. Sündmuse teisaldamiseks teise kalendrisse märkige see tühistatuks, laske käivitusel see eemaldada, siis muutke rida tagasi ja suunake skript uuele kalendrile (või laiendage skripti kalendriveeruga, kui sünkroonite mitut).

Millal seda ei peaks skriptiga tegema

• Klientide broneeringud. Kasutage Google’i kalendri sisseehitatud broneerimislehti; klient valib aja ja tabelit ei ole vaja.
• Vahetuste planeerimine, kus inimesed vahetavad, taotlevad ja kinnitavad. Graafikutööriistad vahetuste vahetamise, kinnituste ja teavitustega on olemas põhjusega. Tabel pluss skript muutub selleks, mida kõik süüdistavad.
• Kõik, kus kalender on tõeallikas. Ülal toodud skript on ühesuunaline. Kui inimesed muudavad sündmusi kalendris ja ootavad, et tabel järgneb, on vaja ka vastupidist sünkroonimist, ja nüüd on teil sünkroonimisprobleem, mis on päris probleem.

Korduma kippuvad küsimused

Miks minu Google’i kalendri sündmused Google’i arvutustabelist luuakse päev liiga vara?

Tavaliselt on ajata väärtus (kesköö arvutustabeli vööndis) loodud ajaga sündmusena ja kuvatakse teises vööndis, või tekstkuupäevad on parsitud skriptiprojekti vööndis. Seadistage arvutustabelile ja skriptile ettevõtte vöönd, hoidke tabelis päris kuupäevaväärtusi ja kasutage ajata ridade jaoks createAllDayEvent.

Kuidas saavutada, et skript ei looks igal käivitusel duplikaatsündmusi?

Hoidke sündmuse ID-d veerus kohe pärast iga sündmuse loomist, uuendage järgmistel käivitustel olemasolevaid sündmusi, mitte ärge looge uusi, ja kasutage lukustust, et kaks käivitust ei saaks kattuda. Ülal toodud skript teeb kõike kolme. See ei saa teha duplikaate võimatuks iga tõrke korral, aga teeb need haruldaseks ja lihtsalt tuvastatavaks.

Kas skript saab käivituda automaatselt, kui tabel muutub?

Jah, installitava “on edit” päästikuga, aga ajastatud päästik (iga tund või päev) on prognoositavam. Lihtsad onEdit päästikud kalendrile ligi ei pääse.

Seotud artiklid

• Teie Google’i arvutustabelite automatiseering lakkas töötamast: 6 minuti piirang, õiguste vead ja millal Apps Scriptist loobuda
• Google’i kalendri broneerimislehed: laske klientidel ise aeg valida
• Hallatud IT-teenused ettevõtetele, kes kasutavad Google Workspace’i

Kui eelistate, et selle seadistab ja hooldab keegi teine, on lihtne ühe kalendri sünkroonimine, nagu ülal kirjeldatud, meie jaoks lühike töö, ja selle hooldus on osa hallatud IT-teenustest.

Automatiseering on katki ja keegi selle eest ei vastuta? Hindame töövoo, selle päästikud ja käivituskontod, seejärel määrame remondi mahu ja hooldusplaani konkreetse vastutajaga. Registreeruge tasuta 30-minutilisele auditile või helistage +371 22 30 50 90.

Hinnata automatiseeringut