Embedded Files
[강의개요]
[강의개요]
구글 스프레드시트와 설문지, 어떤 기능까지 활용해 보셨나요?
편리한 설문 작성 도구인 구글 설문지의 문항별 '응답 확인 기능'을 제대로 알면, 응답자에게 집계자가 원하는 값을 입력하도록 할 수 있습니다.
무료이면서도 가장 널리 쓰이는 구글 스프레드시트의 여러 함수를 따라하기 식으로 쉽게 익혀 봅시다. 또한 데이터 기능을 활용해 나만의 기능을 만들어 활용할 수도 있습니다. 최신의 AI 서비스인 OpenAI의 GPT-4o-mini 및 Google의 Gemini-1.5-flash API를 가져와 내 스프레드시트에 적용해 보세요.
6시간만 따라오시면 여러분도 데이터 수집 및 분석 전문가가 될 수 있습니다.
[강의내용 및 실습자료]
[강의내용 및 실습자료]
실습을 위해 실습용(사본) 시트 및 완성본 시트를 참고하여 따라해 보세요.
(클릭하여 강의원고 내려받기)
1차시 효율적인 자료 수집을 위한 구글 스프레드시트 활용
1차시 효율적인 자료 수집을 위한 구글 스프레드시트 활용
[3~5쪽] 협업을 위한 공유 시트 설정 팁
2차시 자료 정리와 가공을 돕는 함수 익히기
2차시 자료 정리와 가공을 돕는 함수 익히기
3차시 복잡한 작업을 도와주는 데이터 기능 활용
3차시 복잡한 작업을 도와주는 데이터 기능 활용
[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; } }}
[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;}
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; } });}
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차시 효율적인 자료 수집을 위한 구글 설문지 제작
4차시 효율적인 자료 수집을 위한 구글 설문지 제작
[23쪽] 휴대전화번호(정규표현식)
^\d{3}-\d{4}-\d{4}$
[23쪽] 2~4자 한글 이름(정규표현식)
^[가-힣]{2,4}$
[23쪽] 초등학교 이름(정규 표현식)
^.*대구.*초등학교$
5차시 설문지에 응답한 내용 확인을 위한 3가지 방법
5차시 설문지에 응답한 내용 확인을 위한 3가지 방법
[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))
'설문지 응답 시트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))
[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))
'설문지 응답 시트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쪽] 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;}
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차시 수집한 데이터의 시각화와 결과 공유를 위한 방법
6차시 수집한 데이터의 시각화와 결과 공유를 위한 방법
[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) 활용
추가 제공 예제
추가 제공 예제
Page updated
Google Sites
Report abuse