나는 유튜브에서 자동화 관련 영상을 자주 찾아보곤 한다. 그만큼 게으르다는 성격의 방증이다. 그 중 다음에 꼭 써봐야지 하고 저장해두었던 영상이 구글 시트에서 GPT함수를 가져와 시트의 내용을 자동화 하는 것이었다.

이번에 영어 글쓰기 수행평가를 시작하면서 이 기능을 꼭 써봐야겠다고 생각했다. 그러기 위해서는 핸드라이팅 답안을 사용해서는 안된다. 이번 수행평가의 초안을 타이핑으로만 받아 자료 수집이 완료되었다.

그리고 저장해둔 영상을 따라 설정을 하다 보니 GPT4 for sheets and docs라는 확장 프로그램이 유료화 되었다고 한다. 낭패다. OPENAI에 gpt-4 API사용료는 지불할 용의가 있었지만, 사용료에 더불어 확장프로그램에까지 돈을 주고싶은 마음은 추호도 없었다. 그래서 앱스크립트를 사용해 직접 자동화를 하기로 했다.

전체 구상

GPT ASSISTANT에서 수행평가 채점에 특화된 ASSISTANT를 만든다.
구글시트에 학생 답안을 정리한다.
학생의 초안을 assistant에 보낸다.
Assistant가 보내준 답을 학생의 초안 바로 옆 셀에 쓴다.
c-d과정을 반복한다.

나의 착각

Api를 통해 프롬프트를 보내면 바로 gpt의 응답이 수신 될거라 생각했다.

뤼튼에 다음과 같은 명령을 내렸다.

구글 앱스크립트에서 gpt assistant로 프롬프트를 보내고 그 답을 받아 구글 시트에 작성하고 싶어. 전송할 프롬프트는 b열에 나열되어있고, assistant의 응답은 c열에 기록하면 돼

응답은 다음과 같았다.

function sendPromptToGPTAssistant() {

  var sheet = SpreadsheetApp.getActiveSheet();

  var promptRange = sheet.getRange('B2:B');

  var prompts = promptRange.getValues();

  for (var i = 0; i < prompts.length; i++) {

    var prompt = prompts[i][0];

    var gptResponse = sendPromptAndGetResponse(prompt);

    sheet.getRange('C' + (i+2)).setValue(gptResponse);

  }

}

function sendPromptAndGetResponse(prompt) {

  var gptEndpoint = 'YOUR_GPT_ASSISTANT_API_ENDPOINT';

  var apiKey = 'YOUR_API_KEY';

  var requestData = {

    prompt: prompt,

    api_key: apiKey

  };

  var options = {

    method: 'post',

    contentType: 'application/json',

    payload: JSON.stringify(requestData)

  };

  var response = UrlFetchApp.fetch(gptEndpoint, options);

  var gptResponseData = JSON.parse(response.getContentText());

  var gptResponse = gptResponseData.response;

  return gptResponse;

}

YOUR_GPT_ASSISTANT_API_ENDPOINT 여기에 어시스턴트 api만 넣으면 해결될 줄 알았는데 전혀 아니었다. 어시스턴트의 출현이 gpt-4의 학습 데이터 이후이기 때문에 당연한 결과이기도 하다. 결국 예전 방식으로 구글링을 시작했다. 스택오버플로에서 나와 똑같은 생각을 한 사람의 질문을 찾았고, 다행히도 좋은 답변이 달려있었다.

스택오버플로우 링크

한번에 완료되는 것이 아니라 스텝을 단계적으로 거쳐야 내가 원하는 결과를 얻을 수 있는 것이었다. 그도 그럴 것이 gpt가 한번에 답을 주는 경우는 없다. chat gpt를 사용해 본 사람은 알겠지만 원하는 답변을 출력하는데 걸리는 시간이 상당하다. 이 과정이 요청-응답의 몇 밀리초 사이에 완료될 리가 없다.

답변을 남겨주신 Rok Benko님께 감사를 드리며 위 과정을 나의 작업에 맞게 정리해보겠다.

STEP 1: 어시스턴트 만들기

이미 채점용 어시스턴트를 만들어두었기 때문에 이 작업을 할 필요는 없다. Assistant의 아이디만 따오면 그만이다.

STEP 2: 스레드 만들기

Gpt에서의 스레드는 gpt와의 대화 세션에 해당한다. 나는 각 학생별로 새로 스레드를 만들어야 하기 때문에 여기부터 작업을 하면 된다. 공식 문서 링크로 들어가보면 아래와 같은 예제가 있다.

