구글 드라이브 시트 변경 / Google Drive Api, Slack Api

부장으로 진급하면서 매주 월요일 업무 보고를 받아야 한다.

그런데 매주 들어가서 복사해서 만들기 너무 귀찮은게 아닌가. -_-

그래서 자동화를 했다.

Oauth2.0 V3를 사용해서 처리함.

//240112 nhm, 구글 드라이브
implementation 'com.google.api-client:google-api-client:2.0.0'
implementation 'com.google.oauth-client:google-oauth-client-jetty:1.34.1'
implementation 'com.google.apis:google-api-services-drive:v3-rev20220815-2.0.0'
implementation 'com.google.apis:google-api-services-sheets:v4-rev20220927-2.0.0' //https://developers.google.com/sheets/api/quickstart/java?hl=ko
implementation group: 'org.apache.poi', name: 'poi-ooxml', version: '5.0.0'

 

// https://mvnrepository.com/artifact/com.slack.api/slack-api-client
implementation group: 'com.slack.api', name: 'slack-api-client', version: '1.37.0'

 

 

import com.google.api.client.auth.oauth2.Credential;
import com.google.api.client.extensions.java6.auth.oauth2.AuthorizationCodeInstalledApp;
import com.google.api.client.extensions.jetty.auth.oauth2.LocalServerReceiver;
import com.google.api.client.googleapis.auth.oauth2.GoogleAuthorizationCodeFlow;
import com.google.api.client.googleapis.auth.oauth2.GoogleClientSecrets;
import com.google.api.client.googleapis.javanet.GoogleNetHttpTransport;
import com.google.api.client.http.InputStreamContent;
import com.google.api.client.http.javanet.NetHttpTransport;
import com.google.api.client.json.gson.GsonFactory;
import com.google.api.client.util.store.FileDataStoreFactory;
import com.google.api.services.drive.Drive;
import com.google.api.services.drive.DriveScopes;
import com.google.api.services.drive.model.FileList;
import com.google.api.services.sheets.v4.SheetsScopes;
import com.회사꺼임.SystemToolService;
import com.회사꺼임.utils.DateUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Service;

import java.io.*;
import java.security.GeneralSecurityException;
import java.text.MessageFormat;
import java.time.DayOfWeek;
import java.time.LocalDate;
import java.time.temporal.TemporalAdjusters;
import java.util.*;

@Service
public class GoogleDriveServiceImpl implements IGoogleDriveService {
    @Autowired SystemToolService systemToolService;
    @Autowired ISlackService slackService;

    private final String email = "hm.na@회사명.com";
    private final String APPLICATION_NAME = "app-name";
    private final GsonFactory JSON_FACTORY = GsonFactory.getDefaultInstance();
    private final String TOKENS_DIRECTORY_PATH = "tokens";
    private final List<String> SCOPES = Arrays.asList(DriveScopes.DRIVE, SheetsScopes.DRIVE);
    private final String sharedFolderId = "공유폴더 id 공유 드라이브에 아무 드라이브 id여도 되는듯?";
    private final String mime = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

    private final int rowNumber2 = 1;
    private final int cellNumberC = 2;
    private final int cellNumberD = 3;
    private final int rowCount = 9;

    private Credential getCredentials(final NetHttpTransport HTTP_TRANSPORT) throws IOException {
        final String CREDENTIALS_FILE_PATH = "google-api/app-admin.json";
        // Load client secrets.
        InputStream in = new ClassPathResource(CREDENTIALS_FILE_PATH).getInputStream();
        GoogleClientSecrets clientSecrets = GoogleClientSecrets.load(JSON_FACTORY, new InputStreamReader(in));

        // Build flow and trigger user authorization request.
        GoogleAuthorizationCodeFlow flow = new GoogleAuthorizationCodeFlow.Builder(
                HTTP_TRANSPORT, JSON_FACTORY, clientSecrets, SCOPES)
                .setDataStoreFactory(new FileDataStoreFactory(new java.io.File(TOKENS_DIRECTORY_PATH)))
                .setAccessType("offline")
                .build();

        LocalServerReceiver receiver = new LocalServerReceiver.Builder().setPort(8888).build();
        Credential credential = new AuthorizationCodeInstalledApp(flow, receiver).authorize(email);
        credential.refreshToken();
        //returns an authorized Credential object.
        return credential;
    }

    public void googleSheetWeek() throws IOException, GeneralSecurityException {
        final String weekConference = "수정할 파일이 있는 폴더 id";
        lastWorkbookFiles(weekConference);
    }

