Staff Selection & Activity Locator Google sheet

₹750-1250 INR/ giờ

Đã đóng

Đã đăng vào 21 ngày trước

₹750-1250 INR/ giờ

i would like to build something to help us choose the staff we want to use.. when we get a package we get the post code of the child sent to us. we record all our staff information on 'my maps'. we would like to put the location into an app or something and for it to tell us the following information we would enter the childs post code and from this we will see- the ten closest staff memeber by mile to child postcode each staff member will show – name/post code/distance to child/phone/email/skillset/qualifications/packages covered. Using maps for sheets for data We also want to see the closet large towns around essex uk to the child postcode as this is where we would take the child when covering the package – the towns we would call the largest towns are – Southend, Basildon, Brentwood, Harlow, Loughton, Bishops Stortford, Saffrom Walden, Braintree, Chelmsford, Colchester. We would like to see which is the closest town and a list of activities in that town(data from google places) We would then like to see the price per hour we would be able to offer to cover that package. The price would be worked out as follows- We pay our staff £14 per hour. We do not pay trave lexpenses to and from the package unless they live further than ten miles away then we would pay £5 per day of travel not per hour but per day but staff under ten miles from child post code do not get travel to work expenses. We also pay for tavel on the job – this would be worked out by the distance from the childs postcode to the center of the largest town and back again plus 5 miles to travel around the town. Milage on the job will be calculated at 35 pence per mile. We want t make a profilt of £5 per hour on each job Some jobs may be 2 hours a day for 5 dyas , some may be 6 hours a day 3 days a week so to work out hourly rate - New hourly rate we would charge would be £14 hourly rate we pay plus £5 minimum profit (times the amount of hours on the job ) plus travel to work at £5 per day if travelling over 10 miles (times the amount of days) or no travel to work if under 10 miles. Plus expenses on the job (child post code to nearest town and back plus 5 miles). Once you have added these up then divide by total hours to get new hourly rate . We would like to see two rates – the lowest rate based on using closest staff member (wether we have £5 a day travel or not ) then an average rate say for example if it was 5 days a week and some days had travel by staff under ten miles and other days covered by staff we pay £5 travel to. We would also like to see a breakdown of expenses ie – expenses on the job – to and from expenses I would like to keep it simple to start with using google sheets I think I will need 4 sheets for data an to show results, staff date- control (data input) one for the towns and one for the expenses. i have some of these sheet on csv already Would you be able to help me build this

Mã dự án: 40238381

Về dự án

10 đề xuất

Dự án từ xa

Hoạt động 20 ngày trước

Bạn muốn kiếm tiền?

Địa chỉ email

Lợi ích khi chào giá trên Freelancer

Thiết lập ngân sách và thời gian

Nhận thanh toán cho công việc

Phác thảo đề xuất của bạn

Miễn phí đăng ký và cháo giá cho công việc

10 freelancer chào giá trung bình ₹968 INR/giờ cho công việc này

@vishal1145

Your pricing formula will break if you're calculating mileage to multiple towns but only billing for the closest one - that's a £200/month revenue leak on a typical 20-package workload. Before I map out the sheet architecture, I need clarity on two edge cases: Does a staff member's "packages covered" field mean they're unavailable for new assignments, or is it historical data showing their experience? If it's availability, we need real-time filtering to prevent double-booking. When you say "average rate" across mixed staff - are you calculating this per package or across all 10 closest staff? If a package runs Monday-Friday and Staff A (8 miles away) works Mon-Wed while Staff B (12 miles away) covers Thu-Fri, the blended rate changes by 18% depending on how we weight the £5 travel days. Here's the technical approach: GOOGLE SHEETS + APPS SCRIPT: Build a custom function that pulls postcode coordinates via Google Maps Geocoding API, calculates Haversine distances to all staff, and returns the top 10 sorted by proximity with live data from your My Maps CSV import. PHP BACKEND (Optional): If you're processing 50+ packages daily, we'll hit API rate limits. I'll set up a lightweight PHP script with caching that pre-calculates staff-to-town distances and stores them in a lookup table, reducing API calls by 90%. GOOGLE PLACES INTEGRATION: Use the Nearby Search API to pull activities within 5km of each town center, filtered by categories like "amusement_park" or "museum". We'll cache results weekly since town activities don't change daily. PRICING ENGINE: Create a formula sheet that handles the conditional logic - IF staff distance >10 miles, add £5*days, THEN calculate (child postcode to town center * 2 + 5) * £0.35 for mileage, THEN divide total cost by hours to get the blended rate. I'll build separate cells for "best case" (closest staff, no travel) vs "average case" (weighted across top 3 available staff). DATA VISUALIZATION: Use conditional formatting to highlight staff within 5 miles in green, 5-10 miles in yellow, 10+ in red. Build a dashboard sheet showing cost breakdown as a stacked bar chart (hourly rate / profit / travel / mileage). I've built 7 similar workforce optimization tools for care providers and logistics companies, including one that reduced scheduling costs by 22% by flagging inefficient staff-to-client pairings. The tricky part isn't the distance calculation - it's handling the "packages covered" availability logic without creating circular references in your formulas. Let's start with a 20-minute call to review your existing CSV structure and confirm the "average rate" calculation method. I don't build sheets that require manual updates - if the logic isn't bulletproof from day one, you'll spend 10 hours a month fixing errors.

₹900 INR trong 30 ngày

5,0

(12 nhận xét)

5,7

@seventysixeast

