Using PowerQuery to Make POST API Calls in Excel

Yes, Excel Can Make Elaborate API Calls

One day I’ll write a blog post that isn’t about PowerQuery, but today is not that day. You may be familiar with PowerQuery in Excel to grab all the files from a folder, or even to make a GET API call, but you can also use it to create POST API calls to send data to a website or to update or delete records. A word of warning – this is an advanced topic so familiarity with APIs and PowerQuery is a prerequisite to attempting this on your own.

Here’s how it works, using Google AppSheet as an example.

1. Enable APIs and Get Authorization Keys

A lot of web apps and websites offer APIs, but for security reasons they aren’t enabled by default. This is a good time to find your website’s developer docs and start at the top. They should tell you where to go in your account page or Settings menu to enable APIs and create an authorization key.

Sometimes you’ll get one key and sometimes you get two – a consumer key and a consumer secret key. Think of these like a user ID and password. It’s important to store your keys somewhere secure to prevent nefarious actors from gaining access to your data. Also, sometimes you need to give your key permissions for read / write / delete or to access different areas of the website.

In the example of Google AppSheet, the documentation directs us to enable APIs and create an authorization key.

2. Test Your POST API Call

Once everything is enabled it’s time to build your API call. You might be thinking to dive straight into PowerQuery, but instead head to a free API test program or website such as Postman. Writing blank queries in PowerQuery can be frustrating enough without trying to connect to your API provider for the first time. If you’re using Postman, create a Workspace with a Request and start assembling your API call. I recommend starting with a really easy GET API call to test that your authorization and endpoints are correct.

Here are the key steps:

1. Pick an easy GET command and endpoint to start with. In Google AppSheet I ran a GET call to grab a list of all the tables in the app. Refer to the developer docs for inspiration.
2. Assemble the HTTP site you’ll be connecting to. For Google AppSheet I used:

GET https://www.appsheet.com/api/v2/apps/<app-ID-1234>/tables/

1. Put your authorization key or keys in the Headers section. Be sure to follow the developer docs here
2. Click Send and check that you get a HTTP response code of 200 and a response back from your website that makes sense
3. Now you’re ready to create a POST call. Change the Method type from GET to POST, update the API endpoint, and add the required HTTP body to the BODY section of Postman. Click Send again and verify that everything is working correctly. Below is the API call and Body for Google AppSheet’s API to get all rows of a table. Make a note of which parameters are optional as eliminating those can speed things up in the next step.

API Call:
POST https://api.appsheet.com/api/v2/apps/{appId}/tables/{tableName}/Action

Body HTML:
{
"Action": "Find",
"Properties": {
   "Locale": "en-US",
   "Location": "47.623098, -122.330184",
   "Timezone": "Pacific Standard Time",
   "UserSettings": {
      "Option 1": "value1",
      "Option 2": "value2"
   }
},
"Rows": [
]
}

3. Create a Blank Query in PowerQuery

Finally it’s time to use Excel. In the Data ribbon click Get Data > Other Sources > Blank Query. Then navigate to the Advanced Editor and paste in this M language code, updating the URL and body text to match your Postman trial. If you’re using Google AppSheet anything in curly braces will need to be replaced with your own information.

For the body text anything in quotes (“) will need to be replaced with double quotes (“”).

Once you have your PowerQuery blank queried updated to match your API requirements click Done and see if it worked!

let
    url = "https://api.appsheet.com/api/v2/apps/{appId}/tables/{tableName}/Action",
    body  = "{
""Action"": ""Find"",
""Properties"": {
},
""Rows"": [
]
}",
 Source = Json.Document(Web.Contents(
    url,[
        Timeout=#duration(0,0,120,0),
        Headers=[#"ApplicationAccessKey"="{your-key}",#"Content-Type"="application/json"],
        Content=Text.ToBinary(body)
        ]
      )
    ),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

in
     #"Converted to Table"