Google Sheets API を使ってスプレッドシートを操作

2020/03/13
WebAPI

こんにちは、サポート部の明石です。

2020 年 1 月に Elevate 2019 Recap セミナー in Japan を開催しましたが、ご来場いただいた PowerBuilder ユーザーの皆様に記入いただいたアンケートで「関心をもたれているキーワード」を集計したところ『PB 資産の Web 化』に対する回答数がトップでしたが、次いで『WebAPI 利用』が 2 番目に多い回答数となりました。

『PB 資産の Web 化』についてはすでにご存じの方も多いかと思いますが、PowerServer を利用することで PowerBuilder 資産を活用して Web 化することが可能です。セットアップ方法については、PowerServer (PB Edition)セットアップで紹介されていますで興味のある方はぜひご覧ください。

『WebAPI 利用』についても外界への扉、HTTPClient で紹介されていますが、今回は PowerBuilder 2017 R3 で追加された OAuth 2.0 などの新機能を絡めた WebAPI の利用方法、具体的には Google スプレッドシート操作について紹介したいと思います。

2021/1/8 追記

本記事の公開後、別の方法を利用することで簡単に Google スプレッドシートが操作できるとの情報をいただきました。是非こちらも参考にしてみてださい。

クリックGoogle スプレッドシートをローコード操作！？

2021/3/18 追記

お待たせいたしました！！Google API に関する新しい記事が完成しましたので、こちらも活用してみてください。

クリックGmail API を使ってメールを操作

Google Sheets API の設定

Google スプレッドシートを API で操作するには、事前に Google Sheets API を有効にし認証情報を設定しておく必要があるようです。詳細な設定方法については Web で『 Google Sheets API 』といったワードで検索すると様々な情報がヒットしますので、想定される環境に合わせて設定してください。

今回、テスト利用することを目的に Google Cloud Platform の無料トライアルを利用して私が設定した簡単な手順を以下に記載します。

  • Google Cloud Platform からプロジェクトを作成し、Google Sheets API を有効にする
    • My First Project ドロップダウンをクリック
    • 「プロジェクトの選択」画面で新しいプロジェクトをクリック
    • 「新しいプロジェクト」画面でプロジェクト名を入力し [作成] ボタンをクリック
    • 再度 My First Project ドロップダウンをクリックし、作成されたプロジェクトを選択
    • API ライブラリに移動し、G Suite カテゴリから Google Sheets API を選択
    • Google Sheets API で [有効にする] ボタンをクリック
    My First Project ドロップダウン

    My First Project ドロップダウン

    API ライブラリ

    API ライブラリ

  • 有効にした Google Sheets API の認証情報を作成する
    • 「API とサービス」メニューにある認証情報を選択
    • [同意画面を構成] ボタンをクリック
    • OAuth 同意画面で User Type を選択し [作成] ボタンをクリック
    • OAuth 同意画面でアプリケーション名を入力し [保存] ボタンをクリック
    • 「API とサービス」メニューにある認証情報を選択し、認証情報を作成から OAuth クライアント ID を選択
    • 「OAuth クライアント ID の作成」画面でアプリケーションの種類と名前を指定し [作成] ボタンをクリック

作成後に表示されるクライアント ID とクライアント シークレットは後々の処理で使用するので忘れないようにテキストに貼り付けて保存するか、メモっておきましょう。以上で Google 側の事前設定は完了です。

認証情報

認証情報

OAuth クライアント作成

OAuth クライアント作成

Authorization Code の生成と Access Token の取得

それではここから PowerBuilder アプリケーションを作成していきましょう。

アプリケーションからは Access Token を使用して承認済みの API リクエストを送信しますが、この Access Token を取得するため、先に Google にログインして Authorization Code を生成します。

今回、ShellExecuteW 関数を使用して指定した URL を引数としてデフォルトブラウザを立ち上げ Authorization Code を生成するため、Global External Functions に関数を宣言します。

Global External Functions

FUNCTION Long ShellExecute ( Uint ihwnd, String lpszOp, String lpszFile, String lpszParams, String lpszDir, Int wShowCmd ) LIBRARY "Shell32.dll" ALIAS FOR "ShellExecuteW"

ブラウザは、ウィンドウ上に用意したボタンクリック時に立ち上げるようにします。サンプルスクリプトは、以下の記述となります。

[コード取得] ボタン Clicked イベント

