Edits history of script submission #23246 for ' Update Cell (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,
    }))
  }
  
  /**
   * 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()
  }
  

  Submitted by hugo989 2 hours ago