Edits history of script submission #23241 for ' Format Cells (gsheets)'

• bunnative

  Ap­pro­ved version

  Edit in Windmill

  Embed

  //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()
  }
  

  Submitted by hugo989 2 hours ago