2018-01-26

「詳解！GoogleAppsScript 完全入門」を読んで反省した話

書評

発売日には買っていたのですが、ようやく読みました。

詳解! GoogleAppsScript完全入門 ~GoogleApps & G Suiteの最新プログラミングガイド~

作者: 高橋宣成
出版社/メーカー: 秀和システム
発売日: 2017/12/23
メディア: 単行本
この商品を含むブログを見る

GoogleAppsScript は公式リファレンスが日本語に対応しておらず、数少ない書籍も参考にはなるもののボリュームが欲しい、という状況だったので、450ページと分厚く、完全入門と冠した本書にはとても期待をしていました。

とはいえ、自分は GASの開発はある程度出来るし、入門書なら急いで読まなくていいかな～っと思って後回しにしていたのですが、いざ読んでみると出来ていないことがいっぱいで、勉強することはまだまだあるなーと再認識。反省です。

例えばスクリプトエディタの章では「単語補完」の説明があり、「確実に使えるようにしておきましょう」と注釈があるのですが、…自分は全く使っていませんでした笑。長い関数名や変数名はコピペで済ます良くない習慣がついていて、これを機に直すようにしたいと思います。

また、別言語の経験はあるものの JavaScript は軽く触る程度なので、使えてない JavaScript の構文が多々ありました。 GAS についての本ですが、 JavaScript の勉強にもなるのは本当に助かります。

特に例外処理が顕著で、try ～ catch は使っていても finally と throw は全く使っていませんでした。使うべきかどうかは状況によるとは思いますが、頭の中の選択肢にさえなかったので、良くないですね。 JavaScript 本だと DOM 関連など GAS と関係ないことも書いてありますが、この本ならGAS に関係あることだけ書いてあるので、自分の知識を網羅的に再確認できます。

他に優れていると感じたのは「実践を意識している」点です。ただ単に構文を説明するのではなく、著者の高橋さんが躓いたであろうことを注釈してくれたり、規模が大きくなると必要になってくる「オブジェクト」の章も用意されており、学ぶことがゴールではなく、「学んだことを仕事に活かすためのノウハウ」が満載でした。 GAS は実際に使ってみないと分からないことが多いので、これから入門する人には強くオススメできます。

一通り読みましたが、Googleドキュメントなど扱い慣れてないサービスに触れる際のリファレンスとしても使えそうなので、大事に保管して頼りにしていきたいと思います。

2017-11-08

終日イベントの登録

基本編新機能

はじめに

GoogleAppsScript に新しく追加された「終日イベント登録」の検証です（2017.10.10 リリース）。 Calendar.createAllDayEvent(title, date)

f:id:rokuni62:20171107092503p:plain

実は今までは GoogleAppsScript でカレンダーに「終日イベント」を登録するメソッドが無かったんですね。

Gmail 内の日付から Googleカレンダーに登録するスクリプトを書いたことがあるのですが、終日イベントを登録する方法がないことに愕然とした記憶があります…。

仕方がないのでその時は Calendar.createEvent で終日イベントっぽいものを登録しておいたのですが、新メソッドで実現できるようになるのは嬉しいですね。

では早速使ってみたいと思います。なお、比較のため、終日イベントを登録するメソッドが出来る前に書いておいた「終日っぽいイベント」も作成しています。

サンプルコード

function myFunction() {
  
  // createEvent で終日イベント（っぽいもの）を登録
  CalendarApp
  .getDefaultCalendar()
  .createEvent(
    "ブログ公開日（旧）"
    , new Date("2017/11/08 00:00:00")
    , new Date("2017/11/08 00:00:00")
  )

  // createAllDatEvent で終日イベントを登録
  CalendarApp
  .getDefaultCalendar()
  .createAllDayEvent(
    "ブログ公開日（新）"
    , new Date("2017/11/08") 
  )
}

実行結果

f:id:rokuni62:20171107093545p:plain

解説

プログラム自体は難しくないと思うので特に解説しません。

ただ単にイベントを登録しているだけなのですが、 createEvent では 0:00 に登録することで終日イベントっぽい見た目にしています。

ですが createAllDayEvent でちゃんと終日イベントとして登録されている方が分かりやすいですね。当たり前ですが。

