Update Cell

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

  }))

}

/**

 * Update Cell

 * Write one cell (e.g. `B2`). With `USER_ENTERED` the value is parsed as if typed in the UI (formulas like `=SUM(A1:A9)`, dates, numbers); `RAW` stores it as-is.

 */

export async function main(

  auth: RT.Gsheets,

  spreadsheet_id: string,

  sheet_name: DynSelect_sheet_name,

  cell: string,

  value: any,

  value_input_option: "USER_ENTERED" | "RAW" = "USER_ENTERED"

) {

  const range = encodeURIComponent(

    `'${sheet_name.replaceAll("'", "''")}'!${cell}`

  )

  const url = new URL(

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

  )

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

  url.searchParams.append("includeValuesInResponse", "true")

  const response = await fetch(url, {

    method: "PUT",

    headers: {

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

      "Content-Type": "application/json",

      Accept: "application/json",

    },

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

  })

  if (!response.ok) {

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

  }

  return await response.json()

}