    private void lastWorkbookFiles(String parentId) throws GeneralSecurityException, IOException {
        final NetHttpTransport HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport();
        Drive service = new Drive.Builder(HTTP_TRANSPORT, JSON_FACTORY, getCredentials(HTTP_TRANSPORT))
                .setApplicationName(APPLICATION_NAME)
                .build();

        String sharedFolderParentId = String.format("'%s' in parents", parentId);

        FileList result = service.files().list()
                .setCorpora("drive")
                .setDriveId(sharedFolderId)
                .setPageSize(250)
                .setIncludeItemsFromAllDrives(true)
                .setSupportsAllDrives(true)
                .setQ(sharedFolderParentId + " and trashed=false")
                .setOrderBy("createdTime")
                .execute();

        com.google.api.services.drive.model.File lastFile = result.getFiles().get(result.getFiles().size() - 1);
        final String id = lastFile.getId();

        Drive.Files.Export exportFile = service.files().export(id, mime);
        Workbook workbook;

        //https://developers.google.com/drive/api/guides/ref-export-formats?hl=ko
        try {
            workbook = new XSSFWorkbook(exportFile.executeMediaAsInputStream());
        } catch (Exception ex) {
            InputStream content = service.files().get(id).executeMediaAsInputStream();
            workbook = new XSSFWorkbook(content);
        }

        String fileName = createGoogleSubSheet(workbook);

        if(!isFirstMonday() && fileName != null) {
            //처음 올리는 파일이 아니면 파일명 중복나므로, 삭제.
            com.google.api.services.drive.model.File deleteFile = new com.google.api.services.drive.model.File();
            deleteFile.setName(lastFile.getName());
            deleteFile.setParents(lastFile.getParents());
            deleteFile.setTrashed(true);

            //새 파일로 만들지 않으면, 휴지통 이동 안됨.
            service.files().update(lastFile.getId(), deleteFile).setSupportsAllDrives(true).execute();
        }

        if(fileName != null) {
            uploadWorkbookSheet(service, parentId, workbook, fileName);
        }
    }

    private String createGoogleSubSheet(Workbook workbook) {
        //주마다 서브 Sheet를 만들어 업데이트 해야 한다.
        //매주 월요일.
        String fileName = null;
        LocalDate now = LocalDate.now();
        LocalDate startDate = now.with(DayOfWeek.MONDAY);

        try {
            Sheet originalSheet = workbook.getSheetAt(0);
            Sheet newSheet = originalSheet.getWorkbook().cloneSheet(0);

            //가져올 데이터. C열 2번째 추진 계획 날짜 변경. D열 2번째 데이터를 C열 2번째 데이터로 옮겨야 한다.
            //추진 계획 D열 날짜 변경.
            for(int i = 0; i < rowCount; i++) {
                Cell cellD = newSheet.getRow(rowNumber2 + i).getCell(cellNumberD);
                String cellDValue = cellD.getStringCellValue();
                Cell cellC = newSheet.getRow(rowNumber2 + i).getCell(cellNumberC);
                cellC.setCellValue(cellDValue);
                cellD.setCellValue("");
            }

            String monday = DateUtils.dateToStr(startDate, "MM/dd");

            LocalDate endDate = startDate.plusDays(4);
            String friday = DateUtils.dateToStr(endDate, "MM/dd");
            String dayFormat = MessageFormat.format("추진 계획({0}-{1})", monday, friday);

            Cell cellD = newSheet.getRow(rowNumber2).getCell(cellNumberD);
            cellD.setCellValue(dayFormat);

            workbook.setSheetOrder(newSheet.getSheetName(), 0);
            workbook.setSheetName(0, DateUtils.todayYearMonthDay());

            workbook.setActiveSheet(0);

            if (isFirstMonday()) {
                //기존 뒤에 있는 시트 다 지우기.
                int sheetCount = workbook.getNumberOfSheets();

                for(int i = 1; i < sheetCount; i++) {
                    workbook.removeSheetAt(1);
                }
            }

            fileName = MessageFormat.format("주간보고-{0}.xlsx", DateUtils.dateToStr(startDate, "yyyyMM"));
            //createWorkbookFile(workbook, fileName);
        } catch (Exception ex) {
            //upload 실패 메시지 추가.
            String message = MessageFormat.format("보고서 생성 실패 {0}", DateUtils.dateToStr(startDate, "yyyyMM"));
            String smsTos = "오류나면 문자줘";

            Map<String, Object> messageParams = new HashMap<>();
            messageParams.put("msg", message);
            messageParams.put("phone", smsTos);
            systemToolService.sendSms(messageParams);

            System.out.println(ex.getMessage());
        }

        return fileName;
    }

    private boolean isFirstMonday() {
        LocalDate now = LocalDate.now();
        LocalDate startDate = now.with(TemporalAdjusters.firstDayOfMonth());
        LocalDate firstMonday = startDate.with(DayOfWeek.MONDAY);

        return firstMonday.equals(now);
    }