앱스스크립트용 코드가 필요하기 때문에 뤼튼에 앱스스크립트용으로 바꿔달라고 요청한다.

function callOpenAIAssistantThread() {

  var url = '[https://api.openai.com/v1/threads](https://api.openai.com/v1/threads)';

  var payload = JSON.stringify({

  });

  var options = {

    'method': 'post',

    'contentType': 'application/json',

    'headers': {

      'Authorization': 'Bearer ' + OPENAI_API_KEY, // 해당 키는 코드파일 최상단에 전역 변수로 정의해두었다.

      'OpenAI-Beta': 'assistants=v2'

    },

    'payload': payload,

    'muteHttpExceptions': true

  };

  try {

    var response = UrlFetchApp.fetch(url, options);

    var json = response.getContentText();

    var data = JSON.parse(json);

  } catch (error) {

    Logger.log('Error: ' + error.toString());

  }

  return data.id; //뤼튼이 써준 예시는 data전체 를 출력하는 것이었다. 나는 스레드 아이디를 써야해서 이를 리턴하도록 수정하였다.

}

위와 같은 함수를 만들어준다. 해당 코드를 실행하면 스레드를 생성하고 스레드의 아이디를 반환한다.

STEP 3: 스레드에 메시지 첨부하기

정말 이해되지 않는 과정이지만, 공식문서에 따르면 메시지를 넣는 과정과 어시스턴트 구동 과정이 분리되어 있다.

일단 이 스텝에서는 스레드에 메시지를 추가해준다. 시트에서 데이터를 불러올 거라 파라미터에 msg를 넣었다.

function addMessageToThread(threadId, msg) {

  // API 키와 스레드 ID 설정

  var threadId = threadId;

  // API 엔드포인트 구성

  var url = '[https://api.openai.com/v1/threads/](https://api.openai.com/v1/threads/)' + threadId + '/messages';

  // 요청 헤더 설정

  var headers = {

    "Content-Type": "application/json",

    "Authorization": "Bearer " + OPENAI_API_KEY,

    "OpenAI-Beta": "assistants=v2"

  };

  // 요청 본문 데이터 설정

  var payload = JSON.stringify({

    "role": "user",

    "content": msg  //파라미터  메시지는 여기에 들어온다.

  });

  // 요청 옵션 구성

  var options = {

    "method": "post",

    "headers": headers,

    "payload": payload,

    "muteHttpExceptions": true

  };

  // API 요청 수행

  var response = UrlFetchApp.fetch(url, options);

  // 응답 로그 출력

}

STEP 4: 어시스턴트 구동

function createThreadRun(threadId) {

  var threadId = threadId; // 스레드 ID를 실제 값으로 교체하세요.

  var url = '[https://api.openai.com/v1/threads/](https://api.openai.com/v1/threads/)' + threadId + '/runs';

  var payload = JSON.stringify({

    "assistant_id": ASSISTANT_ID // 어시스턴트 ID를 실제 값으로 교체하세요.

  });

  var options = {

    'method': 'post',

    'contentType': 'application/json',

    'headers': {

      'Authorization': 'Bearer ' + OPENAI_API_KEY,

      'OpenAI-Beta': 'assistants=v2'

    },

    'payload': payload,

    'muteHttpExceptions': true

  };

  try {

    var response = UrlFetchApp.fetch(url, options);

    var json = response.getContentText();

    var data = JSON.parse(json);

  } catch (error) {

    Logger.log('Error: ' + error.toString());

  }

  return data.id;

}

스레드 아이디를 넣고 해당 함수를 돌리면 data의 id가 반환된다.

STEP 5: 스레드 상태 확인

어시스턴트가 응답을 완성하기까지 시간이 조금 걸린다. 응답이 완료되지 않은 상태에서 메시지를 가져와 봤자 얻을 수 있는 것이 없기 때문에 스레드의 상태를 주기적으로 확인해줘야한다.

function getThreadRun(threadId, runId) {

  var threadId = threadId; // 실제 스레드 ID로 교체하세요.

  var runId = runId; // 실제 실행 ID로 교체하세요.

  var url = 'https://api.openai.com/v1/threads/' + threadId + '/runs/' + runId;

  var options = {

    'method': 'get',

    'headers': {

      'Authorization': 'Bearer ' + OPENAI_API_KEY,

      'OpenAI-Beta': 'assistants=v2'

    },

    'muteHttpExceptions': true

  };

  try {

    var response = UrlFetchApp.fetch(url, options);

    var json = response.getContentText();

    var data = JSON.parse(json);

  } catch (error) {

    Logger.log('Error: ' + error.toString());

  }

  return data.status;

}

