Keep Open Data Up to Date in Excel (Using APIs)
An Application Programming Interface (API) is a simple way business users can use open data in Excel and keep it up to date.
Follow the steps below to link an Excel workbook to any data.wa.gov dataset's API. The example below links to the Office of Financial Management's "Washington State Cities and Counties" open dataset. Once linked, you can refresh the data at any time.
Steps to Link Excel to a Dataset's API
Step 2. Choose API and then choose Copy.
Step 3. In Excel, from the Data ribbon, choose From Web
Step 4. In the From Web dialog box, paste what you copied in step 2, and choose OK.
Step 5. In Power Query Editor, choose To Table.
Step 6. In the To Table dialog box, choose OK.
Step 7, option 1. Choose the button with two arrows.
Note: If you cannot choose this button, use Step 7, option 2 instead.
Step 7, option 2. Tab to select Column1, then from the Transform ribbon, choose Expand.
Step 8. Deselect Use original column name as prefix and choose OK.
Step 9. From the Home ribbon, choose Close & Load.
This closes the Power Query Editor window and loads the data to a table in Excel.
Save your workbook.
Tips
Learn more about using tables in Excel:
To refresh the data, from the Data ribbon, choose Refresh All.
To get notified about dataset updates, "bookmark" or "watch" the dataset.
This step requires an account. If you don't have one, create a Tyler Data & Insights ID.
Questions?
Contact opendata@wa.gov with any questions.