Automatyczne obliczanie kosztów paliwa

W arkuszu Google możemy błyskawicznie policzyć koszty przejazdów. Wystarczy podać nazwy miejscowości i zużycie paliwa naszego auta. Kalkulator kosztów poradzi sobie z obliczeniem kilometrów, wydobędzie aktualne ceny benzyny i wszystko podsumuje.

Tak to wygląda:

Tutaj jest działający model.

Opisywany w tym wpisie automat posiłkuje się płatnym serwisem Google Distance. Na szczęście jeśli nie liczymy tysięcy tras – opłaty są znikome (0,5 centa za zapytanie, czyli jakieś 1,8 grosza, google na start daje limit 300 dolarów do wydania w ich serwisie).

Jak to zrobić?

Potrzebujemy pobrać dane z dwóch źródeł. Najpierw z Google Directions musimy otrzymać dystans, następnie sprawdzić jaka jest aktualnie cena benzyny czy innego paliwa. Na tej podstawie możemy policzyć ile będzie kosztować podróż samochodem.

Uzyskujemy klucz API

Potrzebujesz około 10 minut i karty płatniczej

API to interfejs pozwalający połączyć się z serwerami i uzyskać od nich potrzebne dane. Wiele API pozwala na anonimowe zapytanie – po prostu wpisujemy adres w przeglądarkę i uzyskujemy odpowiedź. Jednak bardziej skomplikowane i zaawansowane serwisy często są płatne i wymagają przedstawienia się. W tym przypadku służy do tego specjalny klucz dodawany do zapytania. Klucz jest niezbędny by dostać odpowiedź.

Musimy go wygenerować w odpowiednim serwisie google. Niezbędne też będzie podpięcie karty płatniczej, żeby pobierać opłaty za korzystanie z serwisu. Google na “dzień dobry” pozwala na bezpłatne korzystanie ze swoich serwisów aż do limitu 300$

Klucz pozyskujemy tutaj.

Przykładowy klucz wygląda tak:

A tu jest film pokazujący jak przejść przez wszystkie etapy:

Ważne, żeby klucz nie trafił w niepowołane ręce.

Na wszelki wypadek warto wprowadzić ograniczenia budżetu dla klucza. Ograniczenia (quota) ustawiamy tutaj.

Tworzymy zapytanie do serwisu Google Directions by uzyskać trasę i dystans.

Kiedy mamy już klucz, skupmy się na stworzeniu zapytania w arkuszu.

Zapytanie możemy wpisać nawet w przeglądarkę internetową, jednak ciężko będzie coś zrobić z odpowiedzią.

Jak chcemy wstępnie się przyjrzeć odpowiedzi, możemy adres opakować w formułę IMPORTDATA() i zawartość wyświetli się w arkuszu.

Opis wszystkich znaczników można znaleźć tutaj.

Nas interesuje tylko skromny wycinek tej odpowiedzi, czyli całkowity dystans. Jest on zagrzebany pod koniec. Możemy go pobrać z wyniku zapytania przy pomocy formuły IMPORTXML().

Zatem zamiast IMPORTDATA używamy IMPORTXML i definiujemy jaki wycinek chcemy uzyskać.

W ten sposób otrzymaliśmy dystans dla interesującej nas trasy. Jednak wszystko jest zagrzebane w adresie i mało przyjazne dla użytkownika arkusza. Pachnie flanelą i kodowaniem.

Sterujemy zapytaniem przy pomocy arkusza

Idealnie będzie jeśli wpiszemy ciurkiem wszystkie lokalizacje od początku do końca a arkusz już nam skonstruuje zapytanie i zdobędzie dystans w kilometrach.

Jak widzicie w zapytaniu są 3 kategorie lokalizacji – punkt początkowy, punkt docelowy i punkty pośrednie. Zatem jeśli chcemy by zapis w stylu:

Ustrzyki Górne – Warszawa, Plac Zbawiciela – Suwałki

był czytelny, musimy stworzyć mechanizm rozdzielający elementy niezależny od tego ile będzie punktów pośrednich.

Umówmy się też, że punkty będziemy rozdzielać myślnikiem ze spacjami po bokach (dla odmiany Bielsko-Biała piszemy z myślnikiem, ale bez spacji).

Wyciągamy punkt początkowy

Aby uzyskać pierwszy element tabeli, najłatwiej jest użyć formuły index(tabela;nr wiersza; nr kolumny).

Wyciągamy punkt docelowy

Żeby wyciągnąć punkt docelowy, musimy wyciągnąć ostatni element tabeli. W tym celu znów użyjemy formuły index, ale nie możemy wpisać po prostu numeru kolumny, bo nie wiemy ile elementów będzie. Zatem musimy najpierw policzyć elementy tabeli przy pomocy formuły counta() a następnie użyć tej liczby jako numeru kolumny.

Wyciągamy punkty pośrednie

