SmartSheet Attachments in Power BI

Marty Schlafer
Marty Schlafer ✭
in API & Developers

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?

· Share on FacebookShare on Twitter

Answers

  • Alex Argumedo
    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.

    · Share on FacebookShare on Twitter
  • Marty Schlafer
    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.

    · Share on FacebookShare on Twitter
  • Alex Argumedo
    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.

    · Share on FacebookShare on Twitter
  • Marty Schlafer
    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.

    · Share on FacebookShare on Twitter
  • Zanne_Mari
    Zanne_Mari ✭

    Anyone know if this capability has progressed?

    · Share on FacebookShare on Twitter

Categories