Edits history of script submission #23239 for ' Add Column (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,
    }))
  }
  
  /**
   * Add Column
   * Add a column titled `header` right after the last non-empty header cell of row 1, growing the grid when it is full. Returns the new column's letter.
   */
  export async function main(
    auth: RT.Gsheets,
    spreadsheet_id: string,
    sheet_name: DynSelect_sheet_name,
    header: string
  ) {
    const headers = {
      Authorization: `Bearer ${auth.token}`,
      Accept: "application/json",
    }
    const base = `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheet_id}`
    const tab = `'${sheet_name.replaceAll("'", "''")}'`
  
    const [metaRes, rowRes] = await Promise.all([
      fetch(
        `${base}?fields=sheets.properties(sheetId,title,gridProperties.columnCount)`,
        { headers }
      ),
      fetch(`${base}/values/${encodeURIComponent(`${tab}!1:1`)}`, { headers }),
    ])
    for (const r of [metaRes, rowRes]) {
      if (!r.ok) throw new Error(`${r.status} ${await r.text()}`)
    }
    const { sheets } = (await metaRes.json()) as {
      sheets: {
        properties: {
          sheetId: number
          title: string
          gridProperties: { columnCount: number }
        }
      }[]
    }
    const sheet = sheets.find((s) => s.properties.title === sheet_name)
    if (!sheet) throw new Error(`Worksheet "${sheet_name}" not found`)
    const { values = [[]] } = (await rowRes.json()) as { values?: string[][] }
    const index = values[0].length // 0-based index of the new column
  
    if (index >= sheet.properties.gridProperties.columnCount) {
      const grow = await fetch(`${base}:batchUpdate`, {
        method: "POST",
        headers: { ...headers, "Content-Type": "application/json" },
        body: JSON.stringify({
          requests: [
            {
              appendDimension: {
                sheetId: sheet.properties.sheetId,
                dimension: "COLUMNS",
                length: 1,
              },
            },
          ],
        }),
      })
      if (!grow.ok) throw new Error(`${grow.status} ${await grow.text()}`)
    }
  
    let letter = ""
    for (let n = index + 1; n > 0; n = Math.floor((n - 1) / 26))
      letter = String.fromCharCode(65 + ((n - 1) % 26)) + letter
  
    const url = new URL(
      `${base}/values/${encodeURIComponent(`${tab}!${letter}1`)}`
    )
    url.searchParams.append("valueInputOption", "RAW")
    const response = await fetch(url, {
      method: "PUT",
      headers: { ...headers, "Content-Type": "application/json" },
      body: JSON.stringify({ values: [[header]] }),
    })
  
    if (!response.ok) {
      throw new Error(`${response.status} ${await response.text()}`)
    }
  
    return { column: letter, ...(await response.json()) }
  }
  

  Submitted by hugo989 2 hours ago