Re: JSON from Google Sheet to Property Object
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//values/A1:Z5000?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:4d_tech-unsubscr...@lists.4d.com
**
Re: JSON from Google Sheet to Property Object
Last email didn’t work right.
I think a Collection might be the way to go with this. If I create a collection
of objects:
{“ID”:”5912029","First Name”:”Sannyasin”},
{“ID”:”6015906","First Name”:”Mayuran”}
I should be able to access the data very easily.
Sannyasin Siddhanathaswami
On Nov 25, 2018, 11:06 PM -1000, 4D iNug Technical <4d_tech@lists.4d.com>,
wrote:
{“ID”:”5912029","First Name”:”Sannyasin”},
{“ID”:”6015906","First Name”:”Mayuran”}
**
4D Internet Users Group (4D iNUG)
Archive: http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
**
Re: JSON from Google Sheet to Property Object
** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: JSON from Google Sheet to Property Object
That makes sense. No wonder I’m confused. :)
But if I convert:
[
[ID,First Name,Last Name],
[5912029,Sannyasin,Siddhanathaswami],
[6015906,Mayuran,Sokkan]
]
to an array of objects:
[
{“ID”:”5912029","First Name”:”Sannyasin”},
{“ID”:”6015906","First Name”:”Mayuran”}
]
Does than mean I can access the “First Name” value by the “ID”? Somehow?
This is kind of like doing a "Selection to JSON”, parsing that into an object,
then trying to find a “record” in the resulting object.
Sannyasin Siddhanathaswami
On Nov 25, 2018, 10:44 PM -1000, 4D iNug Technical <4d_tech@lists.4d.com>,
wrote:
you are confusing property names and array positions.
$lastName:=$convertedObject[“ID”][“6015906”][“Last Name”]
this would infer
{"ID":{"6015906":{"Last Name":__here_}}}
if the target is
[
[ID,First Name,Last Name],
[5912029,Sannyasin,Siddhanathaswami],
[6015906,Mayuran,Sokkan]
]
then the notation should be
$convertedObject[2][2] //Sokkan
**
4D Internet Users Group (4D iNUG)
Archive: http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
**
Re: JSON from Google Sheet to Property Object
you are confusing property names and array positions.
$lastName:=$convertedObject[“ID”][“6015906”][“Last Name”]
this would infer
{"ID":{"6015906":{"Last Name":__here_}}}
if the target is
[
[ID,First Name,Last Name],
[5912029,Sannyasin,Siddhanathaswami],
[6015906,Mayuran,Sokkan]
]
then the notation should be
$convertedObject[2][2] //Sokkan
2018/11/26 17:39、Sannyasin Siddhanathaswami via 4D_Tech
<4d_tech@lists.4d.com>のメール:
It’s a JSON array of arrays from the Google Sheet that has the headers in the
first “row” of the array "values[0]”:
[
[ID,First Name,Last Name],
[5912029,Sannyasin,Siddhanathaswami],
[6015906,Mayuran,Sokkan]
]
My goal is to be able to reference this new object by doing something like:
$lastName:=$convertedObject[“ID”][“6015906”][“Last Name”]
I don’t really know what I’m doing. Would that even work?
**
4D Internet Users Group (4D iNUG)
Archive: http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
**
