WebアプリケーションからGoogleスプレッドシートへ書き込むサンプル(OAuth2認証, Express)

## 1:目的と概要 <a id="anchor1"></a>

OAuth2で認証後、Google APIを利用するwebアプリのサンプルです。

スプレッドシートのアクセス権限に「リンクを知っている全員に変更」を与えられないような場合でも、アプリが「許可されている本人」になり替わって書き込みを行うことができます。

https://github.com/murasuke/google-oauth-spread

処理の流れ
  * 最初に(スプレッドシートに書き込みできる人で)、Googleへログインを行います
    * [Passport](https://www.passportjs.org/)と、ミドルウェア[Passport-google-oauth2](https://www.passportjs.org/packages/passport-google-oauth2/)を利用
    * ログイン時「スプレッドシートに対し、Webアプリが書き込み」してよいか？許可を求められます
    * 許可すると「Webアプリ」はログインした人になり替わって、スプレッドシートに書き込みを行うことができるようになります
  * 画面で指定したスプレッドシートへデータを書き込みます

## 2:Google APIを使用するための事前設定 <a id="anchor2"></a>

アプリの登録と、認証情報を作成して「`クライアントID`」「`クライアントシークレット`」を取得します

1. Google Cloud Platformでプロジェクトを作成
1. プロジェクトで使用するAPIを有効化
1. APIへのアクセスに必要な認証情報を作成

### Google Cloud Platformでプロジェクトを作成

* [GCPダッシュボード](https://console.cloud.google.com/projectselector2/home/dashboard)へアクセスして、「`プロジェクトの選択`」をクリック ⇒ 「`新しいプロジェクト`」をクリックする
![GCPダッシュボード](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276579/ef7ced84-2be1-42eb-86a5-f5755e5fa3b8.png)

### プロジェクトで使用するAPIを有効化
* 適当な名前を付けてプロジェクトを作成する
![新しいプロジェクト](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276579/4766e09e-7edd-86c9-f323-b3d191a2bd8e.png)


* 左メニューから「`APIとサポート`」⇒「`有効なAPIとサービス`」を選択
![APIとサービス](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276579/916bcefa-dec1-6706-c099-490bb4622321.png)

* 「`APIとサービスの有効化`」をクリック
![APIとサービスの有効化](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276579/273e98ac-dd1f-f603-0650-0ef2da95612a.png)

* 検索欄に「`sheets`」と入力して、検索結果(Google Sheets API)をクリック
![APIとサービスの有効化](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276579/0da0be52-90cd-d9ee-f06f-835f278c73df.png)

* 「`Google Sheets API`」を有効にする
![APIとサービスの有効化](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276579/a21a8055-d8be-5be5-b1c2-9a9222643938.png)

### APIへのアクセスに必要な認証情報を作成
#### OAuth同意画面の登録

* 左メニューから「`OAuth同意画面`」をクリック。Google Workspace の組織外のユーザーを利用する場合は「`外部`」を選択
![OAuth同意画面](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276579/6097dd09-1607-ba68-e281-5c3c68f17443.png)

※後でテスト用ユーザーを追加します(追加したユーザーだけ許可される)

* OAuth同意画面を入力する
  * 適当なアプリ名を入力
  * ユーザーサポートメールと、デベロッパーの連絡先情報にメールアドレスを入力(必須項目だけ入力)
![img061.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276579/c0eda8f5-1383-9769-fb09-9956eb512c21.png)



* スコープの設定画面「`スコープを追加または削除`」をクリック
![スコープの設定](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276579/749567fa-4352-5972-fc6e-1ee9c36bfa5a.png)

* フィルタに「`sheet`」と入力して検索を行い、範囲が「`auth/spreadsheets`」となっている行にチェックを入れて「`更新`」する
![選択したスコープの更新](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276579/9b37f399-ce19-1d15-31f0-3f68352d2a07.png)


* 元の「`スコープの設定画面`」へ戻り、「`保存して次へ`」をクリック


* テストユーザーを追加（自分のメールアドレス）後、「`保存して次へ`」をクリック
![テストユーザー](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276579/80be1a02-03ba-1f6f-af44-15675c1e8960.png)


### 認証情報を作成する

* 左メニューから「`認証情報`」をクリック。「`認証情報を作成`」⇒「`OAuthクライアントID`」をクリックする
![認証情報](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276579/66978479-166f-aac2-1350-32ce29a72098.png)

* AuthクライアントIDの作成　は下記を入力
  * アプリケーションの種類：ウェブアプリケーション
  * 名前：任意の名前
  * 承認済みのリダイレクトURI：http://localhost:3000/auth/google/callback
![OAuthクライアントIDの作成](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276579/ccfa4dec-3864-e1aa-fa57-e70a001dbd3b.png)

    ※ローカルPC上のnode.jsで動作確認を行うため「localhost」を指定する

* OAuthクライアントID確認画面
  * 画面に表示されている「クライアントID」「クライアントシークレット」をメモっておきます（設定ファイル(.env)に記載）
  ![OAuthクライアントID確認画面](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276579/39ca92fb-80fc-606a-f2db-532148e20538.png)

参考ページ
[Google Spreadsheet をサービスアカウントに共有せずにGoogle Spreadsheet APIから読みだす方法](https://qiita.com/7032/items/50fc96b6e8c9ac90c32)

`.env`ファイルに取得したクライアント情報をセットしておきます
```
APP_PORT=3000
GOOGLE_CLIENT_ID=`クライアントID`
GOOGLE_CLIENT_SECRET=`クライアントシークレット`
```

## 3:利用モジュールのインストール <a id="anchor3"></a>
node.js + ExpressでWebアプリを作成します

  * 必要なモジュールをインストールします

```bash
npm init -y
npm i dotenv ejs express express-session passport passport-google-oauth googleapis
```

|  パッケージ  |  内容  |
| ---- | ---- |
|  dotenv  |  環境変数をファイルから読み込むためのツール  |
|  ejs  |  expressで利用するテンプレートエンジン  |
|  express  |  node.jsのウェブ用フレームワーク  |
|  express-session  |  セッションを管理するためのミドルウェア  |
|  passport  | 認証ミドルウェア  |
|  passport-google-oauth  |  Googleを使用して認証するためのストラテジー  |
|  googleapis  |  Google API クライアント  |

## 4:Express設定 <a id="anchor4"></a>
ルートフォルダに`index.js`ファイルを作成し、Webサーバを作成します

* 認証情報の保持にセッションを利用するため`express-session`を読み込む

```js
// index.js
/* *******************************************************************
 * Express設定
 * ・passport.jsでuserを保持するためsessionを利用
 * ******************************************************************/
require('dotenv').config();
const express = require('express');
const app = express();
const session = require('express-session');

app.set('view engine', 'ejs');
// formからのデータ受け取り
app.use(express.urlencoded({ extended: true }));

app.use(
  session({
    resave: false,
    saveUninitialized: true,
    secret: 'SECRET',
  })
);

const port = process.env.APP_PORT || 3000;
app.listen(port, () => console.log('App listening on port ' + port));
```

## 5:Passport設定<a id="anchor5"></a>

ログアウト用の処理と、ログイン後のユーザー情報の保存・復元処理を記載します(上記のコードに追記)

```js
/* *******************************************************************
 * Passport設定
 * ・Strategyはpassport-google-oauthを利用
 * ******************************************************************/
const passport = require('passport');

app.use(passport.initialize());
app.use(passport.session());

// ログアウト
app.get('/logout', (req, res, next) => {
  req.logout((err) => {
    if (err) {
      return next(err);
    }
    res.redirect('/');
  });
});

app.get('/error', (req, res) => res.send('error logging in'));

// ユーザー情報をsessionに保存
passport.serializeUser((user, done) => {
  console.log(user);
  done(null, user);
});

// セッションからユーザー情報の復元(req.user)
passport.deserializeUser((obj, done) => {
  done(null, obj);
});
```
## 6:Google OAuth2設定 <a id="anchor6"></a>

Passportにストラテジー(GoogleのOAuth2)をセットします(上記のコードに追記)
* 事前設定で取得した「`クライアントID`」と「`クライアントシークレット`」をセットします
* スプレッドシートを操作できるようにするため、事前設定で有効化した「https://www.googleapis.com/auth/spreadsheets 」を指定します(scopeの指定)

※.envファイル

```js
/* *******************************************************************
 * Google OAuth2 認証設定
 * ・Google Developer Consoleで認証情報を作成しておく
 *   参考URL https://qiita.com/7032/items/50fc96b6e8c9ac90c32f
 * ・作成した認証情報のclientIdと、clientSecretを環境変数にセットしておくこと
 * ******************************************************************/
const GoogleStrategy = require('passport-google-oauth').OAuth2Strategy;
const { GOOGLE_CLIENT_ID, GOOGLE_CLIENT_SECRET } = process.env;
passport.use(
  new GoogleStrategy(
    {
      clientID: GOOGLE_CLIENT_ID,
      clientSecret: GOOGLE_CLIENT_SECRET,
      callbackURL: `http://localhost:${port}/auth/google/callback`,
    },
    (accessToken, refreshToken, profile, done) => {
      // Verify Function (ユーザー存在チェック等を行う)
      //  問題がなければユーザー情報callbackに渡すことでreq.userに格納される
      return done(null, { ...profile, accessToken, refreshToken });
    }
  )
);

