Linking Framingham Open Data to Spreadsheets

One of the features of Open Data Framingham is the capability to link a spreadsheet to the data maintained by the City. Some of the datasets on Open Data Framingham are updated every business day, others are updated annually.

We support the OData standard for direct data connection so you know you are working with the most recent data.

Data can also be exported in various formats including XML, RSS feed, JSON, and CSV.

Linking to Microsoft Excel

Excel 2010 and newer support the OData standard. Versions of Excel prior to Excel 2016 require a free add-on from Microsoft, Microsoft Power Query. Follow these steps to connect to a dataset:

1.View the dataset’s table view.

2.Click the Export link on the Toolbar and expand the OData option.

3.Copy the link found in OData v4 Endpoint.

OData Connection Example

4.In Excel, click the Data tab and choose New Query > From Other Sources > From OData Feed

Excel Data Connection Screen

5.Paste the OData link into the URL box and click OK.

Enter the ODATA Connection Information

6.The data will populate the spreadsheet.

Spreadsheet with Open Data in cells


Linking to Google Sheets

Google Sheets can connect to external data using its IMPORTDATA(url) function.

  1. View the dataset’s table view.
  2. Click the Export link on the Toolbar and expand the Download option.
  3. Right-Click on the CSV for Excel link and copy the link address.

Linking to CSV

4. Open your Google Sheet and enter the IMPORTDATA formula in a cell, pasting the link you copied as the parameter for the function:
=IMPORTDATA("https://data.framinghamma.gov/api/views/4avi-mhkq/rows.csv?accessType=DOWNLOAD&bom=true&format=true")

Sheet with Imported Data

5.You will need to reformat any date and time fields as date/time format:

Reformat Date and Time Columns