Google Sheets에서 하이퍼클로바X API 활용하기: JSON 응답 자동 추출 및 데이터 관리

[CLOVA Studio 운영자]

 

최근 OpenAI가 'Structured Outputs'라는 새로운 기능을 선보였습니다. 이 기능은 개발자가 제공한 JSON 스키마에 정확히 맞는 모델 출력을 보장합니다.
이는 AI 모델의 출력을 구조화하고 특정 형식에 맞추는 것이 얼마나 중요한지를 잘 보여줍니다. 구조화된 데이터는 다양한 애플리케이션과 시스템에서 더 쉽게 처리되고 활용될 수 있기 때문이죠.

이러한 흐름에 맞춰, 이번 시간에는 Google Sheets를 활용하여 하이퍼클로바X의 구조화된 출력을 자동으로 추출하고 관리하는 방법을 알아보겠습니다.
이를 통해 원하는 데이터를 즉시 스프레드시트에 채울 수 있습니다. 구현 과정은 다음과 같습니다.

① 클로바 스튜디오에서 JSON 형식으로 출력시키는 프롬프트 작성
② Google Sheets App Script에서 API 연동 및 데이터 처리 함수 구현
③ Google Sheets에서 함수 연결 및 데이터 표시

 

JSON 응답

---

하이퍼클로바X를 활용하여 구조화된 데이터를 얻기 위해, JSON 형식으로 응답하도록 시스템 프롬프트를 설정합니다.
이 프롬프트는 요리 이름을 입력받아 재료 목록, 용량, 조리 방법을 정형화된 형식으로 출력합니다.

 

Apps Script 작성

---

Chat Completions API를 Google Sheets에 연동하기 위해 Apps Script를 활용합니다. Google Sheets 상단 메뉴에서 '확장 프로그램'을 클릭한 후
'Apps Script'를 선택하면 새 탭에서 Apps Script 편집기가 열립니다.

 

이제 Google Sheets Apps Script 편집기에서 실제 스크립트를 작성해 보겠습니다. API KEY와 API GW KEY는 클로바 스튜디오에서 생성한 인증 정보를 입력해 주세요.

function callCLOVAStudio(prompt) {
  var apiKey = '{API_KEY}'; // API Key를 입력합니다.
  var apigwKey = '{APIGW_KEY}'; // APIGW API Key를 입력합니다.
  var requestId = Utilities.getUuid(); // request ID입니다.
  
  // 프롬프트는 문자열 형식으로 작성해주세요.
  var userPrompt = prompt ? prompt : " "; // 프롬프트가 null이거나 비어있을 경우 공백으로 기본 설정

  var data = {
    "messages": [
      {"role": "system", "content": "- 요리를 입력하면 json 형식으로 응답합니다.\n- 요리에 들어가는 재료, 용량, 요리 방법을 알려줍니다.\n- json 응답 구조는 아래와 같습니다.\n{\n  \"recipe\": {\n    \"name\": \"김치찌개\",\n    \"ingredients\": [\n      {\"item\": \"김치\", \"amount\": \"300g\"},\n      {\"item\": \"돼지고기\", \"amount\": \"200g\"},\n      {\"item\": \"두부\", \"amount\": \"1모\"}\n    ],\n    \"instructions\": [\n      \"김치를 적당한 크기로 자른다.\",\n      \"돼지고기를 볶는다.\",\n      \"물을 붓고 김치를 넣어 끓인다.\",\n      \"두부를 넣고 마저 끓인다.\"\n    ]\n  }\n}\n\n"},
      {"role": "user", "content": userPrompt}
    ],
    "topP": 0.8,
    "topK": 0,
    "maxTokens": 100,
    "temperature": 0.8,
    "repeatPenalty": 5.0,
    "stopBefore": [],
    "includeAiFilters": true,
    "seed": 0
  };
 
  var options = {
    'method': 'post',
    'contentType': 'application/json',
    'payload': JSON.stringify(data),
    'headers': {
      'X-NCP-CLOVASTUDIO-API-KEY': apiKey,
      'X-NCP-APIGW-API-KEY': apigwKey,
      'X-NCP-CLOVASTUDIO-REQUEST-ID': requestId,
      'Accept': 'application/json'
    }
  };
 
  var response = UrlFetchApp.fetch('https://clovastudio.stream.ntruss.com/testapp/v1/chat-completions/HCX-003', options);
  var responseJson = JSON.parse(response.getContentText());
  return responseJson.result.message.content;
}

