Smartsheets

There isn’t a native reader for Smartsheet in FME, so you’ll have to utilise HTTPCallers and the Smartsheet API:

https://smartsheet.redoc.ly/#section/Code-Walkthrough/Make-Your-First-API-Call

https://support.safe.com/hc/en-us/articles/25407505250189-Making-a-GET-Request-with-HTTPCaller

Thanks @hkingsbury. I’ve checked the two links. The first step is to executing API requests using a tool like cURL or Postman, then create an access token, then use HTTPCaller?

The first document describes the API (with examples). You’ll need to replicate those steps in an HTTPCaller.

Depending on how SmartSheet does authentication you may also need to setup an OAuth Web Connection
https://support.safe.com/hc/en-us/articles/25407422190861-Creating-an-OAuth-2-0-Web-Service-Connection-in-FME

T hank you @hkingsbury. this is my setting for HTTPCaller. but it has just 1 output. What would be the problem?

The response of the API call is JSON most of the time. And sometimes the data is spread out over multiple ‘pages’.

If you look at this example:

{
  "pageNumber": 1,
  "pageSize": 100,
  "totalPages": 1,
  "totalCount": 2,
  "data": [{
             "id": 6141831453927300,
             "name": "My first sheet",
             "accessLevel": "ADMIN",
             "permalink": "https://app.smartsheet.com/b/home?lx=8enlO7GkdYSz-cHHVus33A",
             "createdAt": "2016-01-28T22:02:35Z",
             "modifiedAt": "2016-08-09T17:50:06Z"
           },
           {
             "id": 6141831453927300,
             "name": "Sheet shared to me",
             "accessLevel": "VIEWER",
             "permalink": "https://app.smartsheet.com/b/home?lx=8enlO7GkdYSz-cHHVus33A",
             "createdAt": "2016-01-28T22:02:35Z",
             "modifiedAt": "2016-08-09T17:50:06Z"
           }
  ]
}

This would be in the _request_body property of the feature that comes out of the HTTPCaller.

There is a ‘totalPages’ attribute that tells you in this case you have a single page. If not, you should follow up with a second call to request the next page.

Then there is a ‘data’ attribute within the JSON, that you can extract with a JSONExtractor. And a JSONFragmenter to make multiple features with the properties available.

And then you can make a next request to the permalink for example to get the next step.

As @jkr_wrk suggested, the response will then need to be parsed.

Have a look at the JSONFragmenter - https://docs.safe.com/fme/html/FME-Form-Documentation/FME-Transformers/Transformers/jsonfragmenter.htm

This site here is also useful to visualise your JSON and understand where the data exists in the structure - https://jsonviewer.stack.hu/

Thanks @hkingsbury and @jkr_wrk. 

@jkr_wrk what do you mean by use a JSONFragmenter to make multiple features with the properties available? 

Is it possible to parse all the sheet just with jsonFragmenter? do you think it is correct to set the JSON Query to  json["columns"][]["rows"][] and in attribute to expose enter all the columns headings?

this is a part of error I got: 

I’m not sure if the result of HttpCaller is correct. the first when I set the format of result to json it will not change the format. The response-body has all the data of the sheet but it start with information about name of the sheet, sheet D, my level of access. is that ok?

 I’ve never used SmartSheets before so unsure exactly what the API is expected to return. However looking at this:

this is a part of error I got: 

 

Your response body seems valid and something I would expect to be returned from an API. Your JSON query isn’t valid, which will explain why its not working.

There are some good articles here that outline how to work with JSON in FME
https://support.safe.com/hc/en-us/articles/25407515641997-Tutorial-Getting-Started-with-JSON