Google Apps Script（GAS）でスプレッドシートの複数シートを一括CSVエクスポートする
スプレッドシートをCSVにエクスポートする際、
エクスポートするシートを選んで、「ファイル」→「ダウンロード」→「カンマ区切りの値(.csv、現在のシート)」を押し、1シートずつCSVとしてダウンロードしなければなりません。
シートの数が多いほどめんどくさいです。
複数シートを一括でzipにまとめてダウンロードするGASを実装したので、紹介します。
HTMLやJavaScript に関してはきちんと学んだわけではなく、必要な部分だけ調べてとりあえず動くものを作っているので、お作法など間違っている可能性が高いです。
まず前提
趣味開発に使うマスタデータをスプレッドシートで作ることにしました。(Excel未所持で、WinとMac環境を持っており、やる気になったときにどの環境でも入力作業ができるようにするため)
※ここでのマスタデータとはアプリで使用する共通となる基本的な情報で、RDSに投入する目的で作成する、CSV形式のデータです。
どんなアプリを作るにしても、たいてい必要になりそうなので、趣味開発に本格的に取り掛かる前に作ってしまいます。
使用するスプレッドシート
外部キーなどidだけではわかりづらく入力しづらいものがあるので、タブで日本語のリストを選ぶとVLOOKUP関数などで対応するidが表示されるなど、工夫をします。(別途内容を書くかもです)
そのままCSVに書き出すと、日本語のリストなど邪魔なカラムが存在するので、出力用のシートと、入力用のシートで分けました。
出力用のシートには日本語カラムを抜いた入力用シートを表示します。
入力用のシート名は「#hogehoge」と文頭に「#」シャープをつけています。
出力用のシートはシャープなし「hogehoge」とつけました。
GASで実現したいこと
サイドバーなりなんなりから、ボタン1つで出力用のシートすべてがcsvに変換されzipにまとまられた状態でダウンロードできること。
手順
1. 「#」なしの出力用のシートのみを取得する
2. csvエクスポート用のURLを出力するシート分取得する
3. 2のURLからCSVを取得する
4. 3で取得したCSVをzipにまとめ、GoogleDriveへ保存する(履歴を保存したいため)
5. GoogleDriveへ保存したzipのダウンロード用のURLを取得する
6. スプレッドシートのサイドバー(自作)から、ボタンを押すと、5をダウンロードする
1. 「#」なしの出力用のシートのみを取得する
function getSheetId(){
return "1xiv3mOfz2P95zD96vRzrErAohuBsCsEhaT2BolO5jkU"
}
function getSpreadSheet(){
return SpreadsheetApp.openById(getSheetId())
}
function fillterdSheets() {
const sheets = getSpreadSheet().getSheets()
return sheets.filter(function(sheet){ return (sheet.getSheetName()[0] != "_" ) })
}説明しやすいのとメンテしやすいよう細かく関数を分けたので、
関数ごとに説明します。
「function getSheetId()」 で返している文字列は、マスタデータのスプレッドシートのIDです。後述する出力シートを一覧表示する機能のためにいちいちベタ書きするのがめんどくさくて関数にしました。
どこにあるかというと、スプレッドシートのURL「https://docs.google.com/spreadsheets/d/1xiv3mOfz2P95zD96vRzrErAohuBsCsEhaT2BolO5jkU/edit#gid=0」
太線の部分です。
「function getSpreadSheet()」では、スプレッドシートのオブジェクトを取得しています。取得する際に、どのスプレッドシートを取得するかを指定するために、「getSheetId()」でIDの文字列を取得しています。
「function fillterdSheets()」では、文頭に「#」がついていないシートを取得しています。
「getSpreadSheet().getSheets()」で指定したスプレッドシートのシートオブジェクト(タブ)を全件取得し、「.filter」を使い、条件に一致するオブジェクトのみを抽出します。シートオブジェクトは「.getSheetName()」でシート名が取得できます。「getSheetName()[0]」で最初の文字を取得して判別しています。
2. csvエクスポート用のURLを出力するシート分取得する
どうやらスプレッドシートでは下記のURLを作ることで指定したシートをCSVでダウンロードができるようです。
https://docs.google.com/spreadsheets/d/${getSheetId()}/export?format=csv&gid=${シート(タブ)の固有ID}&access_token=${トークン}こちらのリンクをシート分作り、GAS上でダウンロードすれば、シートの中身を配列で取得して、CSV形式のテキストにして……と面倒な手順を踏まなくて良さそうです。
function getCsvUrls() {
const sheets = fillterdSheets()
const oauthToken = ScriptApp.getOAuthToken()
const urls = sheets.map(function(sheet){
return {fileName: `${sheet.getSheetName()}.csv`, url: `https://docs.google.com/spreadsheets/d/${getSheetId()}/export?format=csv&gid=${sheet.getSheetId()}&access_token=${oauthToken}`}
})
return urls
}シート(タブ)の固有IDを取得するには、シートオブジェクトに「.getSheetId()」を使えば取得できます。
mapを用いて、URLを組み立てて、シート分のURLとファイル名の連想配列の配列を作ります。
3. 2のURLからCSVを取得する
function getCsvFiles() {
const csvUrls = getCsvUrls()
const csvFiles = csvUrls.map(function(url){
Utilities.sleep(3000)
let blob = UrlFetchApp.fetch(url.url).getAs(MimeType.CSV)
blob.setName(url.fileName)
return blob
})
return csvFiles
}map内で2で作ったURLを叩いて、CSVファイル(blob)を取得します。
blobごとに「.setName(url.fileName)」でファイル名を設定します。
どうやら、ダウンロードを短時間に連続で行うと規制がかかるようなので「Utilities.sleep(3000)」で3秒待つようにしています。
4. 3で取得したCSVをzipにまとめ、GoogleDriveへ保存する
function getZip() {
const csvFiles = getCsvFiles()
const now = new Date().toISOString()
return Utilities.zip(csvFiles,`${getSpreadSheet().getSheetName()}_${now}.zip`);
}
function saveZip() {
const folder = DriveApp.getFolderById("18vYHfAUICaO5MPay1WEmt0iACdtOT6xN")
const zip = getZip()
let file = folder.createFile(zip)
file.setName(zip.getName())
return file.getDownloadUrl()
}「function getZip()」で3で取得したCSVファイルをzipにまとめます。
zipファイル名がかぶらないように、日時をファイル名に指定します。
「function saveZip()」でGoogleDriveの指定したフォルダへ保存とそのフォルダのダウンロード用URLを取得します。
「DriveApp.getFolderById("18vYHfAUICaO5MPay1WEmt0iACdtOT6xN"はフォルダのIDです。GoogleDriveでフォルダ作り、そのフォルダのURL「https://drive.google.com/drive/folders/18vYHfAUICaO5MPay1WEmt0iACdtOT6xN」太線の部分です。
「let file = folder.createFile(zip)」フォルダ内にzipファイルを作り、「file.setName(zip.getName())」日時指定のファイル名を指定します(ここはGoogleDriveのオブジェクトに名前をつけています)
「file.getDownloadUrl()」でGoogleDrive上のフォルダのダウンロードURLを取得します。
6. スプレッドシートのサイドバー(自作)から、ボタンを押すと、5をダウンロードする
AppScript上のファイルを追加します。「index.html」とします。
5までの手順で触っていたgsファイルに戻り、「index.html」をスプレッドシート上から見れるようにします。
function onOpen() {
SpreadsheetApp.getUi().createMenu('CSV').addItem('CSV Exporter', 'showSidebar').addToUi();
}
function showSidebar() {
let htmlOutput = HtmlService.createHtmlOutputFromFile('index').setTitle("CSV Exporter").setWidth(200);
SpreadsheetApp.getUi().showSidebar(htmlOutput);
} ここの説明は省きます。
AppsScriprのトリガーを設定します。
権限を求めるポップアップが出るので、承認します。
onOpenを起動時に実行されるようにします。
成功していれば、ヘルプ横に作成したタブが表示され、メニュークリックでサイドバーが開きます。
index.htmlを開きます。
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<div id="downloadMessage"></div>
<a id="download"></a>
<button onclick="viewExportSheets()">リスト更新</button>
<button onclick="exportCsv()">CSV書き出し</button>
<h2>書き出しシートリスト</h2>
<ul id="exportList"><li>読込中...</li></ul>
</body>
<script>
window.onload = function(){
viewExportSheets()
}
function exportCsv() {
document.getElementById("downloadMessage").textContent = "作成中";
google.script.run.withSuccessHandler(function(url){
let download = document.getElementById("download")
download.innerHTML = url
download.href = url
download.target="_blank"
download.rel="noopener noreferrer"
download.click();
document.getElementById("downloadMessage").textContent = "自動ダウンロードできない場合はクリック"
}).saveZip()
}
function viewExportSheets() {
google.script.run.withSuccessHandler(function(names){
let exportList = document.getElementById("exportList")
exportList.innerHTML = "";
names.forEach(function(name) {
let li = document.createElement('li');
li.appendChild(document.createTextNode(name))
exportList.appendChild(li)
})
}).getSheetNames()
}
</script>
</html>このコードを使ったサイドバーでできることは2つあります。
書き出し対象のシート名一覧を見ることとzipのダウンロードです。
5までの手順で触っていたgsファイルの関数はそのままでは呼び出すことができません。
「google.script.run.関数名()」で実行できます。
さらに戻り値を取得して処理するには、
「google.script.run.withSuccessHandler(function(戻り値を受け取る変数名){ }).関数名()」を使います。
// コード.gs()
function hoge(){
return "hello!"
}
// index.html
google.script.run.withSuccessHandler(function(value){
alert(value) // "hello" と書かれたアラートがでる
}).hoge()書き出し対象のシート名一覧
function viewExportSheets() {
google.script.run.withSuccessHandler(function(names){
let exportList = document.getElementById("exportList")
exportList.innerHTML = "";
names.forEach(function(name) {
let li = document.createElement('li');
li.appendChild(document.createTextNode(name))
exportList.appendChild(li)
})
}).getSheetNames()
}htmlを操作するJavaScriptの詳しい説明は省きます。
gsファイルの関数「.getSheetNames()」から、書き出し対象のシート名を受け取り、「<ul id="exportList"><li>読込中...</li></ul>」の<ul></ul>内にリストを入れています。
window.onload = function(){
viewExportSheets()
}index.htmlの読み込み時にリスト表示を行います。
zipのダウンロード
function exportCsv() {
document.getElementById("downloadMessage").textContent = "作成中";
google.script.run.withSuccessHandler(function(url){
let download = document.getElementById("download")
download.innerHTML = url
download.href = url
download.target="_blank"
download.rel="noopener noreferrer"
download.click();
document.getElementById("downloadMessage").textContent = "自動ダウンロードできない場合はクリック"
}).saveZip()
}gsファイルの関数「.saveZip()」から、ダウンロードURLを取得し、「<a id="download"></a>」内に、ダウンロードURLをセットします。
通常なら、aタグにdownload属性をつけると、別タブを開かずにそのままの画面でダウンロードできるようですが、スプレッドシート上では行えないようなので、別タブを開く設定(download.target="_blank")を指定しました。
その上、JavaScript上でダウンロードURLをセットしたaタグをクリックさせます。
一瞬別タブが開き、ダウンロードが行われます。
間違ったことを書いていたりしたら、優しくご指摘いただけるとありがたいです。
参考
この記事が気に入ったらサポートをしてみませんか？