function parseIngredients(jsonString) {
  try {
    // JSON 문자열을 정리합니다.
    jsonString = cleanJsonString(jsonString);
    var data = JSON.parse(jsonString);
    var ingredients = data.recipe.ingredients;
    return ingredients.map(function(ing) {
      return ing.item + ': ' + ing.amount;
    }).join('\n');
  } catch (e) {
    // JSON 파싱에 실패한 경우, 텍스트에서 재료 정보 추출 시도
    var ingredientsMatch = jsonString.match(/"ingredients":\s*\[([\s\S]*?)\]/);
    if (ingredientsMatch) {
      var ingredientsText = ingredientsMatch[1];
      var ingredients = ingredientsText.match(/\{[^}]+\}/g);
      if (ingredients) {
        return ingredients.map(function(ing) {
          var item = ing.match(/"item":\s*"([^"]+)"/);
          var amount = ing.match(/"amount":\s*"([^"]+)"/);
          return (item ? item[1] : 'Unknown') + ': ' + (amount ? amount[1] : 'Unknown');
        }).join('\n');
      }
    }
    return "재료를 추출할 수 없습니다: " + e.message;
  }
}

function parseInstructions(jsonString) {
  try {
    // JSON 문자열 정리
    jsonString = cleanJsonString(jsonString);
    var data = JSON.parse(jsonString);
    return data.recipe.instructions.join('\n');
  } catch (e) {
    // JSON 파싱에 실패한 경우, 텍스트에서 요리 순서 정보 추출 시도
    var instructionsMatch = jsonString.match(/"instructions":\s*\[([\s\S]*?)(?:\]|$)/);
    if (instructionsMatch) {
      var instructionsText = instructionsMatch[1];
      // 쌍따옴표로 둘러싸인 문자열을 찾되, 쌍따옴표가 없는 경우도 포함
      var instructions = instructionsText.match(/(?:"([^"]+)")|([^,]+)/g);
      if (instructions) {
        return instructions.map(function(inst) {
          // 쌍따옴표와 앞뒤 공백 제거
          return inst.replace(/^["'\s]+|["'\s]+$/g, '');
        }).filter(Boolean).join('\n');
      }
    }
    // 위 방법으로도 실패한 경우, 단순히 "instructions" 이후의 모든 텍스트를 반환
    var lastInstructionsIndex = jsonString.lastIndexOf('"instructions":');
    if (lastInstructionsIndex !== -1) {
      return jsonString.slice(lastInstructionsIndex + 15).replace(/^\s*\[|\].*$/g, '').trim();
    }
    return "요리 순서를 추출할 수 없습니다: " + e.message;
  }
}

function cleanJsonString(jsonString) {
  // JSON 문자열에서 줄바꿈, 따옴표 등을 정리
  return jsonString.replace(/[\n\r]/g, ' ')
                   .replace(/\s+/g, ' ')
                   .replace(/\\"/g, '"')
                   .replace(/"{/g, '{')
                   .replace(/}"/g, '}')
                   .trim();
}

 

스크립트 작성이 끝나면 상단의 저장 버튼을 눌러 변경사항을 저장해 주세요.

 

저장 후에는 '실행' 버튼을 클릭하여 스크립트가 정상적으로 동작하는지 확인할 수 있습니다. 아래와 같이 출력된다면 문제없이 정상 출력되는 것을 의미합니다.
처음 실행할 때 권한 요청 팝업이 나타날 수 있는데, '허용'을 클릭하여 필요한 권한을 부여해 주세요.

 

Google Sheets 실행

---

Google Sheets에서 하이퍼클로바X API를 활용하여 레시피 정보를 자동으로 불러오는 과정을 살펴보겠습니다. 먼저 스프레드시트를 다음과 같이 구성해 주세요.

• A 열: '요리' - 조리법을 알고 싶은 요리 이름을 입력합니다.
• B 열: '재료' - API에서 받아온 재료 정보가 채워질 열입니다.
• C 열: '요리 순서' - API에서 받아온 조리 방법이 채워질 열입니다.
• D 열: '출력 결과' - API 호출 결과를 직접 확인할 수 있는 열입니다.

 

이제 Google Sheets에서 각 요리에 대한 정보를 일괄적으로 불러와 보겠습니다. '출력 결과' 열에 API 호출 결과를 표시하기 위해 첫 번째 셀(예: D2)에 다음 수식을 입력합니다: =callCLOVAStudio(A2)
수식을 입력하고 나면, 잠시 후 API 응답이 셀에 표시됩니다. 이 응답은 JSON 형식의 데이터로 요리의 이름, 재료 목록, 조리 방법 등의 정보를 포함하고 있습니다.

 

이 수식을 아래로 드래그하여 복사하면 A 열에 나열된 모든 요리에 대해 자동으로 API를 호출하고 결과를 표시할 수 있습니다. 이를 통해 여러 요리의 정보를 한 번에 얻을 수 있어 효율적인 레시피 데이터베이스 구축이 가능해집니다.

 

• 클로바 스튜디오는 안정적인 서비스 운영을 위해 이용량 제어 정책을 시행하고 있습니다. CLOVA Studio 이용량 제어 정책
• 이용량이 설정된 최대치를 초과할 경우 서비스 이용 정책에 따라 429 응답을 받을 수 있으며 이 경우 다시 시도해 주시기를 바랍니다.
• 프롬프트의 길이가 길거나, Maximum Tokens가 과도하게 높게 설정된 경우 이용량 초과가 발생할 수 있으니 유의해 주세요.

 

A 열에 있는 모든 요리에 대한 API 호출 결과가 '출력 결과' 열에 성공적으로 생성되었습니다.

 

이제 이 결과를 효율적으로 관리하고 불필요한 API 호출을 방지하기 위해 '출력 결과' 열의 내용을 복사하여 새로운 열에
'값만 붙여넣기'(단축키: cmd+shift+v/ctrl+shift+v)를 합니다. 이후 원래의 '출력 결과' 열에서 API 호출 함수를 삭제해 주세요.
이 작업을 하지 않는 경우, Google Sheets에 사용자가 접근할때마다 API가 호출될 수 있습니다.

 

JSON 형식 발라내기

---

JSON 형식의 API 응답에서 필요한 정보를 추출해 보겠습니다. '재료' 열의 첫 셀에 =parseIngredients(D2)를 입력하고,
'요리 순서' 열의 첫 셀에 =parseInstructions(D2)를 입력합니다. 이 함수들은 D2 셀에 있는 JSON 형식의 출력 결과에서 각각 재료와 요리 순서 정보만을 추출하여 표시합니다.

 

JSON 문자열의 줄바꿈, 따옴표 등을 정리하는 로직이 적용되어 있지만 preprocessing 규칙은 작업과 출력 결과에 따라 다를 수 있으므로 필요에 따라 수정해야 할 수 있습니다.

function cleanJsonString(jsonString) {
  // JSON 문자열에서 줄바꿈, 따옴표 등을 정리
  return jsonString.replace(/[\\n\\r]/g, ' ')
                   .replace(/\\s+/g, ' ')
                   .replace(/\\\\"/g, '"')
                   .replace(/"{/g, '{')
                   .replace(/}"/g, '}')
                   .trim();
}

 

• .replace(/[\\n\\r]/g, ' '): 줄 바꿈 문자를 공백으로 치환합니다.
• .replace(/\\s+/g, ' '): 연속된 공백을 하나의 공백으로 치환합니다.
• .replace(/\\\\"/g, '"'): 이스케이프 처리된 따옴표(")를 일반 따옴표(")로 치환합니다.
• .replace(/"{/g, '{'): 따옴표와 중괄호가 연속된 경우 따옴표('"')를 제거하고 중괄호('{')만 남깁니다.
• .replace(/}"/g, '}'): 따옴표와 중괄호가 연속된 경우 따옴표('"')를 제거하고 중괄호('}')만 남깁니다.
• .trim(): 앞뒤 공백을 제거합니다.

 

API 호출과 결과 데이터 추출 자동화하기

---

이제 입력값을 넣으면 API 호출과 데이터 추출을 한 번에 수행하는 방법을 살펴보겠습니다. 먼저 Apps Script에 두 가지 함수를 추가해야 합니다.
첫 번째 함수는 getRecipeIngredients(dish)로, 요리 이름을 입력하면 API를 호출하고 결과를 추출하여 재료 정보만 반환합니다.
두 번째 함수는 getRecipeInstructions(dish)로 요리 이름을 입력하면 API를 호출하고 결과를 파싱하여 요리 순서 정보만 반환합니다.

function getRecipeIngredients(dish) {
  var apiResponse = callCLOVAStudio(dish);
  return parseIngredients(apiResponse);
}

function getRecipeInstructions(dish) {
  var apiResponse = callCLOVAStudio(dish);
  return parseInstructions(apiResponse);
}

 

그런 다음 스프레드시트에서 '재료' 열에 =getRecipeIngredients(A2)를 입력하고 '요리 순서' 열에 =getRecipeInstructions(A2)를 입력합니다.
이 방법을 사용하면 '출력 결과' 열을 따로 만들 필요가 없습니다. 수식을 아래로 드래그하면 A 열에 있는 각 요리에 대한 재료와 요리 순서가 자동으로 추출되어 표시됩니다.

 

이 방법을 통해 여러 요리의 재료와 조리 방법을 한 번에 자동으로 생성할 수 있게 되었습니다. API 호출과 데이터 추출 과정이 단순화되어,
스프레드시트에서 요리 이름만 입력하면 관련 정보가 즉시 채워집니다. 이로써 대량의 레시피 정보를 빠르고 효율적으로 관리할 수 있게 되었고, 스프레드시트의 활용도가 크게 향상되었습니다.

 

여러 열을 묶어서 인풋 구성하기

---

CONCATENATE 함수를 사용하면 여러 열에 있는 내용을 하나의 셀에 합칠 수 있습니다.
예를 들어 각각 다른 셀에 있는 '두부', '감자', '된장'을 "재료: 두부, 감자, 된장"과 같은 형태로 하나의 셀에 모아 모델 입력으로 사용할 수 있습니다. 여러 열의 정보를 구조화된 형태로 결합하여 모델에 전달할 수 있어 유용합니다.

이제 아래 함수를 스프레드시트의 적절한 셀에 입력합니다.

=CONCATENATE("재료: ", A2, ", ", B2, ", ", C2)

이 함수는 A2, B2, C2 셀에 있는 재료들을 하나의 문자열로 결합합니다. 결과적으로 "재료: [A2의 내용], [B2의 내용], [C2의 내용]" 형태의 텍스트가 생성됩니다.

 

Google Sheets는 다양한 함수를 제공하여 데이터 처리와 분석을 더욱 효율적으로 할 수 있습니다.
제공되는 함수들을 활용하면 스프레드시트의 활용도를 높일 수 있습니다. 구글 앱 스크립트에 대한 자세한 내용은 다음 링크를 참고해 주세요. https://developers.google.com/apps-script

 

하이퍼클로바 X는 여러 분야에 적용하고 활용할 수 있는 잠재력이 풍부하며 앞으로 더 많은 혁신적인 응용 사례들이 등장할 것으로 기대됩니다.