- 2022. 02. 05. -
본 포스팅은 앱으로 구글 스프레드시트에 읽기, 쓰기, 수정, 삭제가 모두 가능한 경우에 대한 내용으로 고난이도에 해당합니다. 단순히 앱에서 구글 스프레드시트의 자료를 읽기만 원하는 경우는 다음 포스팅을 참고하시기 바랍니다.
앱인벤터 Tips ::: 구글 스프레드시트(Google Sheets) 사용하기
안녕하세요. 이지이지입니다. 예전에 거의 같은 제목으로 구글 스프레드시트 사용하기에 대해 포스팅한 적이 있었는데요. 블로그의 통계를 보았을 때 생각보다 조회수가 높은 편이어서 다시 한
egeasy.tistory.com
안녕하세요. 이지이지입니다.
지난 앱인벤터 포스팅에서는 앱인벤터에서 엑셀 사용방법에 대해 알아보았습니다.
앱인벤터에서 엑셀 사용하기
안녕하세요. 이지이지입니다. 이번 포스팅에서는 앱인벤터에서 엑셀을 사용하는 방법에 대해 알아보겠습니다. 앱인벤터에서 엑셀을 사용하는 이유로는 강력한 엑셀의 함수 기능, 다른 데이터
egeasy.tistory.com
이번에는 앱인벤터에서 구글 스프레드시트를 사용하는 방법에 대해 알아보도록 하겠습니다.
| 구글 스프레드시트 설정하기 |
1. 구글 드라이브에서 새로운 스프레드시트를 생성합니다.
2. 아래 이미지를 따라 [링크가 있는 모든 사용자에게 공개], [편집자] 권한으로 공유를 설정합니다.
3. [파일]-[웹에 게시]-[게시]를 클릭하여 스프레드시트를 웹에 게시합니다.
[게시] 클릭 후 팝업창이 나타나면 그냥 아무런 설정없이 화면을 닫아줍니다.
4. [도구]-[설문지 만들기]를 클릭하여 사용자로부터 데이터를 입력받을 설문지를 작성합니다.
설문지는 Google Apps Script를 활용하기 위한 것일 뿐, 사용자에게 보이는 것은 아닙니다.
설문지를 추가한 후 스프레드시트 파일을 보면 맨 왼쪽에 설문지 응답 시트가 추가되어 있는 것을 확인할 수 있습니다.
위에서 설명 드렸듯 설문지 항목의 각 제목은 스프레드시트 각 열의 제목이 되며, 타임스탬프는 기본적으로 입력되는 열입니다. 타임스탬프는 그대로 두도록 합니다.
5. [도구]-[스크립트 편집기]를 클릭하여 스크립트 편집화면으로 들어갑니다.
6. 적절한 제목을 입력하고, 스크립트 편집창에 아래 소스코드를 붙여 넣습니다.
전체 소스화면은 다음과 같습니다.
스크립트 소스는 이미지 아래 파일을 내려받기하여 사용하세요.
필수 스크립트에 대해서 설명드리겠습니다.
e.values[1] : 추가, 삭제, 수정, 조회할 때 키(key)로 사용할 스프레드시트의 열입니다.
배열이라 [0]부터 시작합니다. 위에서 예시로 든 시트에서는 ID를 키로 사용할 것이고,
ID는 2열에 있으므로 [1]로 표현합니다.
키로 사용할 스프레드시트의 열이 2열이 아니라면 다른 숫자로 바꾸시면 됩니다.
e.values[4] : 수정(UPDATE)과 삭제(DELETE)를 할 때 필요한 ACTION을 입력할 스프레드시트의 열입니다.
위에서 예시로 든 시트에서는 ACTION을 사용할 것이라 [4]로 표현하였습니다.
만약 열이 더 필요하다면 스프레드시트에서 ACTION을 가장 뒷 쪽 열에 표시하고 스크립트 소스에서 [ ] 안의 숫자를 바꿔주면 됩니다.
upd(key, 1, sheet, values) : e_values[1]과 마찬가지로 1은 키로 사용할 열입니다.
※ upd 함수를 호출할 때(function upd...)는 [1]로 저절로 입력이 되므로, [1]이 아니라 그냥 1로 표현한 것입니다.
if(values[row][1] == key) : 여기서도 [1]은 키로 사용할 스프레드시트의 열입니다.
sheet.getRange(parseInt(lastRow)+1, 5).clear(); : 스프레드시트의 내용을 수정할 때 ACTION열에 UPDATE 또는 DELETE가 표시되게 되는데, 미관상 이를 없애기 위한 것으로, 5는 ACTION의 열을 의미합니다.
위의 설명과 다르게 여기서는 배열이 아니므로 그냥 5로 써 줍니다.
!!! 위에서 키(key)값인 1은 세 군데 모두 동일한 숫자를 사용해야 하며,
ACTION열의 값은 1의 차이가 있다는 것을 주의하시기 바랍니다. !!!
7. 트리거를 설정합니다. 아래 이미지를 따라 차례대로 설정합니다.
8. 트리거 추가 시 [저장] 버튼을 클릭했을 때, 뜨는 경고창을 다음 이미지대로 처리합니다.
이것으로 스프레드시트에 대한 설정은 모두 끝이 났습니다.
| 화면 구성하기 |
먼저 다음 앱인벤터 소스를 다운받아 참고하시기 바랍니다.
다음은 예제 앱의 화면 구성입니다.
위의 화면 구성을 토대로 앱의 흐름을 설명드리겠습니다.
▶ 아이디, 이메일주소, 성별을 입력하고 [추가하기] 버튼을 클릭하면 스프레드시트에 정보가 추가됩니다.
▶ 아이디를 입력하고 [삭제하기] 버튼을 클릭하면 입력한 아이디의 정보를 모두 삭제합니다.
▶ 변경하고자 하는 데이터를 입력하고 [수정하기] 버튼을 클릭하면 변경된 정보가 스프레드시트에 반영됩니다.
▶ 아이디를 입력하고 [조회하기] 버튼을 클릭하면 입력된 아이디의 정보가 앱 화면에 출력됩니다.
▶ [추가하기], [삭제하기], [조회하기] 버튼을 클릭했을 때 결과화면에 정보가 출력되도록 했습니다. 코드를 간략하게 하기 위해 스프레드시트에 입력된 아이디의 리스트만 출력되도록 했습니다.
| 블록코딩 하기 |
전체 블록코딩 화면입니다.
각 블록별 설명입니다.
① 구글 스프레드시트에서 전체 데이터를 가져올 때 사용하는 URL입니다.
- URL을 가져오기 위해 스프레드시트로 이동해 [공유]-[링크복사]합니다.
- URL을 가져와 마지막의 edit?usp=sharing을 export?format=csv로 수정하여 ①의 변수에 입력합니다.
https://docs.google.com/spreadsheets/d/1ehbw3IdimgKI2wfr7Thk-qgvcIJPdjIloCjx_p9Ve3o/edit?usp=sharing
https://docs.google.com/spreadsheets/d/1ehbw3IdimgKI2wfr7Thk-qgvcIJPdjIloCjx_p9Ve3o/export?format=csv
② 스프레드시트에 정보를 전송할 때 사용하는 URL입니다.
- 스프레드시트에서 [설문지]-[현재 설문지로 이동]을 클릭합니다.
- 설문지로 이동하면 Ctrl+U를 눌러 페이지의 소스화면을 확인합니다.
- 페이지 소스화면에서 Ctrl+F를 누르고 form action을 입력합니다.
- form action 뒤의 주소를 복사하여 ②의 변수에 입력합니다.
③ 스프레드시트에서 특정 아이디만 조회할 때 사용할 URL입니다.
- 나머지는 그대로 두고, GetURL에서 빨간색 부분만 가져와 ③의 변수에 입력합니다.
SelURL: https://spreadsheet.google.com/tq?tqx=out:csv&key=1ehbw3IdimgKI2wfr7Thk-qgvcIJPdjIloCjx_p9Ve3o&tq=
GetURL: https://docs.google.com/spreadsheets/d/ 1ehbw3IdimgKI2wfr7Thk-qgvcIJPdjIloCjx_p9Ve3o/export?format=csv
④ ACTION
: 앱인벤터에서 스프레드시트로 UPDATE(수정)를 할지, 또는 DELETE(삭제)할지 여부를 전달할 변수입니다.
⑤ Gender
: 성별 입력을 위한 변수입니다.
⑥ toSave
: 앱인벤터에서 스프레드시트에서 정보를 받은 후 변경(추가, 삭제, 수정)할 때, 변경된 정보를 앱인벤터에 리스트로 저장할지 여부를 기록할 변수입니다.
⑦ idList
: 앱인벤터에 스프레드시트로부터 받은 아이디의 리스트를 저장할 리스트 변수입니다.
⑧ searchDetail
: [조회하기] 버튼을 클릭했을 때, 세부사항을 조회할 지 여부를 저장해 둘 변수입니다.
예제 앱에서는 편의상 추가, 삭제, 변경, 조회 모두 같은 Web1 컴포넌트를 사용하는데, 추가 또는 삭제를 했을 때는 아이디 리스트만 결과화면에 보여주게 코딩하였으나, 조회하기만 세부 내용을 보여주기 위함입니다.
① Screen1이 처음 시작되었을 때
② 변수 toSave에 true를 저장하라.
- Web1 컴포넌트가 스프레드시트로부터 값을 받았을 때와 관련이 있습니다.
- 처음 화면이 실행되었을 때에만 스프레드시트로부터 정보를 받아 아이디를 저장해 둘 것이고, 추가/삭제/변경할 때 아이디의 존재여부를 판단하게 될 것입니다.
③ TinyDB1 action 태그에 값을 공백으로 저장하라.
④ 결과화면에 데이터가 처리중이라는 것을 보여주기 위해 텍스트를 Data collecting...으로 하라.
⑤ Web1 컴포넌트의 URL을 변수 GetURL에 저장된 값으로 하라.
⑥ Web1의 URL 주소로 이동하여 데이터를 가져오라.
① Web1 컴포넌트가 값을 전달받았을 때,
- Web 컴포넌트는 Get으로 호출하여 값을 받아오기도 하지만 뒤에서 볼 PostText로 텍스트를 게시한 이후에도 특정한 정보를 받아오게 됩니다.
② 만약 Web1 컴포넌트에서 받은 responseCode가 200이라면,
- 200은 요청이 성공했을 때 받게 되는 코드입니다.
③ 만약 변수 toSave에 저장된 값이 true라면,
- 이 앱에서 toSave가 true로 저장되는 경우는 화면이 시작되었을 때(initialize)입니다. 처음 실행되었을 때에만 스프레드시트에 있는 아이디를 앱인벤터 리스트에 저장해두기 위한것입니다.
④ 1부터 리스트에 있는 항목 개수만큼 1씩 증가하며 do안에 있는 명령을 실행하라.
- Web1에서 받는 responseContent는 csv형식으로 받게 됩니다. (getURL 마지막 부분을 export?format=csv로 해두었기 때문입니다.) 그래서 list from csv table text 블록을 사용하는 것입니다.
⑤ responseContent에서 가져온 리스트의 number번째 항목에서 2번째에 해당하는 값을 변수 idList에 추가하라.
- 여기서 number번째 항목은 스프레드시트에서 각 행 전체를 나타내게 됩니다.
- 리스트에서 number번째 항목의 2번 항목은 아이디를 나타냅니다.
- 리스트의 구성은 다음 이미지를 참고하세요.
⑥ 만약 변수 searchDetail의 값이 true라면, (조회하기 버튼을 클릭했다면)
- 추가, 삭제, 수정 등은 PostText 블록을 사용하고, 결과화면에 아이디 리스트를 보여주며,
조회는 Get블록을 사용하고, 사용자 정보를 보여줍니다.
- searchDetail은 조회하기 버튼을 클릭할 때만 true가 되도록 코딩되어 있습니다.
⑦ 결과화면에 Web1 컴포넌트가 받은 Content를 보여주어라.
⑧ 변수 searchDetail의 값이 true가 아니라면, (조회하기가 아닌 다른 버튼을 클릭했다면)
결과화면에 아이디 리스트를 보여주어라.
※ ＼n: 줄바꿈
⑨ resoponseCode가 200이 아니라면, (요청이 성공하지 않았다면)
결과화면에 오류가 발생했다는 메시지와 responseCode를 보여주어라.
⑩~⑫
: 변수 설정
① 만약 txt_ID 컴포넌트의 텍스트가 idList에 포함되어 있다면,
② true를 반환하고(결과값으로 전달하고)
③ idList에 포함되어 있지 않다면, false를 반환하라.
①~③
: 만약 chk_Man 컴포넌트가 체크되어 있다면 변수 Gender에 남자를 그렇지 않다면 여자를 저장하라.
※ 오늘 포스팅의 목적이 체크박스가 아니라서, 대충 작성한 코드입니다.
④ Web1의 RequestHeaders를 [["Content-Type","application/x-www-form-urlencoded"]]로 하라.
- 저도 초보 개발자라 정확히는 잘 모르겠으나, PostText블록을 통해 정보를 보낼 때, key-value로 하라는 의미 같습니다.
⑥번 항목과 관련이 있습니다.
⑤ Web1 컴포넌트의 URL 주소를 변수 PostURL에 저장된 값으로 하라.
⑥ Web1의 URL 주소로 텍스트를 게시하라.
- BuildRequestData: name과 value로 이루어진 리스트를 application/x-www-form-urlencoded 유형이 이해할 수 있는 형식으로 변환합니다.
- 각 entry의 번호는 스프레드시트에서 각 열의 제목의 엔트리 번호입니다.
- 각 entry의 번호를 알기 위해서는
▶ 스프레드시트에서 설문지로 이동 후 Ctrl+U를 눌러 페이지 소스 보기로 이동합니다.
▶ Ctrl+F를 누르고 "ID"를 입력, 엔트리 번호를 알아냅니다. 이 때 번호가 앞뒤로 나오는데 뒤의 번호를 입력합니다.
나머지 항목의 entry 번호도 찾기를 통해 알아냅니다. (모두 몰려 있어 확인하기 쉽습니다.)
① [추가하기] 버튼을 클릭했을 때
② isIDIn 프로시저를 호출하여 받은 값이 true라면, (만약 아이디가 이미 존재한다면)
③ 알림 메시지를 띄워라.
④ isIDIn 프로시저를 호출하여 받은 값이 true가 아니라면(만약 아이디가 존재하지 않는다면),
TinyDB1에 Action 태그에 POST를 저장하라.
⑤ 데이터가 전송되기까지 결과화면의 텍스트를 "데이터 전송 중..."으로 하라.
⑥ Post 프로시저를 호출하라.
⑦ 변수 idList에 txt_ID의 텍스트를 더하라.
※ [삭제하기], [수정하기] 버튼은 [추가하기]와 비슷한 관계로 생략하도록 하겠습니다.
① [조회하기] 버튼을 클릭했을 때,
② 만약 isIDIn 프로시저를 호출하여 반환된 값이 true라면, (아이디가 존재한다면)
③ 변수 searchDetail에 true를 저장하라. (Web1 컴포넌트가 텍스트를 받았을 때, 필요한 값입니다.)
④ 결과화면의 텍스트를 "데이터 검색 중..."으로 하라.
⑤~⑥ Web1 컴포넌트의 URL을 설정하라.
⑥ Web1. UriEncode: SelURL에서 사용될 수 있도록 주어진 텍스트를 인코딩하는 것입니다.
변수 selURL에 저장된 URL은 사실 Google Visualization API를 사용하는 것이고, Visialization API에 맞도록 인코딩하는 것입니다.
select C, D where B matches 'txt_ID.Text'
→ 스프레드시트 B열에서 txt_ID.Text의 텍스트와 일치하는 행을 찾고, 그 행의 C열과 D열의 값을 가져오라.
이 예제에서 B열은 ID가, C열에는 Email이, D열에는 성별이 저장되어 있습니다.
txt_ID.text는 반드시 작은 따옴표(' ')로 감싸져야 하기 때문에 Join블록으로 작은 따옴표를 연결한 것입니다.
⑦ Web1의 URL주소에서 값을 가져오라.
⑧ 만약 isIDIn 프로시저를 호출하여 반환된 값이 true가 아니라면, (아이디가 존재하지 않는다면)
알림창을 띄우라.
이상 앱인벤터에서 구글 스프레드시트를 사용하는 방법이었습니다.
이번 포스팅에서는 구글 스프레드시트 사용방법에 초점을 맞추어서 오류처리나 사소한 사항에 대해서는 크게 신경을 쓰지 않았습니다. 여러분의 필요에 맞게 수정하여 사용하시기 바랍니다.~~~
댓글