18 Apr, 2019

Google Sheets To CATIA Via EKL

REST, Representation State Transfer, is a pattern for making APIs (Application Programming Interfaces) that can serve clients (callers of that API). They’re used for web-services. REST’s pattern includes methods names and parameters. You send requests and receive responses.
REST APIs typically include six methods: Delete, Get, Link, Patch, Post, and Put. These API Calls are made URLs (text) that can take parameters.

EKL provides an HTTPClient object, which can be created using the function CreateHTTPClient(). Let’s try calling the Get method provided by this website: https://resttesttest.com/.

//create a http client       
let client(HTTPClient)       
client = CreateHTTPClient()      
//notice that the word `get` is part of the URL below        
let url ="https://httpbin.org/get"       
let response(String)         
//calling the GET method on the web client       
response = client.Get(url)       
//thsi is the return code.       
let res(Integer)         
res = client.ReturnCode      
//if res == 0 then show the response         
if(res==0)       
{        
 Message(response)       
}        

Calling Google Sheets

Let’s do a more complex task– read a Google Sheet! Follow the steps below:

1. Setup A Google Sheet

Create a Google sheet and set its access to public.

This is the link for the Google sheet above:
https://docs.google.com/spreadsheets/d/1sHNXsw7h_7LLi3Ket6pWCsYfMFXSoqVukSduO-wVsL0/edit?usp=sharing

In order for us to access the Google Sheet using the APIs, we need its ID. The ID is the substring between the https://docs.google.com/spreadsheets/d/ and /edit?usp=sharing. So the ID is 1sHNXsw7h_7LLi3Ket6pWCsYfMFXSoqVukSduO-wVsL0

2. Get An API Key

Next, we need to set up a Google Developer Project to get an API key so that we can call the REST APIs. To do this, go to https://console.developers.google.com/apis.

Then, create a new project and activate the Google Sheets API for it. Finally, create credentials on the project. This will generate an API key:

This is the API key we generated from the step above:
AIzaSyB10ZMBOIxHqCP6lCvBsIAdHjx7aObLG8Y

3. Construct The GET URL

REST API calls are sent as URLs (text). This is the pattern for writing the GET method to read GoogleSheets:
https://sheets.googleapis.com/v4/spreadsheets/{sheetID}/values/{range}?key={apiKey}.

We need to fill the three parameters{sheetID}, {range} and {apiKey}:

Thus, the REST API GET call will be written as follows in EKL

let sheetID(String)
sheetID  ="1sHNXsw7h_7LLi3Ket6pWCsYfMFXSoqVukSduO-wVsL0"

let range(String)
range ="A1:C3"

let key(String)
key="AIzaSyB10ZMBOIxHqCP6lCvBsIAdHjx7aObLG8Y"

