저번 컴잘알 이야기에서 설문조사를 해서 x up 10명 넘으면 매뉴얼을 써준다고 했는데 8명 밖에 안되서 쓸까말까 고민하다가, 단 1명을 위해서 공연을 한다는 아티스트들처럼, 이 형도 단 1명의 한국인 캡슐리어를 위해서 프로그래밍을 한다는 마음으로 매뉴얼을 작성했다.

시작하기 앞서 할 말이 좀 많아.

이 매뉴얼은 적어도 구글 스프레드시트 (혹은 엑셀)은 잘 쓸 줄 알아야돼.

무조건 이렇게만 따라하면 된다는 방식보단, 왜 이렇게 해야하는지 컴알못도 이해할 수 있게끔 설명을 할 거야.  왜 그러는지 이해를 하고 따라하는 거랑, 걍 묻지마식 매뉴얼이랑은 차이가 있거든.  이해를 해야 컴알못도 스스로 응용도 해보고 원하는대로 조정도 해보고 그러지.  뭐 솔직히 별로 어렵지도 않아.

마켓 데이터는 Fuzzwork.co.uk 것을 사용할 거야.  이브 공홈 ESI 시스템에서 직접 가져오는 방법도 고려해봤는데 이건 너무 불편하고 사실상 구글시트에서 사용이 불가능해.  왜냐면, Fuzzwork의 경우는 이브 공홈에서 데이터를 가져다가 자기네 웹사이트에 표시하기 위해서 적절한 형태로 재가공을 해놓는데 반해서, 이브 공홈은 재가공이 되어있지 않다보니 중간에 별도의 작업이 필요하더라고.  예를 들자면,

Fuzzwork에서는 Jita에서 판매 중인 Tritanium의 셀 오더를 전부 다 표시해주기보단 최저가(min) 최고가(max)의 형태로 값을 제공해주는데,

이브공홈에서는 셀 오더를 하나하나 일일이 다 보여줘.  그래서 값을 받아다 최저가 최고가를 시트 내부에서 계산해야하니까 아이템 하나마다 수십 수백개의 목록이 나열되니까 쓸데없이 복잡해지지.

Tritanium 같은거 불러오면 수십개가 떠.

또 다른 문제가 있는데, 이브공홈에서는 공홈이니만큼 엄청나게 방대한 양의 데이터를 제공해주지만, 그 중 상당수는 로그인이 필요한 정보들이고 로그인 정보를 넘겨주면서 데이터를 가져오는 건 구글스프레드시트에서는 불가능해 (For 컴잘알: POST로 값을 넘겨서 토큰을 받아야함).

이브공홈과 Fuzzwork에서는 마켓 데이터를 JSON이라는 형태의 포맷으로 데이터를 제공해주는데, 이 JSON은 뭐냐면 보통 프로그래머들이나 사용하는 형태의 글자들인데 일반적으로 스프레드시트처럼 표를 만들어준다고 생각하면 돼.  예를 들어서,

  이름       수량

________________

Tritanium    19

________________

Scordite     20

________________

이라는 데이터가 필요하다면 이는 JSON에서는 이렇게 표현해

{"Tritanium":19, "Scordite":20}

중괄호를 내부에 또 사용하면, 표 안의 표를 만들 수 있어.  보통 이러한 형태의 값을 프로그래밍에서는 배열(Array 혹은 List)이라고 부르기도 하고, 사전 (Dictionary)이라고 부르기도 해.  이렇게 가져온 글자들에서 Tritanium이 19를 가리킨다는 뜻으로 해석하기 위해 괄호 문자도 없애고 홑따옴표도 없애고 하는 작업을 파싱 (Parsing)이라고 하지.  컴덕스러운 얘기는 여기까지만 할게.

암튼, Fuzzwork이나 이브공홈 ESI에서는 JSON으로 데이터를 제공해주는데, 문제는 구글 스프레드시트에서는 이 JSON을 불러오는 기능이 없어. XML이라고 하는 HTML 비스무리한 형태의 텍스트를 불러올 수는 있지만 JSON은 제공이 안돼.  사실, 파싱을 해주지 않기 때문에 못불러오는 거야.

그래서, JSON을 외부 페이지에서 가져와서 파싱을 해주는 별도의 파서Parser 프로그램을 설치해야하는데, 구글닥스에서는 이걸 자바스크립트를 작성함으로서 사용자가 원하는 기능을 사용할 수 있게 해주고 있어.

아래 매뉴얼의 형태는, 설명을 먼저 하고 스샷을 보여주니까 해깔리지 마삼.

나는 영어로 놓고 쓰니까, 한글로 놓고쓰는 캡슐리어들은 알아서 잘 볼 것.

자 먼저 구글 스프레드시트에 들어가서 빈 페이지를 하나 만든다.

