Google Apps Script Developer for Custom Lightspeed X-Series Sales & Inventory Reporting

Overview We are looking for an experienced Google Apps Script developer and data architect to build a custom reporting solution using the Lightspeed X-Series API. While Lightspeed provides standard reporting, their default metrics fall short of giving us true insights into our best and worst-performing products. We need a robust, automated system that pulls sales and inventory payloads from Lightspeed into Google Sheets to build directly searchable and filterable reports that calculate accurate product performance that accounts for out-of-stock periods and inventory shrinkage margins. The Problem We Are Solving Lightspeed's default reports fail us in two major ways: Inaccurate Sell-Through Rates: Their sell-through rates are based on quantity on hand. Because we order high-performing products in massive bulk quantities, these items artificially show a low sell-through rate. Blindness to "Out of Stock" Periods: The "items sold per day" metric does not factor in days when an item was out of stock. A top-performing item might look like a poor performer simply because it was sold out for half the month. Currently, users have to dig deep into multiple UI panels to manually verify out-of-stock dates. The Solution & Core Features We need a custom report built directly in Google Sheets that calculates Items Sold Per Day, accurately excluding dates when the item was technically "out of stock." Key features include: True Performance Metrics: Easily identify the best and worst performers over adjustable date ranges. Dynamic Filtering & Searchability: The Google Sheet report must include user-friendly ways to search, filter, and drill down by SKU, Vendor, Category, etc. Custom Inventory Margins (Shrinkage/Theft): We need the ability to input a dynamic "out of stock margin." For example, if we set the margin to "3" for an item with high theft rates, the system must treat any inventory level of 3 or below as "out of stock" for the purposes of the items-sold-per-day calculation. Technical Requirements & Constraints Tech Stack: Google Apps Script (GAS) and Google Sheets. Database: We prefer to use Google Sheets as the primary database. Important: We currently have over 7,000 SKUs (growing daily). If you believe this ongoing sales logging will hit the 10 million cell limit in Google Sheets in the near future, you must advise us and propose an alternative database solution prior to beginning work. Reporting & UI: The final searchable report and dashboard must be built and formatted completely within Google Sheets. API Management & Rate Limits: * API keys must be securely stored in Google Apps Script Properties. The system must make bulk API calls, not single SKU/API calls to avoid Lightspeed rate limits and not step on the API calls from other systems to Lightspeed. GAS Execution Limits: You are responsible for handling and architecting around Google Apps Script execution time limits/timeouts (e.g., using continuation tokens or chunked processing). Freelancer Responsibilities Testing & Setup: You are completely responsible for setting up a Lightspeed test store, generating test credentials, and generating dummy data as needed to prove the logic works. Development & Integration: Build the GAS pull, the Sheets database architecture, and the interactive, searchable Google Sheets report. Documentation (Specifically for RAG): You must provide documentation formatted specifically for our internal RAG system. It must include: System Overview: A brief explanation outlining the system's architecture, what it is designed to manage, and its primary business purpose. Relevant Links: A dedicated list of explicit, un-embedded URLs required to operate or edit the system (e.g., the Google Sheet URL, the Apps Script project URL, etc.). Every tool must begin with a clear introduction and direct access points. Role-Based Operating Instructions System documentation must be segmented by user roles to ensure staff only consume information relevant to their permissions and job functions. For End-Users (Floor Staff): Document basic daily usage, access instructions, and explicitly explain how their daily actions impact the system's data and automation accuracy . For Managers & Reviewers: Detail the administrative workflows, such as approving automated tasks, overriding schedules or orders, and managing employee onboarding/offboarding within the app . For Developers & IT: Provide a technical breakdown of the system architecture. This must include API bulk limits, pagination handling, webhook security, and complete error routing explanations (e.g., where errors are routed when the API fails) . How to Apply In your proposal, please briefly outline: Your experience with the Lightspeed API (if any) or similar retail/inventory APIs. How you plan to handle the GAS execution time limits given a catalog of 7k+ SKUs. Your thoughts on the Google Sheets 10m cell limit for this specific use case, and if you think it will be an issue.