// Defines URI
let iURI (String)
iURI = TextFormat("https://sheets.googleapis.com/v4/spreadsheets/#/values/#?key=#", sheetID, range, key

4. Call The API From EKL:

Here is the full script (You can run it, the sheet is publicly available)

// Creates an HTTP client
let client (HTTPClient)
client = CreateHTTPClient()
//parameters for the GET API call
let sheetID="1sHNXsw7h_7LLi3Ket6pWCsYfMFXSoqVukSduO-wVsL0"
let range="A1:C3"
let key ="AIzaSyB10ZMBOIxHqCP6lCvBsIAdHjx7aObLG8Y"

// Defines URI
let iURI (String)
iURI = TextFormat("https://sheets.googleapis.com/v4/spreadsheets/#/values/#?key=#", sheetID, range, key)

// Defines output buffer
let oBuffer (string)

// Defines output DataTreeNode
let oDTN (DataTreeNode)

// Example1: Performs a GET request
oBuffer = client.Get(iURI, oDTN)

// Example2: Performs a LINK request
oBuffer = client.Get(iURI)

// Checking if request return code is OK (rc = 0)
if (client.ReturnCode == 0)
{
    // success
    Notify("#", oBuffer)
}
else
{
    // failure
    Notify("Request return code is #", client.ReturnCode)
}

Here is the result

Creating Parameters In CATIA

In the EKL code above, we made two calls:

// Example1: Performs a GET request
let oDTN (DataTreeNode)
oBuffer = client.Get(iURI, oDTN)

// Example2: Performs a LINK request
oBuffer = client.Get(iURI)

The response provided by Google Sheets was captured in the oBuffer variable, whose string values is:

{
  "range": "Sheet1!A1:C3",
  "majorDimension": "ROWS",
  "values": [
    [
      "ParamName",
      "ParamType",
      "ParamValue"
    ],
    [
      "Length1",
      "Length",
      "10mm"
    ],
    [
      "Width1",
      "Length",
      "20mm"
    ]
  ]
}

Making use of the DataTreeNode variable, oDTN, we can convert the response to JSON using oDTN.ToJSON(), which returns:

{
   "range" : "Sheet1!A1:C3",
   "majorDimension" : "ROWS",
   "values" : "ParamName",
   "values" : "ParamType",
   "values" : "ParamValue",
   "values" : "Length1",
   "values" : "Length",
   "values" : "10mm",
   "values" : "Width1",
   "values" : "Length",
   "values" : "20mm"
}

We can also convert it to XML using oDTN.ToXML(), which returns:

<range>Sheet1!A1:C3</range>
<majorDimension>ROWS</majorDimension>
<values>ParamName</values>
<values>ParamType</values>
<values>ParamValue</values>
<values>Length1</values>
<values>Length</values>
<values>10mm</values>
<values>Width1</values>
<values>Length</values>
<values>20mm</values>

Full Script

In order to use this data to create parameters in CATIA, we’ll need to parse the strings:

// Creates an HTTP client
let client (HTTPClient)
client = CreateHTTPClient()
//parameters for the GET API call
let sheetID="1sHNXsw7h_7LLi3Ket6pWCsYfMFXSoqVukSduO-wVsL0"
let range="A1:C3"
let key ="AIzaSyB10ZMBOIxHqCP6lCvBsIAdHjx7aObLG8Y"

// Defines URI
let iURI (String)
iURI = TextFormat("https://sheets.googleapis.com/v4/spreadsheets/#/values/#?key=#", sheetID, range, key)

// Defines output buffer
let oBuffer (string)

// Defines output DataTreeNode
let oDTN (DataTreeNode)

// Example1: Performs a GET request
oBuffer = client.Get(iURI, oDTN)

// Example2: Performs a LINK request
oBuffer = client.Get(iURI) 
// Checking if request return code is OK (rc = 0), if not then exit script

if (client.ReturnCode <> 0) {
    exit
}

//convert the data tree node to JSON string
let jsonData(String)
jsonData = oDTN.ToJSON()
/*here is how the JSON will come in
{
    "range" : "Sheet1!A1:C3",
    "majorDimension" : "ROWS",
    "values" : "ParamName",
    "values" : "ParamType",
    "values" : "ParamValue",
    "values" : "Length1",
    "values" : "Length",
    "values" : "10mm",
    "values" : "Width1",
    "values" : "Length",
    "values" : "20mm"
}
*/

//let's remove the start and end curly brackets.
jsonData =jsonData.Extract(1, jsonData.Length()-2)

//split the string by the commas
let pairs(List)
pairs = SplitString(jsonData, ",")

let i(Integer)
let paramName, paramType, paramValue(String)
//from the example code above, we know that out parameters names, types, and values will start at the 6th line.
for i=6 while i<= pairs.Size(){
    //first is the name, then type, then value.
    paramName = pairs.GetItem(i)
    paramType = pairs.GetItem(i+1)
    paramValue = pairs.GetItem(i+2)
    //lets remove unwanted text from the param info
    //this not the best code out there, but it does the job for an end of day post...
    paramName =ReplaceAll( ReplaceAll( paramName, "\"values\" : \"", ""), "\"", "")
    paramType =ReplaceAll( ReplaceAll( paramType, "\"values\" : \"", ""), "\"", "")
    paramValue =ReplaceAll( ReplaceAll( paramValue, "\"values\" : \"", ""), "\"", "")


    let lenLiteral(Length)
    set lenLiteral = paramValue.ToDimension("Length")

    //text consumed from the GoogleSheets seems to come with special characters. 
    //the following returns the wanted name string
    paramName = paramName.Extract(4, paramName.Length()-4)//(paramName, "|" , "")

    //lets create/set parameters in the model
    pSet.SetAttributeDimension(paramName, lenLiteral, "Length")
    pSet.Update()
    //jump by three steps for i to get to the next set.
    i=i+3
}

Here is the file

Credits:

Maroua Gmati From Dassault Systems answered my questions on EKL’s implementation for calling REST APIs, as well as setting up GoogleSheets for access from EKL.

Hits: 317

Tags: ,

About : Maher Elkhaldi

Maher Elkhaldi is a senior applications engineer at Tesla Motors. He founded the 3DXAutomation blog to help make knowledge of programming CATIA easier to find, and contribute to the open-source community.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.