코프링(코틀린 + 스프링부트) + 구글 스프레드 시트로 슬랙봇 만들기 - ④ 구글 스프레드 시트 사용하기

 

 

이제 구글 스프레드 시트를 데이터베이스로 사용하기 위해서 프로젝트와 연동하자.

 

👍 영상 → https://www.youtube.com/watch?v=8yJrQk9ShPg

사이트 → https://joanne.tistory.com/m/45?category=975630

Interact with Google Sheet API from JAVA (1)

사이트 → https://joanne.tistory.com/m/46

Interact with Google Sheet API from JAVA (2)

사이트 → https://www.baeldung.com/google-sheets-java-client

Interact with Google Sheets from Java | Baeldung

사이트 → https://medium.com/geekculture/how-to-read-data-from-google-sheets-ee335f787de6

How to Read Data From Google Sheets

사이트 → https://velog.io/@devsejong/%EA%B5%AC%EA%B8%80%EC%8B%9C%ED%8A%B8%EB%A5%BC-%EC%9E%84%EC%8B%9C-%EC%96%B4%EB%93%9C%EB%AF%BC%EC%9C%BC%EB%A1%9C..-%EC%BD%94%ED%8B%80%EB%A6%B0%EC%9E%90%EB%B0%94

구글시트 데이터 연동하기 (with.코틀린)

사이트 → http://www.kwangsiklee.com/2017/09/google-spreed-sheet-api-자바로-연동하여-사용하기/

google spreed sheet API 자바로 연동하여 사용하기

https://cfdf.tistory.com/m/2

java, google sheet api 이용하기

https://truesale.tistory.com/entry/구글-Apps-Script-시작하기-개념-편집기-사용법-시트실행

[구글 Apps Script] 시작하기 #개념 #편집기 사용법 #시트실행

https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=lucky3073&logNo=220720120956

[JSP/JAVA] JAVA로 URL연결해서 JSON 데이터 받아 JSP에서 JSON 데이터 파싱

 

 

 

 

 

 

 

구글 클라우드에 프로젝트 생성

먼저 프로젝트에서 구글 스프레드 시트를 사용하기 위해서 클라우드에 프로젝트를 만들어준다.

• 스프레드 시트를 사용할 수 있게끔 사이트에서 설정해준다.
  • 위 사이트에 가서 enable API를 통해서 해당 프로젝트에 사용설정을 해준다.

 

이제 credential을 생성한다.

API API및 서비스 → 사용자 인증정보 → 사용자 인증 정보 만들기 → OAuth2클라이언트 ID 후 정보 입력

• 여기서 redirectURI는 추후에 넣어준다.

 

 

 

생성된 정보를 JSON파일로 다운로드한다.

 

 

 

 

 

구글 스프레드 시트 사용을 위한 dependency 추가하기

implementation("com.google.api-client:google-api-client:1.30.4")
implementation("com.google.apis:google-api-services-sheets:v4-rev581-1.25.0")
implementation("com.google.oauth-client:google-oauth-client-jetty:1.33.3")

 

 

 

 

GoogleConfig 설정하기

이제 구글 credential을 사용하기 위해 configuration을 생성한다.

먼저 위에서 다운로드 한 JSON파일을 resources/credentials.json파일로 옯겨준다.

{
  "web": {
    "client_id": <클라이언트 ID>,
    "project_id": <생성한 프로젝트 ID>,
    "auth_uri": "https://accounts.google.com/o/oauth2/auth",
    "token_uri": "https://oauth2.googleapis.com/token",
    "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
    "client_secret": <클라이언트 SECRET>,
  }
}

 

 

 

이제 다음과 같이 sheets를 bean으로 만들어주고 이를 활용하도록 하자.

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.json.jackson2.JacksonFactory
import com.google.api.client.util.store.FileDataStoreFactory
import com.google.api.services.sheets.v4.Sheets
import com.google.api.services.sheets.v4.SheetsScopes
import org.apache.http.auth.Credentials
import org.springframework.context.annotation.Bean
import org.springframework.context.annotation.Configuration
import java.io.File
import java.io.IOException
import java.io.InputStreamReader
import java.security.GeneralSecurityException


@Configuration
class GoogleAuthorizeConfig {

    private val applicationName = "Google Spread Sheets"

    @Bean
    fun sheets(): Sheets {
        val credential = credential()
        return Sheets.Builder(
            GoogleNetHttpTransport.newTrustedTransport(),
            JacksonFactory.getDefaultInstance(), credential
        )
            .setApplicationName(applicationName)
            .build()
    }

