スプレッドシートで作業をしている時に他のスプレッドシートのデータを簡単に取得ができればと思う時があります。
例えば・・・
Aというスプレッドシートに1日の売り上げを記入
その後Bというスプレッドシートの合計売り上げデータにAで入力した1日の売り上げを加算
同じスプレッドシート内であれば関数などですぐに反映されるのですが、スプレッドシートが違うとそうはいかず。
その場合にBのスプレッドシートをいちいち開いてデータを入れていくのはとても効率が悪いですね。
今回ご紹介するopen系メソッドを使用すればスクリプトを書くだけでこのような処理を一括して行うことが可能になります。
openメソッドには様々な種類もありますので各メソッドについてもご紹介していきます。
スプレッドシートのopenメソッド
スプレッドシートを開く事ができるメソッドです。具体的には、3種類のメソッドがあります。
- open(file)
- openById(sheetId)
- openByUrl(sheetUrl)
3つのメソッドは設定方法は異なりますが、どのメソッドを利用しても、対象となるスプレッドシートが同じであれば返り値は同じものになります。
ただし私たちがスプレッドシートを開いてセル一覧を起動するような形ではなくGoogleのサーバー上で開かれるため実際に目に見える形では表示されません。
クラス：SpreadsheetApp
注意：初めてコードを実行する際にアクセス権限の許可を求められますが許可をしてください。
アクセス権限についての詳細は以下の記事に詳しく載っています。
無料のGmailアカウントでGASに初回の実行許可を与える方法
open(file)
fileオブジェクトとして取得したデータでスプレッドシートを開きます。（file）の部分に入るデータはDriveAppで取得できるfileオブジェクトになります。
今回はGoogleリファレンスのスクリプトを使用して解説します。
//DriveAppで取得したファイルを元にスプレッドシートを取得するサンプルコード1
function sampleCodeForOpenFile1(){
let files = DriveApp.searchFiles(
'starred = true and mimeType = "' + MimeType.GOOGLE_SHEETS + '"');
let spreadSheet, targetSheet;
while (files.hasNext()) {
spreadSheet = SpreadsheetApp.open(files.next());
targetSheet = spreadSheet.getSheets()[0];
console.log(targetSheet.getName());
}
}
//DriveAppで取得したファイルを元にスプレッドシートを取得するサンプルコード2
function sampleCodeForOpenFile2() {
let fileId = "1kQSSciqJ_43r43p6InGz4XZPLFAROApK0YvTQsMfZPo";
let file = DriveApp.getFileById(fileId); //ファイルIDでファイルを取得してみる
let ss = SpreadsheetApp.open(file);
let name = ss.getName();
console.log(name);
}
searchFilesについては以下のリンクで詳しく説明しています。
Google Apps Scriptでドライブのフォルダやファイルを取得してみる
検索内容は以下の2つの指示によって設定されています。
‘starred = true and mimeType = “‘ + MimeType.GOOGLE_SHEETS + ‘”‘
個別に見ていくと
starred = true：スターを設定しているファイル
mimeType = “‘ + MimeType.GOOGLE_SHEETS + ‘”：ファイルの種類がスプレッドシートである
5行目から9行目まで：今回は取得するファイル数がいくつあるかわからない（スターをつけたスプレットシートがどれくらいあるか分からない）ためwhileを使用してループを回しています。
またfiles.hasNext()ではファイルをオブジェクトで取得しています。
そしてスターをつけたスプレッドシートの最初のシート情報を取得し、ログにそのシート名を表示させるというスクリプトが書かれています。
以上が流れです。
補足：スターの付け方
スターをつけることによって検索ですぐに調べることができます。メールではよく使われているこの機能はスプレッドシートなどのグーグルドライブのフォルダ・ファイルでも設定することが可能です。
ですので今回のスクリプトのようにスターがついたファイルだけ検索して取得し一覧で表示させることやスターをつけた中で条件検索するというような使い方もできますのでぜひ参考にしてみてください。
グーグルドライブのファイルやフォルダを右クリックすると出てくる項目の中にスターを追加という項目があります。それをクリックするだけです。
スターがつくとファイル名の隣に星マークがつきます。今回はstar付きファイルサンプルという名称をつけたファイルにスターをつけました。そしてシート１の名前をスター付きサンプルシートとつけています。
スターをつけたら（ドライブの中でスターがついている場合はそれが全て検索されます。）先ほどのスクリプトを実行してみてください。
他のフォルダ・ファイルにスターが無い場合はログを確認すると以下のように表示されます。
このように大量のデータを扱ったりIDやURLが分からないけど対象内容は分かっている場合のスプレッドシートのデータを取得したい場合などにopen(ファイル)メソッドは役に立ちます。
sampleCodeForOpenFile2では、ファイルIDを指定してDriveApp.getFileByIdを使って特定のファイルを取得します。サンプルコードで使っているスプレッドシートファイルはこちらです。このシートのファイル名を取得してログに出力しています。
実行結果はこうなります。
openById(id)
スプレッドシートに与えられたIDを使ってスプレッドシートを開きます。
スプレッドシートのIDはURLから確認する事ができます。
例：スプレッドシートのURL https://docs.google.com/spreadsheets/d/1kQSSciqJ_43r43p6InGz4XZPLFAROApK0YvTQsMfZPo/edit#gid=0
このスプレッドシートのURLの場合、赤字の部分がシートのIDとなります。スプレッドシートファイルにはそれぞれ一意のIDが割り振られています。
今回は先述の『openメソッド用のサンプルファイル』というスプレッドシートにアクセスします。
以下がサンプルコードです。そしてアクセスしたスプレッドシートの名前をgetNameで取得しています。
//スプレッドシートIDを元にスプレッドシートを取得するサンプルコード
function sampleCodeForOpenSheetById(){
let targetSheet = SpreadsheetApp.openById("1kQSSciqJ_43r43p6InGz4XZPLFAROApK0YvTQsMfZPo");
console.log(targetSheet.getName());
}
結果は以下のようにスプレッドシート名のテストがログで取得できます。
openByUrl(url)
先ほどはIDから取得する方法でしたがこちらはスプレッドシートのURLからスプレッドシートを開く方法です。
以下がサンプルコードです。URL部分は先程も利用したサンプルスプレッドシートファイルのURLを設定しています。
//スプレッドシートURLを元にスプレッドシートを取得するサンプルコード
function sampleCodeForOpenSheetByUrl(){
let targetSheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1kQSSciqJ_43r43p6InGz4XZPLFAROApK0YvTQsMfZPo/edit#gid=0");
console.log(targetSheet.getName());
}
結果はopenByIdと同じくスプレッドシートの名前がログに表示されます。
まとめ
今回はスプレッドシートをスクリプトコードを実行して開く（サーバー側で開くため実際に開いているのを見ることは出来ません）ためのopenメソッドについて紹介しました。
このメソッドを利用することによって直接スプレッドシートを起動して複数のスプレッドシート間のデータ連携や複雑な入力操作などを作業者の方が手動で行うことがなくなるため人為的なミスが確実に減らすことが可能になりますので活用してみてください。
ここまでご覧いただきありがとうございました。
コメント