spring boot, google sheets 자동화

1.  개요

이번 프로젝트를 진행하면서 특정 요청에 대해 코드 내부적으로 해결하려 했지만, 시간의 부족으로 인해 축소해야 되는 기능이 생기게 되었다. 그렇다고 완전히 빼기에는 나름 핵심 기능이기 때문에 대안을 찾아야 했다. 그렇게 찾게 된 대안이 특정 요청에 대해서 해당 내용을 엑셀과 같은 오프라인에서 확인하고 해당 요청에 대한 후속 작업을 바로 처리하는 것이다. 원래는 엑셀을 활용해 수기로 해당 요청 데이터를 처리하려고 하였으나 이미 구현된 서비스들이 있을 거 같아서 찾아보게 되었다.

2.  Google Sheets api

그렇게 찾게 된 서비스가 GCP에서의 google sheets api이다

"Google Sheets API는 Google 스프레드시트에 프로그래밍 방식으로 접근하고 조작할 수 있도록 해주는 API입니다. 이 API를 사용하면 Google Sheets를 마치 데이터베이스나 테이블처럼 활용하여 데이터를 읽고 쓰거나, 시트를 생성하고 수정하는 등의 작업을 할 수 있습니다."

 

이처럼 코드단에서 요청에 대한 정보들을 sheets를 통해 관리할 수 있고, 해당 정보들을 사용한 후속 작업을 sheet에서 바로 처리할 수 있다. 또한 Docs에 사용법이 자세히 나와 있기 때문에 적용하기 어렵지 않고, 해당 sheet를 팀원끼리의 공유가 잘 되어야 되는 상황에서 google sheet는 이러한 기능이 이미 구현되어 있었기 때문에 해당 서비스를 이번 프로젝트에 사용하게 되었다

2.1  GCP credential 

먼저 코드에서 GCP 서비스에 접근하기 위해서는 인증키가 필요해서 계정을 발급받아야 된다

 GCP console 에 접속한뒤 서비스 계정을 만든다. 이때 주의할 점은 해당 계정으로 sheet를 수정해야 되는 권한을 가져야 되기 때문에 액세스 권한으로 편집자의 역할을 부여해주어야 된다

계정이 만들어지게 되면 해당 계정에 접속해 새 키를 JSON 형태로 발급받아서 기기에 저장해둔다

그 후에 서비스 계정 페이지도 돌아가서 만들어진 계정의 이메일을 google sheets 에 접근하기 위해 복사해서 저장해둔다. 여기까지 진행하게 되면 인증 준비는 끝이다.

 

2.2 google sheets 준비

먼저 위에서 GCP 계정을 만든 google 계정을 통해 google sheets 를 만든다. 그 후에 오른쪽 위에서 공유 버튼을 누른후 "사용자, 그룹, 캘린더 일정 추가" 칸에 아까 위에서 복사해둔 GCP 계정의 이메일을 붙여넣는다. 그러면 이제 해당 GCP 계정을 통해 해당 google sheets 의 접근 권한을 획득할 수 있게 된다.  그 후에 url을 보면 "/d/~~임의의 값~~/edit" 부분이 있다. 여기서 임의의 값 부분은 복사해서 저장해 둔다

여기까지 진행하면 google sheets 도 세팅 끝

2.3 Spring Boot

이번 프로젝트는 Spring boot를 사용했다. 먼저 gcp 인증과 google sheets를 관리할 수 있는 라이브러리를 추가한다

    //GCP
    implementation 'com.google.apis:google-api-services-sheets:v4-rev516-1.23.0'
    implementation 'com.google.auth:google-auth-library-oauth2-http:0.20.0'

그리고 아까 GCP 계정을 설정하면서 만들었던 키를 /resources 하위로 옮긴다. 

그 후 코드는 다음과 같다.

2.3.1  GoogleController

@Slf4j
@RestController
@RequestMapping("/api/google")
@RequiredArgsConstructor
public class GoogleController {
	private final GoogleService googleService;

	@Value("${google.spreadsheet.id}")
	private String SPREAD_SHEET_ID;
	private static final String RANGE = "sheet1!A1:C1";

	@PostMapping("/write")
	public ResponseEntity<?> writeToSheet() {
		try {
			OrderInfo orderInfo = OrderInfo.builder()
				.id(1L)
				.ItemId(0L)
				.orderDate(now())
				.build();
			googleService.writeToSheet(SPREAD_SHEET_ID, RANGE, orderInfo);
			return ResponseEntity.ok().build();
		} catch (Exception e) {
			e.printStackTrace();
			return ResponseEntity.internalServerError().body("Failed to write data: " + e.getMessage());
		}
	}
}

먼저 위에서 google sheets 를 설정하면서 복사했던 sheets id 값을 application.yml 파일에 저장해서 @Value를 통해서 불러와 사용했다. 그 후에 RANGE 라는 변수를 사용해서 해당 sheets 에 적용될 범위를 설정해 주었다.

