구글시트로 작업을 많이 한다.
구글시트에서 많은 행들로 이루어진 시트를 엑셀이나 csv로 내보내기를 해서 저장을 한 후 파일을 보면, 데이터가 깨져있는 것을 볼 수 있다.
여러 종류의 문제를 겪었는데, 이를 어떻게 해결하였는지 작성하고자 한다.
문제 1: 데이터 깨짐
구글시트에서 다중 row/column을 가진 테이블을 작업하였다.
이를 엑셀이나 csv로 저장하여 파이썬으로 활용하고자 한다.
아래 과정을 통해 데이터를 개별 파일로 저장할 수 있다.\
그렇다면
✅ 구글시트에서 시트 선택 → 파일 → 다운로드 → .xlsx, .csv, .tsv 등 내보내기를 원하는 형식 선택
- 위 과정으로 데이터 저장을 진행한다
그렇다면, csv로 저장한 데이터를 엑셀에서 불러와보자.
csv 파일을 열면, 아래 사진과 같이 글자가 깨져있는 현상을 겪은 적이 있을 것이다.
이는 해당 csv 파일이 utf-8로 인코딩되어 있지 않아서 발생한다.
✅ 이런 현상은 아래와 같은 방법으로 해결할 수 있다.
- 데이터 → 데이터 가져오기 → 파일에서 → 텍스트/CSV에서 → 원하는 파일 선택 → 불러오기 → 로드
로드를 하면 엑셀에 원본 시트가 깨짐 없이 기재되는 것을 볼 수 있다.
후 파일 저장도 utf-8로 진행하는 것이 좋다.
문제 2: 셀 누락
자, 위에서 파일을 csv utf-8로 저장하였다.
해당 파일을 살퍄보자.
참고로 해당 파일은 11개의 컬럼과 217개의 행으로 이루어져 있으며, 각 셀에도 방대한 데이터가 삽입되어 잇어 저장과 로드에 다소 시간이 소요되었다.
이 중에서 문제가 되는 컬럼은 "검색청크"라는 컬럼이었다.
해당 컬럼 내에는 콤마, 따옴표, 개행 등 여러 문자가 복잡하게 얽혀있는 데이터였다.
해당 데이터를 살펴보던 결과,
구글 시트에서는 멀쩡하게 존재하던 셀이
엑셀에서는 저장 중에 누락이 되어 데이터가 통째로 날아가는 현상을 다수 발견하였다.
뿅!
내 눈이 잘못된 건가 싶어서 <chunks>를 검색하여 찾아보았다.
구글 시트에는 <chunks>가 217개가 존재한다.
그러나, 엑셀에는 <chunks>가 194개밖에 존재하지 않는다.
내 데이터!!
✅ 이 문제를 해결해보자.
-
문제: CSV 내보내기 시 컬럼 깨짐, 값 소실 (콤마/따옴표/개행 탓)
-
해결: 구글 시트 → 확장 프로그램 → Apps Script → CSV/TSV/JSONL 내보내기 시도 → 최종 해결은 JSONL
- CSV: 구분 실패 (셀 내부 콤마 존재)
- TSV: 구분 실패 (셀 내부 탭 존재)
- → 탭 대신 JSONL로 전환
JSONL
- json line의 약어, JSON Lines 텍스트 형식
- JSON 내부에 한 줄씩 JSON을 저장할 수 있는 구조화된 데이터 형식
- 생성할 시?
: 일반적인 과정은 json과 동일하나, encoding을utf-8로, 그리고 각각의 행이 입력될 때 \n(행 띄우기)를 추가
# jsonl 형식
{"id": "a", "title": "higuys", "genre": "drama"}
{"id": "b", "title": "make_me_happy", "genre": "action"}Apps Script로 구글 시트를 JSONL로 저장해보자.
- 시트 선택 → 확장 프로그램 → Apps Script → 함수 저장 및 실행
function exportActiveSheetAsJSONL() {
const sh = SpreadsheetApp.getActiveSheet();
const vals = sh.getDataRange().getValues();
const headers = vals.shift();
const rows = vals.map(r => {
const obj = {};
headers.forEach((h,i) => obj[h] = r[i]);
return JSON.stringify(obj);
}).join("\n");
const name = sh.getName()+".jsonl";
const blob = Utilities.newBlob(rows, "application/json", name);
const file = DriveApp.createFile(blob);
Logger.log("Saved: " + file.getUrl());
}선택한 시트에 대해 해당 함수를 실행하면, 해당 함수가 꺠짐 없는 JSONL로 저장된다.
해당 파일을 이용하여 문제 없이 파이썬 작업을 진행하였다.
구글시트에 JSONL 불러오기
이후 작업도 JSONL로 많이 진행했다.
다만 구글시트에서 JSONL을 불러와본 적이 없어서, 이 또한 찾아보아
Apps Script를 활용해 구글시트에 JSONL 파일을 불러오는 과정도 진행하였다.
- 구글 시트가 위치하는 동일한 경로에 JSONL 파일 저장 → Apps Script 실행 → 데이터 확인
# Apps Script
function importJSONL() {
const file = DriveApp.getFilesByName('ragas_gt_eval.jsonl').next();
const content = file.getBlob().getDataAsString('utf-8');
const lines = content.trim().split('\n').map(JSON.parse);
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const keys = Object.keys(lines[0]);
sheet.appendRow(keys);
lines.forEach(obj => sheet.appendRow(keys.map(k => obj[k])));
}
여러 종류의 데이터를 다뤄보면서 많은 오류를 마주하고 해결하기 위해 노력한다.
앞으로도 여러 데이터를 마주하겠지만, 이렇게 정리하면 미래의 나에게 도움이 되지 않을까 싶다.
