Google Apps Script — parsowanie danych w Sheets

Czym jest Google Apps Script?

Google Apps Script to środowisko do pisania skryptów w języku JavaScript (zbliżonym do ES6), które działają bezpośrednio w ekosystemie Google — Sheets, Docs, Gmail, Calendar, Drive. Nie potrzebujesz serwera, chmury ani CI/CD. Piszesz kod, klikasz Uruchom i skrypt wykonuje się na infrastrukturze Google.

Najpopularniejsze zastosowania: automatyczne raporty w Sheets, masowe maile przez Gmail, synchronizacja danych między arkuszami a zewnętrznymi API, generowanie dokumentów PDF w Docs i wiele więcej.

Typy danych — dlaczego to tak ważne?

Google Sheets pod spodem przechowuje wartości jako stringi, liczby lub wartości logiczne — ale nie zawsze tak, jak się spodziewasz. Gdy pobierasz dane przez getValues(), możesz dostać string "123" zamiast liczby 123, albo string "true" zamiast booleana true. Działania arytmetyczne na stringach dają NaN. Porównania mogą zwrócić zaskakujące wyniki.

// Problem — dane z arkusza bez parsowania
function obliczVAT() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const wartosc = sheet.getRange('B2').getValue(); // zwraca: "1500" (string!)
  const vat = wartosc * 0.23;                      // "1500" * 0.23 = NaN
  sheet.getRange('C2').setValue(vat);              // wpisuje NaN
}

// Rozwiązanie — jawne parsowanie
function obliczVATPrawidlowo() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const wartosc = parseFloat(sheet.getRange('B2').getValue()); // -> 1500
  const vat = isNaN(wartosc) ? 0 : wartosc * 0.23;            // bezpieczne
  sheet.getRange('C2').setValue(vat.toFixed(2));
}

Parsowanie zakresu danych z arkusza

Gdy przetwarzasz wiele wierszy, pobierz od razu cały zakres zamiast odczytywać komórki po kolei — to wielokrotnie szybsze i nie blokuje limitu wywołań API:

/**
 * Pobiera dane zamówień i przelicza wartości po parsowaniu typów.
 * @returns {Object[]} Tablica sparsowanych zamówień
 */
function pobierzZamowienia() {
  const SS    = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = SS.getSheetByName('Zamówienia');
  
  // Pobierz CAŁY zakres za jednym wywołaniem
  const lastRow = sheet.getLastRow();
  const data    = sheet.getRange(2, 1, lastRow - 1, 5).getValues();
  // columns: [Data, NrZamówienia, Klient, Kwota, Opłacone]

  return data
    .filter(row => row[0] !== '')   // pomiń puste wiersze
    .map(row => ({
      data:       new Date(row[0]),               // Data  -> obiekt Date
      numer:      String(row[1]).trim(),           // Nr    -> string bez spacji
      klient:     String(row[2]).trim(),
      kwota:      parseFloat(row[3]) || 0,         // Kwota -> float, default 0
      oplacone:   row[4] === true || row[4] === 'TRUE', // -> boolean
    }));
}

function analizaZamowien() {
  const zamowienia = pobierzZamowienia();
  
  const suma    = zamowienia.reduce((acc, z) => acc + z.kwota, 0);
  const oplacone = zamowienia.filter(z => z.oplacone).length;
  
  Logger.log(`Suma: ${suma.toFixed(2)} PLN | Opłacone: ${oplacone}/${zamowienia.length}`);
}

Wyzwalacze (Triggers)

Skrypty mogą uruchamiać się automatycznie — na podstawie czasu, zdarzenia w arkuszu lub otworzenia pliku. To właśnie robi z Apps Script narzędzie do automatyzacji, a nie tylko jednorazowe makra:

/**
 * Konfiguruje wyzwalacz czasowy — skrypt uruchomi się codziennie o 6:00.
 * Wywołaj tę funkcję raz ręcznie, a wyzwalacz zostanie zachowany.
 */
function ustawWyzwalacz() {
  // Usuń stare wyzwalacze tego projektu
  ScriptApp.getProjectTriggers().forEach(t => ScriptApp.deleteTrigger(t));

  ScriptApp.newTrigger('analizaZamowien')
    .timeBased()
    .atHour(6)
    .everyDays(1)
    .create();
}

/**
 * Wyzwalacz zdarzeniowy — uruchamia się gdy zmieni się wartość komórki.
 * @param {GoogleAppsScript.Events.SheetsOnEdit} e
 */
function onEdit(e) {
  const zakres = e.range;
  
  // Reaguj tylko na zmiany w kolumnie D (Kwota) arkusza Zamówienia
  if (
    zakres.getSheet().getName() === 'Zamówienia' &&
    zakres.getColumn() === 4
  ) {
    const nowaWartosc = parseFloat(e.value);
    if (!isNaN(nowaWartosc) && nowaWartosc < 0) {
      zakres.setBackground('#ffcccc');  // czerwone tło dla ujemnej kwoty
      SpreadsheetApp.getUi().alert('Kwota nie może być ujemna!');
    }
  }
}

Integracja z zewnętrznym API

Apps Script obsługuje wywołania HTTP przez UrlFetchApp. Możesz pobierać dane z dowolnego zewnętrznego API i zapisywać je w arkuszu:

/**
 * Pobiera kurs EUR/PLN z publicznego API NBP i zapisuje w arkuszu.
 */
function pobierzKursEUR() {
  const url      = 'https://api.nbp.pl/api/exchangerates/rates/A/EUR/?format=json';
  const response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });

  if (response.getResponseCode() !== 200) {
    Logger.log('Błąd API NBP: ' + response.getContentText());
    return;
  }

  const json   = JSON.parse(response.getContentText());
  const kurs   = parseFloat(json.rates[0].mid);    // jawne parsowanie do float
  const data   = new Date(json.rates[0].effectiveDate);

  const sheet = SpreadsheetApp.getActiveSpreadsheet()
                              .getSheetByName('Kursy');
  sheet.appendRow([ data, 'EUR/PLN', kurs ]);
  Logger.log(`Kurs EUR/PLN z ${data.toLocaleDateString('pl')}: ${kurs}`);
}

Podsumowanie

Google Apps Script to potężne narzędzie niedoceniane przez wiele firm. Kluczem do pracy z nim jest jedno: zawsze parsuj dane na właściwy typ zanim je przetwarzasz. Strings z arkusza nie zachowują się jak liczby — i to jest przyczyną większości błędów w skryptach. Dobry skrypt = jawne konwersje, sprawdzone typy i obsługa błędów na każdym wyprowadzeniu HTTP.