Function fetchSheetState

apps/sim/lib/webhooks/polling/google-sheets.ts:298–349

* Fetches the sheet (A:Z) and returns the row count, auto-detected headers, * and the 1-indexed header row number in a single API call. * * The Sheets API omits trailing empty rows, so `rows.length` equals the last * non-empty row in columns A–Z. Header detection scans the first * {@link HEADER

(
  accessToken: string,
  spreadsheetId: string,
  sheetName: string,
  valueRenderOption: ValueRenderOption,
  dateTimeRenderOption: DateTimeRenderOption,
  requestId: string,
  logger: Logger
)

 * `headerRowIndex = 0` when no header is found within the scan window.
 */
async function fetchSheetState(
  accessToken: string,
  spreadsheetId: string,
  sheetName: string,
  valueRenderOption: ValueRenderOption,
  dateTimeRenderOption: DateTimeRenderOption,
  requestId: string,
  logger: Logger
): Promise<{ rowCount: number; headers: string[]; headerRowIndex: number }> {
  const encodedSheet = encodeURIComponent(sheetName)
  const params = new URLSearchParams({
    majorDimension: 'ROWS',
    fields: 'values',
    valueRenderOption,
    dateTimeRenderOption,
  })
  const url = `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values/${encodedSheet}!A:Z?${params.toString()}`

  const response = await fetch(url, {
    headers: { Authorization: `Bearer ${accessToken}` },
  })

  if (!response.ok) {
    const status = response.status
    const errorData = await response.json().catch(() => ({}))
    if (status === 403 || status === 429) {
      throw new Error(
        `Sheets API rate limit (${status}) — skipping to retry next poll cycle: ${JSON.stringify(errorData)}`
      )
    }
    throw new Error(
      `Failed to fetch sheet state: ${status} ${response.statusText} - ${JSON.stringify(errorData)}`
    )
  }

  const data = await response.json()
  const rows = (data.values as string[][] | undefined) ?? []
  const rowCount = rows.length

  let headers: string[] = []
  let headerRowIndex = 0
  for (let i = 0; i < Math.min(rows.length, HEADER_SCAN_ROWS); i++) {
    const row = rows[i]
    if (row?.some((cell) => cell !== '')) {
      headers = row
      headerRowIndex = i + 1
      break
    }
  }

  return { rowCount, headers, headerRowIndex }
}

async function fetchRowRange(
  accessToken: string,