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

​@hkingsbury ​@farzaneh.ahmadi Hi guys, did you all end up figuring this out? I am actually looking into if it’s possible to do this same exact thing...updating SmartSheet projects from FME.

Assuming there is an API endpoint to achieve what you’re looking to do, then it’s very likely this is possible.

Likely the hardest part will be figuring out the authentication and replicating that in FME

I have used FME with SmartSheet’s API and the way I did it was with the HTTPCaller (You can also use OpenAPICaller) to get the sheet using the ID. Keep in mind that this is an older workspace, so there might be better methods to connect.

For my use case, I fragmented the columns, rows, and cells, then used a FeatureMerger to merge on column ID. Your use case might be different.

Fragment Columns Query: json["columns"][*]

Fragment Rows Query: json["rows"][*]

Fragment Cells Query (row_cells): json[*]

I used prefixes in the JSONFragmenters to help identify them easier with col_, row_, and cell_. I then merged on cell_columnId and col_id. 

Each feature represents a cell with the attributes of the cell, row, and column.

In this way, if you need to manipulate them, you can use a JSONTemplater to create your payload to update the rows with the modified cell values. I’m using the GroupCounter and the ListBuilder to ensure each feature contains the maximum number of updates per API call that it allows.

Here are the settings for the JSONTemplater to give you an idea of how I did it:

JSONTemplater [JSONTemplater]
Group Processing:
    Group Sub-Features By: row_id
    Grouped Sub-Features are Consecutive: No
Template Expression: {
  "id": fme:get-attribute("row_id"),
  "cells": [
    fme:process-features("SUB")
    ]
...
Template File: <not set>
Validation:
    Validate Attribute/Template Names: Yes
Output Attribute Name:
    Result: json_payload
SUB Template Expression: {
  "columnId": fme:get-attribute("col_id"),
  "value": fme:get-attribute("new_cell_value"),
  "strict": false
}   
...

This workflow might not be the most efficient, but hopefully it helps.

-Chris