// 変数宣言
String ls_id
String ls_redirect
String ls_scope
String ls_url
String ls_null

// Null セット
SetNull( ls_null )

// クライアント ID と Redirect URI、Scope を設定
ls_id = "生成されたクライアント ID"
ls_redirect = "urn:ietf:wg:oauth:2.0:oob"
ls_scope = "https://www.googleapis.com/auth/spreadsheets"

// 認証リクエスト用 URL 作成
ls_url = "https://accounts.google.com/o/oauth2/v2/auth?response_type=code&client_id=" + ls_id + "&redirect_uri=" + ls_redirect + "&scope=" + ls_scope
// URL を引数としてブラウザをオープン
ShellExecute( 0, "open", ls_url, ls_null, ls_null, 0 )

// 終了
Return

変数 ls_id には Google Sheets API の設定時に生成されたクライアント ID を設定します。また Redirect の変数に urn:ietf:wg:oauth:2.0:oob を指定していますが、この値は Google の承認サーバーが承認コードをブラウザのタイトル バーに返すことを意味しています。加えて Scope の変数に Google Sheets API の Scope を指定しています。各パラメーターについては、Google のページを参考に設定してください。

ここで生成した Authorization Code は Access Token 取得の際に必要となるので、ウィンドウにシングルラインエディットを用意しそのシングルラインエディットに格納できるようにしておきます。

次に生成した Authorization Code から Access Token を取得する処理を作成します。ここでは Access Token を取得するまでの処理とし API 操作は別の処理で実装するため、取得した Access Token の格納先としてインスタンス変数を用意しておきます。

Instance Variables

String is_access_token

以下は OAuthClient オブジェクトを使用して Access Token を取得するサンプルスクリプトになります。Access Token 取得時は、クライアント ID の他にクライアント シークレットも必要となります。

[Access Token 取得] ボタン Clicked イベント

// 変数宣言
Integer li_rtn
String ls_id
String ls_secret
String ls_redirect
String ls_scope
String ls_authorization
OAuthClient loa_client
TokenRequest ltr_request
TokenResponse ltr_response

// Access Token 初期化
is_access_token = ""

// パラメーター設定
ls_id = "生成されたクライアント ID"
ls_secret = "生成されたクライアント シークレット"
ls_redirect = "urn:ietf:wg:oauth:2.0:oob"
ls_scope = "https://www.googleapis.com/auth/spreadsheets"
// シングルラインエディットから Authorization Code 取得
ls_authorization = sle_1.Text
IF IsNull( ls_authorization ) OR Trim( ls_authorization ) = "" THEN

  Return

END IF

// OAuthClient 作成
loa_client = CREATE OAuthClient

// Authorization Code から Access Token を取得する
ltr_request.Tokenlocation = "https://accounts.google.com/o/oauth2/token"
ltr_request.Method = "POST"
ltr_request.Granttype = "authorization_code"
ltr_request.Clientid = ls_id
ltr_request.Clientsecret = ls_secret
// パラメーターを追加
ltr_request.ClearParams()
ltr_request.AppendParam( "grant_type", "authorization_code" )
ltr_request.AppendParam( "client_id", ls_id )
ltr_request.AppendParam( "client_secret", ls_secret )
ltr_request.AppendParam( "scope", ls_scope )
ltr_request.AppendParam( "code", ls_authorization )
ltr_request.AppendParam( "redirect_uri", ls_redirect )
// Content-Type ヘッダーを設定
ltr_request.ClearHeaders()
ltr_request.SetHeader( "Content-type", "application/x-www-form-urlencoded" )

// トークン情報取得をリクエスト
li_rtn = loa_Client.AccessToken( ltr_request, ltr_response )

// 破棄
DESTROY loa_client

IF li_rtn = 1 AND ltr_response.GetStatusCode() = 200 THEN

  // Access Token 取得
  is_access_token = ltr_response.GetAccessToken()
  // メッセージ
  MessageBox( "確認", "Access Token を取得しました" )

ELSE

  MessageBox( "エラー", "Access Token の取得に失敗しました", StopSign! )

END IF

// 終了
Return

Access Token を取得するには AccessToken 関数を使用してリクエストを送信する必要がありますが、関数を呼び出す前に TokenRequest オブジェクトに認証サーバー URL やプロセスの種類、HTTP メソッドなどを設定する必要があります。

また今回のケースでは、GrantType プロパティに “authorization_code” ( Extension Grant ) を設定しているため AppendParam 関数を使用してパラメーターを追加しています。

