The Client is a company that connects local suppliers with retailers. Suppliers input inventory into a dedicated sheet. The app then picks up the changes and sends them to the Client’s API.
Milestones
1. Identifying Google Sheets with changes
The first milestone is to identify the sheets that have been updated.
The script is bound to a Google Sheet of its own.
It is given a folder ID on Google Drive. It then needs to recursively scan the folder for any Google Sheets files it contains and pull their last modified date and time.
It compares the data it just pulled to the data already contained in the spreadsheet. If a scanned file is new (not already present) it is added to the spreadsheet.
If a file is present in the bound sheet, but was not found in the scanned folder, it is removed from the spreadsheet.
The script builds an array of file IDs that have been modified according to the rules below.
The script then looks at the modified date and time. If it changed since previous scan (stored in the spreadsheet) or the file is new (not yet present in spreadsheet), and a certain amount of time has passed since the last update (a threshold is needed to debounce the changes, say 10 minutes) then the id of that file is pushed to the array.
The script updates the spreadsheet containing file data.
The script updates the spreadsheet (adds new files, removes files no longer present in the directory, updates last modified date and time).
The script then sends a POST request to a webhook with the IDs in the array. The request needs to be sent with this library: https://github.com/WildH0g/gas-api-wrapper
The script runs on a time trigger every 15 minutes.
2. Future Milestones (not part of this specific job)
- Guide the user in data entry - Manage supplier templates versioning: deploying templates to new suppliers and pushing updates to the templates
A 30-minute code review will be required in the end of the milestone.
Stack
Google Sheets Google Apps Script
Dev Environment
clasp + Node.js Yarn Jest ESLint GitHub
Required Skills
Unit testing Git + GitHub OOP
If you are not familiar with all the required tools, that's OK, just mention it in the reply, you will not be necesserily filtered out because of it.