さいごに

「終日イベント」を登録するメソッドの紹介ということでしたが、

いままでは終日イベントが「登録できなかった」
これからは終日イベントが「登録できるようになった」

ということを経緯も含めて覚えていただければ幸いです。

例えば、誰かのスクリプトを修正する際に「終日イベントなのに、なんで終日イベントで登録してないんだ？」と思うこともあるかもしれません。

その時に「ああ、昔は終日イベントで登録出来なかったからか。修正しておこう」と判断できるのではないでしょうか。

2017-11-01

指定した行や列を移動させる

新機能基本編

はじめに

GoogleAppsScript に新しく追加された以下メソッドの検証です（2017.10.10 リリース）。

指定した行や列を移動させるだけなので簡単そうだと思ったものの、意外と使い方に癖がありました。読むだけで理解できるように書いたつもりなので是非ご一読ください。

Sheet.moveColumns(columnSpec, destinationIndex)

https://developers.google.com/apps-script/reference/spreadsheet/sheet#moveColumns(Range,Integer)

Sheet.moveRows(rowSpec, destinationIndex)

https://developers.google.com/apps-script/reference/spreadsheet/sheet#moveRows(Range,Integer)

Sheet.moveColumns(columnSpec, destinationIndex)

サンプルコード

まずは公式のサンプルコードをそのまま動かしてみます。

function myFunction() {
  // The code below moves rows A-B to destination index 5.
  // This results in those columns becoming columns C-D.
  var sheet = SpreadsheetApp.getActiveSheet();
  // Selects column A and column B to be moved.
  var columnSpec = sheet.getRange("A1:B1");
  sheet.moveColumns(columnSpec, 5);
}

実行前

f:id:rokuni62:20171026191253p:plain

実行後

f:id:rokuni62:20171026191141p:plain

結果検証

A,B 列が C,D 列に移動し、もとあった C,D 列が A,B 列にずれ込む結果となりました。

メソッドに渡している Range オブジェクトは Sheet.getRange("A1:B1") でしたが、渡した Range オブジェクトではなく Range オブジェクトが属する列ごと移動する という点には注意が必要かもしれませんね。

パラメータ検証

では、応用するためには理解が必要ということで、公式ドキュメントからパラメータの意味を読み解いてみます。

※自分なりに日本語訳しています

Name	Type	Description
columnSpec	Range	列移動させる範囲の Range オブジェクト。
destinationIndex	Integer	列を移動させる先のインデックス。このインデックスは、列が移動される前の座標に基づいています。列を移動するために削除されている間、スペースに既存のデータも移動します。したがって、データは当初指定されたインデックスとは異なるインデックスで終了する可能性があります。

まず columnSpec は「移動させたい列を含んだ Range オブジェクト」を渡すということで、これは分かりやすいですね。ちなみに Spec は specific（特定）の略だと思うので、「指定した列」という意味合いではないでしょうか。

次に destinationIndex は「列の移動先を指定するインデックス」という意味だと思いますが、先ほどのサンプルコードでは 5 を渡すと A,B 列が C,D 列に移動しました。この関連性はパッと分かりにくと思うので、他にも数字を当てはめて検証していきます。なお destination には目的地、行き先、到着地、といった意味があるようです。

destinationIndex を変えてみる

先ほどのサンプルコードのうち destinationIndex を 5 ⇒ 7 に変更してみます。

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var columnSpec = sheet.getRange("A1:B1");
  sheet.moveColumns(columnSpec, 7);
}

実行前

f:id:rokuni62:20171026191253p:plain

実行後

f:id:rokuni62:20171026194746p:plain

結果検証

destinationIndex の値を 2 増やしたら A,B列 ⇒ C,D列だったのが A,B列 ⇒ E,F列になりました。2 つ右にズレたので納得の結果です。法則性をハッキリさせるため、今度は columnSpec を変えてみます。

columnSpec を変えてみる

サンプルコードの columnSpec を "A1:B1" ⇒ "A1" に変更してみます（destinationIndex は 5 に戻します）。

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var columnSpec = sheet.getRange("A1");
  sheet.moveColumns(columnSpec, 5);
}

実行前

f:id:rokuni62:20171026195541p:plain

実行後