이제부터 아래에 설명할 부분이 사실상 이 매뉴얼에서 가장 중요한 부분이니까 잘 보고 따라할 것.  자바스크립트 설치하는 거야.

메뉴에서 Tools -> Script Editor를 클릭하자.

또 다른 창이 하나 뜬다.

이렇게 생겼어.  거기서 보이는 내용은 전부 다 지워.

웹브라우저를 하나 더 열어서 아래의 주소를 입력해

https://raw.githubusercontent.com/bradjasper/ImportJSON/master/ImportJSON.gs

전부 다 복사해서, 아까 구글시트에서 열었던 스크립트 에디터 있지?  거기다 붙여넣어.

이번에는 파일이름을 바꾸자.  굳이 바꿀 필요는 없지만, 나중에 이게 뭐하는 건지 나중에 알아먹기 위해서는 파일이름을 바꿔주는 게 좋아.

아래 스샷처럼 파일이름 옆에 조그만 삼각형 아이콘을 클릭하면 이름을 변경할 수 있는 메뉴가 나와.

그런다음 이름을 입력하고,

ScriptEditor-Filename.PNG

확인버튼을 누르고나면 이렇게 바뀌어있지.

저장버튼을 눌러줘야돼.  저장버튼은 메뉴에 디스켓 모양-디스켓이 뭔지 모르는 세대는 시계처럼 생긴 아이콘 왼쪽에 있는-아이콘을 눌러주면 돼.

저장이 됐으면 파일이름 탭에 빨간색 * 표시가 없어야 정상이야.  아래 스샷은 저장이 안됐을 때 보여지는 파일이름.

자 이제 여기서 할 일은 다 끝났어.  스크립트 에디터 페이지는 이제 닫아줘도 돼.

구글시트로 돌아와서, 방금 전 스크립트가 제대로 등록됐는지 확인을 해보자.

확인방법은, 명령어 일부를 입력해보면 명령어 목록을 보여주면서 설명도 같이 떠야돼.

import만 쳤을 때 아래 스샷처럼 importJSON이 목록에 보여야돼.

이브의 모든 것들은 다 고유의 번호가 있어. 이것을 보통은 ID 넘버라고 부르지. 이름으로 관리할 수도 있겠지만, 일반적으로 컴퓨터 프로그래밍에서는 이름보단 이렇게 숫자로 관리하는 것이 훨씬 수월하고 빨라.

스테이션에 있는 셀바이 오더를 불러와보자. 어차피 다들 지타에서만 장사하겠지만, 지타 외 다른 유명 상권들의 ID 번호를 시트에 적어두면 도움이 될 거야.

Jita 4-4 CNAP - 60003760

Amarr VIII - 60008494

Dodixie - 60011866

Rens - 60004588

Hek - 60005686

엑셀 잘 다룰 줄 아는 캡슐리어들은 알아서 잘 하겠지만, 여기서는 예를 들어주기 위해서 그냥 내가 임의로 시트를 만들어볼게.

우리는 아이템 이름은 쉽게 알 수 있지만, 아이템 ID넘버를 알아내기는 컴잘알들 아니면 알아내기가 참 어려워.  그래서, 아이템 이름만 쳐도 ID넘버를 자동으로 불러올 수 있는 명령어를 작성해보자.

첫 칸에는 아이템 이름을 치게하고, 둘째칸에서 ID를 가져오는 거야.  ImportJSON 명령어의 사용법은 ImportJSON(URL, Key)의 형태로 되어있어.

Name: Scordite

ID: =ImportJSON( CONCATENATE("https://www.fuzzwork.co.uk/api/typeid.php?typename=", A9), "/typeID", "noHeaders")

CONCATENATE이라는 명령어는 셀 안의 값과 텍스트를 서로 합치는 명령어야.  따라서,

https://www.fuzzwork.co.uk/api/typeid.php?typename=Scordite

라는 주소에서 JSON 데이터를 받아오겠다는 말이 되는 거지.  다들 한 번 웹브라우저 창 하나 열고 위의 주소를 붙여넣어봐바.  아래처럼 나올 거야.

{"typeID": 1228,"typeName": "Scordite"}

위의 스샷처럼 넣고 엔터키를 치면 Fuzzwork에 접속을 시도해서 약간의 시간이 지난 뒤 값을 가져오게 돼.

ID값을 따로 가져와서 시트에 입력하는 이유는, 앞으로 추가할 셀바이 최고/최저값을 역시 마찬가지로 자동화하기 위해서야.

컴잘알들은 자동화에 목숨 걸어.  아주 환장하지.  이제 Scordite의 Jita 4-4의 바이 최고가를 받아와보자.

=ImportJSON( CONCATENATE("https://market.fuzzwork.co.uk/aggregates/?station=", A1, "&types=", B9), CONCATENATE("/", B9, "/buy/max"), "noHeaders")

