구글 스프레드시트와 설문지의 고급 기능을 활용하여 교육 데이터 수집 및 분석 전문가 되기

[강의개요]

구글 스프레드시트와 설문지, 어떤 기능까지 활용해 보셨나요?

편리한 설문 작성 도구인 구글 설문지의 문항별 '응답 확인 기능'을 제대로 알면, 응답자에게 집계자가 원하는 값을 입력하도록 할 수 있습니다.

무료이면서도 가장 널리 쓰이는 구글 스프레드시트의 여러 함수를 따라하기 식으로 쉽게 익혀 봅시다. 또한 데이터 기능을 활용해 나만의 기능을 만들어 활용할 수도 있습니다. 최신의 AI 서비스인 OpenAI의 GPT-4o-mini 및 Google의 Gemini-1.5-flash API를 가져와 내 스프레드시트에 적용해 보세요.

6시간만 따라오시면 여러분도 데이터 수집 및 분석 전문가가 될 수 있습니다.

[강의내용 및 실습자료]

실습을 위해 실습용(사본) 시트 및 완성본 시트를 참고하여 따라해 보세요.

(클릭하여 강의원고 내려받기)

1차시 효율적인 자료 수집을 위한 구글 스프레드시트 활용

[3~5쪽] 협업을 위한 공유 시트 설정 팁

2차시 자료 정리와 가공을 돕는 함수 익히기

[7~10쪽] 데이터 처리 및 다른 데이터 활용을 위한 함수

3차시 복잡한 작업을 도와주는 데이터 기능 활용

[11~13쪽] 정렬과 필터

[13~15쪽] 이름이 지정된 함수

[15~16쪽] 부가기능 활용

[16~18쪽] 매크로와 앱스스크립트

[17쪽] Apps Script - Code.gs (추가)