위 함수를 구동하면 해당 스레드의 상태가 출력된다. pending에서 complete가 될때까지 반복문으로 요청을 보내면 된다.

STEP 6: 메시지 가져오기

ok 사인이 떨어지면 바로 아래 함수를 구동한다.

function getThreadMessages(threadId) {

  var threadId = threadId; // 실제 스레드 ID로 교체하세요.

  var url = 'https://api.openai.com/v1/threads/' + threadId + '/messages';

  var options = {

    'method': 'get', // HTTP 메서드 설정

    'contentType': 'application/json', // 컨텐츠 타입 설정

    'headers': { // 요청 헤더 설정

      'Authorization': 'Bearer ' + OPENAI_API_KEY,

      'OpenAI-Beta': 'assistants=v2'

    },

    'muteHttpExceptions': true // HTTP 예외 발생 시 자세한 정보를 로그로 확인할 수 있게 함

  };

  try {

    var response = UrlFetchApp.fetch(url, options); // API 호출

    var json = response.getContentText(); // 응답 내용을 텍스트로 변환

    var data = JSON.parse(json); // 텍스트를 JSON 객체로 변환

  } catch (error) {

    Logger.log('Error: ' + error.toString()); // 에러 로그 출력

  }

  return data;

}

위 함수에서 반환되는 결과는 스레드 속 모든 대화내역이다. 가장 최근에 생성된 어시스턴트의 응답을 추출해야하기 때문에 아래와 같은 함수를 하나 더 만들었다.

STEP 7: 어시스턴트 메시지 추출하기

