Edits history of script submission #23247 for ' Upsert Row (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,
    }))
  }
  
  /**
   * Upsert Row
   * Update the first row whose `key_column` (a header name or column letter) equals the key, or append `values` as a new row when none does. The key defaults to the value of `values` in the key column; matching is exact on the displayed value. Values are parsed as if typed in the UI. Returns `action` (updated/appended) and `row_number`.
   */
  export async function main(
    auth: RT.Gsheets,
    spreadsheet_id: string,
    sheet_name: DynSelect_sheet_name,
    key_column: string,
    values: any[],
    key_value: string | undefined
  ) {
    const headers = {
      Authorization: `Bearer ${auth.token}`,
      Accept: "application/json",
    }
    const base = `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheet_id}/values`
    const tab = `'${sheet_name.replaceAll("'", "''")}'`
  
    // ponytail: reads the whole tab to find the key; fine up to tens of thousands of rows.
    const read = await fetch(`${base}/${encodeURIComponent(tab)}`, { headers })
    if (!read.ok) {
      throw new Error(`${read.status} ${await read.text()}`)
    }
    const { values: rows = [] } = (await read.json()) as { values?: string[][] }
    const header = rows[0] ?? []
  
    // Header name first, then column letter (A, B, …, AA).
    let index = header.indexOf(key_column)
    if (index === -1 && /^[A-Za-z]{1,3}$/.test(key_column)) {
      index =
        [...key_column.toUpperCase()].reduce(
          (n, c) => n * 26 + c.charCodeAt(0) - 64,
          0
        ) - 1
    }
    if (index === -1) {
      throw new Error(
        `Column "${key_column}" not found in header row: ${header.join(", ")}`
      )
    }
    const key = key_value ?? String(values[index] ?? "")
    const match = rows.findIndex((row, i) => i > 0 && (row[index] ?? "") === key)
  
    if (match !== -1) {
      const url = new URL(`${base}/${encodeURIComponent(`${tab}!A${match + 1}`)}`)
      url.searchParams.append("valueInputOption", "USER_ENTERED")
      const response = await fetch(url, {
        method: "PUT",
        headers: { ...headers, "Content-Type": "application/json" },
        body: JSON.stringify({ values: [values] }),
      })
      if (!response.ok) {
        throw new Error(`${response.status} ${await response.text()}`)
      }
      return {
        action: "updated",
        row_number: match + 1,
        ...(await response.json()),
      }
    }
  
    const url = new URL(`${base}/${encodeURIComponent(tab)}:append`)
    url.searchParams.append("valueInputOption", "USER_ENTERED")
    url.searchParams.append("insertDataOption", "INSERT_ROWS")
    const response = await fetch(url, {
      method: "POST",
      headers: { ...headers, "Content-Type": "application/json" },
      body: JSON.stringify({ values: [values] }),
    })
    if (!response.ok) {
      throw new Error(`${response.status} ${await response.text()}`)
    }
    const result = await response.json()
    return {
      action: "appended",
      row_number: Number(result.updates?.updatedRange?.match(/!\D+(\d+)/)?.[1]),
      ...result,
    }
  }
  

  Submitted by hugo989 2 hours ago