[Google Apps Script] 구글시트와 구글 폼 연동하기 (1/2)

구글 스프레드시트의 내용을 변경하면 자동으로 구글 폼에 연동되도록 하는 프로젝트를 맡아 진행하였습니다.

처음 해보는 것이라 아래 영상을 참고하여 먼저 어느 정도 감을 익힌 후, 실제 프로젝트에 맞게 코드를 수정했습니다.

 

스프레드시트의 내용을 변경하면 구글 폼에 실시간으로 연동되는 프로그램(스크립트 코드) 작성

이번 글에서는 위 링크의 영상에서 충분히 다루지 못했던 코드 설명을 위주로 글을 작성하겠습니다.

 

 

1. 구글 시트와 구글 폼 만들기

1-1 구글 계정에 로그인합니다

1-2 아래 이미지에서 빨간 박스로 표시된 아이콘을 클릭하여 시트와 폼을 만듭니다

2. 스프레드시트에 문제와 보기 작성

2-1 아래 이미지처럼 1행은 문제, 각 열은 문제에 해당하는 보기 옵션으로 적었습니다.

 

 

3. Apps Script 작성

하나씩 차근차근 진행하겠습니다!

 

3-1  [ 확장 프로그램 ] - [ Apps Script ]로 들어가면 이곳에서 코드를 작성할 수 있습니다.

3-2  먼저 구글 시트에서 적었던 내용을 가져오는 함수 getDataFromGoogleSheets()을 만들겠습니다.

  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // 현재 사용 중인 스프레드시트를 반환하거나 없는 경우 null를 반환합니다.

  const sheet = ss.getSheetByName('test');

  // 지정된 이름의 시트를 반환합니다. 이름이 같은 시트가 여러 개 있으면 가장 왼쪽 시트가 반환됩니다. 지정된 이름의 시트가 없는 경우 null를 반환합니다.

  const [header, ...data] = sheet.getDataRange().getDisplayValues();

  // 스프레드시트의 데이터가 있는 범위에서 데이터들을 string 타입으로 반환합니다.

 

구글 시트의 test 시트를 가져오고 문제와 문제의 옵션을 header와 data에 담습니다. 

값이 어떻게 나오는지 확인해 볼까요?

 

Logger.log를 통해 실행 로그에서 확인할 수 있습니다.

 

지금은 각 row ( 가로 - )로 나왔으나 저희는 문제를 기준으로 열 ( 세로 | )로 나와야 하기 때문에 가공을 해보겠습니다.

또한, 위에서 만든 구글 시트를 보면 옵션의 개수가 모두 동일하지 않습니다.

Logger.log(data) 를 통해서도 빈 값이 있는 것을 확인할 수 있죠! 

  

  header.forEach(function(title, index) {

    choices[title] = data.map(row => row[index]).filter(e => e !== '');

  });

 

각 문제에 맞는 옵션으로 정리되었습니다.

 

test와 test2의 차이가 보이시나요? 

구글 시트에서 빈 셀로 되어 있는 것을 거르기 위해 filter() 을 사용하였습니다.

 

3-3 구글 시트에서 가져왔던 내용을 구글 폼에 담아보겠습니다.
함수 이름은 populateGoogleForms()입니다.

 

  const google_form_id = '구글 폼 주소';

  const google_form = FormApp.openById(google_form_id);

  const items = google_form.getItems(); //양식에 있는 모든 항목의 배열을 가져옵니다.

  const choices = getDataFromGoogleSheets();

 

 ** 구글 폼 아이디는 뭐지? **

위에서 만든 구글 폼의 주소창에서 /d 와 /edit 사이에 있는 것이 아이디입니다.

 

문제 유형을 확인하는 코드입니다.
https://developers.google.com/apps-script/reference/forms/item-type

을 통해 더 많은 유형을 확인 할 수 있습니다.

 

items.forEach(function(item){

    const item_title = item.getTitle();



    if (item_title in choices) {

      const item_type = item.getType();



      switch (item_type) {

        // 체크박스

        case FormApp.ItemType.CHECKBOX:

          item.asCheckboxItem().setChoiceValues(choices[item_title]);

        break;

       

        // 목록 항목

        case FormApp.ItemType.LIST:

          item.asListItem().setChoiceValues(choices[item_title]);

        break;



        // 다중 선택 항목

        case FormApp.ItemType.MULTIPLE_CHOICE:

item.asMultipleChoiceItem().setChoiceValues(choices[item_title]);

        break;



        //그 외

        default:

          Logger.log('!!!!');

      }

    }

  });

 

4. 전체 코드

function getDataFromGoogleSheets() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const sheet = ss.getSheetByName('test');

  const [header, ...data] = sheet.getDataRange().getDisplayValues();

  const choices = {};



  Logger.log(header);

  Logger.log(data);



  header.forEach(function(title, index) {

    choices[title] = data.map(row => row[index]).filter(e => e !== '');

  });



  return choices;

}



function populateGoogleForms() {

  const google_form_id = '구글폼 주소';

  const google_form = FormApp.openById(google_form_id);

  const items = google_form.getItems();

  const choices = getDataFromGoogleSheets();



  items.forEach(function(item){

    const item_title = item.getTitle();



    if (item_title in choices) {

      const item_type = item.getType();



      switch (item_type) {

        // 체크박스

        case FormApp.ItemType.CHECKBOX:

          item.asCheckboxItem().setChoiceValues(choices[item_title]);

        break;

       

        // 목록 항목

        case FormApp.ItemType.LIST:

          item.asListItem().setChoiceValues(choices[item_title]);

        break;



        // 다중 선택 항목

        case FormApp.ItemType.MULTIPLE_CHOICE:

          item.asMultipleChoiceItem().setChoiceValues(choices[item_title]);

        break;



        //그 외

        default:

          Logger.log('!!!!');

      }

    }

  });

}

 

5. 트리거 설정하기

 5-1  Apps Script에서 좌측 네비를 보시면 시계 모양 아이콘으로 ‘트리거’라는 것이 있습니다.
트리거란? 쉽게 말해서 ‘조건’이라고 생각하시면 될 것 같습니다.

 

5-2  구글 시트에서 수정할 때마다 구글 폼에서도 수정 내용이 반영되어야 하기 때문에 위 이미지처럼 트리거를 설정하였습니다.

 

6. 참고

6-1 [문서 참고] https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app

6-2 [영상 참고] https://www.youtube.com/watch?v=MvvVwjAILWg

이렇게 구글 시트와 구글 폼 연동을 완료했습니다.

 

그런데 위 방식대로 하면 한 가지 아쉬운 점이 있었습니다.

먼저 구글 시트에 적혀있는 문제를 구글 폼에 똑같이 작성을 해놓아야 한다는 것입니다.

 

구글 시트에는 문제1이 있지만 구글 폼에 문제1을 만들어 놓지 않으면 옵션들이 가져오지 않고 수정돼도 반영되지 않습니다.

 

다음 글에서는 이러한 문제를 보완하고, 다른 예시로 구글 시트와 구글 폼을 연동해보겠습니다.

감사합니다.

블로그 구독자 문의 주소 : info@weirdsector.co.kr

그로스 해킹 파트너, LABBIT 바로가기

LABBIT을 운영하는 Team 위어드섹터 만나러 가기