    @Throws(IOException::class, GeneralSecurityException::class)
    fun credential(): Credential {
        val inputStream = Credentials::class.java.getResourceAsStream("/credentials.json")
        val clientSecrets = GoogleClientSecrets.load(
            JacksonFactory.getDefaultInstance(), InputStreamReader(inputStream)
        )
        val scopes = listOf(SheetsScopes.SPREADSHEETS)
        val flow = GoogleAuthorizationCodeFlow.Builder(
            GoogleNetHttpTransport.newTrustedTransport(), JacksonFactory.getDefaultInstance(),
            clientSecrets, scopes
        )
            .setDataStoreFactory(FileDataStoreFactory(File("tokens")))
            .setAccessType("offline")
            .build()

        return AuthorizationCodeInstalledApp(
            flow, LocalServerReceiver()
        ).authorize("user")
    }
}

 

 

 

 

 

시트bean을 사용하는 메서드 만들고 redirectURI등록하기

redirectURI가 랜덤으로 나와서 고민하다가 다음의 방법으로 문제를 해결했다 😭

• 뭔가 다른 현명하고 편리한 설정이 있을 것 같다.

  fun initializeDatabase() {
      val readResult = sheets.spreadsheets().values()
          .get(sheetsId, "맛집리스트!A2:$endCol$endRow")
          .execute()
  }

초기에 위와 같이 만든 메서드를 실행해보면 링크가 출력되는 것을 확인할 수 있다.

• → redirect URI 설정을 하지 않고 바로 클릭하면 404 redirectURI not match 오류가 발생한다.

여기서 잘 보면 redirectURI 가 출력되는데, 이를 위의 oAuth 정보의 redirecURI로 바꾸어준다.

credentials.json파일도 바꿔준다.

{
  "web": {
    "client_id": <클라이언트 ID>,
    "project_id": <생성한 프로젝트 ID>,
    "auth_uri": "https://accounts.google.com/o/oauth2/auth",
    "token_uri": "https://oauth2.googleapis.com/token",
    "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
    "client_secret": <클라이언트 SECRET>,
		"redirect_uris": [
	      <리다이렉트 URI>
	    ]
  }
}

그리고 프로그램을 다시 실행하면 로그인 창과 permission여부를 묻는 창이 뜨는데 이때 Allow 버튼을 눌러준다.

• 유튜브 강의 영상 출처 사진 참고

한번만 설정해주면 다음부터 정보를 잘 받아오는 것을 확인할 수 있다.

 

 

 

 

 

예제로 데이터 가져오기 및 삭제 확인하기

• 맛집봇은 처음에 프로젝트 실행시 구글 시트정보를 읽어와서 이를 인메모리 데이터베이스에 저장해놓고 변경이나 삭제 생성 등의 요청이 있을 때에 이를 반영하도록 한다.

@Service
class GoogleSheetsService(val sheets: Sheets, val restaurantRepository: RestaurantRepository) {

    private val sheetsId = "시트아이디"
    private val sheetId = 0
    private val endCol = "I"
    private var endRow = 21

    @PostConstruct
    fun initializeDatabase() {
        val readResult = sheets.spreadsheets().values()
            .get(sheetsId, "맛집리스트!A2:$endCol$endRow")
            .execute()
        saveRestaurant(readResult.getValues())
    }

    fun saveRestaurant(values: MutableList<MutableList<Any>>) {
        restaurantRepository.saveAll(values.map { restaurantFromValues(it as List<String>) }.toList())
    }

    fun restaurantFromValues(restaurantInfo: List<String>): Restaurant {
        return Restaurant(
            name = restaurantInfo[0],
            category = restaurantInfo[1],
            flavor = restaurantInfo[2],
            mainMenu = restaurantInfo[3],
            rating = restaurantInfo[4].toFloat(),
            priceRating = restaurantInfo[5].toFloat(),
            numberOfEvaluationStaff = restaurantInfo[6].toInt(),
            location = restaurantInfo[7],
            link = restaurantInfo[8]
        )
    }


// 데이터 생성 
    fun register(restaurant: Restaurant) {
        sheets.spreadsheets().values()
            .append(sheetsId, "맛집리스트", appendRestaurantValue(restaurant))
            .setValueInputOption("USER_ENTERED")
            .setInsertDataOption("INSERT_ROWS")
            .setIncludeValuesInResponse(true)
            .execute()
        endRow++
    }