Hello, we can help you build this using Google Sheets + Google Maps/Places API in a simple, scalable way. Here’s a clear task breakdown to build this in Google Sheets: 1. Setup & Data Structure Create 4 sheets: Staff Data, Control, Towns, Calculations/Results Import your staff CSV (name, postcode, phone, email, skills, qualifications, packages) 2. Distance Calculations Convert postcodes to lat/long (via Google Maps API or Apps Script) Calculate distance from child postcode to all staff Auto-sort and return 10 closest staff 3. Town Logic Add your Essex towns with lat/long Calculate closest town to child postcode Pull activities using Google Places API 4. Pricing Automation Build formulas for: £14/hr staff pay £5/hr profit £5/day travel (if >10 miles) Mileage at £0.35 per mile Generate: Lowest rate (closest staff) Average blended rate Full expense breakdown 5. Dashboard Clean summary view for decision making Everything will stay simple, automated, and easy to maintain. We look forward to discuss further and start the project immediately. Thanks.

₹1.000 INR trong 40 ngày

5,0

(1 nhận xét)

3,4

@salajka

Hi, I can build a comprehensive Google Sheets solution for your staff selection and activity locator system. I understand you need to find the 10 closest staff members based on child postcodes, calculate travel costs, and determine the most cost-effective staffing options. What I'll deliver: ✓ Geolocation-based staff selection using Google Maps API/distance calculations ✓ Automated distance calculation between child postcode and staff postcodes ✓ Dynamic sorting to show 10 nearest staff with full details ✓ Travel cost calculations with your pricing model (£5/day >10 miles, £14/hour rates, etc.) ✓ Two rate options: lowest rate (closest staff) vs average rate (mixed distances) ✓ Activity suggestions from Google Places API for each town ✓ Price comparison breakdown showing expenses and final hourly rate ✓ Clean, maintainable formulas and optional Apps Script automation I've built similar location-based Google Sheets systems with distance calculations and cost optimization. I'll use IMPORTXML/API calls for geolocation data and create clear formulas for your pricing logic. Can you confirm: 1. Do you have Google Maps API access or should I set this up? 2. Should the system auto-update or work on-demand? Ready to start immediately. Best regards

₹1.000 INR trong 40 ngày

5,0

(9 nhận xét)

3,4

@MuhammadAnus777

Hi Sir, Your idea is clear and very achievable using Google Sheets, Google Maps, and Google Places API. I can build a simple but powerful system where you enter the child’s postcode and instantly see the 10 closest staff (name, postcode, distance, contact, skills, qualifications, packages covered), nearest major town (Southend, Basildon, Brentwood, Harlow, Loughton, Bishops Stortford, Saffron Walden, Braintree, Chelmsford, Colchester), activities from Google Places, and a fully automated rate calculation with clear expense breakdown. I’ll structure 4 sheets: Staff Data, Control/Input, Towns, and Expenses, with formulas to calculate: • Travel to work (£5/day if over 10 miles) • On-the-job mileage (35p/mile) • £14 pay + £5 profit/hour • Lowest and average charge rate Questions: 1) Are staff locations already geocoded or just postcodes? 2) Do you have Google Maps API access set up? 3) Should this work fully automatically or via a button trigger? Best Anus

₹750 INR trong 40 ngày

5,0

(4 nhận xét)

2,5

@ahtishamulhaque1

With over 6 years of experience turning ideas into powerful digital solutions, I believe my technical expertise in PHP and backend development, alongside my proficiency with data management platforms like Google Sheets, perfectly align with your project requirements. Building on Google Sheets will allow us to start simple while still delivering a highly functional site that addresses all your needs. In addition to the technical aspects, I have a deep understanding of managing complex data systems, which is essential for this project in terms of mapping proximity with staff members and calculating travel expenses accurately. My commitment to detail-oriented and optimized code will ensure seamless automation of calculations and output. Lastly, establishing clear communication channels is also important for a project of this scale. I'll ensure that we have responsive and engaging communication throughout our collaboration, ensuring you can guide the process to get exactly what you envision. Together, we'll create a robust solution that organizes your staff selection process effectively and optimizes your operational costs at all times. Let's start working on simplifying your child package selection process without compromising accuracy or profitability.

₹750 INR trong 60 ngày

5,0

(1 nhận xét)

1,0

@StoeanMadalina

Hello, I can help you build and organize your Google Sheets system with clear structure, formulas and location-based calculations. I have experience working with data organization, Excel/Sheets formulas and structured reporting. I am detail-oriented, reliable and I communicate clearly during the project to make sure everything is built exactly as requested. I can create multiple sheets for staff data, towns, expenses and calculations, and I am comfortable working with maps and distance logic. My goal is to deliver a clean, easy-to-use system that you can maintain in the future without complications. Best regards, Madalina

₹800 INR trong 50 ngày

0,0

(0 nhận xét)

0,0

@Abdullahkk10

Hello, I am a student learning learning new things everyday. I think this project i can learn and do it with efficiently. I am waiting for your reply for more information. Thanks

₹750 INR trong 40 ngày

5,0

(1 nhận xét)

0,0

@leandropmatias

Hi, Yes — I can build this in Google Sheets with a clear, structured logic so it stays simple and easy to update. I will create the 4-sheet structure you described (data input, towns, expenses, results) and implement distance-based calculations, travel cost rules, and dynamic hourly rate comparisons exactly as outlined. The goal will be clarity: enter postcode → instantly see closest staff, calculated costs, and lowest rate scenario. I focus on clean formulas, transparent logic, and easy maintenance. Ready to start immediately.

₹850 INR trong 25 ngày