f:id:rokuni62:20171026195549p:plain

結果検証

destinationIndex が 5 のままでも columnSpec を A1:B1 ⇒ A1 と変更したら移動先が A列 ⇒ C列（2列移動）だったのが A列 ⇒ D列（3列移動）になりました。これで destinationIndex だけ移動先が決定するわけではなく、columnSpec の範囲も影響することが分かりました。

これまでの検証結果より、列が移動する時の法則性は以下のようになっているのではないでしょうか。

「destinationIndex - 移動させる範囲の列数」の列位置に移動する

最初のサンプルコード 5 - 2 = 3 … 3列目に移動（A,B列 ⇒ C,D列）
destinationIndex を 7 に変更した場合 7 - 2 = 5 … 5列目に移動（A,B列 ⇒ E,F列）
columnSpec を "A1" に変更した場合 5 - 1 = 4 … 4列目に移動（A列 ⇒ D列）

この仕様は destinationIndex パラメータの説明でも「列が移動される前の座標に基づいています」と触れられているように、移動する際に出来るスペースが自動で詰められることが影響しているのでしょう。

例えば最初のサンプルコードでは columnSpec が "A1:B1" で destinationIndex が 5 です。5列目 = E,F 列に移動ということになりますが、もといた A,B 列が削除されてしまうため、C 列が A 列まで移動し、それに伴い、移動したばかりの E,F 列までもが C,D 列に移動してしまう、という動きになっています。

左方向に動かしてみる

さて、今までの検証はすべて右方向への移動でしたので、左方向にも動かしてみたいと思います。 columnSpec は "C1:D1" とし destinationIndex は 1 とします。

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var columnSpec = sheet.getRange("C1:D1");
  sheet.moveColumns(columnSpec, 1);
}

実行前

f:id:rokuni62:20171026203317p:plain

実行後

f:id:rokuni62:20171026203323p:plain

結果検証

C,D 列が A,B 列に移動していますね。

なお、左方向に動かす場合は先ほどの法則は適用されず、単純に destinationIndex の位置に移動しています。なぜかと考えると、左方向に動いた場合、自身が空けたスペースが詰められるのに影響を受けますが、右方向に動いた場合は影響を受けないからではないでしょうか。

f:id:rokuni62:20171026204659p:plain

※移動先に割り込む場合は右側にズレるのが前提

仕様まとめ

以上の検証結果より Sheet.moveColumns(columnSpec, destinationIndex) の仕様をまとめです。

移動方向	移動先
左	destinationIndex で指定した列位置
右	「destinationIndex - columnSpec の列数」の列位置

最終的な移動位置は destinationIndex だけでなく columnSpec で指定した Range オブジェクトの列数も関係する、というのは非常に重要な仕様ですね。

補足

なお、destinationIndex に以下のような値を設定すると実行時エラーになります。

設定値	エラーメッセージ
0	これらの列は範囲外にあります。
マイナス値	これらの列は範囲外にあります。
移動対象の範囲内	移動先のインデックスを移動対象の範囲に含めることはできません。

※ columnSpec に "A1:B2" を指定した場合、destinationIndex に 1～3 は実行時エラーとなる

Sheet.moveRows(rowSpec, destinationIndex)

さて、引き続き moveRows の検証ですが、moveColumns が列移動だったのに対しmoveRows は行移動である、という違いがあるだけで、基本的には同じような仕様ではないでしょうか。

サンプルコード

とりあえず公式のサンプルコードを動かしてみます。

function myFunction() {
 // The code below moves rows 1-2 to destination index 5.
 // This results in those rows becoming rows 3-4.
 var sheet = SpreadsheetApp.getActiveSheet();
 // Selects row 1 and row 2 to be moved.
 var rowSpec = sheet.getRange("A1:A2");
 sheet.moveRows(rowSpec, 5);
}

実行前

f:id:rokuni62:20171026232205p:plain

実行後

f:id:rokuni62:20171026232211p:plain

結果検証

1,2 行目が 3,4 行目に移動していますね。先ほど moveColumns で得た法則がそのまま適用できそうです。

「destinationIndex - 移動させる範囲の行数」の行位置に移動する

行の下移動が検証できたので、次は行の上移動を検証してみます。

