Still messing with Json

[stanl]

While I am more comfortable with Jim's Json functionality with the SQLite Extender, I'd still like to get as much as possible out of the ScriptControl.  The attached uses NHTSA Product Information Catalog Vehicle Listing (vPIC) as a test as it offers a variety of Json Responses. The script uses WinHttp.WinHttpRequest.5.1 for the URI Json response and has a few UDF's to describe and parse the data. One UDF props() has me baffled. It is 'hard-coded' for the Results key in the Json and I would like to display the sub-keys under the key. Would also like a UDF to determine if a key is an array [which Results is in the Json].

Code (WINBATCH) Select Expand



;Winbatch 2020A - Json test
;The NHTSA Product Information Catalog Vehicle Listing (vPIC) Application Programming Interface (API)
;You can go to https://vpic.nhtsa.dot.gov/api/ for full list of API's to call
;several examples below
;
;would like to come up with [hopefully generic] UDF's to describe and parse the Json
;
;Script uses WinHttp.WinHttpRequest.5.1, but CLR assemblies may be used
;Stan Littlefield, October 10, 2020
;======================================================================================================
IntControl(73,1,0,0,0)
Gosub udfs
request=0
oS=0
cFile = 'c:\temp\Output.Json'
main = "https://vpic.nhtsa.dot.gov/api/"
sub =  "vehicles/GetModelsForMake/honda?format=json"
;sub =  "vehicles/GetMakesForManufacturerAndYear/mer?year=2013&format=json"
;sub =  "vehicles/GetMakeForManufacturer/honda?format=json"
;sub =  "vehicles/GetAllMakes?format=json"
;sub =  "vehicles/DecodeVin/5UXWX7C5*BA?format=json&modelyear=2011"
;sub =  "vehicles/DecodeVinValues/5UXWX7C5*BA?format=json&modelyear=2011"
;sub =  "vehicles/DecodeWMI/1FD?format=json"
;sub =  "vehicles/GetWMIsForManufacturer/hon?format=json"
;sub =  "vehicles/GetManufacturerDetails/honda?format=json"
;sub =  "vehicles/GetVehicleVariableList?format=json"


cUrl = main:sub
request = Createobject("WinHttp.WinHttpRequest.5.1")
request.Open("GET", cUrl, @False )
request.Send()
jdata = request.ResponseText
ObjectClose(request)


If jdata == "" Then Terminate(@TRUE,"Exiting","No data returned")


;instantiate here and send a parameter to UDF's
oS = CreateObject ("MSScriptControl.ScriptControl")
oS.Language = "JScript"




if FileExist(cFile) then FileDelete(cFile)


;function and data tests
Message("Raw Json",jdata)
Message("Json Keys",Keys(oS,jdata))
Message("Json Results",props(oS,jdata)) ; need help here


output = GetJSON1(oS,jdata) ; there is also GetJSON(oS,jdata)
ObjectClose(oS)
output = StrReplace(output,",",@TAB)  ;change to Tab-delimited
FilePut(cFile, StrReplace (output, @LF, @CRLF))
if FileExist(cFile) then Run('notepad.exe', cFile)
Exit
;======================================================================================================
:WBERRORHANDLER
Terminate(@TRUE,"Error Encountered",err())
;======================================================================================================


:udfs
#DefineSubRoutine err()
wberroradditionalinfo = wberrorarray[6]
lasterr = wberrorarray[0]
handlerline = wberrorarray[1]
textstring = wberrorarray[5]
linenumber = wberrorarray[8]
errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
Return(errmsg)
#EndSubRoutine




#DefineFunction GetJSON(oS,jdata)
IntControl(73,1,0,0,0)
code = $"
function json2txt(obj)
{
  var txt = '';
  var recurse = function(_obj) {
    if ('object' != typeof(_obj)) {
      txt += ',' + _obj + '\n';
    }
    else {
      for (var key in _obj) {
        if (_obj.hasOwnProperty(key)) {
          txt += key;
          recurse(_obj[key]);
        }
      }
    }
  };
  recurse(obj);
  return txt;
}
$"
oS.AddCode(:code)
Return oS.Eval(: `json2txt(` : jdata : `,'')`)


