Google Apps Script でスプレッドシートデータを CSV にして GCS に出力してみた。
こんにちは、みかみです。
スプレッドシートでは、画面 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行目の clientId と clientSecret は、後ほど 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行目の clientId と clientSecret の値を、
先ほど作成した 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 での活用の幅が広がりそうです！
