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

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

2021.04.29

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

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

スプレッドシートでは、画面 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 での活用の幅が広がりそうです!

参考

この記事をシェアする

FacebookHatena blogX

関連記事

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