Najpierw określamy czy punkty pośrednie w ogóle występują. Czyli, czy mamy więcej niż 2 punkty. Jeśli tak, to bierzemy całą tabelę, pozbawioną pierwszego i ostatniego elementu (bo to są punkty – początkowy i końcowy). Najłatwiej jest to zrobić przy pomocy formuły query i jej modyfikatorów.

Tu dzieje się kilka rzeczy. Najpierw biorę tabelę stworzoną przez split i ustawiam ją w pionie przy pomocy transpose. W ten sposób uzyskuję kolumnę w której są wartości Ustrzyki Górne, pod nimi Warszawa, Plac Zbawiciela, a na spodzie Suwałki. Opakowuję to w query(), które bierze zawartość kolumny, ale przesuniętą o 1 wiersz (offset 1) i przyciętą od dołu o dwa wiersze (counta liczy ile jest wierszy, a limit określa że ma być tyle wierszy minus dwa). W ten sposób pozostają tylko punkty pośrednie – w tym wypadku jeden.

Jest tu jeszcze jeden kruczek: jeśli pojawiają się punkty pośrednie, to w zapytaniu jest wyrażenie:

&waypoints=via:Warszawa…|via:coś jeszcze|via:coś jeszcze

Zatem musimy dodać te via do każdego elementu. A jeśli punktów pośrednich nie ma (czyli tabela ma 2 elementy( to w ogóle nie wyświetlać parametru &waypoints=

Stąd cały zapis związany z punktami pośrednimi ma postać:

if(counta(split(A5;" - ";0;1))>2;"&waypoints=via:"& textjoin("|via:";1; query(transpose(split(A5;" - ";0;1));"select * limit "&counta(split(A5;" - ";0;1))-2&" offset 1")))

Formuła textjoin(“|via:”….) odpowiada za dodanie |via: do każdego punktu pośredniego.

Składamy całe zapytanie

Finalnie jeśli zamiast pojedynczych nazw lokalizacji, wstawimy funkcje wydobywające je ze spisu, otrzymamy taki kod:

=iferror(importxml("https://maps.googleapis.com/maps/api/directions/ xml? language=pl "&if(counta(split(A5;" - ";0;1))>2;"&waypoints=via:"& textjoin("|via:";1; query(transpose(split(A5;" - ";0;1));"select * limit "&counta(split(A5;" - ";0;1))-2&" offset 1")))&" &origin="&index(split(A5;" - ";0;1);;1)&" &destination="&index(split(A5;" - ";0;1);;counta(split(A5;" - ";0;1)))&" &key="&K1 ;"//route/leg/distance/text"))

Pobieramy cenę paliwa

Tutaj będę posiłkował się moim wcześniejszym wpisem:

Na stronie autocentrum.pl znalazłem ładną i aktualną tabelę.

Do ściągnięcia danych używam funkcji IMPORTHTML:

=IMPORTHTML(url; zapytanie; indeks)

• url to oczywiście adres strony z której będziemy chcieli pobrać dane. Adres musimy umieścić w cudzysłowie, ewentualnie wskazać w tym miejscu, w której komórce funkcja ma szukać adresu.
• zapytanie – oznacza rodzaj obiektu jaki ma być znaleziony. Tabela lub lista. A więc w to miejsce wpisujemy „table” lub „list”.
• indeks – określamy którą tabelę lub listę ma dla nas pociągnąć funkcja. Pierwszą, drugą czy inną napotkaną licząc od góry.

Na stronie powyżej widać tylko jedną tabelę więc śmiało wpisuję 1 jako indeks. I udaje się! Dane mam już w Arkuszu!

Ponieważ chcę pobrać średnią cenę dla Polski i cenę benzyny 95, używam funkcji vlookup by wyszukać cenę benzyny (2 kolumna) dla wiersza zawierającego słowo “Polska” w pierwszej kolumnie.

Gdybym szukał ceny gazu (kolumna 6) w woj. świętokrzyskim, to zapytanie wyglądałoby następująco:

=vlookup("świętokrzyskie";importhtml("https://www.autocentrum.pl/paliwa/ceny-paliw/";"table";1);6;false)

Uzyskujemy finalny koszt przejazdów

Pozostaje podstawić w arkuszu średnie spalanie używanego auta na 100 km i podstawić do naszego dystansu i ceny paliwa.

W arkuszu wygląda to trochę bardziej skomplikowanie, bo musimy sobie poradzić jeszcze z formatem w którym podawany jest dystans. A stwarza on problemy – jeśli dystans jest większy niż 1000 km, jest podany ze spacją w środku. Stąd pojawia się formuła substitite kasująca ewentualne spacje. Jest jeszcze druga fromuła substitute, która kasuje przecinek. A ten pojawia się w dystansie, jeśli jedziemy za granicę i przekroczymy 1000 km. Formuła regexextract wydobywa liczbę z wyrażenia (zawierającego również skrót km), a formuła value przeformatowuje z formatu tekstowego na liczbowy.

Trochę tego jest…