// 認証
// ・スプレッドシートを読み書きするためのscopeを指定
app.get(
  '/auth/google',
  passport.authenticate('google', {
    scope: ['profile', 'email', 'https://www.googleapis.com/auth/spreadsheets'],
    accessType: 'offline',
    prompt: 'consent',
  })
);

// 認証後のcallback
app.get(
  '/auth/google/callback',
  passport.authenticate('google', { failureRedirect: '/error' }),
  (req, res) => {
    // 認証成功時
    res.redirect('/');
  }
);
```

## 7:Google API (Sheets) 利用サンプル <a id="anchor7"></a>

指定されたスプレッドシートに追記するサンプルです(上記のコードに追記)

詳細はコメントを参照
```js
/* *******************************************************************
 * Google API (Sheets) 利用サンプル
 * ・書き込み先スプレッドシート(URL)を入力する画面を表示する
 * ・OAuth2認証で取得したaccessToken, refreshTokenを使いSheetに書き込みを行う
 * ******************************************************************/
// メインページ(スプレッドシートURL、書き込みデータ入力画面)を表示(GET)
app.get('/', (req, res) =>
  res.render('pages/sheet', {
    error_message: '',
    sheetURL: '',
    insertValue: '',
    user: req.user,
  })
);

const { google } = require('googleapis');