上方向に動かしてみる

rowSpec の指定を "A3:A4"（3,4行目）、detinationIndex の指定を1（行目）とします。

function myFunction() {
 var sheet = SpreadsheetApp.getActiveSheet();
 var rowSpec = sheet.getRange("A3:A4");
 sheet.moveRows(rowSpec, 1);
}

実行前

f:id:rokuni62:20171026232727p:plain

実行後

f:id:rokuni62:20171026232734p:plain

結果検証

3,4 行目が 1,2 行目に移動していますね。やはり moveColumn と同じ仕様と考えてよいでしょう。

移動方向	移動先
上	destinationIndex で指定した行位置
下	「destinationIndex - columnSpec の行数」の行位置

さいごに

以上で moveColulmn と moveRow の検証は終了です。

簡単そうに見えても実際に検証してみると意外とややこしく、試してみるのは大切だな…と感じました。

と、検証したのは良いものの、これらのメソッドがどういう場面で効果的に使えるのかが思いつきません…。また思いついたら書いてみたいと思います。

2017-10-25

Rangeオブジェクトをfor文で処理する

Tips

はじめに

今回は、タイトルの通り「Range オブジェクトを for 文で処理する」サンプルコードを紹介します。

複数のセルに対して同じ処理を行いたい場合、セルの開始位置から終了位置までを for文で繰り返し、そのループ内で getRange(row, column) とするコードが一般的ではないでしょうか。

もちろんそれでも問題ないのですが、同じ処理でも Range オブジェクトに対する for 文で実現すると、ソースコードの意味が分かりやすくなったり読みやすくなったりします。

どちらが良いかはケースバイケースの面もあるかと思いますが、このような書き方が出来ることを知っておけばコーディングの幅も広がりますので是非読んでみてください。

サンプルコード

コード.gs

function myFunction() {
  var range = SpreadsheetApp.getActive().getSheetByName("シート1").getRange("B3:C5");
  checkRange(range);
}

// Rangeオブジェクトの値をチェックして結果をメモに設定する
function checkRange(range) {
  
  // Rangeオブジェクトを左上から右下に捜査する
  for(var row = 1; row <= range.getNumRows(); row++) {
    for (var column = 1; column <= range.getNumColumns(); column++) {
      
      // 処理対象のセルを取得
      var cell = range.getCell(row, column);
      
      // 値に問題がないかチェックし、結果をメモに設定する
      var result = checkNumber(cell.getValue());
      cell.setNote(result);
      
      // 値に問題があった場合は背景色を黄色、ない場合は白を設定する
      if (result != "") {
        cell.setBackground("yellow")
      } else {
        cell.setBackground("white")
      }
      
    }
  }
}

// 値のチェック結果を返却する（問題ない場合は空白を返す）
function checkNumber(value) {
  
  // 半角数字のみチェック
  if (/[^0-9]/.test(value)) return "半角数字以外が入力されています。";
  
  // 0～100の範囲チェック
  if (value < 0 || 100 < value ) return "0～100の範囲で入力してください。";
  
  // チェックにひっかからなければ問題ないと判断し空白を返す
  return "";
}

実行の流れ

B3:C5 の範囲に処理対象データを用意します。今回は値が 0～100の範囲でないものはエラーにする想定でデータを用意しました。
スクリプトエディタより myFunction を実行すると、不正な値は背景色が黄色になり、不正な理由がメモに設定されます。

解説

(1) まず myFunction() を実行します。

function myFunction() {
  var range = SpreadsheetApp.getActive().getSheetByName("シート1").getRange("B3:C5");
  checkRange(range);
}

ここでは B3:C5 の範囲を Range オブジェクトとして取得し、そのまま checkRange(range) メソッドに渡しています。渡す引数が range だけで済むのでコードをスッキリ見せることが出来ます。

もし for 文の中で getRange(row, column) するやり方だと、以下5つの引数を用意することになるでしょう。

Sheetオブジェクト
開始行（3行目）
開始列（2列目）
終了行（5行目）
終了列（3列目）

(2) 次に Range オブジェクトを for 文で処理します。

