<div id="readability-page-1" class="page"><div role="banner"> <div>  <h4>19 Aug 2021</h4> <p> <i></i> Reading time ~2 minutes </p>
 </div> <h2 id="google-cloud-서비스-계정으로-google-api-사용하기">Google Cloud 서비스 계정으로 Google API 사용하기</h2> <p>Google Sheet API를 사용하기 위해서 Google cloud console에서 서비스 계정을 만들어 진행하는 과정을 정리합니다.</p> <h2 id="준비-사항">준비 사항</h2> <ul> <li><a href="https://nodejs.org/en/">Node.js</a></li> <li><a href="https://console.cloud.google.com/">Google cloud console</a></li> </ul> <h2 id="google-cloud-console-에서-프로젝트--서비스-계정-생성-처리">
<a href="https://console.cloud.google.com/">Google cloud console</a> 에서 프로젝트 &amp; 서비스 계정 생성 처리</h2> <ol> <li>
<code>Project</code> 생성</li> <li>
<code>LEFT MENU</code>&nbsp;» <code>APIs &amp; Services</code> 선택</li> <li>
<code>ENABLE APIS AND SERVICES.</code> 클릭</li> <li>API 항목에서 <code>Google Sheets API</code> 활성화 처리</li> <li>
<code>LEFT MENU</code>&nbsp;» <code>APIs &amp; Services</code>&nbsp;» <code>Credentials</code>&nbsp;» <code>Manage service accounts</code>&nbsp;» <code>Create Service Accounts</code> 선택</li> <li>계정 정보 입력 후 계정 생성 완료(2, 3 단계는 옵션이라 그냥 완료 하면 됩니다.)</li> <li>생성된 계정(email) 정보 클릭&nbsp;» <code>KEYS</code> 선택</li> <li>
<code>ADD KEY</code>&nbsp;» <code>Create new key</code> 선택</li> <li>
<code>JSON</code> TYPE 선택 하여 파일 다운로드</li> </ol> <p>첨부된 파일의 형태는 아래와 같습니다.</p> <div><pre><code>{
  "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"
}
</code></pre></div> <h3 id="동영상으로-따라-하기">동영상으로 따라 하기</h3> <iframe width="560" height="315" src="//www.youtube.com/embed/pb4t5tKXe7E" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen=""></iframe> <h3 id="읽거나-쓰려는-sheet에-권한-부여-하기">읽거나 쓰려는 sheet에 권한 부여 하기</h3> <p>Google sheet에서 Share 버튼을 누른 이후, 생성한 서비스 계정에 READ/WRITE 권한을 부여 해야됩니다.</p> <h2 id="sheet-글-읽기">sheet 글 읽기</h2> <p>다음 라이브러리를 설치 해야 합니다.</p> <div><pre><code>npm install –save googleapis
</code></pre></div> <h3 id="sheet-읽고-쓰는-예제-코드">sheet 읽고 쓰는 예제 코드</h3> <div><pre><code>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;
    }
}
</code></pre></div> <h2 id="참고자료">참고자료</h2> <ul> <li><a href="https://www.section.io/engineering-education/google-sheets-api-in-nodejs/">Getting Started with Google Sheets API in Node.js</a></li> </ul>  </div></div>

lahuman

Daniel Lim

Google Cloud 서비스 계정으로 Google API 사용하기
Google Sheet API를 사용하기 위해서 Google cloud console에서 서비스 계정을 만들어 진행하는 과정을 정리합니다.
준비 사항
Node.js
Google cloud console
Google cloud console 에서 프로젝트 & 서비스 계정 생성 처리
Project 생성
LEFT MENU » APIs & Services 선택
ENABLE APIS AND SERVICES. 클릭
API 항목에서 Google Sheets API 활성화 처리
LEFT MENU »  APIs & Services » Credentials » Manage service accounts » Create Service Accounts 선택
계정 정보 입력 후 계정 생성 완료(2, 3 단계는 옵션이라 그냥 완료 하면 됩니다.)
생성된 계정(email) 정보 클릭 » KEYS 선택
ADD KEY » Create new key 선택
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

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

댓글