Edits history of script submission #23243 for ' Insert Rows or Columns (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,
    }))
  }
  
  /**
   * Insert Rows or Columns
   * Insert `count` empty rows or columns before `position`: a row number for rows, a column letter (or number) for columns. New cells copy the formatting of the row/column before them when `inherit_from_before` is set, otherwise of the one after.
   */
  export async function main(
    auth: RT.Gsheets,
    spreadsheet_id: string,
    sheet_name: DynSelect_sheet_name,
    dimension: "ROWS" | "COLUMNS",
    position: string,
    count: number = 1,
    inherit_from_before: boolean = false
  ) {
    const start = /^\d+$/.test(position)
      ? Number(position)
      : [...position.toUpperCase()].reduce(
          (n, c) => n * 26 + c.charCodeAt(0) - 64,
          0
        )
    if (!(start >= 1)) throw new Error(`Invalid position "${position}"`)
  
    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 = [
      {
        insertDimension: {
          range: {
            sheetId,
            dimension,
            startIndex: start - 1,
            endIndex: start - 1 + count,
          },
          // The API rejects inheritFromBefore on the first row/column.
          inheritFromBefore: inherit_from_before && start > 1,
        },
      },
    ]
  
    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