An App Where Many Write to One Sheet: Concurrency, Duplicates, and Export Design

This post is the places I actually got stuck building the CheckReport Demo, and the code I used at each one. It covers how to design an inspection app where many inspectors share a single Google Sheet — built with GAS alone, so it doesn’t break.

Drawing the screen is easy. The hard part is making sure several people writing the same data at once doesn’t break anything. That’s also where I spent the most time on the inspection app.

Overall structure

브라우저 (HtmlService 프론트, 모바일 UI)
        │  google.script.run (RPC)
        ▼
Google Apps Script 백엔드
  - 입력 정규화/검증 (normalize)
  - 저장/완료/삭제/출력 핸들러 (handlers)  ← LockService
  - 시트 어댑터 (sheets)                    ← 헤더 기반 read/write
  - Drive 출력 어댑터 (drive-output)        ← 시간 예산
        │  SpreadsheetApp.openById()
        ▼
Google Sheets (공유 DB)
  InspectionRecords / InspectionOutputs /
  InspectionCompletions / InspectionRecordDeletes

The database is a single sheet, the backend is GAS, and the screen is HtmlService. Only the public address is handled by a Cloudflare Pages wrapper. You don’t rent a server or a DB separately.

Stuck point 1 — concurrent saves break rows

appendRow is not atomic. If two users hit save at the same instant, they race for the same last row and one gets overwritten, or numbers collide. (I covered that ceiling separately in LockService and the 30-run ceiling.)

The fix is to lock the entire save section and always release it in finally. If you can’t get the lock, fail fast and tell the user to try again.

function saveInspection(payload) {
  const generatedAt = new Date().toISOString();
  let lock = null;
  let lockAcquired = false;

  try {
    const normalized = normalizeInspectionSavePayload_(payload);
    const spreadsheetId = getBootstrapSpreadsheetId_();

    lock = LockService.getScriptLock();
    lockAcquired = lock.tryLock(INSPECTION_SAVE_LOCK_TIMEOUT_MS); // 8000ms
    if (!lockAcquired) {
      throw makeSaveError_('LOCK_TIMEOUT', '다른 저장 작업이 진행 중입니다. 잠시 후 다시 시도해주세요.');
    }

    const spreadsheet = SpreadsheetApp.openById(spreadsheetId);
    const sheet = ensureInspectionRecordsSheet_(spreadsheet);

    // ... duplicate check + append row ...

  } catch (error) {
    return buildInspectionSaveError_(error, generatedAt);
  } finally {
    if (lock && lockAcquired) {
      try { lock.releaseLock(); } catch (_) {}
    }
  }
}

Stuck point 2 — a retry saves the same inspection twice

The field has shaky networks. If the save response is slow, the user taps again. Then the same inspection goes in as two rows.

Trying to block it on the server by time fails. Instead, look up the client-generated idempotency key (clientIdempotencyKey) once before saving — if it already exists, don’t write a new row, just return the existing result.

const duplicate = findInspectionRecordByIdempotencyKey_(sheet, normalized.clientIdempotencyKey);
if (duplicate) {
  // 이미 저장된 기록 → 새 행을 만들지 않고 그대로 성공 응답
  return buildInspectionSaveSuccess_(duplicate, true, generatedAt, spreadsheetId);
}

const recordId = generateInspectionRecordId_(generatedAt, normalized.clientIdempotencyKey);
const row = buildInspectionRecordValues_(normalized, recordId, generatedAt);
appendObjectByHeaders_(sheet, INSPECTION_RECORD_HEADERS, row, makeSaveError_, INSPECTION_RECORDS_SHEET_NAME);

The lookup finds the clientIdempotencyKey column position from the header and scans the rows. At demo scale, a simple linear scan is enough.

function findInspectionRecordByIdempotencyKey_(sheet, clientIdempotencyKey) {
  const values = sheet.getDataRange().getValues();
  if (!Array.isArray(values) || values.length < 2) return null;
  const headers = values[0].map(toText_);
  const keyIndex = headers.indexOf('clientIdempotencyKey');
  if (keyIndex < 0) return null;
  for (let i = 1; i < values.length; i += 1) {
    if (toText_(values[i][keyIndex]) === clientIdempotencyKey) {
      return { rowNumber: i + 1, /* ...스냅샷 필드... */ };
    }
  }
  return null;
}

Stuck point 3 — putting any value in a cell can break or be dangerous

A sheet cell has three traps.

(1) Cell length limit. A sheet cell stops at 50,000 characters. If the answer JSON gets big, the whole save breaks. Block it before it crosses the limit. (See Stuffing JSON or images into a cell will break it.)

function jsonCell_(value, label) {
  const text = JSON.stringify(value == null ? null : value);
  if (text.length > INSPECTION_MAX_JSON_CELL_CHARS) { // 45000
    throw makeSaveError_('VALIDATION_FAILED', `${label} 데이터가 너무 큽니다.`);
  }
  return text;
}

(2) Formula injection. If a user writes a note starting with =, +, @, or -, the sheet runs it as a formula. Check the front of every cell value once before it goes in, and neutralize dangerous starting characters with a single quote.

