구글 스프레드시트에서 json 파싱하기(기초)

안녕하세요.

 

이번 시간에는 구글 스프레드시트 내에서 json 형태의 데이터를 파싱 하는 방법을 알아보겠습니다.

 

언어: JavaScript

환경: Google Sheets / Apps Script

 

우선 하나의 셀 내에 아래의 데이터를 입력합니다.

{
  "student": {
    "id": "101",
    "name": "김철수",
    "email": "kchs@spreadsheet.com",
    "phone": "010-1234-5678",
    "address": {
      "street": "구글로123 11길 22",
      "city": "서울",
      "zip_code": "54321"
    },
    "courses": [
      {
        "course_name": "물리학과",
        "grade": "A"
      },
      {
        "course_name": "영문학과",
        "grade": "B+"
      }
    ]
  }
}

 

이런 식으로 json 데이터가 입력되었습니다(figure 1 참고).

figure 1

 

그 후, 상단 메뉴에서 "확장 프로그램" > "Apps Script"를 클릭하여 코드 입력창으로 진입해 줍니다(figure 2, figure 3 참고).

figure 2

figure 3

 

이제 이곳에 특정 data를 추출하는 코드를 입력해 보겠습니다.

function getName(jsonString) {
  try {
    if (!jsonString) return "";
    var jsonData = JSON.parse(jsonString);
    var studentInfo = jsonData.student.name;
    return studentInfo;
  } catch(e) {
    console.error("Error:", e);
    return 'error'; // Return 0 if any error occurs
  }
}

위와 같이 입력해 준 후, ctr + s를 눌러 저장해 줍니다. name data를 반환하도록 짜두었습니다.

 

셀에 적용해 보겠습니다.

B2 cell

=getName(A2)

함수를 호출하면서 parameter로 json이 들어있는 셀 주소를 넘겨주고 있습니다.

figure 4

정상적으로 가져오고 있습니다.

 

하지만 코드를 이런 식으로 짜면 동일한 계층 상에 있는 key들을 위한 함수를 모두 추가적으로 짜서 호출해 주어야 하는데, 이는 비효율적인 것 같습니다. 그렇다면 key 값 또한 parameter로 넘겨 해당하는 data를 받아올 수 있도록 수정해 보겠습니다.

 

코드를 아래와 같이 수정 후 ctr + s로 다시 저장해 줍니다.

function getInfo(jsonString, key) {
  try {
    if (!jsonString) return "";
    var jsonData = JSON.parse(jsonString);
    var studentInfo = jsonData.student[key];
    return studentInfo;
  } catch(e) {
    console.error("Error:", e);
    return 'error'; // Return 0 if any error occurs
  }
}

parameter로 key를 추가하였고, 해당하는 key를 찾아 파싱 하여 return 해 주도록 수정한 코드입니다.

 

이제 호출하는 cell 부분도 수정해 보겠습니다.

B2 cell

=getInfo(A2, "name")

figure 5

위와 같이 수정해 주었고, name 값을 정상적으로 가져오고 있습니다.

 

옆에 id도 추가해 보겠습니다.

figure 6

성공적으로 추출이 됩니다.

 

여기서 한 층 더 깊은 계층의 데이터도 추출해 보겠습니다.

 

기존의 함수 아래에 새로운 함수를 추가 작성합니다.

function getCoursesInfo(jsonString, key) {
  try {
    if (!jsonString) return "";
    var jsonData = JSON.parse(jsonString);
    var studentInfo = jsonData.student.courses[0][key];
    return studentInfo;
  } catch(e) {
    console.error("Error:", e);
    return 'error'; // Return 0 if any error occurs
  }
}

 

"student" key에서 한 단계 더 내려가 "courses"에 접근하였습니다.

"courses" 키는 배열의 형태로 되어있기 때문에 위와 같이 [0] 등을 사용해 가져올 데이터를 특정해 주어야 합니다.

아시다시피 배열은 0부터 시작하기에 두 번째 데이터를 가져오고 싶다면 [1]을 사용해 주어야 합니다. 그 후 이전 예시와 같이 key를 붙여줍니다.

 

아래 두 함수 호출문을 각각의 셀에 입력해 데이터를 가져오도록 하겠습니다.

D2, E2 cells

=getCoursesInfo(A2, "course_name")
=getCoursesInfo(A2, "grade")

학과 명과 성적을 호출합니다.

 

figure 7

정상적으로 반환받고 있습니다.

 

오늘의 포스팅은 여기서 마치도록 하겠습니다.

 

감사합니다.