Google Apps Script でスプレッドシートデータを CSV にして GCS に出力してみた。

2021.04.29

こんにちは、みかみです。

スプレッドシートでは、画面 GUI 操作でデータを CSV 形式でダウンロードすることもできますが、 ダウンロードした CSV を GCS に配置したい場合、いちいち手動でアップロードするのは面倒です。

ということで。

やりたいこと

  • スプレッドシートデータを CSV ファイルで GCS に出力したい

前提

ファイル出力先の GCS バケットは作成済みです。

また、本エントリでご紹介の内容は、以下の記事を参考にさせていただきました。 どうもありがとうございました!

スプレッドシートを準備

まずはスプレッドシートを作成します。 シート名を data に変更して、出力データを追加します。

シートを追加して config という名前に変更後、以下の GCS バケット情報を記入します。

  • 出力先バケット名
  • 出力ファイルパス

入力したバケット名とファイルパスのセルを「名前付き範囲」として指定しておきます。 [データ]メニューから[名前付き範囲]を選択して、「範囲を追加」します。 以下 2 つの名前付き範囲を設定しました。

  • gcsBucket:バケット名記入セル
  • gcsFilePath:ファイルパス記入セル

[ツール]メニューから[スクリプトエディタ]をクリックして、Google Apps Script のエディタ画面を表示します。

「ライブラリ」から、OAuth2 のライブラリを追加します。 ライブラリ追加ポップアップで、以下の ID を入力して「検索」します。

 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF

プルダウンから最新バージョンを選択して「追加」します。

エディタに以下のスクリプトを入力しました。

/*********************************************
 * スプレッドシート関連処理
 *********************************************/
// データシート名
var SHEET_NAME = 'data';

// 連携データ取得
function getMainSheet(){
  return SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
}
// 連携先GCSバケット情報取得
function getGCSSettings(){
  var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  var settings = {};
  settings.bucketName = spreadSheet.getRangeByName("gcsBucket").getValue();
  settings.filePath = spreadSheet.getRangeByName("gcsFilePath").getValue();
  if(settings.bucketName && settings.filePath){
    Logger.log("settings: " + JSON.stringify(settings));
    return settings;
  }
  return null;
}

// CSVデータ生成
function convertSheet2CSV(){
  var sheet = getMainSheet();
  var values = sheet.getDataRange().getValues();
  Logger.log(values);
  var csv = values.join("\r\n");
  Logger.log(csv);

  return csv;
}
// CSVファイルをGCSにアップロード
function uploadCSVFile(){
  var csv = convertSheet2CSV();
  var content = Utilities.newBlob(csv)
  var gcs = getGCSSettings();
  if(storeContentIntoGCS(content, gcs.bucketName, gcs.filePath)){
    Browser.msgBox("アップロードが完了しました。");
  }
}

/*********************************************
 * GCP関連処理
 *********************************************/
// OAuth 2.0 クライアント認証情報
function getClientConfig() {
  return {
    clientId: "xxxxxxxx",
    clientSecret: "xxxxxxxx"
  };
}
// 認証用サービス取得
function getStorageService() {
  var cfg = getClientConfig();
  Logger.log("clientId: " + cfg.clientId + " / clientSecret: " + cfg.clientSecret);
  return OAuth2.createService('provisioning')
  .setAuthorizationBaseUrl('https://accounts.google.com/o/oauth2/auth')
  .setTokenUrl('https://accounts.google.com/o/oauth2/token')
  .setClientId(cfg.clientId)
  .setClientSecret(cfg.clientSecret)
  .setCallbackFunction('authCallback')
  .setPropertyStore(PropertiesService.getScriptProperties())
  .setScope('https://www.googleapis.com/auth/devstorage.full_control')
  .setParam('login_hint', Session.getActiveUser().getEmail())
}
// 認証コールバック
function authCallback(request) {
  var service = getStorageService();
  var isAuthorized = service.handleCallback(request);
  if (isAuthorized) {
    Logger.log("isAuthorized!");
    return HtmlService.createHtmlOutput('<center>認証完了<br>タブを閉じてください。</center>');
  } else {
    Logger.log("is not Authorized...");
    return HtmlService.createHtmlOutput('認証エラー<br>認証情報をお確かめください。');
  }
}

