Aloha all,
I created the method to convert a Google Sheet JSON to a property:value
collection of objects. This has the advantage of being able to use all of the
collection method (such as query). Here’s the code.
//get Google Sheet via JSON and convert to property:value Collection of
objects (can query and use all the collection methods)
C_OBJECT($result)
C_COLLECTION($formattedCol)
$formattedCol:=New collection
//sample data
$result:=JSON
Parse("{\"range\":\"Sheet1!A1:Z1003\",\"majorDimension\":\"ROWS\",\"values\":[[\"ID\",\"First
Name\",\"Last
Name\"],[\"5912029\",\"John\",\"Smith\"],[\"6015906\",\"Martian\",\"Marsa\"]]}")
//format for grabbing a Google Sheet JSON (needs to be public, and a key
created)
//$sheetURL:="https://sheets.googleapis.com/v4/spreadsheets/<SHEET
ID>/values/A1:Z5000?key=<API KEY>"
//$err:=HTTP Get($sheetURL;$result)
$headers:=$result.values[0] //collection of an array of just the headers
For ($dataRowNum;1;$result.values.length-1) //loop through each data row
starting with the second row (1 since it starts with 0)
$formattedCol[$dataRowNum-1]:=New object //create a new object in each element
of the collection
For ($HeaderEleNum;0;$headers.length-1) //loop through each header value
$thisHeaderValue:=$headers[$HeaderEleNum]
$formattedCol[$dataRowNum-1][$thisHeaderValue]:=$result.values[$dataRowNum][$HeaderEleNum]
//set the property and value for each header
End for
End for
Hope someone finds this a bit useful. At least for better understanding
collections. I’m using this currently to get data from a Thinkific course, to
Google Sheets via Zapier, then to 4D.
Sannyasin Siddhanathaswami
**********************************************************************
4D Internet Users Group (4D iNUG)
Archive: http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:[email protected]
**********************************************************************
