Hello Coco,
I'm wondering if you or anyone else would be able to let me know how to access the collections of items found in JSON format? I have code that works, but I cant dive down into individual items.. I'm pretty noob at describing syntax at this point so plz forgive me if I"m using the wrong terms.
here is my working code using your JXON function:
Code: Select all
FileRead, JSONdata, %WBdir%\URLscores.json
readJSON := Jxon_Load(JSONdata) ; load new
Xl.ActiveSheet.Range("B" URLcell).Value := (readJSON.categories.performance.score * 100)
Xl.ActiveSheet.Range("C" URLcell).Value := (readJSON.categories.pwa.score * 100)
Xl.ActiveSheet.Range("D" URLcell).Value := (readJSON.categories.accessibility.score * 100)
Xl.ActiveSheet.Range("E" URLcell).Value := (readJSON["categories","best-practices","score"] * 100)
Xl.ActiveSheet.Range("F" URLcell).Value := (readJSON.categories.seo.score * 100)
works great, but say i wanted to grab something like this:
Xl.ActiveSheet.Range("F" URLcell).Value := readJSON.categories.performance.details.items[5].url
I get nothing back as a return value. I'm sure it's just my syntax, as I don't quite fully understand your documentation above and have been working off some examples I found around the AHK forums.. can you point me in the right direction?
Also, another issue I found was that if the JSON object had a "-" in the middle of it's name, I had to use your alternate syntax - is there any other way around this? I think it's an AHK thing, but I can't seem to find anything about escaping the "-" character in a variable. I've tried using " ` and {} next to it, but no luck.
Thank you so much for your libraries, they have been mighty helpful!! I'm using it to scrape Google Lighthouse data through the API into an excel spreadsheet, here is the full code (WIP) if you are interested. I'm still working out a few bugs, but has been super efficient using your libs (:
Code: Select all
#SingleInstance, force ; Doesn't allow the script to run multiple instances at once.
#NoEnv ; Recommended for performance and compatibility with future AutoHotkey releases.
;~ #Warn ; Enable warnings to assist with detecting common errors.
SendMode Input ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir% ; Ensures a consistent starting directory.
SetTitleMatchMode, 2
SetKeyDelay, 10, 10
DetectHiddenWindows, On
CoordMode, Screen
;[][][][][][][][][][][][][][][][] CONNECT TO EXCEL [][][][][][][][][][][][][][][][];
Xl := ComObjCreate("Excel.Application") ; creates Excel handle
;[][][][][][][][][][][][][][][][] SELECT URL FILE & WRITE DIRECTORY [][][][][][][][][][][][][][][][];
MsgBox, 0x1030, Select URL file, Please select the excel file that contains the URLs you need Lighthouse Performance scores from.`n`nThe URLs must be listed downwards starting from cell A2.
fileToOpen := Xl.GetOpenFilename(FileFilter := "Excel Files [*.xls* or *.csv], *.xls*; *.csv", FilterIndex := 1, Title := "OPEN URL FILE", MultiSelect := False)
if fileToOpen = 0
{
SoundPlay *-1
MsgBox, 0x1010, No FIle Selected,No file was selected.`n`nLighthouse Score Scraper will close.
ExitApp
}
else If fileToOpen <> 0
Xl.Workbooks.Open(FileName := fileToOpen, UpdateLinks := 0)
WinActivate, ahk_exe EXCEL.EXE
MsgBox, 0x1034, Run in Background?, Would you like to be able to watch the data load into the spreadsheet?`n`nIf yes`, make sure that you don't edit that spreadsheet while the data is loading or URLs may fail to load into it.`n`nIf no`, your Excel file will load data in the background.
IfMsgBox Yes
{
Xl.Visible := True
}
else
{
}
;[][][][][][][][][][][][][][][][] GRAB OPENED FILE'S DIRECTORY [][][][][][][][][][][][][][][][];
SplitPath, fileToOpen, WBFileName, WBdir, WBext, WBnameNoExt, WBdrive
saveToOpenPath := SubStr(WBdir, 4)
#ofURLs := Xl.Workbooks(WBnameNoExt).ActiveSheet.Range("A2:A" Xl.ActiveSheet.UsedRange.Rows.Count).Count ; the range of used cells
;[][][][][][][][][][][][][][][][] SET TEMPLATE IN EXCEL FILE [][][][][][][][][][][][][][][][];
Xl.ActiveSheet.Range("A1").Value := "URL"
Xl.ActiveSheet.Range("B1").Value := "Performance"
Xl.ActiveSheet.Range("C1").Value := "PWA"
Xl.ActiveSheet.Range("D1").Value := "Accessibility"
Xl.ActiveSheet.Range("E1").Value := "Best Practices"
Xl.ActiveSheet.Range("F1").Value := "SEO"
Xl.ActiveSheet.Range("G1").Value := "Timestamp"
SoundPlay *-1
MsgBox, 0x1030, Pulling Data thru API, Pulling data from Lighthouse API. Please wait..., 3
;[][][][][][][][][][][][][][][][] LIGHTHOUSE API CALL - WRITE 2 .JSON FILE & PARSE SCORES [][][][][][][][][][][][][][][][];
MainLoop:
Loop, %#ofURLs%
{
;/][//][//][//][//][//][//][//][ Reset Score Variable Values ][//][//][//][//][//][//][//][/;
RepeatScrape:
readJSON.categories.performance.score :=
readJSON.categories.pwa.score :=
readJSON.categories.accessibility.score :=
readJSON["categories","best-practices","score"] :=
readJSON.categories.seo.score :=
;/][//][//][//][//][//][//][//][ Grab Latest URL Value ][//][//][//][//][//][//][//][/;
URLcell := 1 + (A_Index)
URLaddress := Xl.ActiveSheet.Range("A" URLcell).Value
;/][//][//][//][//][//][//][//][ Start / Update Progress Bar ][//][//][//][//][//][//][//][/;
loaded := (((A_Index - 1) / #ofURLs) * 100)
loader := SubStr(loaded, 1 , 5)
if (URLcell = 2)
{
progressX := 0
progressY := 500
}
WinGetPos , progressX, progressY, , , Lighthouse Score Scraping in Progress... ahk_class AutoHotkey2
Progress, a m2 t w500 x%progressX% y%progressY% c10 fm8 fs7 wm300 ws200, %loader%`% || %URLcell% / %#ofURLs%, Currently scraping:`n`n%URLaddress%, Lighthouse Score Scraping in Progress...
Progress, %loader%
if (loader = 100)
{
SoundPlay *-1
MsgBox 0x1030, All Finished, Lighthouse SEO and Performance scores have populated your chosen excel sheet. Want to open it now??
Progress, Off
break
}
;/][//][//][//][//][//][//][//][ Set CMD Prompt directory to Opened File's Location ][//][//][//][//][//][//][//][/;
Jump2SaveDirectory := "cd\" saveToOpenPath ; go to main directory
; ||||||||||||||||||||||||||||||||||| Skip Cell if URL Cell is Nonexistant ||||||||||||||||||||||||||||||||||| ;
if (!URLaddress)
Goto, Skip
;/][//][//][//][//][//][//][//][ Run Command Line to Call Lighthout API & Write to .JSON file ][//][//][//][//][//][//][//][/;
RunWait, %comspec% /k %Jump2SaveDirectory% && lighthouse %URLaddress% --quiet --chrome-flags=""--headless"" --output=json --output-path=.\URLscores.json && exit ,,Hide ,CMDpid
; ************* remove cd\ command and give full output path
;/][//][//][//][//][//][//][//][ Read and Parse JSON Data ][//][//][//][//][//][//][//][/;
FileRead, JSONdata, %WBdir%\URLscores.json
readJSON := Jxon_Load(JSONdata) ; load new
Xl.ActiveSheet.Range("B" URLcell).Value := (readJSON.categories.performance.score * 100)
Xl.ActiveSheet.Range("C" URLcell).Value := (readJSON.categories.pwa.score * 100)
Xl.ActiveSheet.Range("D" URLcell).Value := (readJSON.categories.accessibility.score * 100)
Xl.ActiveSheet.Range("E" URLcell).Value := (readJSON["categories","best-practices","score"] * 100)
Xl.ActiveSheet.Range("F" URLcell).Value := (readJSON.categories.seo.score * 100)
; ||||||||||||||||||||||||||||||||||| Embed Timestamp ||||||||||||||||||||||||||||||||||| ;
FormatTime, TimeStamp, , MM-dd-yyyy h:mm:ss tt
Xl.ActiveSheet.Range("G" URLcell).Value := TimeStamp
; ||||||||||||||||||||||||||||||||||| Delete JSON file ||||||||||||||||||||||||||||||||||| ;
FileDelete, %WBdir%\URLscores.json
;/][//][//][//][//][//][//][//][ Ask User If They Want to Repeat Scrape (It Failed) ][//][//][//][//][//][//][//][/;
If (!Xl.ActiveSheet.Range("F" URLcell).Value)
{
;~ SoundPlay, A_WorkingDir\cow-moo4.wav
MsgBox, 0x1010, Failed to get URL scores..., The Lighthouse scores for %URLaddress% were unable to be retrieved.`n`nit's possible the web address was invalid.,6
;~ SoundPlay, A
;~ IfMsgBox Yes
;~ {
;~ GoTo, RepeatScrape
;~ }
;~ else
;~ {
Xl.ActiveSheet.Range("B" URLcell).Value := "Unable"
Xl.ActiveSheet.Range("C" URLcell).Value := "To"
Xl.ActiveSheet.Range("D" URLcell).Value := "Access"
Xl.ActiveSheet.Range("E" URLcell).Value := "Website"
Xl.ActiveSheet.Range("F" URLcell).Value := "Data"
;~ }
Process, Priority, %CMDpid%, High
Process, Close, %CMDpid%
; ||||||||||||||||||||||||||||||||||| Save Workbook ||||||||||||||||||||||||||||||||||| ;
Xl.Workbooks(WBnameNoExt).Save
; ||||||||||||||||||||||||||||||||||| Get Progress Window's Latest Location ||||||||||||||||||||||||||||||||||| ;
WinGetPos , progressX, progressY, , , Lighthouse Score Scraping in Progress... ahk_class AutoHotkey2
}
Skip:
}
;/][//][//][//][//][//][//][//][ Ask User If They Want to Open Their New Report ][//][//][//][//][//][//][//][/;
;~ SoundPlay, A_WorkingDir\drumroll3.wav
MsgBox, 0x1024, Open Report? ,Your file has successfully saved!`n`nWould you like to open your report?
IfMsgBox Yes
{
Xl.Visible := True
Process, Priority, %CMDpid%, High
Process, Close, %CMDpid%
ExitApp
}
else
{
try
{
Xl.Workbooks(WBnameNoExt).Close
}
catch e
{
}
try
{
Xl.Quit
}
catch e
{
}
Process, Priority, %CMDpid%, High
Process, Close, %CMDpid%
ExitApp
}
End::
Xl.DisplayAlerts := False
try
{
Xl.Workbooks(WBnameNoExt).Save
Xl.Workbooks(WBnameNoExt).Close
}
catch e
{
}
Xl.DisplayAlerts := True
try
{
Xl.Quit
}
catch e
{
}
FileDelete, %WBdir%\URLscores.json
Process, Priority, %CMDpid%, High
Process, Close, %CMDpid%
MsgBox Lighthouse Score Scraper Closed. The Excel file was saved.
ExitApp