// OAuth2Clientを初期化
const oAuth2Client = new google.auth.OAuth2({
  clientId: process.env.GOOGLE_CLIENT_ID,
  clientSecret: process.env.GOOGLE_CLIENT_SECRET,
});

// 指定されたスプレッドシートにデータを追記する(POST)
app.post('/', async (req, res) => {
  // フォーム入力値
  const { sheetURL, insertValue, redirectToSheet } = req.body;

  // スプレッドシートIDを抜き出す
  const match = sheetURL.match(/spreadsheets\/d\/([0-9a-zA-Z\-_]+)/);
  if (!match) {
    res.render('pages/sheet', {
      ...req.body,
      user: req.user,
      error_message: 'URLが無効です',
    });
    return;
  }
  const spreadsheetId = match[1];

  // ログイン時に取得したトークンをセット
  oAuth2Client.setCredentials({
    refresh_token: req.user.refreshToken,
    access_token: req.user.accessToken,
  });

  // API経由でスプレッドシートへ書き込み
  const sheets = google.sheets({ version: 'v4', auth: oAuth2Client });
  const param = {
    spreadsheetId: spreadsheetId,
    range: 'シート1!A1',
    valueInputOption: 'USER_ENTERED',
    insertDataOption: 'INSERT_ROWS', // 最後に追加
    resource: {
      values: [insertValue.split(',')],
    },
  };
  // スプレッドシートにデータを追加
  await sheets.spreadsheets.values.append(param);

  if (redirectToSheet == 'checked') {
    // リダイレクトを指定した場合、スプレッドシートを表示
    res.redirect(
      `https://docs.google.com/spreadsheets/d/${spreadsheetId}/edit#gid=0`
    );
  } else {
    res.render('pages/sheet', {
      ...req.body,
      user: req.user,
      error_message: '',
    });
  }
});
```
## 8:画面作成(ログインボタン＋書き込み先スプレッドシートURL) <a id="anchor8"></a>

概要
* 未ログイン時、Googleのログインボタンを表示（ログインするまで下記項目は入力不可）
* スプレッドシートのURLと書き込む内容を入力
* 「書き込み」ボタンでスプレッドシートに追記する
![img100.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276579/3d16d891-7146-433d-e2ef-cba6008a1b62.png)

「`views/pages/sheet.ejs`」ファイルを作成して下記内容を記載します
```html
<!-- views/pages/sheet.ejs -->
<!doctype html>
<html>
<head>
    <title>Google API(oAuth2) sample</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css">
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css">
</head>
<body>
<div class="container">
    <div class="jumbotron text-center text-primary pb-2 mt-5">
        <h1 class="display-5"><span class="fa fa-table"></span>Google API(oAuth2, sheets) sample</h1>
        <p class="lead">Googlenにログイン後、スプレッドシートにデータを追記するサンプル</p>
        <hr class="my-4">
        <p>
          ようこそ<%= user? user.displayName: 'ゲスト' %>さん
          <% if (user) { %>  （<a href="/logout">ログアウト</a>） <% } %></p>
    </div>
    <% if (!user) { %>
    <div class="border border-secondary rounded p-4 mb-2">
      <p class="lead">①スプレッドシートに書き込みできるユーザーでログインを行う</p>
      <a href="/auth/google" class="btn btn-danger"><span class="fa fa-google"></span> SignIn with Google</a>
    </div>
    <% } %>
    <form method="POST" id="frm" class="border border-secondary rounded p-4 ">
      <p class="lead">②スプレッドシートのURLと書き込む内容を入力して「書き込み」ボタンをクリック</p>

      <div class="form-group">
        <label for="sheetURL">書き込み先スプレッドシートのURL</label>
        <input class="form-control" type="text" id="sheetURL" name="sheetURL" placeholder="https://docs.google.com/spreadsheets/d/&lt;spread sheet id&gt;/edit#gid=0" value="<%= sheetURL %>" <%= !user? 'readonly': ''%>>
      </div>
      <div class="form-group">
        <label for="insertValue">追記するデータ(csv形式)</label>
        <input class="form-control" id="insertValue" name="insertValue"
        placeholder="Aセル,=row(),12" value="<%= insertValue %>" <%= !user? 'readonly': ''%>></input>
      </div>
      <div class="form-group form-check">
        <input type="checkbox" class="form-check-input" id="redirectToSheet"  name="redirectToSheet" value="checked" <%= !user? 'disabled': ''%>>
        <label class="form-check-label" for="redirectToSheet">書き込み後、スプレッドシートにリダイレクトする</label>
      </div>
      <div>
        <p class="text-warning"><%= error_message %></p>
        <input type="submit" class="btn btn-primary" value="書き込み" <%= !user? 'disabled': ''%>></input>
        <span class="text-secondary align-bottom"></span>
      </div>
    </form>