    private void createWorkbookFile(Workbook workbook, String fileName) throws IOException {
        //local test, mac.
        String fileFullName = MessageFormat.format("/Users/Shared/02.파일/{0}", fileName);
        workbook.write(new FileOutputStream(fileFullName));
    }

    private void uploadWorkbookSheet(Drive drive, String parentId, Workbook workbook, String fileName) throws IOException {
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        byte[] fileData = outputStream.toByteArray();
        InputStreamContent content = new InputStreamContent(mime, new ByteArrayInputStream(fileData));

        com.google.api.services.drive.model.File file = new com.google.api.services.drive.model.File();
        file.setName(fileName);
        file.setMimeType(mime);
        file.setParents(Collections.singletonList(parentId));

        //https://developers.google.com/drive/api/guides/manage-uploads?hl=ko
        com.google.api.services.drive.model.File uploadFIle = drive.files().create(file, content).setSupportsAllDrives(true).execute();

        String location =  "https://docs.google.com/spreadsheets/d/" + uploadFIle.getId();
        slackService.sendSlackMessage("업무보고 테스트 : " + location);
    }

    public void reCheckWorkbookSheet() throws GeneralSecurityException, IOException {
        final String weekConference = "변경해야 할 폴더 위치. 전역으로 빼던가 그냥 여기다가 쓰던가..";

        final NetHttpTransport HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport();
        Drive service = new Drive.Builder(HTTP_TRANSPORT, JSON_FACTORY, getCredentials(HTTP_TRANSPORT))
                .setApplicationName(APPLICATION_NAME)
                .build();

        String sharedFolderParentId = String.format("'%s' in parents", weekConference);

        FileList result = service.files().list()
                .setCorpora("drive")
                .setDriveId(sharedFolderId)
                .setPageSize(250)
                .setIncludeItemsFromAllDrives(true)
                .setSupportsAllDrives(true)
                .setQ(sharedFolderParentId + " and trashed=false")
                .setOrderBy("createdTime")
                .execute();

        com.google.api.services.drive.model.File lastFile = result.getFiles().get(result.getFiles().size() - 1);

        String location =  "https://docs.google.com/spreadsheets/d/" + lastFile.getId();
        slackService.sendSlackMessage("화요일 업무보고 확인 테스트 : " + location);
    }
}

 

public interface IGoogleDriveService {
    void googleSheetWeek() throws IOException, GeneralSecurityException, InterruptedException;

    void reCheckWorkbookSheet() throws GeneralSecurityException, IOException;
}

 

@Scheduled(cron = "0 0 9 * * 1")
public void monitorGoogleWeekCreator() {
    if (SERVER_MODE.equalsIgnoreCase("상용만")) {
        try {
            googleDriveService.googleSheetWeek();
        } catch (Exception ex) {
            logBatchError("monitorGoogleWeekCreator Upload 실패. message : {}", ex.getMessage());

            String smsTos = "";
            Map<String, Object> params = new HashMap<>();
            params.put("msg", "monitorGoogleWeekCreator Upload 실패");
            params.put("phone", smsTos);

            systemToolService.sendSms(params);
        }
    }
}

@Scheduled(cron = "0 0 9 * * 2")
public void monitorGoogleWeekRecheck() {
    if (SERVER_MODE.equalsIgnoreCase("상용만")) {
        try {
            googleDriveService.reCheckWorkbookSheet();
        } catch (Exception ex) {
            logBatchError("monitorGoogleWeekCreator Upload 실패. message : {}", ex.getMessage());

            String smsTos = "";
            Map<String, Object> params = new HashMap<>();
            params.put("msg", "monitorGoogleWeekRecheck 실패");
            params.put("phone", smsTos);

            systemToolService.sendSms(params);
        }
    }
}

 

public interface ISlackService {
    void sendSlackMessage(String message);
}

 

@Service
public class SlackServiceImpl implements ISlackService {
    private String token = "xoxb-...";
    private String channelId = "채널 id";

    public void sendSlackMessage(String message) {
        Slack slack = Slack.getInstance();
        MethodsClient methods = slack.methods(token);

        // Build a request object
        ChatPostMessageRequest request = ChatPostMessageRequest.builder()
                .channel(channelId)
                .text(message)
                .build();

         try{
             ChatPostMessageResponse response = methods.chatPostMessage(request);
         } catch (Exception ex) {
             System.out.println(ex.getMessage());
         }
    }
}

 

 

일은 기계한테 시키고 설계에 집중하자.

 

https://api.slack.com/

Slack은 생산성 플랫폼입니다

 

https://console.cloud.google.com/

Google 클라우드 플랫폼