:WBERRORHANDLER
Terminate(@TRUE,"Error Encountered",err())
#EndFunction


#DefineFunction GetJSON1(oS,jdata)
IntControl(73,1,0,0,0)
code = $"
function json2txt(obj,path){
   var txt='';
   for(var key in obj){
      if(obj.hasOwnProperty(key)){
         if('object'==typeof(obj[key])){
         txt += json2txt(obj[key],path+(path?''::'')+key);}
         else{txt+=path+','+key+','+obj[key]+'\n';}
      }
   }
   return txt;
}
$"
oS.AddCode(:code)
Return oS.Eval(: `json2txt(` : jdata : `,'')`)


:WBERRORHANDLER
Terminate(@TRUE,"Error Encountered",err())


#EndFunction




#DefineFunction Keys(oS,jdata)
oS.AddCode(: `function getKeys(obj,path){ var keys = ''; for (var i in obj) { keys += i + '\n'; } return keys; }`)
Return oS.Eval(: `getKeys(` : jdata : `,'')`)
#EndFunction


#DefineFunction props(oS,jdata)
code = $"
function getprops(obj,path) {
var x = ''; for (i = 0; i < obj.Results.length; i++)
{ x += obj.Results[i] + '\n' ; }
return x }
$"
oS.AddCode(:code)
Return oS.Eval(: `getprops(` : jdata : `,'')`)
#EndFunction


Return
;======================================================================================================




[stanl]

If I re-write the prop() UDF as hard-coded for the array associated with the key 'Results' I get a values.

Code (WINBATCH) Select Expand



#DefineFunction props(oS,jdata)
code = $"
function getprops(obj,path) {
var x = ''; for (i = 0; i < obj.Results.length; i++)
{ x += obj.Results[i].Make_Name + '\t' + obj.Results[i].Model_ID +
'\t' + obj.Results[i].Model_Name + '\n' ; }
return x }
$"
oS.AddCode(:code)
Return oS.Eval(: `getprops(` : jdata : `,'')`)
#EndFunction



But I want to determine [Results] => Make_Name : Model_ID : Model_Name  as variable names prior to calling the UDF. 

[stanl]

... and it is messy. But I got the array data broken down. Of course have to deal with more than one array in a response. It was raining and getting colder so had fun with my Jscript 101 knowledge  :o


if anyone cares, give the code a shot : [EDIT: modified one UDF to treat NULL onjects]

Code (WINBATCH) Select Expand



;Winbatch 2020A - Json test
;The NHTSA Product Information Catalog Vehicle Listing (vPIC) Application Programming Interface (API)
;You can go to https://vpic.nhtsa.dot.gov/api/ for full list of API's to call
;several examples below
;
;would like to come up with [hopefully generic] UDF's to describe and parse the Json
;
;Script uses WinHttp.WinHttpRequest.5.1, but CLR assemblies may be used
;Stan Littlefield, October 10, 2020
;======================================================================================================
IntControl(73,1,0,0,0)
Gosub udfs
request=0
oS=0
cFile = 'c:\temp\Output.Json'
if FileExist(cFile) then FileDelete(cFile)
main = "https://vpic.nhtsa.dot.gov/api/"
;sub =  "vehicles/GetModelsForMake/honda?format=json"
;sub =  "vehicles/GetMakesForManufacturerAndYear/mer?year=2013&format=json"
;sub =  "vehicles/GetMakeForManufacturer/honda?format=json"
sub =  "vehicles/GetAllMakes?format=json"
;sub =  "vehicles/DecodeVin/5UXWX7C5*BA?format=json&modelyear=2011"
;sub =  "vehicles/DecodeVinValues/5UXWX7C5*BA?format=json&modelyear=2011"
;sub =  "vehicles/DecodeWMI/1FD?format=json"
;sub =  "vehicles/GetWMIsForManufacturer/hon?format=json"
;sub =  "vehicles/GetManufacturerDetails/honda?format=json"
;sub =  "vehicles/GetVehicleVariableList?format=json"