PowerBuilder から Google スプレッドシートを操作

Access Token が取得できましたので、これでやっと API を使用してスプレッドシートを操作することができます。

以下のサンプルスクリプトでは、新規にスプレッドシートを作成しタイトルの編集とデータウィンドウからデータを取得して作成したスプレッドシートに値をセットする操作をしています。

[スプレッドシート操作] ボタン Clicked イベント

// 変数宣言
Integer li_rtn
Integer li_cnt_col
Long ll_root
Long ll_cnt_row,ll_cnt_column
Long ll_cnt_child,ll_cnt_array,ll_cnt_array_option,ll_base_array,ll_head_array
Long ll_root_object,ll_child_object,ll_child_array
String ls_id_sheet
String ls_title = '{"requests":[{"updateSpreadsheetProperties":{"properties":{"title":"ぱわあびるだあ"},"fields": "title"}}]}'
String ls_data
String ls_body
String ls_url
String ls_col_name
String ls_col_com
HTTPClient lnv_httpclient
OAuthClient loa_client
OauthRequest loa_request
ResourceResponse lrr_response
JsonParser ljp_parser
JsonGenerator lnv_jsongenerator

// OAuthClient 作成
loa_client = CREATE OAuthClient

// 1.新規スプレッドシートを作成する
loa_request.Method = "POST"
loa_request.Url = "https://sheets.googleapis.com/v4/spreadsheets"
loa_request.SetAccessToken( is_access_token )
li_rtn = loa_client.RequestResource( loa_request, lrr_response )
IF li_rtn <> 1 THEN

  // 破棄
  DESTROY loa_client

  // 終了
  Return

END IF

//  スプレッドシートIDを取得する
// JsonParser 作成
ljp_parser = CREATE JsonParser

// Body データ取得
ls_body = lrr_response.GetHeaders()
lrr_response.GetBody( ls_body )

// ルートアイテムのハンドルを取得
ljp_parser.LoadString( ls_body )
ll_root = ljp_parser.GetRootItem()

// スプレッドシートIDのみ取得
ls_id_sheet = ljp_parser.GetItemString( Ll_root, "spreadsheetId" )

// 2.スプレッドシートのタイトルを変更する
// HTTPClient 作成
lnv_httpclient = CREATE HTTPClient

// スプレッドシート操作用URL
ls_url = "https://sheets.googleapis.com/v4/spreadsheets/" + ls_id_sheet + ":batchUpdate"

// リクエストヘッダーを設定
lnv_httpclient.SetRequestHeader( "Host", "sheets.googleapis.com" )
lnv_httpclient.SetRequestHeader( "Content-type", "application/json" )
lnv_httpclient.SetRequestHeader( "Authorization", "Bearer " + is_access_token )

// タイトル変更リクエスト送信
li_rtn = lnv_httpclient.SendRequest( "POST", ls_url, ls_title )
IF li_rtn = 1 AND lnv_httpclient.GetResponseStatusCode() = 200 THEN
ELSE

  // メッセージ
  MessageBox( "エラー", String( lnv_httpclient.GetResponseStatusCode() ), StopSign! )

  // 破棄
  DESTROY ljp_parser
  DESTROY lnv_httpclient

  // 終了
  Return

END IF

// 3.スプレッドシートにデータをセットする
// サンプル用データウィンドウからデータを取得
// JsonGenerator 作成
lnv_jsongenerator = CREATE JsonGenerator

// オブジェクトのルート項目作成
ll_root_object = lnv_jsongenerator.CreateJsonObject()

// 配列の子項目を追加
ll_head_array = lnv_jsongenerator.AddItemArray( ll_root_object, "requests" )
// オブジェクトの子項目作成
ll_child_object = lnv_jsongenerator.AddItemObject( ll_head_array )
ll_child_array = lnv_jsongenerator.AddItemObject( ll_child_object, "updateCells" )
ll_child_object = lnv_jsongenerator.AddItemObject( ll_child_array, "start" )
lnv_jsongenerator.AddItemNumber( ll_child_object, "sheetId", 0 )
lnv_jsongenerator.AddItemNumber( ll_child_object, "rowIndex", 0 )
lnv_jsongenerator.AddItemNumber( ll_child_object, "columnIndex", 0 )

