Google Apps Script 実践メモ（スプレッドシート）

先日、仕事で必要に迫られ、Googleフォームからスプレッドシート周りをごにょごにょGoogle Apps Scriptで開発しました。
シリーズの初回は、Form Submitについて書きましたが、第2弾としてスプレッドシート周りのあれこれをまとめます。

スプレッドシート(Spreadsheet)

他のシートを読み込んだり書き込んだりしたい

やりたかったのは、Form Submitで入ってきたレコードに「ユーザー名」というGoogle Appsのアカウント名が入ってくるのですが、それをキーに社員マスタのようなシートから情報を引っ張ってくる、といったようなことです。

URLなどからまったく別のスプレッドシートを開く

フォームの回答シートとはまったく別のスプレッドシートを開くには、SpreadsheetAppというクラスを使用します。開きたいスプレッドシートのURLが分かっていればopenByUrl(url)で開けます。

なお、Driveフォルダからスプレッドシートを探して名前とかでひっかけるのであれば、別途まとめるDriveのところでやり方を書くのでそちらをご参照ください。

function myFunction(){
  //変数spreadsheetに指定のスプレッドシートオブジェクトを取得します
  var url = "https://docs.google.com/spreadsheets/d/**************/edit#gid=*****";
  var spreadsheet = SpreadsheetApp.openByUrl(url);

  //あとはSpreadsheetクラスのメソッドを利用して目的の処理をすればよいです
  //例えば、スプレッドシートに含まれるシートオブジェクトを全て取得して、シート名でひっかけるとか
  var sheets = spreadsheet.getSheets();
  for ( var i in sheets ){
    if ( sheets[i].getSheetName() == "社員マスタ" ) {
      //あとはシートの内容を取得してさらに処理するのですがその辺は後述
    }
  }
}

• URLはシートID(gid)までは特に不要ですがまぁその辺はよしなに

• あと、URLの"~/d/*****/edit"のアスタリスクの部分がスプレッドシートのIDになってまして、それを指定してSpreadsheetApp.openById(id)で開くこともできます。また逆にスプレッドシートオブジェクトがすでに取得できているのなら、getId()でIDを取得できたりもします。
• Spreadsheetクラスのドキュメントはこちらです。いろんなことができますねー。

シートを特定できたら

シートのオブジェクトを取得するところまでできたら、いよいよセルの値を取得したりセルを更新したりしたいわけですが、Excel VBAのような書き方をするととにかく処理が遅くなります。
つまり、クラウドサービスなので処理はサーバでやってるわけで、1,000行もループしてgetRange(row, column).getValue()とかAPI呼びまくってると果てしなく遅くなるわけです。

ということで、getSheetValues(startRow, startColumn, numRows, numColumns)で多次元配列にがさっと取得してそれを使うことにしました。
範囲指定して取得できるメソッドですが、私の場合はとにかくシートの内容を全部取得してから後続の処理をしました。

function myFunction(){
  //上記の変数spreadsheetや変数sheetsを継続して使いまわします
  var sheet = sheets[0];             //一番左のシートは配列のindex"0"で指定します

  //シートの最終行番号、最終列番号を取得
  var startrow = 1;
  var startcol = 1;
  var lastrow = sheet.getLastRow();
  var lastcol = sheet.getLastColumn();

  //がさっと取得
  var sheetdata = sheet.getSheetValues(startrow, startcol, lastrow, lastcol);

  //これ以降、sheet.getRange(1,1).getValue()とかしなくて済みます
  //例えばA1セルは[0][0]、B2セルは[1][1]です
  Logger.log(sheetdata[0][0]);       
}

セルへの更新

値を探したりするのは、がさっと取得の配列をfor in文とかで回せばいいのですが、セルに値を更新する場合はさすがにAPIを使わないと、ということでsetValue("hoge")すればよいです。

function myFunction(){
  //変数sheetやsheetdata[][]が上記と同じように定義・取得されているものとします

  //ちょっと変な処理ですが、A列の値をE列にコピーする場合はこんな感じで。
  //ちなみに、配列なのでA列のindexは"0"、RangeなのでE列の列番号は"5"となっております
  for ( var i in sheetdata ){
    sheet.getRange(i+1, 5).setValue(sheetdata[i][0]);  
  }
}

• JavaScriptの話ですが、for inはindexの順番は保証されないのであしからず

• Rangeクラスのドキュメントはこちらです。いろんなことができますねー。

スプレッドシートを新規作成し、そこに書き込みたい

次にやりたかったのは、Form Submitで入ってきたレコードを利用して、アカウントごと（人毎）に新規にスプレッドシートを作成して、そこにレコードを転記する、といったようなことです。
なんでそんなことするのかというと、要件として、回答者個々人のFormの回答内容を閲覧できる人が、回答者によってバラバラだったんです。しょうがないからFormの回答内容を個別のスプレッドシートに分けて、それに権限をつけることにした次第です。（権限周りはDriveの回にまとめます）

テンプレートをコピーして新規作成する

今回は、元となるテンプレートを用意しておき、アカウントごと（人毎）にテンプレートをコピーしてスプレッドシートを新規作成していきます。
必要なのは、テンプレートのスプレッドシートオブジェクトと、スプレッドシートを作成する先のフォルダオブジェクトです。

ただ、ここにちょっとした落とし穴がありました。
SpreadsheetAppと同じようなことができるDriveAppというクラスもあるのですが、スプレッドシートをコピー作成するmakeCopy(name, destination)というメソッドを含むFileクラスは、DriveAppで取得したファイルオブジェクトでないと扱えないのです。

ということで、前述のSpreadsheetApp.openByUrl(url)でスプレッドシートオブジェクトを取得してもmakeCopy()をコールできないので、DriveApp.getFileById(id)を使う必要があります。

さらに‼︎　URLを指定できるかと思いきや、実はgetFileByUrl(url)なるメソッドがなくて、ID指定のメソッドしかないんですよね。。仕方がないので、テンプレートのURLからIDだけ抜き出してそれを使用することにしました。

function myFunction(){
  //テンプレートオブジェクトの取得
  var templateid = "*******";
  var template = DriveApp.getFileById(templateid);

  //保存先フォルダオブジェクトの取得
  var destfolderid = "*******";       //destination folderを略してます
  var destfolder = DriveApp.getFolderById(destfolderid);

  //コピー作成
  var filename = "hogehoge";          //実際にはfor inでsheetdata[][]回しながらアカウント名を取得してファイル名にプラスしました
  var newfile = template.makeCopy(filename, destfolder);

  //ファイルの共有設定（※権限付与はまた別途）
  newfile.setSharing(DriveApp.Access.PRIVATE, DriveApp.Permission.EDIT);
}

• DriveApp.Access.PRIVATE・・・「特定のユーザーがアクセスできます」

• DriveApp.Permission.EDIT・・・その人たちに編集権限を付与します。※実際にはaddEditor()やaddViewer()などで個別に指定するので、「特定のユーザー〜」の場合は関係ないかも

コピー作成したスプレッドシートに書き込む

これは、このページの前半部分に書いたものを組み合わせて実装すればよいです。
（華麗に省略！）

その他あれやこれや

今回はないかな・・・
長文になりましたが以上です！

積み残しの内容

以下の内容は、Driveの方に書きたいと思います。

• スプレッドシートに権限をつけたり外したり
• フォルダの作成、権限の付与、スプレッドシートのフォルダへの追加