Google Sheets Automation and Pivot Table
Budget: €8 – €30 EUR
I'm looking for someone experienced in Google Sheets who can help me automate tasks and create a pivot table with filters.
I have a Google Sheet with data from a web scraper called Octoparse. I will be adding more data to this sheet every week, day, or month.
Each row (line) in the sheet is a property. Each property has:
A unique reference number in Column A (Ref)
The property price in Column B (Price)
Other details in other columns
Task 1: Managing Property Data
Date of Addition:
Every time I add new data, the sheet has to automatically add the current date to each new property. This will help us know which properties are new. Old properties (those already in the sheet) should keep their original "date added."
Example:
If today there are 4000 properties, all should have today’s date automatically added in the "date added".
If next week I add data and there are 4100 properties, 4000 of these are old and should keep the date they had before the "date added". The 100 new properties should get the new date of that day.
Handling Removed Properties:
When I scrape the data again for example in a month, some properties may be missing because they were removed from the original website (they were sold).
I want to have the sheet automatically finding the old properties that are now missing and move these missing (sold) properties to another sheet called "Sold Properties." and add another date of when were moved. The properties in the second sheet named "Sold Properties" will have two dates: The date when they were first added and the date when they were removed (sold).
Task 2: Pivot Tables and Filters
Create a pivot table based on:
Column B (Price)
Column C (Location)
To find the average price of properties for each location.
Example:
If there are 500 properties in London and 350 in Rome, show the average price for each location.
Example:
London: 200,000;
Rome: 150,000
Filters:
Add dropdown filters to refine the pivot table data by:
Number of Bedrooms (Column D):
Example:
London 3 Bedroom: 250,000;
London 2 Bedroom: 200,000;
London 1 Bedroom: 150,000.
Number of Bathrooms (Column E):
Example:
London 3 Bedroom, 3 Baths: 250,000;
London 3 Bedroom, 2 Baths: 240,000;
London 3 Bedroom, 1 Bath: 220,000.
Property Size in SQM (Column F):
The Size Square Meters (SQM) ranges are from:
0-20, 20-40, 40-60, 60-80, 80-100, 100-120, 120-140, 140-160, 160-180, 180-200, 200-230, 230-260, 260+.
Example:
London 3 Bedroom, 3 Baths, 180-200 SQM: 255,000;
London 3 Bedroom, 3 Baths, 120-140 SQM: 225,000;
London 3 Bedroom, 3 Baths, 80-100 SQM: 195,000.
Agency Filter (Column M):
Add a filter for the Agency type column with these options:
For Sale Agency
For Sale Selected
For Sale
Example the sheet should be able to filter properties in London to see the average PRICE for 3 Bedroom, 3 Baths, 180-200 SQM properties listed by "For Sale Agency."
Communication and Application:
- Communication: I prefer instant messaging for our communication.
- Application: Please highlight your experience in Google Sheets and automation tasks in your application.
I have a Google Sheet with data from a web scraper called Octoparse. I will be adding more data to this sheet every week, day, or month.
Each row (line) in the sheet is a property. Each property has:
A unique reference number in Column A (Ref)
The property price in Column B (Price)
Other details in other columns
Task 1: Managing Property Data
Date of Addition:
Every time I add new data, the sheet has to automatically add the current date to each new property. This will help us know which properties are new. Old properties (those already in the sheet) should keep their original "date added."
Example:
If today there are 4000 properties, all should have today’s date automatically added in the "date added".
If next week I add data and there are 4100 properties, 4000 of these are old and should keep the date they had before the "date added". The 100 new properties should get the new date of that day.
Handling Removed Properties:
When I scrape the data again for example in a month, some properties may be missing because they were removed from the original website (they were sold).
I want to have the sheet automatically finding the old properties that are now missing and move these missing (sold) properties to another sheet called "Sold Properties." and add another date of when were moved. The properties in the second sheet named "Sold Properties" will have two dates: The date when they were first added and the date when they were removed (sold).
Task 2: Pivot Tables and Filters
Create a pivot table based on:
Column B (Price)
Column C (Location)
To find the average price of properties for each location.
Example:
If there are 500 properties in London and 350 in Rome, show the average price for each location.
Example:
London: 200,000;
Rome: 150,000
Filters:
Add dropdown filters to refine the pivot table data by:
Number of Bedrooms (Column D):
Example:
London 3 Bedroom: 250,000;
London 2 Bedroom: 200,000;
London 1 Bedroom: 150,000.
Number of Bathrooms (Column E):
Example:
London 3 Bedroom, 3 Baths: 250,000;
London 3 Bedroom, 2 Baths: 240,000;
London 3 Bedroom, 1 Bath: 220,000.
Property Size in SQM (Column F):
The Size Square Meters (SQM) ranges are from:
0-20, 20-40, 40-60, 60-80, 80-100, 100-120, 120-140, 140-160, 160-180, 180-200, 200-230, 230-260, 260+.
Example:
London 3 Bedroom, 3 Baths, 180-200 SQM: 255,000;
London 3 Bedroom, 3 Baths, 120-140 SQM: 225,000;
London 3 Bedroom, 3 Baths, 80-100 SQM: 195,000.
Agency Filter (Column M):
Add a filter for the Agency type column with these options:
For Sale Agency
For Sale Selected
For Sale
Example the sheet should be able to filter properties in London to see the average PRICE for 3 Bedroom, 3 Baths, 180-200 SQM properties listed by "For Sale Agency."
Communication and Application:
- Communication: I prefer instant messaging for our communication.
- Application: Please highlight your experience in Google Sheets and automation tasks in your application.