어때 쉽지?  셀 오더는 sell이고 최저값은 min을 넣으면 돼.  그리고 해당 최고/최저가의 거래 갯수(수량 아님. 오더 갯수)를 알고싶으면 sell/orderCount 혹은 buy/orderCount를 넣으면 돼.

=ImportJSON( CONCATENATE("https://market.fuzzwork.co.uk/aggregates/?station=", A1, "&types=", B9), CONCATENATE("/", B9, "/sell/min"), "noHeaders")

아래는 orderCount

위의 구글시트 명령어를 잘 보면 알겠지만, CONCATENATE 명령어를 사용해서 시트에 불러와진 JSON 데이터 값과 Fuzzwork의 URL을 하나로 합쳐주고, Fuzzwork에서 불러온 데이터 중에서 원하는 항목만 빼서 보기위해 또 CONCATENATE 명령어를 사용한 거지.

noHeaders 안쓰면 지저분해지니까 꼭 사용하고.  어떻게 되는지 궁금하면 빼고 써봐.

이번엔 이름이 좀 더 길고 복잡한 아이템을 해보자.

예를 들어서 내가 현재 Gist X-Type Large Shield Booster를 노리고 있다고 치자.  

주의할 점은, 아이템 이름은 반드시 정확한 명칭을 틀리지 않고 적어넣어야돼.

Name만 제대로 적어주면, 나머지 B, C, D 컬럼은 걍 복붙하면 돼.  쉽지?

위의 Fuzzwork 주소(https://market.fuzzwork.co.uk/aggregates/?station=60003760&types=19200)를 또 다른 웹브라우저 열어서 보면 알겠지만, 사실 셀/바이, 최저/최고가, orderCount 등을 한 번에 보여주지.  그럼 저걸 한 번에 불러와서 다른 셀로 쪼개보자.  한 번에 불러와서 여러 셀에 나눌 수만 있으면 로딩하는 시간도 단축할 수 있지않겠어?

위에서 ImportJSON 명령어의 형식이 (URL, Key)이라고 했잖아?  이 Key에 콤마를 붙여줌으로서 여러 개를 적을 수 있어.

바이 최고, 바이 오더 카운트, 셀 최저, 셀 오더 카운트 이렇게 4개의 값을 불러와보자.

=ImportJSON( CONCATENATE("https://market.fuzzwork.co.uk/aggregates/?station=", A1, "&types=", B9), CONCATENATE("/", B9, "/buy/max", ",/", B9, "/buy/orderCount", ",/", B9, "/sell/min", ",/", B9, "/sell/orderCount"), "noHeaders")

CONCATENATE 때문에 해깔릴 수 있는데, 왜 CONCATENATE을 쓰는지 궁금하면 CONCATENATE 없이 써봐.  값이 붙여진 셀은 텍스트가 아니기 때문에 CONCATENATE이 아니면 텍트스를 붙일 수가 없어.

ImportJSON 명령어의 Key 부분을 CONCATENATE 없이 정리를 해주자면, 

"/1228/buy/max, /1228/buy/orderCount, /1228/sell/min, /1228/sell/orderCount"

이렇게 되는 거지.  즉, 

=ImportJSON( "https://market.fuzzwork.co.uk/aggregates/?station=60003760&types=1228", "/1228/buy/max, /1228/buy/orderCount, /1228/sell/min, /1228/sell/orderCount", "noHeaders")

이게 CONCATENATE 명령어로 조립되어지는 최종 명령어가 되겠다.

근데 저렇게 보니까 자릿수 표시가 없어서 불편하지?  저 상태에서 자릿수 표시하는 상단 메뉴바 버튼을 클릭해도 작동이 안돼.  왜냐면 저기 숫자들은 텍스트로 쓰여진 값이 아니라고 위에 얘기했지?  그래서 TEXT 명령어를 써야하지 (엑셀 기초 강의 하는 것 같군?) 참고로 난 컴쟁이라 엑셀 안씀.  쓸 일이 없어서.

=TEXT(ImportJSON( CONCATENATE("https://market.fuzzwork.co.uk/aggregates/?station=", A2, "&types=", B10), CONCATENATE("/", B10, "/buy/max"), "noHeaders"), "#,###")

어느 입갤러가 자기가 올린 셀/바이 오더를 불러왔으면 좋겠다고 했는데, 그건 맨 위에 설명했듯이 로그인이 필요한 정보라서 구글시트로는 수행이 불가능해.  그런걸 하려면 별도의 웹사이트를 만들어야하는데, 내가 요즘 웹사이트 만드는 일을 여럿 하는 중이라 지금은 못하겠고... 향후 미래에 이브 한인 유저용 통합 포탈 사이트 만들지도?

필요한 건 다 알려준 것 같은데, 혹시나 다른 알고싶은게 더 있으면 리플 달아주삼.  내용 업데이트 해줄게.

끗.