function sheetSafeCell_(value) {
  if (value == null) return '';
  if (typeof value !== 'string') return value;
  const trimmed = value.trimStart();
  if (/^\d{4}-\d{1,2}$/.test(trimmed)) return `'${value}`;   // 'YYYY-M' 날짜 오인 방지
  return /^[=+\-@]/.test(trimmed) ? `'${value}` : value;     // 수식 주입 차단
}

(3) Don’t put photo bodies in the record row. Drop a photo into the inspection record as a data URL / base64 and you hit (1) immediately. So the record row takes only metadata, and rejects file bodies or Drive IDs if they come mixed in.

Object.keys(photo).forEach(key => {
  if (/dataurl|base64|content|blob|fileid|driveid|url/i.test(key) || /^bytes$/i.test(key)) {
    throw makeSaveError_('PHOTO_OUTPUT_NOT_ALLOWED',
      'InspectionRecords에는 파일 본문이나 Drive ID를 저장하지 않고 metadata만 저장합니다.');
  }
});

Good code / bad code — handle columns by name

The most common bad code when working with sheets is hardcoding column positions as numbers.

// 나쁨: 열 순서가 바뀌면 조용히 엉뚱한 칸에 쓴다
sheet.appendRow([payload.id, payload.name, payload.status, '', payload.memo]);
const name = row[1]; // 누가 열 하나 끼워넣으면 전부 밀린다

Instead, build and read rows by header name. Even if the column order changes or a new column appears, it doesn’t break, and if a required header is missing it fails explicitly.

// 좋음: 헤더에서 위치를 찾아 값 객체를 행으로 변환
function appendObjectByHeaders_(sheet, requiredHeaders, valuesByHeader, makeError, sheetName) {
  const headers = requireSheetHeaders_(sheet, requiredHeaders, makeError, sheetName);
  const row = headers.map(header =>
    Object.prototype.hasOwnProperty.call(valuesByHeader, header) ? valuesByHeader[header] : ''
  );
  sheet.appendRow(sheetSafeRow_(row)); // 쓰기 직전 전 셀을 sheetSafeCell_로 정리
}

Keep the schema (the header list) as a constant in one place. The truth in the sheet must match the constant in the code.

const INSPECTION_RECORD_HEADERS = [
  'schemaVersion', 'recordId', 'clientIdempotencyKey', 'facilityNo',
  'plantNameSnapshot', /* ... */ 'answersJson', 'photosJson',
  'clientCreatedAt', 'serverSavedAt', 'appVersion',
];

Stuck point 4 — the export hits the 6-minute wall

Generating several PDFs/PNGs hits the GAS execution-time limit (the 6-minute ceiling). Try to do it all at once and fail entirely, and the user has to start over from the beginning.

The fix is to set a soft time budget, and when it’s crossed, save what you have and guide the user to “continue.” Call it again with the same request and it skips the outputs already saved (here too, the idempotency key does the work).

function enforceInspectionOutputTimeBudget_(startedAtMs, nextIndex, outputs, files) {
  if (nextIndex >= outputs.length) return;
  const elapsedMs = Math.max(0, currentTimeMillis_() - Number(startedAtMs || 0));
  if (elapsedMs <= INSPECTION_OUTPUT_SOFT_TIME_LIMIT_MS) return; // 270000ms = 4.5분
  throw makeOutputError_('TIME_BUDGET_EXCEEDED',
    '출력 저장 시간이 길어져 일부만 저장했습니다. 같은 요청으로 다시 실행하면 이어서 저장합니다.',
    { partialFiles: files.slice(), resume: { retrySamePayload: true, processedCount: nextIndex, totalCount: outputs.length } });
}

The PDF itself is built with HtmlService, and blocked if it’s too big. (The mobile trap when pulling the screen out as a PNG with canvas is covered separately in Saving the mobile screen as an image.)

const blob = HtmlService.createHtmlOutput(html)
  .setTitle(normalized.fileName)
  .getAs(MimeType.PDF)
  .setName(normalized.fileName);
if (blob.getBytes().length > INSPECTION_OUTPUT_MAX_FILE_BYTES) { // 12MB
  throw makeOutputError_('PAYLOAD_TOO_LARGE', 'PDF는 12MB 이하여야 합니다. 사진 수나 해상도를 줄여주세요.');
}

To embed in a wrapper, allow framing in doGet

To put the public address behind a Cloudflare Pages wrapper and run the GAS app inside it, you have to allow frame exposure in doGet. (How to keep the address from changing on redeploy is a separate topic.)

function doGet() {
  return HtmlService.createTemplateFromFile('index')
    .evaluate()
    .setTitle('일반 설비 점검 모바일')
    .addMetaTag('viewport', 'width=device-width, initial-scale=1.0, viewport-fit=cover')
    .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

Wrap-up — principles to take

When this approach doesn’t fit

• When thousands of concurrent saves a day pile up — the linear scan and the single sheet hit their limit.
• When the system itself has to make the legal safety judgment.
• When fully offline + automatic sync is a hard requirement.

In those cases, consider a dedicated backend or an external DB from the start. I’ve gathered the limit signals in When to leave GAS. GAS is strong at binding the field-record-and-report flow lightly, and as long as the scope is exact, it does that job with no hosting bill.