// Rangeオブジェクトの値をチェックして結果をメモに設定する
function checkRange(range) {
  
  // Rangeオブジェクトを左上から右下に捜査する
  for(var row = 1; row <= range.getNumRows(); row++) {
    for (var column = 1; column <= range.getNumColumns(); column++) {
      
      // 処理対象のセルを取得
      var cell = range.getCell(row, column);
      
      …（中略）…
      
    }
  }
}

Range オブジェクトは Cell が二次元配列のように格納されているイメージなので、二次元配列と同じような形の for 文になります。

二次元配列をループさせる時との違いは以下の 3 つです。

ループが 1 から始まる（配列は 0 から）
ループの終点は getNumRows() や getNumColumns() で取得する
セルは getCell(row, column) で取得する

(3) 最後にループ内で各セルのチェック処理を行っています。

      // 値に問題がないかチェックし、結果をメモに設定する
      var result = checkNumber(cell.getValue());
      cell.setNote(result);
      
      // 値に問題があった場合は背景色を黄色、ない場合は白を設定する
      if (result != "") {
        cell.setBackground("yellow")
      } else {
        cell.setBackground("white")
      }

// 値のチェック結果を返却する（問題ない場合は空白を返す）
function checkNumber(value) {
  
  // 半角数字のみチェック
  if (/[^0-9]/.test(value)) return "半角数字以外が入力されています。";
  
  // 0～100の範囲チェック
  if (value < 0 || 100 < value ) return "0～100の範囲で入力してください。";
  
  // チェックにひっかからなければ問題ないと判断し空白を返す
  return "";
}

この記事の本筋ではないため簡単な説明になりますが、ここでは以下処理を行っています。

値のチェック処理を行う
1. 半角数字以外が入力されているとエラー
2. 0～100の範囲でない場合はエラー
チェック結果をもとに処理を行う
1. エラー内容をセルのメモに設定（エラーがない場合は空白になる）
2. エラーの場合は背景色を黄色、そうでない場合は白に設定

メモの設定は setNote(note)、背景色の設定は setBackground(color) で行っており、どちらも Range クラスに属するメソッドですが、Cell も Range クラスに属しているため問題なく使用できます。

Range と Cell は用途によって呼び分けられているだけで、どちらも Range オブジェクトです。Cell は Range の中でも単一セルを指す場合に使用されます。

さいごに

Range オブジェクトを for 文で処理する方法の説明は以上となります。

繰り返しになりますが、この方法を使えば Range オブジェクト関連の処理をメソッドにする場合に引数を少なくできますし、「Range オブジェクトに対して処理をする」という意味合いが強まり、コードが読みやすくなるかと思いますので、是非試してみてください。

2017-10-18

ダイアログを使ってタイムアウトを回避する

Tips

GoogleAppsScript を使っていて度々悩まされるのは「6分でプログラムがタイムアウト終了する」ことです。

Quotas for Google Services | Apps Script | Google Developers

直ぐに終わる処理なら気にする必要ありませんが、データを処理するようなプログラムを書く場合、データが増えるにつれてどうしても処理時間が伸びてしまうため、いまは6分以内に処理が終了していたとしても、将来のことを考えるとタイムアウトの回避方法は考えておいた方が良いでしょう。

タイムアウト対策としてよく知られているのは「タイムアウト前に次のプログラム起動をトリガーで設定する」ことです。それもひとつの方法ですが、より簡単にタイムアウトを回避する方法を考えたので紹介したいと思います。

簡単に説明すると「ダイアログからGASを細かく起動する」という方法です。 GAS では HTML ファイルをもとにダイアログを表示することができますが、HTML ファイルの中に GAS を埋め込むこともできます。「6分でタイムアウトする」というのは GAS を一回起動した時の制限になりますので、トータルで10分かかる GAS の処理でも、HTML から1分間隔で10回 GAS を呼びだせば、トータル処理時間は同じでも、タイムアウトを回避することが出来ます。

もちろんこの方法が使用できないシチュエーションもあります。例えばプログラムを定期実行している場合はダイアログを操作する人がいないため、この方法は使えません。ですが「人が操作していて」「処理時間が長くなる」ようなケースでは効果的な回避方法になりえます。