    fun appendRestaurantValue(restaurant: Restaurant): ValueRange {
        return ValueRange().setValues(
            listOf(
                listOf(
                    restaurant.name,
                    restaurant.getCategory(),
                    restaurant.getFlavor(),
                    restaurant.getMainMenu(),
                    restaurant.getRating(),
                    restaurant.getPriceRating(),
                    restaurant.getNumberOfEvaluatedStaff(),
                    restaurant.getLocation(),
                    restaurant.getLink()
                )
            )
        )
    }


// 데이터 변경 
    fun rate(restaurant: Restaurant) {
        val body = ValueRange().setValues(
            listOf(
                listOf(
                    restaurant.getRating(),
                    restaurant.getPriceRating(),
                    restaurant.getNumberOfEvaluatedStaff()
                )
            )
        )
        val id = sheetRowIndexFromRestaurantId(restaurant.id)
        sheets.spreadsheets().values()
            .update(sheetsId, "E${id}:G${id}", body)
            .setValueInputOption("RAW")
            .execute()
    }


// 데이터 삭제 
    fun delete(restaurant: Restaurant) {
        val request = DeleteDimensionRequest()
            .setRange(
                DimensionRange()
                    .setSheetId(sheetId)
                    .setDimension("ROWS")
                    .setStartIndex(restaurant.id?.toInt())
                    .setEndIndex(sheetRowIndexFromRestaurantId(restaurant.id))
            )

        val body = BatchUpdateSpreadsheetRequest().setRequests(
            listOf(Request().setDeleteDimension(request))
        )
        endRow--
        sheets.spreadsheets()
            .batchUpdate(sheetsId, body)
            .execute()
    }


    fun sheetRowIndexFromRestaurantId(id: Long?): Int? {
        return id?.let { id + 1 }?.toInt()
    }

}

 

 

 

 

 

실제 프로젝트 내용 확인하기

먼저 실행 전에 구글시트에 테스트용 데이터를 다음과 같이 넣어두었다.

 

ChannelJoinEvent

• 채널에 입장했을 때 다음과 같이 앱 사용 방법이 조회되는 것을 확인할 수 있다.

 

 

 

AppMentionEvent

• 앱 멘션이벤트를 사용하면 앱 사용 방법을 조회할 수 있다.

 

 

 

 

/random commands

• 랜덤 커맨드를 사용하면

• 랜덤으로 조회한 레스토랑 정보를 조회할 수 있다.

 

 

 

 

 

/category commands

• 카테고리 커맨드를 사용하면

• 카테고리 선택 버튼을 확인할 수 있다.

• 해당 카테고리를 선택하면 카테고리에 맞는 맛집 정보를 조회할 수 있다.

 

 

 

 

/flavor commands

• 플레이버 커맨드를 사용하면

• 맛 선택 버튼 메세지를 확인할 수 있고

• 맛 선택시 해당 맛에 해당하는 레스토랑 정보를 조회할 수 있다.

 

 

 

 

/category&flavor commands

• 카테고리&맛 추천 커맨드를 이용하면

• 원하는 음식의 종류, 맛 선택 모달이 뜨고,

• 요청시 그에 해당하는 맛집을 추천받을 수 있다.

 

 

 

/Restaurant {맛집 이름} commands

• 레스토랑 커맨드를 사용하면

• 해당 레스토랑 정보를 확인할 수 있다.

 

 

 

/register commands

• 레지스터 커맨드를 사용하면

• 맛집 등록하기 modal 이 뜨고 정보를 입력하면,

• 해당 레스토랑이 등록되는 것을 확인할 수 있다.

• 다음과 같이 구글 시트 데이터베이스에도 적용이 되는 것을 확인할 수 있다.

 

 

 

/rating {맛집 이름} commands

• 레이팅 커맨드를 사용하면,

• 점수 매기기 modal이 뜨고 점수를 입력하면,

• 점수 등록이 완료되는 것을 확인할 수 있다.

• 구글 스프레드 시트 데이터베이스에도 반영이 되어 점수가 변경되는 것을 확인할 수 있다.

 

 

 

 

/delete commands

• 삭제 커맨드를 사용하면

• 삭제 정보 확인 modal이 뜨고 요청을 누르면,

• 삭제 확인 메세지를 받을 수 있다.

• 구글시트 데이터베이스에도 삭제되는 것을 확인할 수 있다.

 

 

 

 

완성! ✨