ll_base_array = lnv_jsongenerator.AddItemArray( ll_child_array, "rows" )
lnv_jsongenerator.AddItemString( ll_child_array, "fields", "userEnteredValue" )

// タイトルをセットする
// オブジェクトの子項目を追加
ll_cnt_child = lnv_jsongenerator.AddItemObject( ll_base_array )
// 配列の子項目を追加
ll_cnt_array = lnv_jsongenerator.AddItemArray( ll_cnt_child, "values" )

// カラム総数を取得
li_cnt_col = Integer( dw_1.Object.DataWIndow.Column.Count )
For ll_cnt_column = 1 To li_cnt_col

  // カラム名を取得しタイトルとしてセットする
  ls_col_name = dw_1.Describe( "#" + String( ll_cnt_column ) + ".Name" )
  // オブジェクトの子項目を追加
  ll_cnt_child = lnv_jsongenerator.AddItemObject( ll_cnt_array )
  ll_cnt_array_option = lnv_jsongenerator.AddItemObject( ll_cnt_child, "userEnteredValue" )
  lnv_jsongenerator.AddItemString( ll_cnt_array_option, "stringValue", ls_col_name )

Next

// オブジェクトの子項目を追加
ll_cnt_child = lnv_jsongenerator.AddItemObject( ll_cnt_array )
ll_cnt_array_option = lnv_jsongenerator.AddItemObject( ll_cnt_child, "userEnteredValue" )
lnv_jsongenerator.AddItemString( ll_cnt_array_option, "stringValue", "合計" )

// 行数分ぐるぐる…
FOR ll_cnt_row = 1 TO dw_1.RowCount()

  // オブジェクトの子項目を追加
  ll_cnt_child = lnv_jsongenerator.AddItemObject( ll_base_array )
  // 配列の子項目を追加
  ll_cnt_array = lnv_jsongenerator.AddItemArray( ll_cnt_child, "values" )

  // データを取得しセットする  
  For ll_cnt_column = 1 To li_cnt_col

    // オブジェクトの子項目を追加
    ll_cnt_child = lnv_jsongenerator.AddItemObject( ll_cnt_array )
    ll_cnt_array_option = lnv_jsongenerator.AddItemObject( ll_cnt_child, "userEnteredValue" )

    // このサンプルでは 1 列目は String 型、その他は Long 型が設定されているデータウィンドウ    
    CHOOSE CASE ll_cnt_column

      // String 型
      CASE 1

        lnv_jsongenerator.AddItemString( ll_cnt_array_option, "stringValue", dw_1.GetItemString( ll_cnt_row, ll_cnt_column ) )

      // Long 型
      CASE ELSE

        lnv_JsonGenerator.AddItemNumber( ll_cnt_array_option, "numberValue", dw_1.GetItemNumber( ll_cnt_row, ll_cnt_column ) )

    END CHOOSE

  Next

  // 合計用の計算式追加
  ll_cnt_child = lnv_jsongenerator.AddItemObject( ll_cnt_array )
  ll_cnt_array_option = lnv_jsongenerator.AddItemObject( ll_cnt_child, "userEnteredValue" )
  ls_col_com = "=SUM(B" + String( ll_cnt_row + 1 ) + ":D" + String( ll_cnt_row + 1 ) + ")"
  lnv_jsongenerator.AddItemString( ll_cnt_array_option, "formulaValue", ls_col_com )

NEXT

// 作成した JSON 形式のデータを取得
ls_data = lnv_jsongenerator.GetJsonString()

// データ変更リクエスト送信
li_rtn = lnv_httpclient.SendRequest( "POST", ls_url, ls_data )
IF li_rtn = 1 AND lnv_httpclient.GetResponseStatusCode() = 200 THEN
ELSE

  // メッセージ
  MessageBox( "エラー", String( lnv_httpclient.GetResponseStatusCode() ), StopSign! )

END IF

// 破棄
DESTROY ljp_parser
DESTROY lnv_httpclient
DESTROY lnv_jsongenerator

// メッセージ
MessageBox( "エラー", "スプレッドシートが作成されました" )

// 終了
Return

Google Sheets API に関するメソッドについては、Google Sheets API リファレンスに今回使用しているメソッド以外も多数掲載されているのでアプリケーション作成時の参考にしてください。

