SmartSheet Attachments in Power BI

Marty Schlafer

The out-of-the-box Power BI connector doesn't include attachments to sheets or rows. But we can use the SmartSheet API to pull all attachments. I have successfully tested in Power BI.

However, there is a catch…

• SmartSheet uses AWS s3 as the file store
• The documents are stored as private
• The full URI returned by the api includes the query text needed to authorize access with AWS s3
• The expiration is set to 120000 ms — 2 minutes(!)

How can we alter the expiration to a longer time, 24 hrs for example?

Alex Argumedo

As far as I know, it is not possible, unless an SS Engineering guy comes and do a custom solution for you. I had to change the logic of my code in order to work around this issue, I request the download url only before I need it. If that is not possible, then you need your own bucket, collect all attachments you need and then call them from there.

Marty Schlafer

These options are unpleasant — managing a duplicate store of attachments or hiring SS to provide a custom solution.

Instead, we'll explore using an intermediary such as Power Automate cloud flow.
* Store a URL with the Smart Sheet data consumed in Power BI that links to our Power Automate cloud flow.
* The cloud flow triggers on HTTP(S) request with parameters for Smart Sheet API to get a "fresh" AWS s3 document URL.
* On clicking the URL in Power BI it calls the cloud flow which in turn issues a redirect to the AWS s3 secured document.

Alex Argumedo

yes, that is what I do, but with an small code that goes and repeat the request, once I have the ID of the sheet and the ID of the Attachment, it is just a matter of call it on demand. If Power automate is a cost effective solution for you, that is perfect.

Marty Schlafer

Additional note… Power BI doesn't have the ability to bundle data as JSON into a HTTP request body, so any parameters passed to Power Automate cloud flow would be exposed as query text. As I want to make the cloud flow sterile and not contain the api client key for Smart Sheet and instead pass as a parameter, I'll need to obfuscate/encrypt the query text in the URL to be safe.