이때 해당 작성법은 먼저 가장 앞에는 해당 sheet의 이름이 붙는다. 예를 들어 아래의 사진의 경우에는 시트1 과 sheet1 이렇게 두개의 sheet가 존제한다. 그중에서 sheet1을 타겟으로 하기 때문에 sheet1! 을 붙여서 분리한다. 만약 sheet가 1개만 존제할 경우에는 생략해도 정상적으로 동작한다.

두개의 sheet중 sheet1을 타겟으로 한다

그 후에는 행, 열의 범위를 설정해 준다. 먼저 [열1][행1]:[열2][헹2] 구조를 통해서 열1~열2, 행1~행2 까지를 타켓으로 한다. 이때 범위를 설정하지 않고 한개의 셀에 대해서만 적용할수도 있는데 이때는 [열][행] 의 구조로 예를 들어 A1인 경우에는 A1의 셀만을 타겟으로 정한다. 이후의 기능은 service 단에서 구현하였다

 

2.3.2  GoogleService

@Slf4j
@Service
@RequiredArgsConstructor
public class GoogleService {
	private static final JacksonFactory JSON_FACTORY = JacksonFactory.getDefaultInstance();
	private static final String CREDENTIALS_FILE_PATH = "/google/google.json";
	private Sheets sheetsService;
	private Sheets getSheetsService() throws IOException, GeneralSecurityException {
		if(sheetsService == null){
			GoogleCredentials credential = GoogleCredentials.fromStream(
					new ClassPathResource(CREDENTIALS_FILE_PATH).getInputStream())
				.createScoped("https://www.googleapis.com/auth/spreadsheets");
			sheetsService = new Sheets.Builder(
				GoogleNetHttpTransport.newTrustedTransport(), JSON_FACTORY, new HttpCredentialsAdapter(credential)).setApplicationName("uniflee").build();
		}
		return sheetsService;
	}

	public void writeToSheet(String spreadsheetId, String range, OrderInfo request) {
		try {
			List<List<Object>> values = List.of(
				List.of(request.getId().toString(), "미수령", request.getOrderDate().toString(), request.getItemId().toString())
			);
			Sheets service = getSheetsService();
			ValueRange body = new ValueRange().setValues(values);
			AppendValuesResponse result = service.spreadsheets()
				.values()
				.append(spreadsheetId, range, body)
				.setValueInputOption("USER_ENTERED")
				.setIncludeValuesInResponse(true)
				.execute();
		} catch (Exception e) {
			log.error("Failed to write data to the spreadsheet", e);
			throw new RuntimeException("Failed to write data to the spreadsheet: " + e.getMessage(), e);
		}
	}
}

기본 세팅으로는 CREDENTIALS_FILE_PATH를 통해 GCP 계정의 키가 저장된 위치를 불러온다. 그 후에 getSheetsService()를 통해 기존에 sheets를 관리할 수 있는 sheetsService 가 이미 존재하면 통과하고 없으면 해당 키를 사용해서 권한을 얻은 후에 sheetsService 를 획득하게 된다.

이제 sheets를 관리할 수 있는 모든 것이 갖추어 졌다. 먼저 해당 sheets 에 데이터를 넣을떄 List<List<Object>> 라는 형식으로 들어가게 된다. 확인해보지는 않았지만 n*m 형식 그대로 sheets에 들어가지 않을까? 싶다. 그 후에 위에서 정의한 getSheetsService를 통해 sheetsService를 획득하고 위에서의 절차대로 진행하게 되면 데이터가 등록되게 된다. 이때 주의할 점은 데이터를 전달할때 방식이 여러가지가 있다는 점이다. 정확한 정보는 다음의 블로그-Interact with Google Sheet API from JAVA (2)를 참고하면 좋을거 같다. 여기서 우리 프로젝트는 데이터가 지속적으로 쌓이기 때문에 update방식을 사용했다.

3. 결과

다음의 코드를 실행시키면 우리의 sheets에서는 다음과 같은 결과를 보여준다

추가적으로 데이터 타입, 형식만 잘 지키면 String 뿐만 아니라 날짜 데이터, label 등 여러 형식의 데이터에 대해서도 잘 작동하는 것을 알수 있다. 이제 우리는 특정 요청이 들어오게 되면 sheets에 행이 추가되고 이를 통해 미수령 값을 수령으로만 바꿔주면 된다

4. 생각할 점

github를 통해 프로젝트를 관리하고 있기 때문에 /resource 에 저장되어 있는 GCP 계정 키는 올리지 못하였다. 이 때문에 팀원간에 테스트를 위해서는 해당 키를 공유를 해줘야 되는데 이를 application.yml 파일에 저장해서 숨겨두려 했지만 잘 적용이 되지 않았다. 방법을 한번 찾아봐야 될거 같다.

그리고 생각보다 필요한 기능은 대부분 구현되어져 있다고 느꼈다. 생각보다 쉽게 끝나긴 했지만 재밌는 경험이였던것 같다