他のスプレッドシートの読み書き

Google Spreadsheetは、クラウド上で動くスプレッドシートということもあって、通常のエクセルのように他の表計算ファイルに対して容易に読み書きが出来るようにはなっていません。エクセルであれば他のファイルから情報を持ってくるのは、さして難しくなく（式は複雑になりますが）、外部参照という形でリンクされています。しかし、Google Spreadsheetの場合、シート間の参照に関してはエクセルと変わりませんが、外部参照やリンクする機能自体がありません。Google Driveの特殊な仕様も相まって、結果として諦めるなんていうシーンも現場では多く見受けられます（最終的にはここを手入力やコピペで貼付けなどで対応するといった、しょっぱい対応をせざるを得ません）。

そこで、このページでは非常に分かりにくいこの件に関して外部データの取り込みや書き出しなどについてまとめてみました。

Importrange関数を使用する

他のファイルから値を呼び出すことしか出来ませんが、スプレッドシート上の関数として利用できるものです。Google Spreadsheetでのみ用意されている特別な関数でデータの塊をまるごと取ってくる非常に便利な関数です。配列で帰ってくる特殊な関数なので、やり方によっては結果をsum関数などに食わせて合計値を出すといったテクニックも使用することが出来ます。

関数の組み方がちょっとだけクセがあり、また、シートをリンクするにあたってもちょっとだけ壁があります。Importrange関数そのものの使い方に関してはGoogle独自のSpreadsheet関数のIMPORTRANGE関数の項を参照して下さい。

実はこの関数は他のスプレッドシートを参照する時にそのままだと計算結果がでません。計算式を入れたセルにカーソルを載せると以下のような画面が出てきて、アクセス許可をしてあげないと計算が出来ません。また、エクセルのリンクに似たような機能ですが、取り込んだ値のエリアの編集はできません

図１：アクセス許可をしてあげましょう

図２：他のファイルのデータを引っ張ってきた様子

スクリプトを使用したデータの入出力

Google Appsそのものには、ScriptDb以外にDBと呼べそうなものはなく、Google Cloud SQLはあくまでもGoogle Appsの範疇にあるサービスではありません。BigQueryなどは正直日常で使うには特殊すぎる。

ということで、多くの方は所定のスプレッドシートにデータを蓄積し、そこから第三者が集計して編集といった作業をしているかと思われます（特に、複数の営業所や支店があるケースでファイルを共有するといっても、１つのファイルでまかなうのは正直難しいケースはたくさんあり、１つのテンプレートを複数枚用意して支店ごとに作成し集計などなど）。

また、他のアプリケーション（ドキュメントやフォームなど）からスプレッドシートへの読み書きなんてのもケースによってはあるでしょう。そうなった場合、スプレッドシート専用の関数であるImportrange関数は使えず、また、柔軟な機能を付けたくてもこのままでは難しい。ということで、他のスプレッドシートへの読み書きを中心に、それをスクリプトで実現する方法を下記に記します。この方法は、自分が日常的によく使っているものですが、パフォーマンスが良いかどうかは不明なのであしからず。

イメージ図

※考えられるケース２つを上記図に示してみました。およそこのようなパターンが殆どだと思います。

実際のコード

今回はいくつか考えられるケースにより、以下のメソッドと配列から該当する値の取り出しなどを考えた自作の関数を使います。

  1. SpreadsheetApp.openById()
  2. sheetByName()
  3. getRangeByName()、getDataRange()
  4. データの塊から特定のレコードをヒットさせて取り出す関数（自作）
  5. getRange().setValue()、 getRange().setValues()

想定しているケースは、

  • データが入っているスプレッドシートの名前は「database」である。
  • 対象のデータ群が入っているシートの名前も「database」としてある。
  • 取得したいデータの範囲はカラム行を除いたA2:C14までの範囲である。
  • また、その範囲には「rangeman」という名前付き範囲を設定してある。

他のシートからデータの塊を持ってくる

//名前付き範囲で一発取得する
function sheetgetter(){

var sheet = SpreadsheetApp.openById("スプレッドシートのID");

var sheetman = sheet.getRangeByName("rangeman").getValues();


・・・ここに取り出したデータの塊を処理するルーチンを記述する・・・


}
//指定の範囲で取得する
funtion sheetgetter2(){

var sheet = SpreadsheetApp.openById("スプレッドシートのID");

var sheetman = sheetman.getSheetByName("database").getRange("A2:C14").getValues();

・・・ここに取り出したデータの塊を処理するルーチンを記述する・・・

}
//データのある範囲全部まるごと取得する
function sheetgetter3(){

var sheet = SpreadsheetApp.openById("スプレッドシートのID");

var sheetman = sheetman.getSheetByName("database").getDataRange();

・・・ここに取り出したデータの塊を処理するルーチンを記述する・・・


}

※後は、取り出した値はsheetman変数に２次元配列で取得されているので、sheetman[1][0]といった具合で取り出すことが出来る。getDataRange()の場合の時には、カラム行が含まれているので、forループ処理などの時には１行目を飛ばすようにすると良い。

※名前付き範囲を指定して取得するほうが一番早いが、範囲が固定化されるので注意。

※指定の範囲で取得する事例の場合、getRangeの範囲をA2:Cと記述すると、データ行が追加されても追尾してくれるので便利。

他のシートのデータの塊から特定のレコードの特定のセルの値をもらってくる

function tlookup(keyID, rangename, targetID, columnNum){
    //変数を宣言

var gettergetVal = "";


//keyIDを元にスプレッドシートを開き、名前付き範囲で値を取得

    var sheet = SpreadsheetApp.openById(keyID);
    var sheetman = sheet.getRangeByName(rangename).getValues();

//取得した値からtargetIDとヒットする値を取得

    for(var i = 0;i < sheetman.length;i++){
      if(sheetman[i][0] == targetID){
        //targetIDにヒットしたら、columnNumを元に値を取得

gettergetVal = sheetman[i][columnNum];

      }else{
      }
    }
    
    //送信者自身のメールアドレスをCCに追加する
    return getterrgetVal;
}

他のシートの特定の場所に値を書き込む

ヒント

関連リンク