Google Sheets: Add WebSocket Support for Real-Time Communication Between Code.gs and Client-Side HTML

[seanwessmith]

Problem Description

Google Apps Script currently lacks support for persistent, bidirectional communication between server-side Code.gs and client-side HTML served via HtmlService. This forces developers to use inefficient polling mechanisms to achieve pseudo-real-time updates.

Current Limitations

• google.script.run only supports one-way RPC calls from client to server
• No WebSocket or Server-Sent Events (SSE) support
• Polling with setInterval creates unnecessary server load and latency
• 6-minute execution limit prevents long-running server processes

Use Cases Impacted

1. Real-time collaboration tools - Displaying live cursor positions or selection changes
2. Dashboard applications - Live data updates without page refresh
3. Progress indicators - Streaming progress of long-running operations
4. Chat/notification systems - Instant message delivery within Sheets add-ons

Proposed Solution

Implement WebSocket support in Google Apps Script with the following API:

// Code.gs - Server-side
function onWebSocketConnection(ws) {
  // Handle new WebSocket connection
  ws.on('message', (data) => {
    // Process incoming message
    const parsed = JSON.parse(data);
    
    if (parsed.type === 'subscribe') {
      // Set up sheet listeners
      const listener = SpreadsheetApp.onSelectionChange((e) => {
        ws.send(JSON.stringify({
          type: 'selectionUpdate',
          range: e.range.getA1Notation(),
          sheet: e.range.getSheet().getName(),
          timestamp: new Date().getTime()
        }));
      });
      
      ws.on('close', () => {
        listener.remove(); // Clean up
      });
    }
  });
  
  ws.on('error', (error) => {
    console.error('WebSocket error:', error);
  });
}

// Alternative: SSE for one-way streaming
function* onServerSentEvent() {
  const cache = CacheService.getUserCache();
  while (true) {
    const data = cache.get('latestUpdate');
    if (data) {
      yield data;
      cache.remove('latestUpdate');
    }
    Utilities.sleep(100); // Non-blocking in actual implementation
  }
}

// index.html - Client-side
<script>
  // WebSocket approach
  const ws = google.script.websocket.connect();
  
  ws.onopen = () => {
    ws.send(JSON.stringify({ type: 'subscribe' }));
  };
  
  ws.onmessage = (event) => {
    const data = JSON.parse(event.data);
    updateDisplay(data);
  };
  
  // Alternative: EventSource for SSE
  const eventSource = google.script.eventSource.connect();
  
  eventSource.onmessage = (event) => {
    const data = JSON.parse(event.data);
    updateDisplay(data);
  };
</script>

Benefits

1. Reduced server load - Eliminate constant polling requests
2. Lower latency - Instant updates instead of polling intervals
3. Better UX - True real-time features in Sheets add-ons
4. Resource efficiency - Maintain single connection vs repeated HTTP calls
5. Cleaner code - Event-driven instead of interval-based patterns

Backward Compatibility

• Keep existing google.script.run API unchanged
• WebSocket/SSE features would be opt-in via new APIs
• Graceful fallback to polling if WebSocket unavailable

Security Considerations

• Maintain same OAuth scopes and permissions model
• WebSocket connections authenticated via existing session
• Rate limiting to prevent abuse
• Automatic timeout after inactivity period