Rozdział 20 · 20.1
Automatyczne przenoszenie wiersza do arkusza archiwum, gdy status to Klaar
Lista zamówień, zadań lub faktur rośnie z tygodnia na tydzień. Tego, co zostało zakończone, nie chcą Państwo usuwać, ale też nie chcą Państwo widzieć tego wśród bieżącej pracy. W Excelu wiele osób robiło to za pomocą makra VBA lub skryptu w Google Sheets: wszystko ze statusem Klaar trafia do arkusza Archief. W Euro-Office można zrobić to samo za pomocą makra w JavaScript. Jedno kliknięcie i lista znów jest przejrzysta.
Jako przykład posłuży nam lista zamówień kwiaciarni Bloemisterij Het Zonnetje, klienta WebOké. W arkuszu Orders w kolumnach od A do E znajdują się numer zamówienia, klient, zamówienie, kwota i status. Istnieje też drugi arkusz Archief z takimi samymi nagłówkami kolumn.

Najpierw: okno Makra
Makra są w WebOké Nextcloud włączone; nie trzeba niczego aktywować. Znajdą je Państwo w arkuszu kalkulacyjnym i w dokumencie tekstowym na karcie Widok, pod przyciskiem Makra. Okno ma po lewej stronie dwie listy, Makra i Funkcje niestandardowe (zob. Formuły Excela z LET lub LAMBDA: co zrobić zamiast tego w Euro-Office), a po prawej kod. Nad kodem znajdują się przyciski Uruchom (trójkącik), Debugowanie, Kopiuj, Zmień nazwę, Usuń oraz pole wyboru Autostart.
Makro należy do pliku, w którym zostało utworzone. Jest zapisywane w pliku xlsx i jest przenoszone razem z nim, gdy plik zostanie skopiowany lub udostępniony.
Krok 1: arkusz Archief
- Prosimy kliknąć na dole plus, aby dodać nowy arkusz, i nadać mu dokładnie nazwę Archief (prawy przycisk myszy na karcie arkusza, Zmień nazwę).
- Prosimy skopiować wiersz nagłówka z arkusza Orders do wiersza 1 arkusza Archief. Makro zaczyna wypełniać dane od wiersza 2.
Krok 2: tworzenie makra
- Prosimy wrócić do arkusza Orders. To ważne: makro działa na arkuszu, który jest aktualnie otwarty.
- Na karcie Widok prosimy kliknąć Makra.
- Obok listy Makra prosimy kliknąć plus (1). Pojawi się puste makro z kilkoma wierszami kodu.
- Prosimy kliknąć Zmień nazwę i nazwać makro Naar archief (2).
- Prosimy usunąć kod po prawej stronie i wkleić w jego miejsce poniższy kod:
(function () {
// Wiersze ze statusem Klaar przenieść do arkusza Archief.
const bron = Api.GetActiveSheet();
const doel = Api.GetSheet("Archief");
if (!doel) { return; }
// Pierwszy pusty wiersz w arkuszu Archief (kolumna A).
let doelRij = 2;
while (doel.GetRange("A" + doelRij).GetValue() !== "") { doelRij++; }
// 1. Od góry do dołu: skopiować zakończone wiersze we właściwej kolejności.
const laatste = bron.GetUsedRange().GetRowsCount();
const weg = [];
for (let r = 2; r <= laatste; r++) {
const status = String(bron.GetRange("E" + r).GetValue()).trim().toLowerCase();
if (status === "klaar") {
bron.GetRange("A" + r + ":E" + r).Copy(doel.GetRange("A" + doelRij));
doelRij++;
weg.push(r);
}
}
// 2. Usuwać od dołu do góry, aby numery wierszy się zgadzały.
for (let i = weg.length - 1; i >= 0; i--) {
bron.GetRows(weg[i]).Delete("up");
}
})();

Krok 3: uruchomienie i zapisanie
- Prosimy kliknąć Uruchom, czyli trójkącik (3). Makro od razu wykonuje swoje zadanie; okno pozostaje otwarte.
- Prosimy kliknąć Zapisz. W ten sposób makro zostanie zapisane w pliku. Jeśli klikną Państwo Anuluj, makro zniknie, ale zmiany, które już wprowadziło, pozostaną.


