Google Sheets 인증 및 Maven Dependency는 Interact with Google Sheet API from JAVA (1)을 참고해주세요!
우선, 내가 Google Sheets를 이용해서 자동화할 작업은 write,insert,update 총 3가지이다. (자동적으로 삭제하는 것, 선택해 결과를 취하는 것은 기능에 포함되지 않았기 때문이다.)
여기서 용어가 헷갈릴 수 있는데,
write는 행을 새로 삽입하지 않고 작성하는 것을 의미한다.
즉, 다음과 같이 A열 1행과 B열 2행에 내용이 작성되어있을 때, 안녕이라는 데이터로 A열에 write를 실행한다면 2행에 단순히 작성된다.
write 실행 전
| A | B | |
| 1 | 테스트입니다. | |
| 2 | 테스트입니다. |
write 실행 후 (2행에 작성되었다.)
| A | B | |
| 1 | 테스트입니다. | |
| 2 | 안녕 | 테스트입니다. |
| 3 |
insert는 행을 삽입하는 것을 의미한다.
즉, 다음과 같이 A열 1행에 내용이 작성되어있고, B열 2행에 내용이 작성되어있을 때, 안녕이라는 데이터로 A열에 insert를 실행한다면 2행에 삽입된다.
insert 실행 전
| A | B | |
| 1 | 테스트입니다. | |
| 2 | 테스트입니다. |
insert 실행 후 (2행이 삽입되었다.)
| A | B | |
| 1 | 테스트입니다. | |
| 2 | 안녕 | |
| 3 | 테스트입니다. |
update는 말 그대로 수정이다. 행을 변경하지 않고 지정된 위치의 값을 수정한다.
본격적인 메소드 이전에 나는 시트별로 받아야하는 정보를 별도의 객체로 구성했다. 이유는 해당 시트의 입력할 위치에 따라 조회해야하는 쿼리도 모두 다르고, 입력해야할 행, 날짜가 적혀있는 기준 행, 삽입 시 취해야할 옵션 등이 모두 달랐기 때문이다.
혹시 몰라 내가 구성한 SheetEntity 코드도 공유한다.
public class SheetEntity {
private String dbName; // 쿼리를 조회할 데이터베이스 명
private String sheetName; // 해당 데이터를 입력할 시트명
private Integer sheetId; // 해당 데이터를 입력할 시트의 시트 아이디 (url의 gid=xxx에서 xxx가 해당한다.)
private String query; // 해당 데이터를 조회할 쿼리
private String column; // 날짜를 제외한 데이터를 입력할 열
private String dateColumn; // 날짜가 작성되어있는 열
private String memo; // 해당 객체를 알아보기 위한 메모
private InsertOption insertOption; // 삽입 시 취할 옵션
private Integer endIndex; // 행과 행 사이에 삽입할 때 삽입할 행의 번호 (ex. 3행과 4행 사이에 삽입해야하는 경우에는 4를 입력)
private Boolean includeDate; // 데이터를 업데이트할 때, 날짜 값 또한 포함해서 업데이트 시켜야하는 데이터의 경우 True
// 이후 Constructor는 생성 시 필요하다고 생각되는 멤버변수들을 포함하여 자유롭게 생성하면 된다 :-)
}Write
public void write(SheetEntity sheetEntity, List<Object> content) throws IOException {
String range = makeSearchRange(sheetEntity.getSheetName(), sheetEntity.getDateColumn());
ValueRange appendBody = new ValueRange().setValues(Collections.singletonList(content));
sheetsService.spreadsheets()
.values()
.append(SPREADSHEET_ID, range, appendBody)
.setValueInputOption("USER_ENTERED")
.setInsertDataOption(sheetEntity.getInsertOption().toString())
.setIncludeValuesInResponse(true)
.execute();
}Update
public void update(SheetEntity sheetEntity, Object content, String column, int row) throws IOException {
String range = makeSearchRange(sheetEntity.getSheetName(), makeRangeLayout(column, row));
ValueRange body = new ValueRange().setValues(
Collections.singletonList(
Collections.singletonList(content.toString())
)
);
sheetsService
.spreadsheets()
.values()
.update(SPREADSHEET_ID, range, body)
.setValueInputOption("USER_ENTERED")
.execute();
}
여기서 makeRangeLayout 메서드는 열과 행번호를 입력받아 C3과 같이 만들어주는 간단한 메서드이다.
작성과 업데이트는 쉬운데, Insert는 꽤 취해야할 옵션이 많다. 단순히 가장 마지막 행에 삽입만 하면 된다면 아주 쉽지만.. 행과 행 사이에 삽입을 해야하는 경우도 있기 때문에 이 부분은 우선 간단히 Insert할 때 이용하는 코드를 작성해두고, 필요 시 새로운 포스트로 업데이트 할 생각... 😉
Insert
InsertDimensionRequest insertRow = new InsertDimensionRequest();
// Set Index
int defaultStartIndex = 1;
int defaultEndIndex = 2;
// Set Range
insertRow.setRange(new DimensionRange()
.setDimension("ROWS")
.setStartIndex(defaultStartIndex)
.setEndIndex(defaultEndIndex)
.setSheetId(sheetEntity.getSheetId()));
// Set Coordinate
PasteDataRequest data = new PasteDataRequest().setData(makeListObjectToString(content)).setDelimiter("\t")
.setCoordinate(new GridCoordinate()
.setColumnIndex(getInteger(sheetEntity.getDateColumn()))
.setRowIndex(defaultStartIndex)
.setSheetId(sheetEntity.getSheetId()));
BatchUpdateSpreadsheetRequest r = new BatchUpdateSpreadsheetRequest().setRequests(Arrays.asList(
new Request().setInsertDimension(insertRow),
new Request().setPasteData(data)
));
sheetsService.spreadsheets().batchUpdate(SPREADSHEET_ID, r).execute();
여기서 작성된 모든 코드들이 부분 부분 커스텀한 경우가 많아서,
단순히 CRUD 를 위해 작업하실 분들을 위해 참고하기에 아주 좋은 youtube 자료를 첨부한다.
완전 강추 !! 👍