それではサンプルとして「10秒間隔で1分間シートに書き込み続けるGAS」を「HTMLから10回呼び出す」プログラムを紹介します。トータル処理時間は10分になりますので、これが最後まで動作したら「6分のタイムアウトを回避した」と言えるのではないでしょうか。

サンプルコード

以下2ファイルが登場します。

コード.gs
dialog.html

コード.gs

function dialog() {
  var html = HtmlService.createHtmlOutputFromFile("dialog");
  SpreadsheetApp.getUi().showModalDialog(html, "ダイアログ")
}

function continueWritingOneMinute() {  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シート1");
  for (var i = 0; i < 6; i++) {
    Utilities.sleep(10000);  //10秒
    sheet.appendRow([Utilities.formatDate(new Date(), "JST", "HH:mm:ss")]);
  }
  return true;
}

function msgBox(message) {
  Browser.msgBox(message);
}

dialog.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <script>
      var i = 0;
      
      // run() を呼び出す
      function onSuccess() {
        i++;
        if (i < 10) {
          run();
        } else {
          google.script.run.msgBox("処理が終了しました");
        }
      }
      // GAS を呼び出す
      function run() {
        google.script.run
          .withSuccessHandler(onSuccess)
          .continueWritingOneMinute();
      }
      
    </script>
  </head>
  <body>
    <input type="button" value="書き込む" onClick="run()">
  </body>
</html>

実行の流れ

当サンプルコードを実行した時の流れです。

GAS側の dialog() を実行すると GAS 起動用のダイアログが表示されます。
「書き込む」ボタンを押すと処理が開始され、少し待つとシートに時刻が設定されていきます。
10分経つと処理終了のポップアップが表示されます。
シートを確認してみると10分間、10秒間隔で時刻が設定されていることが確認できます。

解説

(1) dialog() では dialog.html をダイアログとして表示しています。

function dialog() {
  var html = HtmlService.createHtmlOutputFromFile("dialog");
  SpreadsheetApp.getUi().showModalDialog(html, "ダイアログ")
}

(2) 呼び出された dialog.html の「書き込む」ボタンを押すと script タグ内の run() が呼び出されます。

<input type="button" value="書き込む" onClick="run()">

(3) run() では 1分間10秒間隔で書き込み続ける GAS のcontinueWritingOneMinute() を呼び出しています。

 // GAS を呼び出す
function run() {
  google.script.run
    .withSuccessHandler(onSuccess)
    .continueWritingOneMinute();
}

ここで重要なのは withSuccessHandler(onSuccess)のところです。これは continueWritingOneMinute() が正常終了したら onSuccessを実行する、という意味になります。もし単純に for 文で continueWritingOneMinute() を10回呼び出したとすると、それぞれの処理が平行に実行されてしまい、思った通りに動いてくれません。

(4) continueWritingOneMinute() は「シート1」というシートに対して10秒間隔で時刻を記載する処理を6回繰り返します（つまり1分）。

function continueWritingOneMinute() {  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シート1");
  for (var i = 0; i < 6; i++) {
    Utilities.sleep(10000);  //10秒
    sheet.appendRow([Utilities.formatDate(new Date(), "JST", "HH:mm:ss")]);
  }
  return true;
}

(5) continueWritingOneMinute() の処理が終了したら dialog.html の onSuccess() が実行されます。

var i = 0;
      
// run() を呼び出す
function onSuccess() {
  i++;
  if (i < 10) {
    run();
  } else {
    google.script.run.msgBox("処理が終了しました");
  }
}

グローバル変数 i を呼出回数を記録するために用意しておき、10回未満であれば run() 経由で continueWritingOneMinute() を呼び出し、すでに10回呼び出しているのであれば処理終了メッセージを表示します。「10回繰り返す」という処理を繰返し構文ではなく再帰処理で実現している形になります。最後に処理終了メッセージを表示して処理は終了です。

参考

HTML Service: Communicate with Server Functions | Apps Script | Google Developers

2017-09-13

ローカルファイルを編集（アップロード・ダウンロード）

応用編

ローカルファイルを読み込むとローカルにファイルダウンロードでそれぞれローカルファイルのアップロード・ダウンロードを実現したので、それらを合わせて「ローカルファイルの編集」をするサンプルコードを紹介したいと思います。

編集内容は「全角英数字を半角に変換」とします。

