Format Cells

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

  }))

}

// A1 range without the tab name (A1:F1, B:B, 2:2, C3) → GridRange; omitted bounds stay open.

function gridRange(sheetId: number, range: string) {

  const m = range.toUpperCase().match(/^([A-Z]*)(\d*)(?::([A-Z]*)(\d*))?$/)

  if (!m || range === "") throw new Error(`Invalid A1 range "${range}"`)

  const [, c1, r1, c2 = c1, r2 = r1] = m

  const col = (c: string) =>

    [...c].reduce((n, ch) => n * 26 + ch.charCodeAt(0) - 64, 0)

  const grid: { [key: string]: number } = { sheetId }

  if (c1) grid.startColumnIndex = col(c1) - 1

  if (c2) grid.endColumnIndex = col(c2)

  if (r1) grid.startRowIndex = Number(r1) - 1

  if (r2) grid.endRowIndex = Number(r2)

  return grid

}

// "#1a73e8" / "#fff" → Sheets Color (0-1 floats)

function color(hex: string) {

  let h = hex.replace(/^#/, "")

  if (h.length === 3) h = [...h].map((c) => c + c).join("")

  if (!/^[0-9a-f]{6}$/i.test(h)) throw new Error(`Invalid hex color "${hex}"`)

  const [red, green, blue] = [0, 2, 4].map(

    (i) => parseInt(h.slice(i, i + 2), 16) / 255

  )

  return { red, green, blue }

}

/**

 * Format Cells

 * Style a range (A1 notation without the tab name: `A1:F1`, `B:B`, `2:2`, `C3`). Only the attributes you set change; the rest of the formatting is kept. Colors are hex (`#1a73e8`). Number format patterns follow the Sheets syntax, e.g. `#,##0.00`, `0%`, `yyyy-mm-dd`.

 */

export async function main(

  auth: RT.Gsheets,

  spreadsheet_id: string,

  sheet_name: DynSelect_sheet_name,

  range: string,

  bold: boolean | undefined,

  italic: boolean | undefined,

  underline: boolean | undefined,

  strikethrough: boolean | undefined,

  font_size: number | undefined,

  text_color: string | undefined,

  background_color: string | undefined,

  horizontal_alignment: "LEFT" | "CENTER" | "RIGHT" | undefined,

  vertical_alignment: "TOP" | "MIDDLE" | "BOTTOM" | undefined,

  wrap_strategy: "OVERFLOW_CELL" | "CLIP" | "WRAP" | undefined,

  number_format_type:

    | "TEXT"

    | "NUMBER"

    | "PERCENT"

    | "CURRENCY"

    | "DATE"

    | "TIME"

    | "DATE_TIME"

    | "SCIENTIFIC"

    | undefined,

  number_format_pattern: string | undefined

) {

  const format: { [key: string]: any } = {}

  const fields: string[] = []

  const textFormat: { [key: string]: any } = {}

  for (const [k, v] of Object.entries({

    bold,

    italic,

    underline,

    strikethrough,

    fontSize: font_size,

  })) {

    if (v != null) {

      textFormat[k] = v

      fields.push(`userEnteredFormat.textFormat.${k}`)

    }

  }

  if (text_color) {

    textFormat.foregroundColor = color(text_color)

    fields.push("userEnteredFormat.textFormat.foregroundColor")

  }

  if (Object.keys(textFormat).length > 0) format.textFormat = textFormat

  if (background_color) {

    format.backgroundColor = color(background_color)

    fields.push("userEnteredFormat.backgroundColor")

  }

  for (const [k, v] of Object.entries({

    horizontalAlignment: horizontal_alignment,

    verticalAlignment: vertical_alignment,

    wrapStrategy: wrap_strategy,

  })) {

    if (v) {

      format[k] = v

      fields.push(`userEnteredFormat.${k}`)

    }

  }

  if (number_format_type || number_format_pattern) {

    format.numberFormat = {

      type: number_format_type ?? "NUMBER",

      ...(number_format_pattern ? { pattern: number_format_pattern } : {}),

    }

    fields.push("userEnteredFormat.numberFormat")

  }

  if (fields.length === 0) throw new Error("Set at least one format attribute")

  const headers = {

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

    Accept: "application/json",

  }

  const meta = await fetch(

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

    { headers }

  )

  if (!meta.ok) {

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

  }

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

    sheets: { properties: { sheetId: number; title: string } }[]

  }

  const sheet = sheets.find((s) => s.properties.title === sheet_name)

  if (!sheet) throw new Error(`Worksheet "${sheet_name}" not found`)

  const sheetId = sheet.properties.sheetId

  const requests = [

    {

      repeatCell: {

        range: gridRange(sheetId, range),

        cell: { userEnteredFormat: format },

        fields: fields.join(","),

      },

    },

  ]

  const response = await fetch(

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

    {

      method: "POST",

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

      body: JSON.stringify({ requests }),

    }

  )

  if (!response.ok) {

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

  }

  return await response.json()

}