</div>
</body>
</html>

```
## 9:動作確認 <a id="anchor9"></a>

Webアプリを起動して、http://localhost:3000 へアクセスする
```
$ node index.js
```

* ログインボタンをクリック
![img110.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276579/5b38a463-547a-1113-8790-94e814ab9c86.png)

* アカウント情報(メールアドレス、パスワード)を入力（ログイン済みの場合は、アカウントを選択）
![img120.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276579/a850f26a-e03a-8c25-6401-16ab8ccd6f6d.png)

* テスト用の状態でアプリ登録しているため確認画面が表示される（続行をクリック）
![img130.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276579/058c3aaa-4ed2-afb1-6ab4-1910566bb3fa.png)

* アクセス権限(スプレッドシートの編集)が付与されていることを確認して「続行」をクリック
![img140.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276579/75defa09-e984-70a7-350a-c9e6fde7380a.png)

* 元の画面(入力可能)へ戻るので、スプレッドシートのURLと、書き込みたい内容を記載して「書き込み」をクリック
![img150.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276579/9f792bb2-bdea-fb1b-f52d-8d359bd63315.png)

* スプレッドシートへデータが書き込みできたことを確認（「`書き込み`」をクリックした分だけ順に追記される）
![img160.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/276579/abed6487-cbe8-e471-f3ef-6748d371d2b8.png)

## 10:参考ページ <a id="anchor10"></a>

* [Google OAuth2 Authentication in NodeJS](https://www.loginradius.com/blog/engineering/google-authentication-with-nodejs-and-passportjs/)

* [Google Spreadsheet をサービスアカウントに共有せずにGoogle Spreadsheet APIから読みだす方法](https://qiita.com/7032/items/50fc96b6e8c9ac90c32f)

* [スプレッドシート API で行を追加する](https://qiita.com/castaneai/items/a53d0b89bca5b84654be)