Read and write Sheets in batches

Read the Requests sheet once, compute the updated statuses in JavaScript, and write the status column back in one call.

Start with a known table

Create a new spreadsheet, name a tab Requests, then copy the comma-separated text from sample.csv shown below into cell A1 (or import sample.csv shown below with File > Import). Select column A, choose Data > Split text to columns, set the separator to Comma, and verify four rows by four columns. Keep the header row as shown:

Request ID,Requester,Summary,Status
R-100,Alex,Review the meeting notes,
R-101,Sam,Update the project brief,Hold
R-102,Jo,Prepare a draft agenda,

Open Extensions > Apps Script and replace the editor contents with Code.gs. In Project Settings, select Show "appsscript.json" manifest file in editor, then replace that file's contents with the included appsscript.json manifest. Run updateQueuedRequestStatuses once and approve access to the current spreadsheet when prompted.

/**
 * Marks blank request statuses as Ready in the bound spreadsheet.
 *
 * @returns {void}
 */
function updateQueuedRequestStatuses() {
  const sheet = getRequestsSheet_();
  const values = readRequestTable_(sheet);
  if (values.length === 1) {
    Logger.log('No request rows to update.');
    return;
  }

  const statusValues = values.slice(1).map(function (row) {
    return [row[3] === '' ? 'Ready' : row[3]];
  });
  const changedCount = statusValues.filter(function (row, index) {
    return row[0] !== values[index + 1][3];
  }).length;

  if (changedCount === 0) {
    Logger.log('No blank request statuses found.');
    return;
  }

  sheet.getRange(2, 4, statusValues.length, 1).setValues(statusValues);
  Logger.log('Marked ' + changedCount + ' request(s) Ready.');
}

/**
 * @returns {SpreadsheetApp.Sheet}
 */
function getRequestsSheet_() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Requests');
  if (!sheet) {
    throw new Error('Requests sheet is missing. Import sample.csv into a sheet named Requests.');
  }
  return sheet;
}

/**
 * Reads and validates the complete request table before any write occurs.
 *
 * @param {SpreadsheetApp.Sheet} sheet
 * @returns {Array<Array<*>>}
 */
function readRequestTable_(sheet) {
  const values = sheet.getDataRange().getValues();
  const expectedHeader = ['Request ID', 'Requester', 'Summary', 'Status'];
  const header = values[0];

  if (
    header.length !== expectedHeader.length ||
    header.some(function (value, index) {
      return value !== expectedHeader[index];
    })
  ) {
    throw new Error('Requests must use this header row: Request ID, Requester, Summary, Status.');
  }

  values.slice(1).forEach(function (row, index) {
    const rowNumber = index + 2;
    if (row.length !== expectedHeader.length || !row[0] || !row[1] || !row[2]) {
      throw new Error('Request row ' + rowNumber + ' is incomplete.');
    }
  });

  return values;
}

{
  "timeZone": "Etc/UTC",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets.currentonly"
  ]
}

The function requires all four headers in that exact order. It stops before writing if the sheet is missing, its header row does not match, or a data row has an empty request ID, requester, or summary. Without the header check, a reordered column could cause the status write to overwrite the wrong column.

Keep service calls outside the row loop

The script reads the table once with getValues(), updates ordinary JavaScript arrays in memory, and writes the status column in one setValues() call. The Apps Script best-practices guide recommends batching reads and writes because alternating service calls inside a loop is much slower.

Range.setValues() requires a rectangular two-dimensional array whose row and column counts match the target range. Because the target is a single status column, each row in the output array is a one-cell array such as ['Ready'].

Keep checkpoints beside external actions

Batching fits calculations whose only output is a Sheets range. Workflows that perform an external action per row, such as sending an email or creating a Drive file, have a different recovery tradeoff: after an external action succeeds for a row, write that row's completion marker immediately instead of waiting until the end of the loop.

Per-row markers reduce duplicate actions if a run stops mid-way, though they are not a transaction across services. An external action can succeed before the marker write fails, and Sheets buffers writes; SpreadsheetApp.flush() applies pending spreadsheet changes rather than rolling back a sent message.

Run manual action workflows one at a time without overlapping runs. After an interrupted run, check the destination service before retrying so you can mark any completed row before running the script again.

What changes on the sample data

After the first run, R-100 and R-102 have status Ready, R-101 remains Hold, and the other three columns are unchanged. The function logs that two statuses changed. A second run writes nothing because no status cell is blank.

Because the script is bound to the spreadsheet, a manual run executes as the user who clicked Run and requests access only to the current spreadsheet.

Common failures

• Requests sheet is missing. Create or rename the tab Requests, then split column A into four columns before running again.

• Requests must use this header row... Restore the four headers in order in row 1 without a title row above them.

• Request row N is incomplete. Fill in the required ID, requester, and summary on spreadsheet row N.

• Nothing changed. Every status cell already has a non-empty value.

Exercise: add another safe state

Add a complete row: R-103, Kim, Book a room, Waiting. Run updateQueuedRequestStatuses and confirm its status stays Waiting. Clear Waiting so the status cell is blank, then run the function again; only R-103 changes to Ready.

Next, write custom functions for Sheets formulas and validate, highlight, and react to Sheets edits before installing a time-driven trigger for scheduled status updates.