なお、ローカルファイルを直接編集するのではないため、アップロードファイルとダウンロードファイルは別々になることにご注意ください。

また、今回スプレッドシートで値は読み書きせず、ダイアログの土台としてのみ利用しています。

サンプルコード

以下4ファイルが登場します。

コード.gs
upload.html
download.html
upload.txt

コード.gs

var readFileData;  // アップロードしたファイルデータを保管しておくためのグローバル変数

function main() {
  var html = HtmlService.createHtmlOutputFromFile("upload");
  SpreadsheetApp.getUi().showModalDialog(html, 'ファイルアップロード');
}

function readFileAndShowDownloadDialog(formObject) {
 
  // フォームで指定したテキストファイルを読み込む
  var fileBlob = formObject.myFile;
  
  // テキストとして取得（Windowsの場合、文字コードに Shift_JIS を指定）
  var text = fileBlob.getDataAsString("sjis");  
  
  // テキストデータをグローバル変数に保管する
  readFileData = text;
  
  // ファイルダウンロード用のダイアログを表示する
  var html = HtmlService.createTemplateFromFile("download").evaluate();
  SpreadsheetApp.getUi().showModalDialog(html, 'ファイルダウンロード');
  
}


function getData() {    
  
  // 読み込んだテキストデータの全角英数字を半角に変換する
  return readFileData.replace(/[ａ-ｚＡ-Ｚ０-９]/g, function(s) {
    return String.fromCharCode(s.charCodeAt(0) - 65248);
  });
  
}

upload.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 handleFormSubmit(formObject) {
        // GASで定義した関数を呼び出す
        google.script.run.readFileAndShowDownloadDialog(formObject);

      }
    </script>
  </head>
  <body>
    <form id="myForm" onsubmit="handleFormSubmit(this)" enctype="multipart/form-data">
      <input name="myFile" type="file" /><br>
      <button type="submit">読込</button>
    </form>
  </body>
</html>

download.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <script>
      
      // ダウンロード
      function handleDownload() {
        var content = <?= getData(); ?>;  // 出力データを GAS から取得する
        var blob = new Blob([ content ], { "type" : "text/csv"});
        document.getElementById("download").href = window.URL.createObjectURL(blob);
      }

    </script>
  </head>
  <body>
    <a id="download" href="#" download="download.txt" onclick="handleDownload()">ダウンロード</a>
  </body>
</html>

upload.txt

1234567890
０９８７６５４３２１
abc
ｃｂａ
XYZ
ＺＹＸ

実行の流れ

当サンプルコードを実行した時の流れです。

GAS側の function main() を実行するとファイルアップロード用のダイアログが表示されます。
「ファイルを選択」からアップロードするファイルを選択します。
「読込」ボタンを押してファイルを読み込みます。
次にファイルダウンロード用のダイアログが表示されます。
「ダウンロード」を押すと編集後のテキストファイルがダウンロードされます。
ダウンロードされたファイルを確認すると全角英数字が半角に変換されています。

解説

アップロード・ダウンロードの解説はそれぞれローカルファイルを読み込むとローカルにファイルダウンロードに書いてあるので省略します。

当サンプルコードで工夫した点は2つ。

アップロード機能とダウンロード機能を別ファイルに分けたこと
アップロードファイルのデータをグローバル変数に保管したこと

アップロード機能とダウンロード機能を別ファイルに分けた理由

最初はアップロード機能・ダウンロード機能をひとつのHTMLファイルにまとめようとしましたが、上手くいきませんでした。

その理由はダウンロード機能の var content = <?= getData(); ?>; の箇所です。

<?= ... ?> という記法は HtmlService.createTemplateFromFile("XXX").evaluate() でHTMLを生成したタイミングで固定されてしまいます。

そのためひとつのHTMLファイルに機能をまとめようとすると、「ファイルがアップロードされる前にダウンロードファイルデータが固定」されてしまい、「アップロードファイルを編集する」という目的を達成できません。

そこでアップロード機能・ダウンロード機能でそれぞれHTMLファイルを分割し、アップロードファイルを読み込んだ後にダウンロード用HTMLダイアログを生成することで「編集」を実現しました。

アップロードファイルのデータをグローバル変数に保管した理由