cUrl = main:sub
request = Createobject("WinHttp.WinHttpRequest.5.1")
request.Open("GET", cUrl, @False )
request.Send()
jdata = request.ResponseText
ObjectClose(request)


If jdata == "" Then Terminate(@TRUE,"Exiting","No data returned")


;instantiate here and send a parameter to UDF's
oS = CreateObject ("MSScriptControl.ScriptControl")
oS.Language = "JScript"


Message("Raw Json",jdata)
BoxOpen("Parsing Raw Json","Please Wait....")
jkeys = Keys(oS,jdata)
n = ItemCount(jkeys,",")
For i = 1 To n-1
   k = ItemExtract(i,jkeys,",")
   skey =int(KeyLen(oS,jdata,k))
   If skey>0
      vals = rKeys(oS,jdata,k)
      If Strlen(vals)>2
          parsedData = StrReplace(vals,",",@TAB):@LF:props(oS,jdata,k,vals)
          FilePut(cFile, StrReplace (parsedData, @LF, @CRLF))
          BoxShut()
          if FileExist(cFile) then Run('notepad.exe', cFile)
      Endif
   Endif
Next


;uncomment below to see other displays


;output = GetJSON(oS,jdata) ; there is also GetJSON(oS,jdata)
ObjectClose(oS)
;output = StrReplace(output,",",@TAB)  ;change to Tab-delimited
;FilePut(cFile, StrReplace (output, @LF, @CRLF))
;if FileExist(cFile) then Run('notepad.exe', cFile)
Exit
;======================================================================================================
:WBERRORHANDLER
Terminate(@TRUE,"Error Encountered",err())
;======================================================================================================


:udfs
#DefineSubRoutine err()
wberroradditionalinfo = wberrorarray[6]
lasterr = wberrorarray[0]
handlerline = wberrorarray[1]
textstring = wberrorarray[5]
linenumber = wberrorarray[8]
errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
Return(errmsg)
#EndSubRoutine




#DefineFunction GetJSON(oS,jdata)
IntControl(73,1,0,0,0)
code = $"
function json2txt(obj)
{
  var txt = '';
  var recurse = function(_obj) {
    if ('object' != typeof(_obj)) {
      txt += ',' + _obj + '\n';
    }
    else {
      for (var key in _obj) {
        if (_obj.hasOwnProperty(key)) {
          txt += key;
          recurse(_obj[key]);
        }
      }
    }
  };
  recurse(obj);
  return txt;
}
$"
oS.AddCode(:code)
Return oS.Eval(: `json2txt(` : jdata : `,'')`)


:WBERRORHANDLER
Terminate(@TRUE,"Error Encountered",err())
#EndFunction


#DefineFunction GetJSON1(oS,jdata)
IntControl(73,1,0,0,0)
code = $"
function json2txt(obj,path){
   var txt='';
   for(var key in obj){
      if(obj.hasOwnProperty(key)){
         if('object'==typeof(obj[key])){
         txt += json2txt(obj[key],path+(path?''::'')+key);}
         else{txt+=path+','+key+','+obj[key]+'\n';}
      }
   }
   return txt;
}
$"
oS.AddCode(:code)
Return oS.Eval(: `json2txt(` : jdata : `,'')`)


:WBERRORHANDLER
Terminate(@TRUE,"Error Encountered",err())


#EndFunction




#DefineFunction Keys(oS,jdata)
oS.AddCode(: `function getKeys(obj,path){ var keys = ''; for (var i in obj) { keys += i + ','; } return keys; }`)
Return oS.Eval(: `getKeys(` : jdata : `,'')`)
#EndFunction


