Create automated planning application spreadsheet

I am looking for someone to create an automated spreadsheet/report that pulls the previous week’s UK planning applications from the UK PlanIt data/API.

The purpose of the report is to help identify potential trade opportunities for builders, loft conversion companies, kitchen/renovation firms, roofers and other home improvement trades.

I need the spreadsheet to be refreshable each week, so I can update it with the latest planning applications and then export or share the data with ChatGPT for analysis.

What I need is a spreadsheet system, ideally in Google Sheets or Excel, that can:

1. Pull planning application data from UK PlanIt.
2. Refresh the report weekly to show the previous 7 days of applications.
3. Cover all UK counties / planning areas, not just one local authority.
4. Handle multiple planning authorities within each county where needed.
5. Pull clean, structured data into a spreadsheet.
6. Allow me to filter by county, planning authority, date, application type and likely trade opportunity.
7. Create summary tabs that make the data easy to review.
8. Be simple enough for a non-technical user to refresh each week.

**Important functionality required:**
The spreadsheet should include:

* A refresh button or clear refresh process.
* A date range selector, ideally defaulting to the last 7 days.
* County / area filtering.
* Planning authority filtering.
* Keyword filtering for relevant trade opportunities.
* Automatic categorisation where possible, for example:

* Extensions
* Loft conversions
* Garage conversions
* Renovations/refurbishments
* Kitchens
* Roofing
* Outbuildings
* Commercial fit-outs
* Other building works
* A clean export tab that can be copied into ChatGPT for analysis.
* Basic error handling if the API limit is reached or if a request fails.
* A simple instruction tab explaining how I refresh and use the report.

**Suggested spreadsheet tabs:**

1. **Instructions**
Simple user guide explaining how to refresh the data and use the spreadsheet.

2. **Settings / Control Panel**
Date range, counties/areas to include, keywords, refresh controls and any API settings.

3. **Raw Planning Applications**
The unedited data pulled from PlanIt.

4. **Cleaned Applications**
Clean version of the data with standardised columns.

5. **Trade Categorisation**
Applications categorised by likely trade relevance.

6. **County Summary**
Number of opportunities by county and trade type.

7. **Planning Authority Summary**
Number of opportunities by local authority.

8. **ChatGPT Export**
A clean tab designed specifically so I can copy/export the data and ask ChatGPT to analyse it.

**Required data fields:**
Where available from PlanIt, I would like the spreadsheet to include:

* Application name/reference
* Planning authority
* County / area
* Application start date
* Address
* Description
* Application type
* Development type
* Status
* Decision, if available
* Applicant / agent details, if available
* Link to planning application
* Latitude / longitude, if available
* Last scraped / last changed date
* Suggested trade category
* Opportunity score, if possible

**Trade opportunity scoring:**
Ideally, I would like a simple scoring system to highlight the best opportunities. For example:

* High relevance: extension, loft conversion, conversion, major renovation
* Medium relevance: alterations, outbuildings, roof works, garage conversion
* Low relevance: tree works, signage, minor admin applications, discharge of conditions

I am happy for the freelancer to suggest the best scoring approach.

**Technical requirements:**
The freelancer should be comfortable working with:

* APIs
* Google Sheets Apps Script and/or Excel Power Query
* CSV/JSON data imports
* Pagination
* Rate limits
* Data cleaning
* Building refreshable dashboards/reports

The PlanIt API has paging and request limits, so the system must be built responsibly and should not rely on one huge request.

**End goal:**
Each week I want to be able to refresh the spreadsheet, see the latest planning applications across all counties, identify the best trade opportunities, and then ask ChatGPT to analyse the data by county, trade type and opportunity quality.

**Deliverables:**

1. A working Google Sheet or Excel workbook.
2. Automated or semi-automated weekly refresh process.
3. All required tabs and filters.
4. Clean data structure ready for ChatGPT analysis.
5. Simple instructions for use.
6. A short handover call or written walkthrough.
7. Notes on any limitations of the PlanIt API or recommended future improvements.

**Please include in your response:**

* Whether you recommend Google Sheets or Excel for this.
* Examples of similar API/spreadsheet automation work.
* How you would handle all counties and multiple planning authorities.
* How you would manage API limits and pagination.
* Estimated delivery time.
* Fixed price quote.