今回、プログラムの流れとしては以下のようになっています。

アップロード用HTMLダイアログ生成（GAS）
ファイルのアップロード（JavaScript）
アップロードされたファイルデータの読込み（GAS）
ダウンロード用HTMLダイアログ生成（GAS）
ダウンロードするファイルデータの展開・編集（GAS⇒JavaScript）
ファイルのダウンロード（JavaScript）

ここで重要なのは「5.ダウンロードするファイルデータの展開・編集（GAS⇒JavaScript）」です。

GAS は「2.ファイルのアップロード（JavaScript）」で得たファイルデータを「5.ダウンロードするファイルデータの展開・編集（GAS⇒JavaScript）」で展開まで保管しておかなければなりません。

HTML ファイルを分割したため、データの橋渡しを GAS が行う必要があるということです。

変数は必要がなければローカル定義が望ましいですが、ファイルデータを受け渡す必要があったためグローバル変数を定義することで実現しました。

2017-09-06

ローカルにファイルダウンロード

基本編

今回はGoogle スプレッドシートの値をCSV形式にしてローカルにファイルダウンロードするサンプルコードを紹介します。

ローカルファイルを読み込むで紹介した方法と同様に、Google スプレッドシートはサーバー上に存在するため、ダイアログを介してファイルをダウンロードします。

データ作成は GoogleAppsScript で行い、ファイルダウンロードは JavaScript で行います。

サンプルコード

以下2ファイルが登場します。

コード.gs（GoogleAppsScript）
dialog.html （HTML, JavaScript）

コード.gs

function main() {
  
  // dialog.html をもとにHTMLファイルを生成
  // evaluate() は dialog.html 内の GAS を実行するため（ <?= => の箇所）
  var html = HtmlService.createTemplateFromFile("dialog").evaluate();
  
  // 上記HTMLファイルをダイアログ出力
  SpreadsheetApp.getUi().showModalDialog(html, "ファイルダウンロード");
}

function getData() {
  
  // スプレッドシート上の値を二次元配列の形で取得
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = sheet.getDataRange().getValues();

  // 二次元配列をCSV形式のテキストデータに変換
  var dataArray = [];
  for (var i = 0; i < values.length; i++) {
    dataArray.push(values[i].join(","));
  }
  return dataArray.join("\r\n");  // 改行コードは windows を想定
  
}

dialog.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <script type='text/javascript'>    
      function handleDownload() {
        var content = <?= getData(); ?>;  // 出力データを GAS から取得する
        var blob = new Blob([ content ], { "type" : "text/csv"});
        document.getElementById("download").href = window.URL.createObjectURL(blob);
      }
  </script>
  </head>
  <body>
    <a id="download" href="#" download="test.txt" onclick="handleDownload()">ダウンロード</a>
  </body>
</html>

実行の流れ

当サンプルコードを実行した時の流れです。

スプレッドシートに値が設定されている
スクリプトエディタよりmain()を実行すると、スプレッドシート上にファイルダウンロード用のダイアログが表示される
「ダウンロード」をクリックするとファイルがダウンロードされる
ファイルを開くとスプレッドシートの値がCSV形式で保存されている

解説

前述したように Google スプレッドシートはサーバー上に存在しているため、ローカルにファイルを直接ダウンロードすることができません。処理するためには仲介役が必要になります。ここではダイアログが仲介役となります。

プログラムの流れ

GAS側のfunction main() を実行し dialog.html を元にしたダイアログを表示します。このとき dialog.html にはGASのコードが埋め込まれており、それを実行した結果を取得するためにcreateTemplateFromFile().evaluate() としています
表示されたダイアログの「ダウンロード」をクリックすると HTML側の handleDownload() が実行され、ファイルがダウンロードされます
この時、ダウンロードするファイルの内容は <?= getData(); ?> により取得され、GAS側の function getData() が呼び出されます
function getData() ではスプレッドシート上の値をCSV形式に変換しテキストデータを返却します（改行コードはWindowsを想定し\r\nとしています）
ダウンロードされたファイルの内容を確認するとスプレッドシートの値がCSV形式でダウンロードできていることが分かります

参考記事

qiita.com HTML Service: Templated HTML | Apps Script | Google Developers