Upsert Row

//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,

  }))

}

/**

 * Upsert Row

 * Update the first row whose `key_column` (a header name or column letter) equals the key, or append `values` as a new row when none does. The key defaults to the value of `values` in the key column; matching is exact on the displayed value. Values are parsed as if typed in the UI. Returns `action` (updated/appended) and `row_number`.

 */

export async function main(

  auth: RT.Gsheets,

  spreadsheet_id: string,

  sheet_name: DynSelect_sheet_name,

  key_column: string,

  values: any[],

  key_value: string | undefined

) {

  const headers = {

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

    Accept: "application/json",

  }

  const base = `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheet_id}/values`

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

  // ponytail: reads the whole tab to find the key; fine up to tens of thousands of rows.

  const read = await fetch(`${base}/${encodeURIComponent(tab)}`, { headers })

  if (!read.ok) {

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

  }

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

  const header = rows[0] ?? []

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

  let index = header.indexOf(key_column)

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

    index =

      [...key_column.toUpperCase()].reduce(

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

        0

      ) - 1

  }

  if (index === -1) {

    throw new Error(

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

    )

  }

  const key = key_value ?? String(values[index] ?? "")

  const match = rows.findIndex((row, i) => i > 0 && (row[index] ?? "") === key)

  if (match !== -1) {

    const url = new URL(`${base}/${encodeURIComponent(`${tab}!A${match + 1}`)}`)

    url.searchParams.append("valueInputOption", "USER_ENTERED")

    const response = await fetch(url, {

      method: "PUT",

      headers: { ...headers, "Content-Type": "application/json" },

      body: JSON.stringify({ values: [values] }),

    })

    if (!response.ok) {

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

    }

    return {

      action: "updated",

      row_number: match + 1,

      ...(await response.json()),

    }

  }

  const url = new URL(`${base}/${encodeURIComponent(tab)}:append`)

  url.searchParams.append("valueInputOption", "USER_ENTERED")

  url.searchParams.append("insertDataOption", "INSERT_ROWS")

  const response = await fetch(url, {

    method: "POST",

    headers: { ...headers, "Content-Type": "application/json" },

    body: JSON.stringify({ values: [values] }),

  })

  if (!response.ok) {

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

  }

  const result = await response.json()

  return {

    action: "appended",

    row_number: Number(result.updates?.updatedRange?.match(/!\D+(\d+)/)?.[1]),

    ...result,

  }

}