なお今回のサンプルプログラムではデータウィンドウからデータを取得して JSON 形式のデータ作成をしています。データウィンドウ上にカラムは 4 つ用意されていて 1 列目は String 型になりますが、それ以外は Long 型が設定されています。データ設定の際、セルによって設定するプロパティが異なりますので注意してください。また、2 列目から 4 列目の合計列を作成していますがこちらも作成されるサンプルプログラムにあわせて変更してください。

軽い気持ちで検証したつもりが、今までのブログの中でもかなりボリューミーなスクリプトになってしまいました、お腹いっぱいですね。。。

実行

では作成したアプリケーションを実行してみましょう。作成されたアプリケーションの画面イメージは以下のようになっています。

画面イメージ

はじめに [コード取得] ボタンをクリックします。するとブラウザが立ち上がり Google アカウントの選択画面 (もしくはログイン画面) が表示されます。使用するアカウントを選択するとアプリ登録していないので「このアプリは確認されていません」ワーニングメッセージが表示されます。

画面下の [詳細] をクリックしご自身が作成されたアプリ経由であるなど信頼性が保証できるアプリである場合は、[ “OAuth クライアント ID の名前” (安全ではないページ) に移動] リンクをクリックします。

※安全性を十分ご確認の上、自己責任においてページ移動をしてください。

ワーニング1
ワーニング2

“OAuth クライアント ID の名前” への権限の付与ページに遷移し、[許可] ボタンをクリックすると「選択内容を確認してください」画面が表示されるので改めて [許可]ボタンをクリックします。

権限付与
選択内容確認

ログイン画面に遷移し、画面上にコードが表示されるのでこのコードをコピーします。これが Authorization Code です。

uthorization Code

PowerBuilder アプリケーションに戻り、コピーしたコードを authentication code 欄に貼り付けて [Access Token 取得] ボタンをクリックします。成功すると「Access Token を取得しました」のメッセージが表示されます。

最後に [スプレッドシート操作] ボタンをクリック。成功すると「スプレッドシートが作成されました」のメッセージが表示されます。

[スプレッドシート操作] ボタンクリック前の Google ドライブのマイドライブには何も格納されていないのが確認できるかと思います。

作成前

[スプレッドシート操作] ボタンクリック後、再度マイドライブを確認すると。。。

作成後

おお、スプレッドシートが作成されている。中身も確認すると、

シート内

うん、想定通りのデータがセットされているのが確認できました。

最後に

今回はより業務に近いと思われる Google スプレッドシートの利用について紹介しましたが、Appeon 社の Community には YouTube Data API や Twitter に関するブログも公開されていますので、興味のある方は併せて確認してみてください。

また、「PowerBuilder を利用してこんな風に Web API と連携したよ ! 」という強者ユーザー様がおりましたら、弊社営業や SNS 経由でも構いませんのでぜひとも情報提供をいただけると非常にありがたいです (笑)

さらにブログを書いていただけると (ry

以上、明石でした。

テクニカルブログ 一覧を見る

月間アクセスランキング

PowerBuilder マイグレーション
PowerBuilder学習、動画で始めちゃう？

週間アクセスランキング

2024/12/25

～物流を止めない～ PowerBuilderで実現する自動車整備業界のDX　株式会社滝川自工様事例
oracle client
2019/12/19

Oracle Client 32bit / 64bit 共存環境の構築
動的SQLの使い方
2022/08/31

ダイナミックに SQL を実行！～動的 SQL の使い方【前編】～
2023/03/16

PowerBuilder とは？ ～PowerBuilder 入門！！ 第 1 弾～
Google ドライブへアクセス
2021/09/14

Google Drive API で Google ドライブへアクセス

最新記事

PowerServer が生まれ変わります
2024/10/01

PowerServer が生まれ変わります
PowerBuilder 入門！！ シリーズ まとめ
2024/09/02

PowerBuilder 入門！！ シリーズ まとめ
最終回: デバッガの便利な機能 ～PowerBuilder 入門！！第 9 弾 (2) ～
2024/02/21

最終回: デバッガの便利な機能 ～PowerBuilder 入門！！第 9 弾 (2) ～
デバッガの機能紹介 ～PowerBuilder 入門！！第 9 弾 (1) ～
2024/02/20

デバッガの機能紹介 ～PowerBuilder 入門！！第 9 弾 (1) ～
アプリの配布方法とランタイムパッケージャ ～PowerBuilder 入門！！第 8 弾 (2) ～
2024/02/20

アプリの配布方法とランタイムパッケージャ ～PowerBuilder 入門！！第 8 弾 (2) ～