Sonarr and Radarr do not have an “easy” way of viewing media listings in a way that is easy to export. Sometimes, we just want a text file that we can manipulate and filter out extra data. In this article, I’ll walk through the steps to make an excel sheet with the data that you want, and nothing else.
As of writing this document, the versions of Radarr and Sonarr are listed below. Future versions may change functionality, so please keep this in mind and let me know if things stop working.
Sonarr: 18.104.22.1687 (apiv3)
- Note your base url.
- Radarr: https://<URL>api/v3/movie?apikey=<key>
- Sonarr: https://<URL>api/series?apikey=<key>
- Record your API key for both Sonarr and Radarr
- Click on the “Settings” link, then “General”
- In the “Security” section, there will be a field called “API Key”.
- Copy the API Key and modify the above links to include that key.
- Test your link by pasting it into your browser and ensure that it works.
- If the link works, you’re ready to proceed with the next section.
Import data into excel
- Click Data, “From Web”
- Paste your link into the URL field
- Power Query Editor will open. In the new window, click “To Table”.
- In the next window, leave the default values and click “OK”.
- Now that the data is in a table format, let’s define how to format our columns. Click on the “Expand Column” icon.
- Select which fields you’d like to keep, then click “OK”:
- From here, if you’re happy with the data, you could elect to simply click “Close and Load” (top left corner). If you’d like to refine your tables a bit more, continue to rename the columns.
- To rename columns, double-click the title of one of the columns (in this case, Column1.title), and enter a new name for it, then hit enter. You may do this for as many of the columns as you wish. While renaming, you will see an “Applied Step” of “Rename Columns”. all of the renamed columns will “live” within this one step, as long as you don’t perform any other data manipulation between renamings.
- Now that we’ve got the data displayed as desired, click “Close and Load” to load the data into excel as a table.
- To make things easier to read, I like to see the “size on disk” displayed as a value in GB. Right-click on that column and select “Format Cells”
- In the pop-up window, select Category: “Custom”, then enter the following into the “Type” field: ,##0.0,,,”GB”
- Your size on disk will now be displayed in a more readable format. Save or print the file and you’re good to go!
If you notice any areas of improvement to be made in this document, please comment on it and I’ll work to make it more usable in future revisions.