Google-WPS Spreadsheet Macro Designer
Budget: £20 – £250 GBP
1. Macro for Transferring and Transforming Quantities
Purpose: Automate the transfer of SKU quantities from a Google Spreadsheet to a WPS Spreadsheet and format the data into predefined ranges.
Data Source:
Retrieve quantities for each SKU from a specific Google Spreadsheet.
Data Destination:
Transfer the retrieved quantities into a designated WPS Spreadsheet.
Convert the raw quantity numbers into predefined ranges:
0 → Display "X."
1-5 → Display "1 tick."
6-10 → Display "2 ticks."
11-15 → Display "3 ticks."
16-20 → Display "4 ticks."
21+ → Display "5 ticks."
Based on the SKU, place the transformed values into the correct boxes in the WPS Spreadsheet.
Example:
If a SKU has 50 items in stock, the macro will place "5 ticks" in the corresponding cell in the WPS Spreadsheet.
Trigger:
Create a button in the WPS Spreadsheet that, when clicked, executes the macro.
2. Macro for Creating Invoices
Purpose: Automate the process of generating a printable invoice in a new tab within the WPS Spreadsheet.
Filter Data:
Remove any products (SKUs) that do not have a value entered next to them.
Invoice Layout:
Create a new tab in the WPS Spreadsheet with:
The header section from the original sheet.
Only the products that have values entered, along with their associated data (e.g., quantity, price).
Formatting:
Ensure the new tab is formatted and ready for printing.
Example:
A sheet containing SKUs with missing values is cleaned to only display products on order, with quantities and prices ready for invoice generation.
Trigger:
Create a button in the WPS Spreadsheet that, when clicked, executes the macro.
Additional requirements:
- Error Handling:
Handle errors such as missing data or incorrect formatting gracefully, with appropriate error messages for the user.
- Add a settings tab in the WPS Spreadsheet for defining custom ranges (e.g., 0-2 → "Low stock," 3-7 → "Medium stock").
Save user preferences for future use.
Purpose: Automate the transfer of SKU quantities from a Google Spreadsheet to a WPS Spreadsheet and format the data into predefined ranges.
Data Source:
Retrieve quantities for each SKU from a specific Google Spreadsheet.
Data Destination:
Transfer the retrieved quantities into a designated WPS Spreadsheet.
Convert the raw quantity numbers into predefined ranges:
0 → Display "X."
1-5 → Display "1 tick."
6-10 → Display "2 ticks."
11-15 → Display "3 ticks."
16-20 → Display "4 ticks."
21+ → Display "5 ticks."
Based on the SKU, place the transformed values into the correct boxes in the WPS Spreadsheet.
Example:
If a SKU has 50 items in stock, the macro will place "5 ticks" in the corresponding cell in the WPS Spreadsheet.
Trigger:
Create a button in the WPS Spreadsheet that, when clicked, executes the macro.
2. Macro for Creating Invoices
Purpose: Automate the process of generating a printable invoice in a new tab within the WPS Spreadsheet.
Filter Data:
Remove any products (SKUs) that do not have a value entered next to them.
Invoice Layout:
Create a new tab in the WPS Spreadsheet with:
The header section from the original sheet.
Only the products that have values entered, along with their associated data (e.g., quantity, price).
Formatting:
Ensure the new tab is formatted and ready for printing.
Example:
A sheet containing SKUs with missing values is cleaned to only display products on order, with quantities and prices ready for invoice generation.
Trigger:
Create a button in the WPS Spreadsheet that, when clicked, executes the macro.
Additional requirements:
- Error Handling:
Handle errors such as missing data or incorrect formatting gracefully, with appropriate error messages for the user.
- Add a settings tab in the WPS Spreadsheet for defining custom ranges (e.g., 0-2 → "Low stock," 3-7 → "Medium stock").
Save user preferences for future use.