function resultParser(data){

// 데이터 배열을 순회하면서 'assistant' 역할의 메시지 검색

var assistantMessages = data.data.filter(function(item) {

  return item.role === 'assistant';

});

// 'assistant' 역할의 첫 번째 메시지에서 'text value' 추출

if (assistantMessages.length > 0) {

  var textValue = assistantMessages[0].content[0].text.value;

  return textValue; 

} else {

  console.log('Assistant 메시지를 찾을 수 없습니다.');

}

STEP 8: 모든 함수를 조립하기

아래 함수 useAssistant에 학생의 초안을 넣으면 알고리즘에 따라 작업을 수행한 뒤 결과물인 피드백을 반환한다.

function useAssistant(msg){ //파라미터 msg로 학생의 초안이 들어옵니다.

  var threadid = callOpenAIAssistantThread(); //스레드 함수로  threadid변수에 아이디를 저장합니다.

  addMessageToThread(threadid, "다음 학생의 글을 인스트럭션에 따라 피드백 해줘"+msg); // 저는 메시지에 "다음 학생의 글을 인스트럭션에 따라 피드백 해줘" 라는 단서를 덧붙였습니다. 그래야 결과가 좀 더 낫더군요.

  var runid = createThreadRun(threadid); //메시지가 추가된 스레드를 구동합니다.

  var status = getThreadRun(threadid, runid); //스레드의 상태를 얻어옵니다.

  while(status != "completed"){ // 스레드 상태가 completed로 변할 때 까지 2초간격으로 요청을 보냅니다.

    status = getThreadRun(threadid, runid);

    Utilities.sleep(2000);

  }

  var finalData = getThreadMessages(threadid); //완료가 되었으니 데이터를 finalData에 저장합ㄴ디ㅏ.

  return resultParser(finalData); //어시스턴트의 대답만 추려서 반환합니다.

}

STEP 9: 시트에 적용하는 진짜 최종 함수 만들기

시트에 데이터를 정리하고, 반복문을 통해 여러 행의 학생 초안을 검토할 수 있다. 이를 위해 내 시트의 구조에 맞게 아래 함수를 만들어보았다.

function mainaction() {

  // 스프레드시트와 활성 시트를 가져옵니다.

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // D열의 데이터 범위를 가져옵니다. 학생의 초안을 D열에 저장해두었습니다.

  var lastRow = sheet.getLastRow();

  // 각 행에 대해 반복문을 실행합니다.

  for (var i = 2; i <= lastRow; i++) { //1행에는 제목이 있기 때문에 2행부터 시작합니다.

    // D열에서 현재 행의 값을 가져옵니다.

    var currentValue = sheet.getRange(i, 4).getValue(); // 4번째 열은 D열임

    if(currentValue !== ''){ //내용이 있을 때만 작업을 시작합니다. api요금 아껴야해요.

      var newValue = useAssistant(currentValue); // 학생 초안 셀에 값이 있으면 우리가 만든 피드백 함수를 구동합니다. 생성된 피드백은 newValue라는 변수에 저장됩니다.

      // E열의 같은 행에 새 값을 설정합니다.

      sheet.getRange(i, 5).setValue(newValue); // 5번째 열은 E열임, newValue를 E열에 기록합니다.

    }

  }

}

STEP 10: 실행하기

데이터가 있는 시트에서 확장프로그램 -> 앱스스크립트를 선택합니다.

함수가 mainaction으로 설정되어있는지 확인 후 run을 눌러 스크립트를 실행합니다.

위와 같은 시트에서 스크립트를 구동하면 아래와 같이 초안 옆에 피드백이 생성됩니다.

마치며

약 170개 정도의 초안을 검토하면서 약 10달러 정도의 요금이 차감되었다. 많다면 많고 적다면 적을 수 있는 금액이지만, 답변의 퀄리티와 늘어난 내 여유시간을 생각하면 아깝지는 않은 금액이다.

단 gpt가 만능은 아닌지라 간단히 재검토 정도는 해줘야한다. 피드백을 모두 영어로 주는 경우도 있고, 지나치게 어려운 문장을 제시하는 경우도 있기 때문이다. 직접 해보고싶은 분들을 위해 코드 전문과 스크립트가 적용된 시트를 공유한다.

코드 전문

var OPENAI_API_KEY = '따옴표 속에 본인의 openai api키';
var ASSISTANT_ID = '따옴표 속에 본인의 assistant id';


function callOpenAIAssistantThread() {

  
  var url = 'https://api.openai.com/v1/threads';
  var payload = JSON.stringify({
 
  });
  
  var options = {
    'method': 'post',
    'contentType': 'application/json',
    'headers': {
      'Authorization': 'Bearer ' + OPENAI_API_KEY,
      'OpenAI-Beta': 'assistants=v2'
    },
    'payload': payload,
    'muteHttpExceptions': true // 에러 발생 시도 자세한 정보를 로그로 확인할 수 있게 함
  };
  
  try {
    var response = UrlFetchApp.fetch(url, options);
    var json = response.getContentText();
    var data = JSON.parse(json);

  } catch (error) {
    Logger.log('Error: ' + error.toString());
  }
  return data.id;
}

function addMessageToThread(threadId, msg) {
  // API 키와 스레드 ID 설정

  var threadId = threadId;

  // API 엔드포인트 구성
  var url = 'https://api.openai.com/v1/threads/' + threadId + '/messages';

  // 요청 헤더 설정
  var headers = {
    "Content-Type": "application/json",
    "Authorization": "Bearer " + OPENAI_API_KEY,
    "OpenAI-Beta": "assistants=v2"
  };

  // 요청 본문 데이터 설정
  var payload = JSON.stringify({
    "role": "user",
    "content": msg
  });

  // 요청 옵션 구성
  var options = {
    "method": "post",
    "headers": headers,
    "payload": payload,
    "muteHttpExceptions": true
  };

  // API 요청 수행
  var response = UrlFetchApp.fetch(url, options);

  // 응답 로그 출력

}

function createThreadRun(threadId) {

  var threadId = threadId; // 스레드 ID를 실제 값으로 교체하세요.
  var url = 'https://api.openai.com/v1/threads/' + threadId + '/runs';
  
  var payload = JSON.stringify({
    "assistant_id": ASSISTANT_ID // 어시스턴트 ID를 실제 값으로 교체하세요.
  });
  
  var options = {
    'method': 'post',
    'contentType': 'application/json',
    'headers': {
      'Authorization': 'Bearer ' + OPENAI_API_KEY,
      'OpenAI-Beta': 'assistants=v2'
    },
    'payload': payload,
    'muteHttpExceptions': true
  };
  
  try {
    var response = UrlFetchApp.fetch(url, options);
    var json = response.getContentText();
    var data = JSON.parse(json);

  } catch (error) {
    Logger.log('Error: ' + error.toString());
  }
  return data.id;
}

function getThreadRun(threadId, runId) {

  var threadId = threadId; // 실제 스레드 ID로 교체하세요.
  var runId = runId; // 실제 실행 ID로 교체하세요.
  var url = 'https://api.openai.com/v1/threads/' + threadId + '/runs/' + runId;
  
  var options = {
    'method': 'get',
    'headers': {
      'Authorization': 'Bearer ' + OPENAI_API_KEY,
      'OpenAI-Beta': 'assistants=v2'
    },
    'muteHttpExceptions': true
  };
  
  try {
    var response = UrlFetchApp.fetch(url, options);
    var json = response.getContentText();
    var data = JSON.parse(json);
 
  } catch (error) {
    Logger.log('Error: ' + error.toString());
  }
  return data.status;
}

function getThreadMessages(threadId) {

  var threadId = threadId; // 실제 스레드 ID로 교체하세요.
  var url = 'https://api.openai.com/v1/threads/' + threadId + '/messages';
  
  var options = {
    'method': 'get', // HTTP 메서드 설정
    'contentType': 'application/json', // 컨텐츠 타입 설정
    'headers': { // 요청 헤더 설정
      'Authorization': 'Bearer ' + OPENAI_API_KEY,
      'OpenAI-Beta': 'assistants=v2'
    },
    'muteHttpExceptions': true // HTTP 예외 발생 시 자세한 정보를 로그로 확인할 수 있게 함
  };
  
  try {
    var response = UrlFetchApp.fetch(url, options); // API 호출
    var json = response.getContentText(); // 응답 내용을 텍스트로 변환
    var data = JSON.parse(json); // 텍스트를 JSON 객체로 변환

  } catch (error) {
    Logger.log('Error: ' + error.toString()); // 에러 로그 출력
  }
  return data;
}

function resultParser(data){


// 데이터 배열을 순회하면서 'assistant' 역할의 메시지 검색
var assistantMessages = data.data.filter(function(item) {
  return item.role === 'assistant';
});

// 'assistant' 역할의 첫 번째 메시지에서 'text value' 추출
if (assistantMessages.length > 0) {
  var textValue = assistantMessages[0].content[0].text.value;
  return textValue; // 출력: Hello
} else {
  console.log('Assistant 메시지를 찾을 수 없습니다.');
}
}

function mainaction() {
  // 스프레드시트와 활성 시트를 가져옵니다.
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // D열의 데이터 범위를 가져옵니다.
  var lastRow = sheet.getLastRow();
  
  // 각 행에 대해 반복문을 실행합니다.
  for (var i = 2; i <= lastRow; i++) {
    // D열에서 현재 행의 값을 가져옵니다.
    var currentValue = sheet.getRange(i, 4).getValue(); // 4번째 열은 D열임
    
    // 값이 숫자인 경우에만 1을 더합니다.
    if(currentValue !== ''){
      var newValue = useAssistant(currentValue);
      
      // E열의 같은 행에 새 값을 설정합니다.
      sheet.getRange(i, 5).setValue(newValue); // 5번째 열은 E열임
    }

    
  }
}




function useAssistant(msg){
  var threadid = callOpenAIAssistantThread();
  addMessageToThread(threadid, "다음 학생의 글을 인스트럭션에 따라 피드백 해줘"+msg);
  var runid = createThreadRun(threadid);
  var status = getThreadRun(threadid, runid);
  while(status != "completed"){
    status = getThreadRun(threadid, runid);
    Utilities.sleep(2000); 
  }
  var finalData = getThreadMessages(threadid);

  return resultParser(finalData);
}

구글 시트 공유

https://docs.google.com/spreadsheets/d/1jeW54Za0FlQthncmyk2FjqQor9I2bEDZbOLJb_K6HN0/edit?usp=sharing

스크립트가 담긴 시트를 링크를 통해 확인할 수 있다. 사용 방법은 간단하다. 링크를 타고 들어간 후 아래 그림과 같이 복사본을 만들어 본인의 드라이브에 저장한다.

D열의 데이터를 어시스턴트에 보내서 그 결과를 E열에 받는 방식이다. A~C는 사용하지 않아도 무관하다. 채점 외에 다른 용도로 사용하고 싶다면(예를 들어 블로그 글 생성), 소스 자료를 D열에 넣고 본인이 만든 어시스턴트를 연결 후 구동하면 그 결과가 E열에 기록될 것이다.

스크립트를 구동하려면 아래 그림과 같이 확장 – 앱스스크립트를 선택한다.

스크립트 창이 아래와 같이 열리면 api키와 어시스턴트 아이디를 따옴표 속에 입력하자.

어시스턴트에게 줄 자료를 시트에 모두 정리했다면 mainaction함수를 아래와 같이 선택 후 Run버튼을 누르자. 그러면 짜잔! 자동으로 어시스턴트의 답변을 받을 수 있다!

구글 시트로 학생 답안 자동 피드백 주기(ChatGPT Assistant 활용)