CODEWITHSUNDEEP
azuregoogle-sheetsgoogle-apps-scriptcustom-functionazure-openai
DavidG
DavidG

Reputation: 11

Google Sheet Apps Script custom function by Azure OpenAI API

I created a custom function in Google App Script to call the OpenAI API. It was functioning properly, but I am currently transitioning to the Azure OpenAI API service for improved performance and stability. However, I encountered issues when attempting to recode the Apps Script according to the Azure OpenAI instructions. The current version of the script is as follows:

const api_key = "azure openai api key1";  
const max_tokens = 500;  
  
function gpt(userprompt, userrole = "user", temperature = 1) {  
  
  const gptprompt = [{role:userrole,content:userprompt},  
  ];  
  
  const url = "azure openai endpoint";  
  const payload = {  
    engine:"gpt-35-turbo",  
    messages: gptprompt,
    temperature: temperature,  
    max_tokens: max_tokens,
  };  
  
  // Use 'method' property with value 'POST' instead of default 'GET' method  
  const options = {  
    method: "POST",  
    contentType: "application/json",  
    headers: {  Authorization: "Bearer " + api_key },  
    payload: JSON.stringify(payload),  
    muteHttpExceptions: true  
  };  
  
  try {  
    const response = UrlFetchApp.fetch(url, options);  
    console.log(response.getContentText());  
    const results = JSON.parse(response.getContentText());  
    console.log(results);  
    const text = results?.choices?.[0]?.message?.text?.trim() ?? "No text generated"; // Update this line to use 'choices' and 'message'  
    console.log(text);  
    return text;  
  } catch (error) {  
    // Log error message and return a custom error message  
    console.error("Error: ", error);  
    return "Error: Unable to fetch data.";  
  }  
}

but return error according to log:

6:52:23 AM Notice Execution started 

6:52:23 AM Info { "statusCode": 401, "message": "Unauthorized. Access token is missing, invalid, audience is incorrect (https://cognitiveservices.azure.com), or have expired." } 

6:52:23 AM Info { statusCode: 401, message: 'Unauthorized. Access token is missing, invalid, audience is incorrect (https://cognitiveservices.azure.com), or have expired.' } 

6:52:23 AM Info No text generated 6:52:24 AM Notice Execution completed

I have checked the API key and endpoint is work in python Am I missed some part in the apps script?

Upvotes: 1

Views: 713

Answers (1)

Duk Jin Lee
Duk Jin Lee

Reputation: 1

According to Azure guide, the header for API key has been changed.

So, try "api-key: YOUR_API_KEY"

instead of "Authorization: Bearer YOUR_API_KEY"

#Note: The openai-python library support for Azure OpenAI is in preview.

import os
import openai
openai.api_type = "azure"
openai.api_base = "YOUR_API_ENDPOINT"
openai.api_version = "2023-03-15-preview"
openai.api_key = os.getenv("OPENAI_API_KEY")

response = openai.ChatCompletion.create(
  engine="YOUR_MODEL_NAME_DEPLOYED",
  messages = [{"role":"system","content":"You are an AI assistant that helps people find information."},{"role":"user","content":"Hi"},{"role":"assistant","content":"Hi! What can I do for you?"}],
  temperature=0.7,
  max_tokens=800,
  top_p=0.95,
  frequency_penalty=0,
  presence_penalty=0,
  stop=None)

`

Upvotes: -1

Related Questions