ローカルファイルを読み込む
Googleスプレッドシートはサーバー上に存在するため、ローカルファイルを処理するには一工夫する必要があります。
そこで、ローカルファイルをスプレッドシートに読み込むサンプルコードを紹介してみようと思います。
サンプルコード
GoogleAppsScript の コード.gs と、GoogleAppsScript から呼び出す HTMLファイルの dialog.html 、そしてアップロードするローカルファイル upload.txt を用意します。
コード.gs
function main() { var html = HtmlService.createHtmlOutputFromFile("dialog"); SpreadsheetApp.getUi().showModalDialog(html, 'ローカルファイル読込'); } function writeSheet(formObject) { // フォームで指定したテキストファイルを読み込む var fileBlob = formObject.myFile; // テキストとして取得（Windowsの場合、文字コードに Shift_JIS を指定） var text = fileBlob.getDataAsString("sjis"); // 改行コードで分割し配列に格納する var textLines = text.split(/[\s]+/); // 書き込むシートを取得 var sheet = SpreadsheetApp.getActiveSheet(); // テキストファイルをシートに展開する for (var i = 0; i < textLines.length; i++) { sheet.getRange(i + 1, 1).setValue(textLines[i]); } // 処理終了のメッセージボックスを出力 Browser.msgBox("ローカルファイルを読み込みました"); }
dialog.html
<!DOCTYPE html> <html> <head> <base target="_top"> <script> // すべてのフォームをイベントリスナーに登録する function preventFormSubmit() { var forms = document.querySelectorAll('form'); for (var i = 0; i < forms.length; i++) { forms[i].addEventListener('submit', function(event) { event.preventDefault(); }); } } window.addEventListener('load', preventFormSubmit); // フォームのサブミットで呼ばれる処理 function readFile(formObject) { // GASで定義した関数を呼び出す google.script.run.writeSheet(formObject); } </script> </head> <body> <form id="myForm" onsubmit="readFile(this)" enctype="multipart/form-data"> <input name="myFile" type="file" /><br> <button type="submit">読込</button> </form> </body> </html>
upload.txt
一行目 二行目 三行目
実行の流れ
当サンプルコードを実行した時の流れです。
スクリプトエディタより
main()を実行すると、スプレッドシート上にローカルファイルのアップロード用ダイアログが表示される
「ファイルを選択する」よりアップロードするローカルファイルを指定する
「読込」ボタンを押して処理開始
読込みが終了すると「ローカルファイルを読み込みました」とポップアップが表示される
ローカルファイルの値がスプレッドシートに書き込まれている
解説
前述したように Googleスプレッドシートはサーバー上に存在しているため、ローカルファイルを直接処理することができません。処理するためには仲介役が必要になります。ここではダイアログが仲介役となります。
プログラムの流れ
- GAS側の
function main()を実行しdialog.htmlをもとにしたダイアログを表示する - アップロードするローカルファイルを指定したあと、ダイアログの「読込」ボタンを押すことで
<form>のonsubmitに指定したreadFile(this)が実行される。ここでthisには form の情報が設定される function readFile(formObject)でgoogle.script.run.writeSheet(formObject)を呼び出し、form の情報を HTMLからGASへ受け渡す- GASの
function writeSheet(formObject)ではvar fileBlob = formObject.myFileによりローカルファイル情報を Blob 型で受け取る - Blob 型をテキストとして処理するため
var text = fileBlob.getDataAsString("sjis")とする。なお Windows 環境のテキストファイルであるため文字コードに Shift_JIS を指定している - テキストデータの改行コードを処理するため
var textLines = text.split(/[\s]+/)とし、テキストデータの各行を配列に分割する - これでテキストデータを配列に格納できたため、あとはスプレッドシートに設定して終了となる
補足
preventFormSubmit()について
dialog.html のpreventFormSubmit() は上記説明で登場しませんでしたが、これは <form> を使用する際に必要となるもので、これを記述することでボタン押下時のイベントが正しく起動するようになっています。ですので必ず記述するようにしてください。
参考記事
スプレッドシートの読込み・書込み
Googleスプレッドシートを操作するにあたって、一番の基本は値の「読込み」と「書込み」です。
例として、A～C列に設定された値を読込み、四則演算の結果をE列に書込むサンプルプログラムを紹介します。
実行前
E列には値が設定されていません。
サンプルコード
このコードを実行します。
function myFunction() {
// 現在選択中のシートを取得する
var sheet = SpreadsheetApp.getActiveSheet();
// 1行目から最終行まで処理を繰り返す
for (var row = 1; row <= sheet.getLastRow(); row++) {
// A～C列の値を読込む
var a = sheet.getRange("A" + row).getValue(); // 四則演算の左辺を取得（A列）
var ope = sheet.getRange("B" + row).getValue(); // 四則演算の記号を取得（B列）
var b = sheet.getRange("C" + row).getValue(); // 四則演算の右辺を取得（C列）
// 四則演算の記号に応じた計算を行う
var ans = 0;
switch(ope) {
case "+":
ans = a + b;
break;
case "-":
ans = a - b;
break;
case "*":
ans = a * b;
break;
case "/":
ans = a / b;
break;
}
// E列に計算結果を書込む
sheet.getRange("E" + row).setValue(ans);
}
}
実行後
計算結果がE列に設定されています。
説明
プログラムの流れとしては以下の通りです。
- 処理対象のシートを取得する（ SpreadsheetApp.getActiveSheet() ）
- A～C列の値を読み込む（ getRange().getValue() ）
- E列に計算結果を書き込む（ getRange().setValue() ）
- 2～3を先頭行から最終行まで繰り返す（ getLastRow() ）
スプレッドシートのオブジェクトは
Spreadsheet > Sheet > Range > value
という親子関係になっています。
慣れるまでは親の方から順番にアクセスするように気をつけた方が良いでしょう。
Rangeオブジェクトに直接値を代入しようとしてエラーになるケースもよくありますので、 必ず getValue() や setValue() を介して操作するようにしましょう。
getLastRow() は「シート内で値が設定されている最終行を返す」というものです。他にも appendRow() という「最終行の次行に値を追加する」といった GoogleAppsScript 独自の便利機能が多数ありますので、英語ですが公式リファレンスに目を通してみるのも良いでしょう。
