Beyond the Grid: Building Secure, Zero-Cost B2B Client Portals on Google Sheets

The Illusion of Frontend Filtering

When engineering teams attempt to step away from the raw spreadsheet interface, they frequently fall into a catastrophic design pattern. They build an independent web frontend using Google Apps Script's native HTML Service and web app routing engine. However, to populate the dashboard, they fetch the entire multi-client dataset from the master sheet and rely on client-side JavaScript arrays to dynamically filter and hide rows belonging to other accounts.

This creates a massive data isolation vulnerability. Because the raw data is transmitted directly to the browser, any tech-savvy operator can simply open Google Chrome Developer Tools, navigate to the console or network memory, and extract the unencrypted master payload. The server environment must never blindly trust the client layer. Every single data request must be filtered and validated independently at the root level before it ever travels across the network.

The Blueprint: Server-Side Data Isolation

To achieve genuine enterprise-grade security on top of Google Workspace, you must shift your application layout to a strict server-side filtering paradigm. Under this decoupled framework, the frontend user interface remains completely blind to the macro-level dataset. The architecture functions through a secure three-step handshake:

• Bespoke Authentication: The external partner accesses your web app URL and enters their unique credentials (such as an email and a secure client PIN).
• Cloud Verification: The backend architecture intercepts these inputs and matches them against an isolated, internal "Clients" registry sheet on Google's servers.
• Isolated Data Extraction: If the credentials are valid, the server isolates that specific user's relational ID, parses the master data ledger, extracts only the matching rows, and transmits that clean subset back to the client browser.

Through this method, if Client A logs in, the invoices or project statuses of Client B are never even pulled into server memory for transmission. The browser literally cannot expose what it never receives.

Structuring Sheets Like a Relational Database

To make this serverless gatekeeper logic seamless, you must transition your spreadsheets from flat, chaotic logging documents into clean relational schemas. Instead of writing a client's full name across dozens of independent operational rows, assign every partner a unique, immutable identifier (such as a relational Client ID key).

• The Core Directory: A protected tracking tab maps the user's secure access credentials directly to their unique Client ID.
• The Operational Ledger: Your active data sheets (invoices, inventory updates, logistics trackers) lead with a dedicated Client ID column. When a query hits the backend, the script loops through the ledger rows inside Google's cloud infrastructure, strips the relational IDs out of the payload to keep the data clean, and packs only the structural metrics into the client-side return array.

The "Execute-as-Me" Secure Gateway Pattern

The final piece of the security puzzle lies in how you deploy the finished web application within your Google Workspace ecosystem. When publishing your Apps Script project as a Web App, the cloud platform presents two critical configuration options that allow you to bypass traditional third-party software licensing costs:

• Execute as: Me (The Administrator)
• Who has access: Anyone

By choosing this configuration, the underlying script executes database reads and writes using your administrative master permissions behind the scenes. Consequently, your external B2B partners can seamlessly view their dedicated dashboards and submit operational updates through the web portal without you ever having to click "Share" on your source Google Sheet files. Your master database remains entirely hidden from public view, locked down within your corporate cloud perimeter.

Own Your Automation

Building sophisticated external workflows doesn't require ballooning your IT budget on rigid SaaS portal platforms or complex database infrastructure. By pairing a relational spreadsheet design with server-side cloud filtering, you unlock elite data isolation with zero ongoing hosting or per-user subscription fees.

The full guide with code examples and the complete pattern is available on the MageSheet blog.