// GCSにcontentを保存
function storeContentIntoGCS(content, bucketName, filePath) {
  var service = getStorageService();
  if (!service.hasAccess()) {
    // 認証ダイアログ表示
    var authorizationUrl = service.getAuthorizationUrl();
    var template = HtmlService.createTemplate(
      '<center><a href="<?= authorizationUrl ?>" target="_blank">認証実行</a></center>' +
      '<br><br><center>GCPプロジェクト認証が必要です。<br>認証実行後にもう一度アップロードしてください。</center>'
    );
    template.authorizationUrl = authorizationUrl;
    var page = template.evaluate();
    SpreadsheetApp.getUi().showModalDialog(page, "Google API認証");
    return false
  } else {
    // CSVデータ送信
    var url='https://storage.googleapis.com/'+bucketName+filePath;
    UrlFetchApp.fetch(url,{
      headers: {
        Authorization: "Bearer " + service.getAccessToken(),
      },
      method: "PUT",
      contentType: "application/javascript;charset=utf-8",
      host: bucketName + ".storage.googleapis.com",
      payload: content
    });
  }
  return true;
}

/*********************************************
 * 「CSV出力」メニュー追加
 *********************************************/
function onOpen(){
  var gcsSettings = getGCSSettings();
  if(!gcsSettings){
    Browser.msgBox("GCS設定が不足しています。GCSアップロード設定のシートを確認してください");
  }

  var ui = SpreadsheetApp.getUi();
  ui.createMenu('CSV出力')
    .addItem('アップロード', 'uploadCSVFile')
    .addToUi();
}

50, 51行目の clientIdclientSecret は、後ほど GCP OAuth 2.0 クライアント ID を追加後に更新します。

スクリプトの入力が完了したら、「プロジェクトの設定」から「スクリプト ID」をコピーしておきます。

GCP で OAuth 2.0 クライアントを作成

GCP 管理コンソールナビゲーションメニューから、「API とサービス」「認証情報」の順にクリックして、「認証情報を作成」します。

初めてOAuth 2.0 クライアントを作成する場合には、先に「OAuth 同意画面」で同意する必要があります。

OAuth クライアント ID 作成画面で「アプリケーションの種類」プルダウンから「ウェブアプリケーション」を選択。 任意の「名前」を入力して「承認済みのリダイレクト URI」に以下の URL を入力して「作成」をクリック。

 https://script.google.com/macros/d/[スクリプトID]/usercallback

[スクリプトID]部分は、スプレッドシート準備の最後にコピーしておいたスプレッドシートの「スクリプト ID」に書き換えます。

クライアント作成ポップアップに表示される「クライアント ID」と「クライアントシークレット」をコピーしておきます。

スクリプト実行

スプレッドシートのスクリプトエディタから、50, 51行目の clientIdclientSecret の値を、 先ほど作成した OAuth 2.0 クライアントの「クライアント ID」と「クライアントシークレット」で更新したら、準備完了です。 スクリプトに認証情報が含まれることになるため、スプレッドシートのアクセス権限にはご注意ください。

スプレッドシートをリロードして少し待つと、メニューバーに「CSV出力」メニューが追加されるので、「アップロード」をクリックします。

初回実行時にはスプレッドシートのアクセス許可ポップアップ画面が表示されるので、内容を確認して「許可」します。

さらに、OAuth 2.0 クライアント認証ポップアップで「認証実行」します。

認証完了の別タブが開くのを確認したら、再度「CSV出力」メニューから「アップロード」を実行します。 アップロード完了ポップアップが表示されればOKです。

CSV ファイルが正常に出力されたか、GCS 管理画面から確認してみます。

ファイルが出力されていることが確認できました。 中身のデータも確認してみます。

無事、スプレッドシートのデータを CSV ファイルとして GCS に出力することができました!

まとめ(所感)

スプレッドシートで管理しているデータを BigQuery に取り込みたいケースもあるのではないでしょうか?

そんな時には、GCS イベントトリガーで実行する CloudFunctions を合わせて作成しておけば、出力した CSV をそのまま BigQuery にロードすることもできます。

Google Apps Script は JavaScript ベースの親しみやすいスクリプト言語で、ブラウザ上で編集可能でデバッグ機能も利用できるので、メンテナンス性も良いのではないかと思います。

Google Apps Script から GCP サービスにアクセスする場合の認証方法は、今回利用した OAuth2 の他にサービスアカウントによる認証も利用できるとのことです。 また、今回利用した GCS 以外にも、API 経由で他の GCP サービスと連携することも可能です。 Google Apps Script を利用すれば、スプレッドシートデータの GCP での活用の幅が広がりそうです!

参考