Google Cloud 서비스 계정 생성 부터 sheet API를 활용한 조회 처리

Google Cloud 서비스 계정으로 Google API 사용하기

Google Sheet API를 사용하기 위해서 Google cloud console에서 서비스 계정을 만들어 진행하는 과정을 정리합니다.

준비 사항

• Node.js
• Google cloud console

Google cloud console 에서 프로젝트 & 서비스 계정 생성 처리

1. Project 생성
2. LEFT MENU >> APIs & Services 선택
3. ENABLE APIS AND SERVICES. 클릭
4. API 항목에서 Google Sheets API 활성화 처리
5. LEFT MENU >> APIs & Services >> Credentials >> Manage service accounts >> Create Service Accounts 선택
6. 계정 정보 입력 후 계정 생성 완료(2, 3 단계는 옵션이라 그냥 완료 하면 됩니다.)
7. 생성된 계정(email) 정보 클릭 >> KEYS 선택
8. ADD KEY >> Create new key 선택
9. JSON TYPE 선택 하여 파일 다운로드

첨부된 파일의 형태는 아래와 같습니다.

{
  "type": "service_account",
  "project_id": "keen-clarity-323809",
  "private_key_id": "cd53576f5c47b2b654605c8a7528cabd87bdee93",
  "private_key": "-----BEGIN PRIVATE KEY-----\nMIIEvAIBADANBgkqhkiG9w0BAQEFAASCBKYwggSiAgEAAoIBAQClhKfQF6zmI34T\nuetB4vZullnrXI65fkH1/YusnE/uU1rzmWPNarBqhbxzGeSJf0xedVvSW4cNavkG\nDHu3+inlbbieX9xh++2lNGpYwA5BsXPA3xD1lcaWyc71gk6V6kVtHJMFSocLCYZ/\nDCvGfYEcCiZRsrro1M6AuGy6Nug1qld7utCHJY8OwBp/KLyCmpmhXZE5qim8eul/\nLssM2QizCVxegXS3sH6BCUBZOJVVFXvfNveXc23TWvBQJP85/vaJARjVOJeNJbFQ\nObPOdvEo9RYEfmkrIaaFq/natGP/buBYLdPNnS6+x+6fKRyT5rm1q5LUrpyYKadi\nl40HJFNNAgMBAAECggEAHScTQ5SX8GT59NF/PTiTfQDgT5Ma1FMHnSMOMdXKQkjO\nMS+oI2sSLYDmo0NBPHJ+afiztoZGcDeSKt2SAOJ/9Jfxt3tc3qk3vyxPOtiA6lMt\n9x+S5h5+PtrGp/lOjBsaJVCuGHV6+Zew57mmECzUy7KB80vOnem1gXh6NgYruh/A\ntOATcB7BVk7DEDu7mUAM4BPBWb946WeCUr1L4ZD5VM8YdLAydTCGL39/6TKIDAGl\njDEe+x9IK6DTjm0JHsY9orTDzBLzMCRulOLeAIFzZxpnUnihafk/IQ9+l8SH+rkM\nc/008CMOnUpySLx6wwdCvz+TOhucW1a2RfbGYFUExQKBgQDivirN1jvMzzyQIu8U\nzaNsNPVNIMktLSUNzQEnZsMUGXBUfnAszi3OAHY6NYSEJjYMBFThIvuHqhZCUStV\nNPFTzuHx6mg5Oi4QQC8iW/x9mbeQNBsTiY/nZomgnVS8Kko2SKmINxOf1WkGFcyV\nKk+porKTIkW/m7AEsYLzby07wwKBgQC64BkyQib1sxS1hMVhP3l+A+m7OdbWLDDO\nDRJpxHNZW/Svs1egSF2wNqPZFKJ/LHwAPKxNaBCnfQMjxweDY9VzKBIwXl3boJ7m\nvRulzFsBTwRtYG9QKXYLRhvyZRHFs12XV3KISAy5X+kWG7JeZOj4jeAr0N0Qun6F\n9M+THYMTrwKBgAWTv+HIPzhOcKLq+Q598GMc+lunTst5rMumGz0o3euEpKqvYXr4\np0/F2yKbZmMJvZKWGLBg9+biXCHNdU9nOfhhwlT8+wtNTwy/R2mE8bT1LvqxDWlx\nnMSIVSJKPGdUcvba2rCrCiSIT0kDCEEzBTqa8eGEmkqYPcAfzaTHO0mZAoGADGJD\naTA3ErsuWSUWS26AZ5hsycp4cTL2fQEiwj2Rg09ztJ0G5olFJCNK0lzqs9DH2uAq\nburBh8fiCGHtHojkIUB7jBcE00Qeo53OkjsroLeSzIjCd6Z3uyGHQpXuCpLrVdcm\nsN9NcI9pi9yEAntfcPE99MlfjPc+4TOq+c3P3OMCgYB6YFCFBNpPdYqc8EkQ8OUc\nA2MDWRcCW9oKYvu2zvDLjq9zkznUMcW5PaPql1nIEfNW9LNzn/Bc3nSQ1iHaulGZ\nZjaCKdD6pftkMYC73FWIZXIsvmyC8T7TvRvFppdBHjPcSurOJi6ot74MHKhFudm6\nezS2Wh+8ABQ+ucrWUxpMOw==\n-----END PRIVATE KEY-----\n",
  "client_email": "sheetapi@keen-clarity-323809.iam.gserviceaccount.com",
  "client_id": "114733049967179666126",
  "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_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/sheetapi%40keen-clarity-323809.iam.gserviceaccount.com"
}

동영상으로 따라 하기

읽거나 쓰려는 sheet에 권한 부여 하기

Google sheet에서 Share 버튼을 누른 이후, 생성한 서비스 계정에 READ/WRITE 권한을 부여 해야됩니다.

sheet 글 읽기

다음 라이브러리를 설치 해야 합니다.

npm install –save googleapis

sheet 읽고 쓰는 예제 코드

import { google, Auth } from 'googleapis';
class SheetApi {
    auth: Auth.GoogleAuth = new google.auth.GoogleAuth({
        keyFile: "credentials.json", //the key file
        //url to spreadsheets API
        scopes: "https://www.googleapis.com/auth/spreadsheets",
    });

    writeSheet() {
        //Auth client Object
        const authClientObject = await this.auth.getClient();
        //Google sheets instance
        const googleSheetsInstance = google.sheets({ version: "v4", auth: authClientObject });
        const spreadsheetId = "sheetId";

        //write data into the google sheets
        await googleSheetsInstance.spreadsheets.values.append({
            auth, //auth object
            spreadsheetId, //spreadsheet id
            range: "Sheet1!A:B", //sheet name and range of cells
            valueInputOption: "USER_ENTERED", // The information will be passed according to what the usere passes in as date, number or text
            resource: {
                values: [["첫번째 A 열 데이터", "1열 B열"], ["두번째 A 열 데이터", "2열 B열"]],
            },
        });
    }

    readSheet() {
        //Auth client Object
        const authClientObject = await this.auth.getClient();
        //Google sheets instance
        const googleSheetsInstance = google.sheets({ version: "v4", auth: authClientObject });
        const spreadsheetId = "sheetId";

        //Read front the spreadsheet
        const { data: { values } } = await googleSheetsInstance.spreadsheets.values.get({
            auth: this.auth, //auth object
            spreadsheetId, // spreadsheet id
            range: "DATA!A:B", //range of cells to read from.
        });

        return values;
    }
}

참고자료

• Getting Started with Google Sheets API in Node.js