#DefineFunction rKeys(oS,jdata,r)
code= $"
function rKeys(obj,path){ var subkeys = '';
for (var j in obj.%r%[1]) {
subkeys += j + ','; }
return subkeys; }
$"
oS.AddCode(:code)
Return oS.Eval(: `rKeys(` : jdata : `,'')`)
#EndFunction


#DefineFunction KeyLen(oS,jdata,k)
IntControl(73,21,0,0,0)
oS.Reset()
code = "function klen(obj,path){ if (obj.%k% && obj.%k%.length) {return obj.%k%.length } }"
oS.AddCode(:code)
Return oS.Eval(: `klen(` : jdata : `,'')`)


:WBERRORHANDLER
Terminate(@TRUE,"Error Encountered",err())
#EndFunction
                                           
#DefineFunction props(oS,jdata,k,vals)
v=ItemCount(vals,",")-1


code = $"
function getprops(obj,path) {
var x = ''; for (i = 0; i < obj.%k%.length; i++)
{ x +=
$"
For v1 = 1 to v
   code = code: "obj.%k%[i].":ItemExtract(v1,vals,","):" + '\t' + "
Next
code = code:"'\n' ; } return x }"
oS.AddCode(:code)
Return oS.Eval(: `getprops(` : jdata : `,'')`)
#EndFunction


#DefineFunction jsnObj(oS,jdata)
oS.Reset()
code = "var jsonObject = ":jdata
oS.AddCode(:code)
jobj = oS.CodeObject.jsonObject
Return jobj          ;oS.Eval(`(` : jobj : `)`)
#EndFunction


Return
;======================================================================================================



[JTaylor]

Good work.   Was going to play around earlier but had another job that took longer than planned.



Jim

[stanl]

Good work.   Was going to play around earlier but had another job that took longer than planned.
Jim

Appreciate it. Below is basically the same script, but with an API that returns Json without array, so the code to iterate keys will shoot blanks.  Real challenge will be Json to capture both keys + array values in same output

Code Select Expand


{"name":"John","age":30,"cars":[ "Ford", "BMW", "Fiat" ]}


Code (WINBATCH) Select Expand



;Winbatch 2020A - Json test
;this works with targetlock api - returns info based on aubmitted zipcode
;this data does not involve a Json array
;
;Stan Littlefield, October 10, 2020
;======================================================================================================
IntControl(73,1,0,0,0)
Gosub udfs
request=0
oS=0
cFile = 'c:\temp\Output.Json'
if FileExist(cFile) then FileDelete(cFile)


zip = '27603'
cUrl = "https://api.targetlock.io/v1/post-code/":zip
request = Createobject("WinHttp.WinHttpRequest.5.1")
request.Open("GET", cUrl, @False )
request.Send()
jdata = request.ResponseText
ObjectClose(request)


If jdata == "" Then Terminate(@TRUE,"Exiting","No data returned")


;instantiate here and send a parameter to UDF's
oS = CreateObject ("MSScriptControl.ScriptControl")
oS.Language = "JScript"


Message("Raw Json",jdata)
BoxOpen("Parsing Raw Json","Please Wait....")
jkeys = Keys(oS,jdata)


n = ItemCount(jkeys,",")
For i = 1 To n-1
   k = ItemExtract(i,jkeys,",")
   skey =int(KeyLen(oS,jdata,k))
   If skey>0
      vals = rKeys(oS,jdata,k)
      If Strlen(vals)>2
          parsedData = StrReplace(vals,",",@TAB):@LF:props(oS,jdata,k,vals)
          FilePut(cFile, StrReplace (parsedData, @LF, @CRLF))
          BoxShut()
          if FileExist(cFile) then Run('notepad.exe', cFile)
      Endif
   Endif
Next


If ! FileExist(cFile) ;no array data found
   goto planB
Else
   goto endit
Endif




;uncomment below to see other displays
:planB
output = GetJSON(oS,jdata) ; there is also GetJSON(oS,jdata)
ObjectClose(oS)
output = StrReplace(output,",",@TAB)  ;change to Tab-delimited
FilePut(cFile, StrReplace (output, @LF, @CRLF))
if FileExist(cFile) then Run('notepad.exe', cFile)


:endit
Exit
;======================================================================================================
:WBERRORHANDLER
Terminate(@TRUE,"Error Encountered",err())
;======================================================================================================


:udfs
#DefineSubRoutine err()
wberroradditionalinfo = wberrorarray[6]
lasterr = wberrorarray[0]
handlerline = wberrorarray[1]
textstring = wberrorarray[5]
linenumber = wberrorarray[8]
errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
Return(errmsg)
#EndSubRoutine




#DefineFunction GetJSON(oS,jdata)
IntControl(73,1,0,0,0)
code = $"
function json2txt(obj)
{
  var txt = '';
  var recurse = function(_obj) {
    if ('object' != typeof(_obj)) {
      txt += ',' + _obj + '\n';
    }
    else {
      for (var key in _obj) {
        if (_obj.hasOwnProperty(key)) {
          txt += key;
          recurse(_obj[key]);
        }
      }
    }
  };
  recurse(obj);
  return txt;
}
$"
oS.AddCode(:code)
Return oS.Eval(: `json2txt(` : jdata : `,'')`)


:WBERRORHANDLER
Terminate(@TRUE,"Error Encountered",err())
#EndFunction


#DefineFunction GetJSON1(oS,jdata)
IntControl(73,1,0,0,0)
code = $"
function json2txt(obj,path){
   var txt='';
   for(var key in obj){
      if(obj.hasOwnProperty(key)){
         if('object'==typeof(obj[key])){
         txt += json2txt(obj[key],path+(path?''::'')+key);}
         else{txt+=path+','+key+','+obj[key]+'\n';}
      }
   }
   return txt;
}
$"
oS.AddCode(:code)
Return oS.Eval(: `json2txt(` : jdata : `,'')`)


:WBERRORHANDLER
Terminate(@TRUE,"Error Encountered",err())


#EndFunction




#DefineFunction Keys(oS,jdata)
oS.AddCode(: `function getKeys(obj,path){ var keys = ''; for (var i in obj) { keys += i + ','; } return keys; }`)
Return oS.Eval(: `getKeys(` : jdata : `,'')`)
#EndFunction


#DefineFunction rKeys(oS,jdata,r)
code= $"
function rKeys(obj,path){ var subkeys = '';
for (var j in obj.%r%[1]) {
subkeys += j + ','; }
return subkeys; }
$"
oS.AddCode(:code)
Return oS.Eval(: `rKeys(` : jdata : `,'')`)
#EndFunction


#DefineFunction KeyLen(oS,jdata,k)
IntControl(73,21,0,0,0)
oS.Reset()
code = "function klen(obj,path){ if (obj.%k% && obj.%k%.length) {return obj.%k%.length } }"
oS.AddCode(:code)
Return oS.Eval(: `klen(` : jdata : `,'')`)


:WBERRORHANDLER
Terminate(@TRUE,"Error Encountered",err())
#EndFunction
                                           
#DefineFunction props(oS,jdata,k,vals)
v=ItemCount(vals,",")-1


code = $"
function getprops(obj,path) {
var x = ''; for (i = 0; i < obj.%k%.length; i++)
{ x +=
$"
For v1 = 1 to v
   code = code: "obj.%k%[i].":ItemExtract(v1,vals,","):" + '\t' + "
Next
code = code:"'\n' ; } return x }"
oS.AddCode(:code)
Return oS.Eval(: `getprops(` : jdata : `,'')`)
#EndFunction


#DefineFunction jsnObj(oS,jdata)
oS.Reset()
code = "var jsonObject = ":jdata
oS.AddCode(:code)
jobj = oS.CodeObject.jsonObject
Return jobj          ;oS.Eval(`(` : jobj : `)`)
#EndFunction


Return
;======================================================================================================




[stanl]

Below is a test from the U.S. Patent Office (fairly verbose) - added a function to insert data into Excel. Response data is formatted as TAB delimited, but could be kept as CSV for insert into a table [ACE Provider SQL].

Code (WINBATCH) Select Expand



;Winbatch 2020A - Json test USPTO [United States Patent Office]
;Data sent to Excel
;Stan Littlefield, October 11, 2020
;======================================================================================================
IntControl(73,1,0,0,0)
Gosub udfs
request=0
oS=0
cFile = 'c:\temp\Output.Json'
if FileExist(cFile) then FileDelete(cFile)


cUrl = "https://developer.uspto.gov/ipmarketplace-api/search/query"
request = Createobject("WinHttp.WinHttpRequest.5.1")
request.Open("GET", cUrl, @False )
request.Send()
jdata = request.ResponseText
ObjectClose(request)


If jdata == "" Then Terminate(@TRUE,"Exiting","No data returned")


;instantiate here and send a parameter to UDF's
oS = CreateObject ("MSScriptControl.ScriptControl")
oS.Language = "JScript"


Message("Raw Json",jdata)
BoxOpen("Parsing Raw Json To Excel","Please Wait....")
jkeys = Keys(oS,jdata)


n = ItemCount(jkeys,",")
For i = 1 To n-1
   k = ItemExtract(i,jkeys,",")
   skey =int(KeyLen(oS,jdata,k))
   If skey>0
      vals = rKeys(oS,jdata,k)
      If Strlen(vals)>2
          parsedData = StrReplace(vals,",",@TAB):@LF:props(oS,jdata,k,vals)
          FilePut(cFile, StrReplace (parsedData, @LF, @CRLF))
          If FileExist(cFile)
             ObjectClose(oS)
             ClipPut("")
             ClipPut(FileGet(cFile))
             toXL()
          Endif
      Endif
   Endif
Next


If ! FileExist(cFile) ;no array data found
   goto planB
Else
   goto endit
Endif




;uncomment below to see other displays
:planB
output = GetJSON(oS,jdata) ; there is also GetJSON(oS,jdata)
ObjectClose(oS)
output = StrReplace(output,",",@TAB)  ;change to Tab-delimited
FilePut(cFile, StrReplace (output, @LF, @CRLF))
If FileExist(cFile)
   ClipPut("")
   ClipPut(FileGet(cFile))
   toXL()
Endif


:endit
Exit
;======================================================================================================
:WBERRORHANDLER
Terminate(@TRUE,"Error Encountered",err())
;======================================================================================================


:udfs
#DefineSubRoutine err()
wberroradditionalinfo = wberrorarray[6]
lasterr = wberrorarray[0]
handlerline = wberrorarray[1]
textstring = wberrorarray[5]
linenumber = wberrorarray[8]
errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
Return(errmsg)
#EndSubRoutine




#DefineFunction GetJSON(oS,jdata)
IntControl(73,1,0,0,0)
code = $"
function json2txt(obj)
{
  var txt = '';
  var recurse = function(_obj) {
    if ('object' != typeof(_obj)) {
      txt += ',' + _obj + '\n';
    }
    else {
      for (var key in _obj) {
        if (_obj.hasOwnProperty(key)) {
          txt += key;
          recurse(_obj[key]);
        }
      }
    }
  };
  recurse(obj);
  return txt;
}
$"
oS.AddCode(:code)
Return oS.Eval(: `json2txt(` : jdata : `,'')`)


:WBERRORHANDLER
Terminate(@TRUE,"Error Encountered",err())
#EndFunction


#DefineFunction GetJSON1(oS,jdata)
IntControl(73,1,0,0,0)
code = $"
function json2txt(obj,path){
   var txt='';
   for(var key in obj){
      if(obj.hasOwnProperty(key)){
         if('object'==typeof(obj[key])){
         txt += json2txt(obj[key],path+(path?''::'')+key);}
         else{txt+=path+','+key+','+obj[key]+'\n';}
      }
   }
   return txt;
}
$"
oS.AddCode(:code)
Return oS.Eval(: `json2txt(` : jdata : `,'')`)


:WBERRORHANDLER
Terminate(@TRUE,"Error Encountered",err())


#EndFunction




#DefineFunction Keys(oS,jdata)
oS.AddCode(: `function getKeys(obj,path){ var keys = ''; for (var i in obj) { keys += i + ','; } return keys; }`)
Return oS.Eval(: `getKeys(` : jdata : `,'')`)
#EndFunction


#DefineFunction rKeys(oS,jdata,r)
code= $"
function rKeys(obj,path){ var subkeys = '';
for (var j in obj.%r%[1]) {
subkeys += j + ','; }
return subkeys; }
$"
oS.AddCode(:code)
Return oS.Eval(: `rKeys(` : jdata : `,'')`)
#EndFunction


#DefineFunction KeyLen(oS,jdata,k)
IntControl(73,21,0,0,0)
oS.Reset()
code = "function klen(obj,path){ if (obj.%k% && obj.%k%.length) {return obj.%k%.length } }"
oS.AddCode(:code)
Return oS.Eval(: `klen(` : jdata : `,'')`)


:WBERRORHANDLER
Terminate(@TRUE,"Error Encountered",err())
#EndFunction
                                           
#DefineFunction props(oS,jdata,k,vals)
v=ItemCount(vals,",")-1


code = $"
function getprops(obj,path) {
var x = ''; for (i = 0; i < obj.%k%.length; i++)
{ x +=
$"
For v1 = 1 to v
   code = code: "obj.%k%[i].":ItemExtract(v1,vals,","):" + '\t' + "
Next
code = code:"'\n' ; } return x }"
oS.AddCode(:code)
Return oS.Eval(: `getprops(` : jdata : `,'')`)
#EndFunction


#DefineFunction jsnObj(oS,jdata)
oS.Reset()
code = "var jsonObject = ":jdata
oS.AddCode(:code)
jobj = oS.CodeObject.jsonObject
Return jobj          ;oS.Eval(`(` : jobj : `)`)
#EndFunction


#DefineFunction toXL()
IntControl(73,21,0,0,0)
oXL = CreateObject("Excel.Application")
oXL.Visible          = @TRUE  ; change this to @FALSE to run hidden
oXL.ScreenUpdating   = @TRUE  ; if running hidden, change this to @FALSE
oXL.UserControl      = @TRUE
oXL.DisplayAlerts    = @FALSE
oXL.WorkBooks.Add()
oWS = oXL.ActiveWorkBook.Worksheets(1)
oWS.Activate()
BoxShut()
oWS.Name = "Patents"
oWS.Cells(1,1).Select()
oWS.Paste()
oWS.UsedRange.Select()
oXL.Selection.Font.Name = 'Tahoma'
oXL.Selection.Font.Size = 9
oXL.Selection.Font.Bold = @True
oWS.UsedRange.Columns.Autofit()
oWS.ListObjects.Add(:1,oWS.UsedRange, , 1).Name ="Table1"
oWS.Range("Table1[#All]").Select()
oWS.ListObjects("Table1").TableStyle = "TableStyleLight15"
oXL.ActiveWindow.DisplayGridlines = @False
oWS.Cells(1,1).Select()
oWS=0
oXL=0
Pause("Data Loaded Into Excel","Save or Close Workbook")
Exit
:WBERRORHANDLER
oWS=0
oXL=0
Terminate(@TRUE,"Error Encountered",err())
#EndFunction




Return
;======================================================================================================




[stanl]

Found another gotcha. It will not affect any of the previous scripts I included for specific requests.

Code (WINBATCH) Select Expand


cUrl = "https://api.openbrewerydb.org/breweries/search?query=Raleigh"



select your own city ;D .  This will fail if used as request in previous scripts. The Json is just sent as an array, so there is not a key to parse from.  I added a one liner which appears to work.

Code (WINBATCH) Select Expand



BoxOpen("Parsing Raw Json To Excel","Please Wait....")
;add this to prevent errors if only an array is sent
If Strsub(jdata,1,1) == "[" Then jdata = '{"Results":':jdata:'}'