Hello! I'd like to use my SmartSuite apps as a source data for Excel or Power BI, but I can't figure out how to access the API from Web. Has anyone done this? When you select "Get Data" from Web, there's a prompt for the API endpoint URL/HTTPS, but nothing I try seems to work. Can anyone help?
Step 1: Here's the prompt I follow in Excel:
Step 2: What do I put in this dialog box asking for a URL?
So, I heard from Peter Novosel, and he gave me some great tips. I'll try to copy them here:
Hi Ben, I hadn't tried this in Excel or Power BI (they have the same connection options) but I got it to work. The process is a little daunting, however, since you have to use the Power Query Editor to make HTTP POST requests.
Here are the basic steps to get you started:
1. Open Excel
2. Click on Data in the ribbon
3. Select Get Data > From Other Sources > Blank Query
4. Click on Advanced Editor
5. In View, open "Advanced Editor"
In the query editor, enter this (replace the capitalized values with your own):
In the query editor, enter this (replace the capitalized values with your own):
let
url = "https://app.smartsuite.com/api/v1/applications/APP_ID/records/list/",
body = "",
Source = Json.Document(Web.Contents(
url,[
Timeout=#duration(0,0,120,0),
Headers=[#"Authorization"="Token YOUR API TOKEN",#"Content-Type"="application/json",#"account-id"="ACCOUNT_ID"],
Content=Text.ToBinary(body)
]
)
),
items = Source[items]
in
itemsYou will get something along these lines:
Right click the column header and select "To Table" - then click the "expand" icon in the dark green header and say Ok:
The display should now look like this:
From the Home menu select "Close and Load" and you will get:
You'll notice a bunch of field values in [parens] - this means they're nested objects. You can keep going back to the query editor, expanding those items into their own columns. You're working with raw API data here so it's often going to include ids - you can pass a "hydrated": true value as the body of the request to get label values for those items.
Sorry that this is so complex, Microsoft doesn't make it particularly easy!