function updateStudentData() {  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('매크로와 앱스스크립트');    // 입력된 학생명 및 도전 내용 가져오기  var studentName = sheet.getRange('A4').getValue();  var tumblerCount = sheet.getRange('B4').getValue();  var deliveryCount = sheet.getRange('C4').getValue();    // 학생 목록이 있는 범위 가져오기  var studentRange = sheet.getRange('A6:A15');  var students = studentRange.getValues();    for (var i = 0; i < students.length; i++) {    if (students[i][0] == studentName) {      // 텀블러 이용 횟수와 배달용기 대체 횟수 업데이트      var currentTumbler = sheet.getRange(i + 6, 2).getValue();      var currentDelivery = sheet.getRange(i + 6, 3).getValue();            // 기존 값에 새로운 값을 더해줌      sheet.getRange(i + 6, 2).setValue(currentTumbler + tumblerCount);      sheet.getRange(i + 6, 3).setValue(currentDelivery + deliveryCount);      break;    }  }}

[18~19쪽] 생성형AI(Gemini, GPT) 함수 활용하기

[19쪽] Apps Script (=GEMINI)

// Gemini API(=GEMINI) 호출 함수// 출처(https://sbctech.net/sbc-news/%EA%B5%AC%EA%B8%80-%EC%8A%A4%ED%94%84%EB%A0%88%EB%93%9C%EC%8B%9C%ED%8A%B8%EC%97%90%EC%84%9C-gemini-%EC%82%AC%EC%9A%A9%ED%95%98%EA%B8%B0/)const GEMINI_API_KEY = "apikey";
function GEMINI(prompt) {  initialPrompt = "다음 명령을 간결하고 정확하게 답변해 주기 바람 : "
  const data = {    "contents": [{      "parts": [{        "text": initialPrompt + prompt      }]    }],    "generationConfig": {      "temperature": 0.2,      "topK": 1,      "topP": 1,      "maxOutputTokens": 2048,      "stopSequences": []    }  };  const options = {    'method': 'post',    'contentType': 'application/json',    'payload': JSON.stringify(data)  };    let response = UrlFetchApp.fetch('https://generativelanguage.googleapis.com/v1beta/models/gemini-1.5-flash-latest:generateContent?key=' + GEMINI_API_KEY , options);
  const payload = JSON.parse(response.getContentText());  const text = payload.candidates[0].content.parts[0].text.trim();  return text;}

[19쪽] =FEEDBACK(GEMINI(...))

=GEMINI("상황: 학생들이 여름방학동안 참여한 '용기내 챌린지'는 텀블러 이용 횟수 및 배달용기 대체 횟수를 감안하여 우수학생을 선정한 행사였음 / 처리할 내용: 학생("&학생명&")의 실적(텀블러 이용 횟수 "&텀블러&"회, 배달용기 대체 횟수 "&배달용기&"회)과 선정여부("&선정여부&"), 작성한 학생 소감("&소감&")을 토대로 교사의 의견 제시 / 글의 어조: 교사가 학생들의 성취를 인정하면서, 각 학생이 더 나아질 수 있다는 격려와 기대감을 담고, 학생들의 아이디어와 노력을 계속해서 독려하는 내용으로 경어체로 줄바꿈 없이 2문장으로 작성")

[19쪽] Apps Script (=GPT)

// OpenAI API(=GPT) 호출 함수const OPENAI_API_KEY = 'apikey';
function GPT(prompt) { // Get the active spreadsheet and the cell that called this function var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var cell = sheet.getActiveCell();  // Process the prompt to replace cell references with their values prompt = processCellReferences(prompt, sheet, cell);  if (!prompt) {   return "Error: Please provide a prompt."; }  const apiUrl = 'https://api.openai.com/v1/chat/completions';  const payload = {   'model': 'gpt-4o-mini',  // or whichever model you're using   'messages': [     {'role': 'system', 'content': 'You are a helpful assistant.'},     {'role': 'user', 'content': prompt}   ],   'max_tokens': 1000 };  const options = {   'method': 'post',   'contentType': 'application/json',   'headers': {     'Authorization': 'Bearer ' + OPENAI_API_KEY   },   'payload': JSON.stringify(payload) };  try {   const response = UrlFetchApp.fetch(apiUrl, options);   const json = JSON.parse(response.getContentText());   return json.choices[0].message.content.trim(); } catch (error) {   return "Error: " + error.toString(); }}
function processCellReferences(prompt, sheet, cell) { // Regular expression to match cell references like A1, B2, etc. var cellRefRegex = /\b[A-Z]+\d+\b/g;  return prompt.replace(cellRefRegex, function(match) {   try {     var value = sheet.getRange(match).getValue();     return value.toString();   } catch (e) {     // If the cell reference is invalid, return the original match     return match;   } });}

[19쪽] =FEEDBACK(GPT(...))

=GPT("상황: 학생들이 여름방학동안 참여한 '용기내 챌린지'는 텀블러 이용 횟수 및 배달용기 대체 횟수를 감안하여 우수학생을 선정한 행사였음 / 처리할 내용: 학생("&학생명&")의 실적(텀블러 이용 횟수 "&텀블러&"회, 배달용기 대체 횟수 "&배달용기&"회)과 선정여부("&선정여부&"), 작성한 학생 소감("&소감&")을 토대로 교사의 의견 제시 / 글의 어조: 교사가 학생들의 성취를 인정하면서, 각 학생이 더 나아질 수 있다는 격려와 기대감을 담고, 학생들의 아이디어와 노력을 계속해서 독려하는 내용으로 경어체로 줄바꿈 없이 2문장으로 작성")

4차시 효율적인 자료 수집을 위한 구글 설문지 제작

[23쪽] 휴대전화번호(정규표현식)

^\d{3}-\d{4}-\d{4}$

[23쪽] 2~4자 한글 이름(정규표현식)

^[가-힣]{2,4}$

[23쪽] 초등학교 이름(정규 표현식)

^.*대구.*초등학교$

5차시 설문지에 응답한 내용 확인을 위한 3가지 방법

[26쪽] (1) 입력한 내용을 확인하기 위한 시트

[26쪽] Step3 수식

=ARRAYFORMULA(QUERY({
    '설문지 응답 시트1'!B2:B,
    REPLACE('설문지 응답 시트1'!C2:C, 2, LEN('설문지 응답 시트1'!C2:C) - 2, "*"),
    '설문지 응답 시트1'!E2:E
}, "SELECT Col1, Col2, Col3 WHERE Col1 IS NOT NULL ORDER BY Col1, Col2 ASC", 0))

[27쪽] (2) 온라인 연수 운영 관리를 위한 시트

[28쪽] Step3 수식

=ARRAYFORMULA(QUERY({
    '설문지 응답 시트1'!B2:B,
    REPLACE('설문지 응답 시트1'!C2:C, 2, LEN('설문지 응답 시트1'!C2:C) - 2, "*"),
    IF('설문지 응답 시트1'!E2:E="시작", "○", ""),
    IF('설문지 응답 시트1'!E2:E="종료", "○", "")
}, "SELECT Col1, Col2, MIN(Col3), MAX(Col4) WHERE Col1 IS NOT NULL GROUP BY Col1, Col2 LABEL MIN(Col3) '', MAX(Col4) ''", 0))

[29쪽] (3) 특정 사용자의 응답 내용만 출력하는 화면

[29쪽] Apps Script - Code.gs (기존코드 수정)

function doGet(e) {  var htmlOutput =  HtmlService.createTemplateFromFile('FilterHeaders');  htmlOutput.n_id = '';  htmlOutput.n_name = '';  return htmlOutput.evaluate();}
function doPost(e) {    Logger.log(JSON.stringify(e));
  var htmlOutput =  HtmlService.createTemplateFromFile('FilterHeaders');  htmlOutput.n_id =  e.parameter.n_id;  htmlOutput.n_name =  e.parameter.n_name;  return htmlOutput.evaluate();     }
function getSheetData()  {   var ss= SpreadsheetApp.getActiveSpreadsheet();  var dataSheet = ss.getSheetByName('설문지 응답 시트1');   var dataRange = dataSheet.getDataRange();  var dataValues = dataRange.getValues();    return dataValues;}
function getUrl() { var url = ScriptApp.getService().getUrl(); return url;}

[29쪽] Apps Script - FilterHeaders.html (추가)

<!DOCTYPE html><html>  <head>    <base target="_top">    <style>      body {        font-family: Arial, sans-serif;        background-color: #f2f2f2;        margin: 0;        padding: 0;        display: flex;        justify-content: center;        align-items: center;        height: 100vh;      }      h1 {        text-align: center;        color: #333;      }      form {        background-color: #fff;        padding: 20px;        border-radius: 8px;        box-shadow: 0 0 10px rgba(0, 0, 0, 0.1);        margin-bottom: 20px;        text-align: center;        width: 300px; /* Adjust the form width to desired size */      }      label {        display: block;        margin-bottom: 8px;        font-weight: bold;        color: #333;      }      input[type="text"] {        width: 90%;        padding: 8px; /* Adjust padding to reduce the input size */        margin-bottom: 20px;        border: 1px solid #ccc;        border-radius: 4px;      }      input[type="submit"] {        background-color: #4CAF50;        color: white;        padding: 10px 20px;        border: none;        border-radius: 4px;        cursor: pointer;        font-size: 16px;      }      input[type="submit"]:hover {        background-color: #45a049;      }      table {        width: 100%;        border-collapse: collapse;        background-color: #fff;        margin: 20px 0;        box-shadow: 0 0 10px rgba(0, 0, 0, 0.1);      }      th, td {        padding: 15px;        text-align: left;        border-bottom: 1px solid #ddd;      }      th {        background-color: #4CAF50;        color: white;      }      tr:hover {        background-color: #f5f5f5;      }    </style>  </head>  <body>    <div>      <h2>구글 설문지 연수 신청내용 확인</h2>      <?var tableData = getSheetData();?>      <?var url = getUrl();?>      <form method="post" action="<?= url ?>">        <label>성함</label>        <input type="text" name="n_name" value="<?= n_name ?>" />        <label>확인번호</label>        <input type="text" name="n_id" value="<?= n_id ?>" />        <input type="submit" name="searchButton" value="Search" />      </form>            <? if(n_name != '' & n_id != '')      {          var output_array = [];          tableData.forEach(function(value, index) {                      if(index == 0){              output_array.push([value[1],value[2],value[3],value[4]]);            }else if(index > 0 && n_name != '' && n_id != ''){              if(value[2] == n_name && value[5] == n_id){                output_array.push([value[1],value[2],value[3],value[4]]);              }            }          });      }else{        var output_array = [[],[],[],[]]       }  ?>            <table border="1" cellpadding="5px">        <?for(var i = 0; i < output_array.length; i++) { ?>          <?if(i == 0) { ?>            <tr>              <?for(var j = 0; j < output_array[i].length; j++) { ?>                <th><?= output_array[i][j] ?></th>              <? } ?>            </tr>          <? } else { ?>            <tr>              <? for(var j = 0; j < output_array[i].length; j++) { ?>                <td><?= output_array[i][j] ?></td>              <? } ?>            </tr>          <? } ?>        <? } ?>      </table>      <h4>검색에 시간이 소요될 수 있습니다.<br>☎문의: 000-0000</h4>    </div>  </body></html>

6차시 수집한 데이터의 시각화와 결과 공유를 위한 방법

[31~33쪽] 스프레드시트 자체 기능 활용
(요약 시트, 스파크라인, 차트, 조건부서식, 피봇테이블, 웹에 게시, 사이트도구)

[32쪽] 데이터2

A2

=FLATTEN({'데이터1'!A4:A13, '데이터1'!A4:A13, '데이터1'!A4:A13})

B2

=FLATTEN(SPLIT(REPT("1반,2반,3반,", COUNTA('데이터1'!A4:A13)), ","))

C2

=FLATTEN({'데이터1'!B4:B13, '데이터1'!C4:C13, '데이터1'!D4:D13})

(실습용 시트에는 값이 바로 입력되어 있음)

[32쪽] 요약 시트(추이)

 B5
=AVERAGE(INDEX('데이터1'!$B$4:$D$13, MATCH(B$4, '데이터1'!$A$4:$A$13, 0), 0))

[32쪽] 요약 시트(최다/최소)

B9

=INDEX('데이터2'!$A$2:$A$31,MATCH(D9,'데이터2'!$C$2:$C$31,0),0)

C9

=INDEX('데이터2'!$B$2:$B$31,MATCH(D9,'데이터2'!$C$2:$C$31,0),0)

D9

=MAX('데이터2'!$C$2:$C$31)

D10

=MIN('데이터2'!$C$2:$C$31)

E9

=D9/L5

F9

=IF(E9>1,SPARKLINE(E9,{"charttype","bar";"max",2;"color1", "red"}),SPARKLINE(E9,{"charttype","bar";"max",2;"color1", "blue"}))

[35~36쪽] 확장 프로그램(Looker Studio) 활용

추가 제공 예제

[예제1] 줄넘기 급수제 관리
설문지로 입력하면 학생별, 단계별 도달 정도를 확인하는 내용

[예제2] 간단가계부 with AI
예산관리, 지출입력(설문지), 차트와 AI 답변이 있는 월별 보고서

[예제3] 자동화 예제(웹, 앱스스크립트)