Find Rows

//native

export type DynSelect_sheet_name = string

// Dropdown of the spreadsheet's worksheet (tab) names.

export async function sheet_name(auth: RT.Gsheets, spreadsheet_id: string) {

  const response = await fetch(

    `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheet_id}?fields=sheets.properties.title`,

    {

      headers: {

        Authorization: `Bearer ${auth.token}`,

        Accept: "application/json",

      },

    }

  )

  if (!response.ok) {

    throw new Error(`${response.status} ${await response.text()}`)

  }

  const { sheets } = (await response.json()) as {

    sheets: { properties: { title: string } }[]

  }

  return sheets.map((s) => ({

    value: s.properties.title,

    label: s.properties.title,

  }))

}

/**

 * Find Rows

 * Find the rows whose value in a column equals the given value (exact match on the displayed value). Row 1 is read as the header row; `column` is a header name or a column letter. Returns each match's 1-based `row_number` and its values keyed by header.

 */

export async function main(

  auth: RT.Gsheets,

  spreadsheet_id: string,

  sheet_name: DynSelect_sheet_name,

  column: string,

  value: string

) {

  const range = encodeURIComponent(`'${sheet_name.replaceAll("'", "''")}'`)

  const response = await fetch(

    `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheet_id}/values/${range}`,

    {

      headers: {

        Authorization: `Bearer ${auth.token}`,

        Accept: "application/json",

      },

    }

  )

  if (!response.ok) {

    throw new Error(`${response.status} ${await response.text()}`)

  }

  const { values = [] } = (await response.json()) as { values?: string[][] }

  const [headers = [], ...rows] = values

  // Header name first, then column letter (A, B, …, AA).

  let index = headers.indexOf(column)

  if (index === -1 && /^[A-Za-z]{1,3}$/.test(column)) {

    index =

      [...column.toUpperCase()].reduce(

        (n, c) => n * 26 + c.charCodeAt(0) - 64,

        0

      ) - 1

  }

  if (index === -1) {

    throw new Error(

      `Column "${column}" not found in header row: ${headers.join(", ")}`

    )

  }

  return rows.flatMap((row, i) =>

    (row[index] ?? "") === value

      ? [

          {

            row_number: i + 2,

            values: Object.fromEntries(

              headers.map((h, j) => [h, row[j] ?? ""])

            ),

          },

        ]

      : []

  )

}