Edits history of script submission #22815 for ' Execute SuiteQL (netsuite)'

• bunnative

  Ap­pro­ved version

  Edit in Windmill

  Embed

  //native
  
  const percentEncode = (s: string) =>
    encodeURIComponent(s).replace(
      /[!'()*]/g,
      (c) => "%" + c.charCodeAt(0).toString(16).toUpperCase()
    )
  
  function restBase(auth: RT.Netsuite) {
    return `https://${auth.account_id.trim().toLowerCase()}.suitetalk.api.netsuite.com/services/rest`
  }
  
  // Bearer when an OAuth 2.0 token is set, otherwise TBA (OAuth 1.0a HMAC-SHA256).
  async function authHeader(auth: RT.Netsuite, method: string, url: URL) {
    if (auth.token !== undefined && auth.token !== "") {
      return `Bearer ${auth.token}`
    }
    // Re-serialize the query with %20 instead of '+' for spaces so the URL on
    // the wire matches the form the signature base string encodes
    url.search = [...url.searchParams.entries()]
      .map(([k, v]) => `${percentEncode(k)}=${percentEncode(v)}`)
      .join("&")
    const nonce = Array.from(crypto.getRandomValues(new Uint8Array(16)), (b) =>
      b.toString(16).padStart(2, "0")
    ).join("")
    const timestamp = Math.floor(Date.now() / 1000).toString()
    const oauthParams: [string, string][] = [
      ["oauth_consumer_key", auth.consumer_key ?? ""],
      ["oauth_nonce", nonce],
      ["oauth_signature_method", "HMAC-SHA256"],
      ["oauth_timestamp", timestamp],
      ["oauth_token", auth.token_id ?? ""],
      ["oauth_version", "1.0"],
    ]
    const sortedParams = [...url.searchParams.entries(), ...oauthParams]
      .map(([k, v]) => [percentEncode(k), percentEncode(v)])
      .sort(([ak, av], [bk, bv]) =>
        ak === bk ? (av < bv ? -1 : 1) : ak < bk ? -1 : 1
      )
      .map(([k, v]) => `${k}=${v}`)
      .join("&")
    const baseString = [
      method.toUpperCase(),
      percentEncode(`${url.protocol}//${url.host}${url.pathname}`),
      percentEncode(sortedParams),
    ].join("&")
    const key = await crypto.subtle.importKey(
      "raw",
      new TextEncoder().encode(
        `${percentEncode(auth.consumer_secret ?? "")}&${percentEncode(auth.token_secret ?? "")}`
      ),
      { name: "HMAC", hash: "SHA-256" },
      false,
      ["sign"]
    )
    const signature = btoa(
      String.fromCharCode(
        ...new Uint8Array(
          await crypto.subtle.sign(
            "HMAC",
            key,
            new TextEncoder().encode(baseString)
          )
        )
      )
    )
    const realm = auth.account_id.trim().toUpperCase().replace(/-/g, "_")
    return `OAuth realm="${realm}", oauth_consumer_key="${percentEncode(auth.consumer_key ?? "")}", oauth_token="${percentEncode(auth.token_id ?? "")}", oauth_signature_method="HMAC-SHA256", oauth_timestamp="${timestamp}", oauth_nonce="${nonce}", oauth_version="1.0", oauth_signature="${percentEncode(signature)}"`
  }
  
  /**
   * Execute SuiteQL
   * Run a SuiteQL query (NetSuite's SQL — e.g. SELECT id, entityid, email FROM customer WHERE email LIKE '%@example.com'). Returns one page of up to limit rows (max 1000); page with offset and the hasMore/totalResults response fields.
   */
  export async function main(
    auth: RT.Netsuite,
    query: string,
    limit: number | undefined,
    offset: number | undefined
  ) {
    const url = new URL(`${restBase(auth)}/query/v1/suiteql`)
    if (limit !== undefined) {
      url.searchParams.append("limit", String(limit))
    }
    if (offset !== undefined) {
      url.searchParams.append("offset", String(offset))
    }
    const response = await fetch(url, {
      method: "POST",
      headers: {
        Authorization: await authHeader(auth, "POST", url),
        Prefer: "transient",
        "Content-Type": "application/json",
        Accept: "application/json",
      },
      body: JSON.stringify({ q: query }),
    })
    if (!response.ok) {
      throw new Error(`${response.status} ${await response.text()}`)
    }
    return await response.json()
  }
  

  Submitted by hugo989 5 hours ago