Uspelo mi je ustvariti grdo pošast. Pošast, ki deluje in jo imam rad. Pred kratkim sem namreč želel nekoliko olajšati pregled nad ponudbo malic v restavracijah okoli moje službe. Vse kar sem vedel je, da rabim orodje, s katerim bom lahko postrgal podatke s treh spletnih strani (v okolici so tri restavracije in vsaka od njih ima na svoji spletni strani objavljeno dnevno ponudbo). Želel sem tudi, da bi bila zadeva dostopna za vse sodelavce preko spleta. To pomeni, da je treba poiskat rešitev, ki bo živela bodisi v oblaku ali pa na mojem serverju, kjer gostuje tale blog. Po nekaj minutah googlanja sem naletel na podatek, da Google Sheets ponuja možnost uvoza podatkov s spletnih strani.
Izkazalo se je, da potrebujem dve funkciji:
=IMPORTHTML(url, query, index) =IMPORTXML(url, xpath_query)
IMPORTHTML zna uvažati zgolj elemente <table> in <list>, medtem ko IMPORTXML omogoča nekoliko več svobode, ker podpira naslavljanje elementov preko XPath metode. Super, imam orodje, s katerim lahko uvozim podatke s spletne strani v tabelo. Zmaga. Gremo pogledati, kako stvar deluje na konkretnem primeru.
Za začetek bom postrgal meni s spletne strani restavracije Svetilnik. S pomočjo orodja Inspect, ki ga ponuja brskalnik Chrome, sem poiskal HTML element, v katerem se nahaja dnevni meni.
Perfect, meni je znotraj tebele, kar pomeni da lahko uporabimo naslednjo funkcijo:
=IMPORTHTML("http://www.svetilnik.eu/dnevna-ponudba","table",1)
Poglejmo si parametre. Prvi parameter je URL, drugi parameter pove, da iščemo tabelo in tretji je samo indeks tebele. Ker gre za prvo (in edino) tabelo na strani, pokličemo tabelo št. 1.
Super, sedaj imamo podate, ki pa so vsi zapisani v isti celici, ker je meni na strani res slabo kodiran. Jaz pa bi rad, da imam vsako postavko v svoji celici. Zato je treba fuknciji dodati še eno.
=SPLIT(IMPORTHTML("http://www.svetilnik.eu/dnevna-ponudba","table",1),CHAR(10),TRUE,TRUE)
Funkcija SPLIT razdeli podatke na podlagi znaka, ki ga določimo. Poglejmo parametre. Prvi parameter poda vir podatkov, ki je v našem primeru prejšjna IMPORTHTML funkcija. Drugi parameter določi znak po katerem bomo delili podatke. Na srečo so vrstice med seboj deljene s HTML elementom <br>. Google Sheets prelom vrstnice definira z znakom 10. Pokličemo ga s funkcijo CHAR(10). Ostaneta še dva parametra TRUE. Prvi pove funkciji, da naj definira novo celico za vsak prelom vrstice, medtem ko drugi funkciji pove, da ignorira prazne vrstie (s tem se izognemo praznim clicam, ko je več prelomov vrstic).
Super, sedaj imamo vse postavke menija ločene po celicah, a so te v isti vrstici. Ni OK – zato moramo sedaj vse podatke pretvoriti še s funkcijo TRANSPOSE. Ker tega ne moremo naredi mimogrede, rabimo nov list z urejenimi podatki. Tam bomo poklicali vrstico s surovimi podatki:
=TRANSPOSE(data!A3:AM3)
Funkcija ima samo en parameter, ki kliče vrstico iz lista z imenom data – specifično celice od A3 do AM3 (razpon je večji, kot je količina podatkov – za vsak slučaj, če se kdaj pojavi daljši meni). Sedaj imamo vse podatke ločene po vrsticah v stolpcu. Misija končana. Ali pač?
Funkcija namreč samo enkrat postrga podatke in se potem ne izvaja več. Jaz pa bi rad, da se v tabeli podatki osvežujejo dnevno, da imamo potem na enem mestu menije. Za to bomo potrebovali skripto. V odprtem Google Sheets dokumentu v meniju klikni na Tools > Script editor. Skripta, ki sem jo uporabil je sledeča:
function RefreshImports() {
var lock = LockService.getScriptLock();
if (!lock.tryLock(5000)) return; // Wait up to 5s for previous refresh to end.
// At this point, we are holding the lock.
var id = "1zZA7HGd8ythoqzBNvMebLVXTJTWIy0InPUtknCv_KVU";
var ss = SpreadsheetApp.openById(id);
var sheets = ss.getSheets();
for (var sheetNum=0; sheetNum<sheets.length; sheetNum++) {
var sheet = sheets[sheetNum];
var dataRange = sheet.getDataRange();
var formulas = dataRange.getFormulas();
var tempFormulas = [];
for (var row=0; row<formulas.length; row++) {
for (col=0; col<formulas[0].length; col++) {
// Blank all formulas containing any "import" function
// See https://regex101.com/r/bE7fJ6/2
var re = /.*[^a-z0-9]import(?:xml|data|feed|html|range)\(.*/gi;
if (formulas[row][col].search(re) !== -1 ) {
tempFormulas.push({row:row+1,
col:col+1,
formula:formulas[row][col]});
sheet.getRange(row+1, col+1).setFormula("");
}
}
}
// After a pause, replace the import functions
Utilities.sleep(5000);
for (var i=0; i<tempFormulas.length; i++) {
var cell = tempFormulas[i];
sheet.getRange( cell.row, cell.col ).setFormula(cell.formula)
}
// Done refresh; release the lock.
lock.releaseLock();
}
}
Funkcije nisem pisal sam, ampak je pobrana s StackOverflow. Vse kar funkcija naredi, je to da periodično preišče dokument za vse funkcije tipa IMPORT, jih kopira, zbriše in ponovno prilepi v celico ter požene. Da bo zadeva delovala v tvojem dokumntu, je potrebno spremeniti samo to vrstico:
var id = "1zZA7HGd8ythoqzBNvMebLVXTJTWIy0InPUtknCv_KVU";
ID svojega dokumenta pa najdeš v URLju:
Na podoben način lahko potem bodisi s pomočjo IMPORTHTML ali pa IMPORTXML funkcij postrgamo podatke tudi z drugih strani. Takole zgleda funkcija za restavracijo Petrinka:
=IMPORTHTML("https://www.ostarijapetrinka.si/danes-v-ponudbi/","list",3)
Ker je meni na spletni strani restavracije Petrinka v obliki seznama lahko spet uporabimo IMPORTHTML, izberemo parameter list in index 3, ker je to tretji seznam na strani (ni ti treba it po kodi štet, ampak samo začni z 1 in potem dodajaj +1, dokler ne prideš do pravega indeksa s podatki o meniju.
Na tak način lahko potem prideš do menijev na enem mestu. Vem, naredil sem pošast in obstaja nebroj bolj ustreznih rešitev. Ampak jaz sem imel problem, ki sem ga žeel rešiti in tole mi je bilo najbolj pri roki tisti trenutek. Zadeva dela in če dela, potem ni tako neumna, kajne?