Jak działa ten kod
- Api.GetActiveSheet() to aktualnie otwarty arkusz, a Api.GetSheet("Archief") to archiwum. Jeśli taki arkusz nie istnieje, makro kończy działanie, nic nie zmieniając.
- Wiersz z while wyszukuje pierwszy pusty wiersz w arkuszu Archief. Dzięki temu mogą Państwo uruchamiać makro co tydzień: nowe zakończone zamówienia trafiają pod wcześniejsze.
- GetUsedRange().GetRowsCount() liczy, ile wierszy jest wypełnionych.
- Dla każdego wiersza odczytywany jest status. trim() usuwa spacje, a toLowerCase() zamienia litery na małe. Dzięki temu uwzględniane są też "klaar" oraz "Klaar " ze spacją, jak w naszym przykładzie przy B-106 i B-108.
- Copy kopiuje wiersz razem z formatowaniem, na przykład z formatem kwoty w euro.
- Wiersze są usuwane dopiero na końcu, od dołu do góry. Gdyby najpierw usunąć wiersz 2, wszystko przesunęłoby się o jedno miejsce i jeden wiersz zostałby pominięty.
Dostosowanie do własnej listy
- Inna kolumna statusu: prosimy zastąpić "E" w GetRange("E" + r) literą własnej kolumny.
- Więcej kolumn: jeśli lista sięga do kolumny H, prosimy zastąpić ":E" przez ":H".
- Inne słowo: prosimy zastąpić "klaar" na przykład słowem "zapłacone" lub "zakończone", zawsze małymi literami.
- Inne archiwum: prosimy zastąpić "Archief" nazwą własnego arkusza, dokładnie tak, jak jest zapisana na karcie arkusza.
Wskazówka: warto umieścić w arkuszu przycisk uruchamiający to makro, wtedy współpracownicy nie będą musieli otwierać okna Makra. Zob. Przycisk w arkuszu kalkulacyjnym uruchamiający makro.
Pułapki
- Najpierw prosimy przetestować na kopii. Makro wykonuje bardzo wiele zmian naraz. W naszym teście Cofnij nie wycofało działania makra, nawet po dwukrotnym kliknięciu. Prosimy wcześniej wykonać kopię pliku lub skorzystać z historii wersji, aby wrócić do poprzedniego stanu (zob. Historia wersji: przywracanie starszej wersji).
- Otwarty niewłaściwy arkusz? Jeśli uruchomią Państwo makro, gdy otwarty jest arkusz Archief, będzie ono szukać statusu Klaar w archiwum. Wtedy nic się nie stanie albo stanie się coś dziwnego. Prosimy zawsze najpierw przejść do arkusza Orders.
- Tabela nie zaczyna się w wierszu 1? GetRowsCount liczy od pierwszego wypełnionego wiersza. Jeśli nad tabelą znajduje się tytuł i pusty wiersz, prosimy dodać te wiersze do wartości laatste.
- Formuły w wierszu? Copy kopiuje również formuły. Jeśli formuła odwołuje się do innego wiersza w arkuszu Orders, w archiwum może już nie dawać poprawnego wyniku.
- Wspólna praca w pliku? Makro powinna uruchamiać tylko jedna osoba. Zob. także Bezpieczne korzystanie z makr: ustawienia makr, autostart u współpracowników i ponowne użycie.
Czego nie da się zrobić
- Automatyczne przeniesienie, gdy tylko ktoś wybierze Klaar: w zasadzie jest to możliwe za pomocą makra reagującego na zmiany (zob. Automatyczne wstawianie daty i godziny po zmianie komórki), ale usuwanie wiersza, gdy ktoś jeszcze pisze, jest niestabilne i podatne na błędy. Przycisk jest bardziej niezawodny.
- Przeniesienie do innego pliku: makro działa wyłącznie we własnym pliku. Jeśli chcą Państwo mieć archiwum dla każdego roku w osobnym pliku, prosimy na koniec roku ręcznie skopiować arkusz Archief.
- Nagrywanie zamiast programowania: Nagraj makro nie rejestruje kopiowania i wklejania. To makro trzeba więc rzeczywiście wkleić jako kod. Więcej o nagrywaniu w Samodzielna konwersja makra VBA na JavaScript: tabela odpowiedników.
