Read and write Sheets in batches

Read a Requests table once with getValues(), compute the new statuses in JavaScript, and write the Status column back with one setValues() call. You need a new spreadsheet and the four-row sample table below.

Start with a known table

1. Create a new spreadsheet and name a tab Requests.

2. Copy the comma-separated text from sample.csv, shown below, into cell A1, or import the file with File > Import.

3. If you pasted the text, select column A, choose Data > Split text to columns, and set the separator to Comma.

4. Check that the table fills A1:D4, four rows by four columns, with this header row:

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,

5. Open Extensions > Apps Script and replace the Apps Script editor contents with Code.gs.

6. In Project Settings, select Show "appsscript.json" manifest file in editor, then replace that file's contents with the included appsscript.json manifest. Save both files.

/**
 * 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"
  ]
}

Run updateQueuedRequestStatuses once and approve access to the current spreadsheet when prompted.

The function requires all four headers in that exact order. It stops before writing if the sheet is missing, if the header row does not match, or if a data row has an empty request ID, requester, or summary. Without the header check, the script could write statuses into the wrong column after someone reorders the columns.

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 this because a spreadsheet service call inside a loop is much slower than one read and one write.

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']. A mismatched outer array length fails with a row count mismatch, and a mismatched inner array length fails with a column count mismatch. Requesting zero rows on a header-only sheet fails with invalid range dimensions.

Mark each row after an external action

Batching fits calculations whose only output is a Sheets range. If a workflow sends an email or creates a Drive file for each row, it must track completed actions in case the run stops partway. 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 the risk of duplicate actions after an interrupted run. They cannot undo work in another service, and saving a marker can fail after the external action succeeds.

Sheets also buffers writes. SpreadsheetApp.flush() applies pending spreadsheet changes. It does not roll back a sent message.

Run workflows that perform external actions by hand. Wait for the active run to finish before starting another. After an interrupted run, check the destination service and mark any row whose action already completed. Then run 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, and check that the data is split 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 a row with another status

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.