Great question—this is a classic concurrency pitfall when using Google Sheets as a lightweight backend, and you’re totally right that the naive "read-then-write" approach fails with multiple users. The good news is there are reliable server-side solutions using Google’s native tools to eliminate race conditions. Here are the best approaches:

1. Use Google Apps Script for Server-Side Atomic Operations #

The most straightforward fix is to move your increment/decrement logic to Google Apps Script (Google’s server-side JavaScript environment for Sheets). This way, the entire read-modify-write cycle happens on Google’s servers in a single, atomic operation—no more wide client-side race windows.

First, create custom functions in your Sheet’s Apps Script editor:

function incrementCell(cellReference) {
  // Get a script-wide lock to block concurrent executions
  const lock = LockService.getScriptLock();
  try {
    // Wait up to 10 seconds to acquire the lock (adjust timeout as needed)
    lock.waitLock(10000);
    
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    const cell = sheet.getRange(cellReference);
    const currentValue = parseInt(cell.getValue()) || 0; // Handle empty cells gracefully
    const newValue = currentValue + 1;
    cell.setValue(newValue);
    
    return newValue; // Return updated value to the client
  } catch (error) {
    throw new Error(`Failed to increment cell: ${error.message}`);
  } finally {
    // Always release the lock, even if an error occurs
    lock.releaseLock();
  }
}

// Add a matching decrement function
function decrementCell(cellReference) {
  const lock = LockService.getScriptLock();
  try {
    lock.waitLock(10000);
    
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    const cell = sheet.getRange(cellReference);
    const currentValue = parseInt(cell.getValue()) || 0;
    const newValue = Math.max(0, currentValue - 1); // Prevent negative quantities if needed
    cell.setValue(newValue);
    
    return newValue;
  } catch (error) {
    throw new Error(`Failed to decrement cell: ${error.message}`);
  } finally {
    lock.releaseLock();
  }
}

Deploy this script as a Web App (Settings > Deploy > New deployment > Select "Web app") with access set to your preferred user group. Your frontend can then send HTTP requests to the Web App URL to trigger these functions—all logic runs server-side, so race conditions are eliminated.

2. Optimistic Locking with the Google Sheets API #

If you want to stick to client-side code without Apps Script, you can implement optimistic locking using the Sheets API’s etag system. This lets you detect if the cell was modified between your read and write attempts, and retry the operation if a conflict occurs.

Here’s how it works in practice (JavaScript pseudocode):

async function safeIncrement(spreadsheetId, cellRange) {
  let success = false;
  while (!success) {
    try {
      // Step 1: Read the cell's current value and its etag (version identifier)
      const readResponse = await gapi.client.sheets.spreadsheets.values.get({
        spreadsheetId: spreadsheetId,
        range: cellRange,
        fields: 'values,etag'
      });
      
      const currentValue = parseInt(readResponse.result.values[0][0]) || 0;
      const cellEtag = readResponse.result.etag;
      
      // Step 2: Attempt to update the cell, using the etag to verify no changes
      await gapi.client.sheets.spreadsheets.values.update({
        spreadsheetId: spreadsheetId,
        range: cellRange,
        valueInputOption: 'RAW',
        resource: { values: [[currentValue + 1]] },
        headers: { 'If-Match': cellEtag }
      });
      
      success = true;
    } catch (error) {
      // If we get a 412 Precondition Failed error, the cell was modified—retry
      if (error.status !== 412) {
        throw error; // Propagate non-conflict errors
      }
    }
  }
}

This approach ensures your write only succeeds if no other user modified the cell since you read it. You’ll need to handle retries, but it’s a solid alternative if Apps Script isn’t your preference.

Key Takeaways #

• Apps Script + LockService is the most reliable solution: it handles concurrency directly on Google’s servers, with minimal client-side code.
• Optimistic locking works well if you want to avoid server-side scripts, but requires retry logic in your frontend.

Ditch the client-side read-then-write—let Google’s servers handle the atomic operations, and you’ll eliminate those frustrating lost increments.

内容的提问来源于stack exchange，提问作者Arnost Trtusek