【GAS】スプレッドシートを開く事ができるopenメソッドをマスター

  • このエントリーをはてなブックマークに追加
  • Pocket
  • LINEで送る

スプレッドシートで作業をしている時に他のスプレッドシートのデータを簡単に取得ができればと思う時があります。

例えば・・・

Aというスプレッドシートに1日の売り上げを記入

その後Bというスプレッドシートの合計売り上げデータにAで入力した1日の売り上げを加算

同じスプレッドシート内であれば関数などですぐに反映されるのですが、スプレッドシートが違うとそうはいかず。

その場合にBのスプレッドシートをいちいち開いてデータを入れていくのはとても効率が悪いですね。

今回ご紹介するopenメソッドを使用すればスクリプトを書くだけでこのような処理を一括して行うことが可能になります。

openメソッドには様々な種類もありますので各メソッドについてもご紹介していきます。


スプレッドシートのopenメソッド

スプレッドシートを開く事ができるメソッドです。

ただし私たちがスプレッドシートを開いてセル一覧を起動するような形ではなくあくまでサーバーサイドで開かれるため実際に目に見える形では表示されません。

クラス:SpreadsheetApp

注意:初めてコードを実行する際にアクセス権限の許可を求められますが許可をしてください。

アクセス権限についての詳細は以下の記事に詳しく載っています。

無料アカウントでGoogleサービスへのスコープを設定する方法

open(ファイル)

ファイルオブジェクトとして取得したデータでスプレッドシートを開きます。(ファイル)の部分に入るデータはファイルオブジェクトになります。

今回はGoogleリファレンスのスクリプトを使用して解説します。

 

スクリプト(横にスクロールできます)

function openFile(){
  var files = DriveApp.searchFiles(
      'starred = true and mimeType = "' + MimeType.GOOGLE_SHEETS + '"');
  while (files.hasNext()) {
    var spreadSheet = SpreadsheetApp.open(files.next());
    var targetSheet = spreadSheet.getSheets()[0];
    Logger.log(targetSheet.getName());
  }
}

流れとしては

2行目〜3行目:ファイルを検索するメソッドDriveApp.searchFilesを使用し検索をしています。

searchFilesについては以下のリンクで詳しく説明しています。

Google Apps Scriptでドライブのフォルダやファイルを取得してみる

検索内容は以下の2つの指示によって設定されています。

‘starred = true and mimeType = “‘ + MimeType.GOOGLE_SHEETS + ‘”‘

個別に見ていくと

starred = true:スターを設定しているファイル

mimeType = “‘ + MimeType.GOOGLE_SHEETS + ‘”:ファイルの種類がスプレッドシートである

4行目から8行目まで:今回は取得するファイル数がいくつあるかわからない(スターをつけたスプレットシートがどれくらいあるか分からない)ためwhileを使用してループを回しています。

またfiles.hasNext()ではファイルをオブジェクトで取得しています。

そしてスターをつけたスプレッドシートの最初のシート情報を取得し、ログにそのシート名を表示させるというスクリプトが書かれています。

以上が流れです。

補足:スターの付け方

スターをつけることによって検索ですぐに調べることができます。メールではよく使われているこの機能はスプレッドシートなどのグーグルドライブのフォルダ・ファイルでも設定することが可能です。

ですので今回のスクリプトのようにスターがついたファイルだけ検索して取得し一覧で表示させることやスターをつけた中で条件検索するというような使い方もできますのでぜひ参考にしてみてください。

グーグルドライブのファイルやフォルダを右クリックすると出てくる項目の中にスターを追加という項目があります。それをクリックするだけです。

2020-09-02 23.10.28

 

スターがつくとファイル名の隣に星マークがつきます。今回はstar付きファイルサンプルという名称をつけたファイルにスターをつけました。そしてシート1の名前をスター付きサンプルシートとつけています。

スターをつけたら(ドライブの中でスターがついている場合はそれが全て検索されます。)先ほどのスクリプトを実行してみてください。

2020-09-02 23.12.06

他のフォルダ・ファイルにスターが無い場合はログを確認すると以下のように表示されます。

2020-09-02 23.18.30

このように大量のデータを扱ったりIDやURLが分からないけど対象内容は分かっている場合のスプレッドシートのデータを取得したい場合などにopen(ファイル)メソッドは役に立ちます。

 

openById(id)

スプレッドシートに与えられたIDを使ってスプレッドシートを開きます。

スプレッドシートのIDはURLから取得する事ができます。

例:スプレッドシートのURL https://docs.google.com/spreadsheets/d/abc1234567/edit#gid=0

このスプレッドシートのURLの場合、黒字のabc1234567がIDとなります。

今回はテストというスプレッドシートにアクセスします。

2020-09-01 18.46.17

以下がサンプルコードです。openById(“*****”)の部分はスプレッドシートのIDが入ります。そしてアクセスしたスプレッドシートの名前をgetNameで取得しています。

function openSheetById(){
  var targetSheet = SpreadsheetApp.openById("********");
  Logger.log(targetSheet.getName());
}

結果は以下のようにスプレッドシート名のテストがログで取得できます。

2020-09-01 19.19.09

openByUrl(url)

先ほどはIDから取得する方法でしたがこちらはスプレッドシートのURLからスプレッドシートを開く方法です。

以下がサンプルコードです。ID部分は****で表示しています。

function openSheetByUrl(){
  var targetSheet = SpreadsheetApp.openByUrl(
    'https://docs.google.com/spreadsheets/d/*******/edit');
  Logger.log(targetSheet.getName());
}

結果はopenByIdと同じくスプレッドシートの名前がログに表示されます。

まとめ

今回はスプレッドシートをスクリプトコードを実行して開く(サーバー側で開くため実際に開いているのを見ることは出来ません)ためのopenメソッドについて紹介しました。

このメソッドを利用することによって直接スプレッドシートを起動して複数のスプレッドシート間のデータ入力や複雑な入力操作などを作業者の方が手動で行うことがなくなるため人為的なミスが確実に減らせることが可能になりますので活用してみてください。

ここまでご覧いただきありがとうございました。

  • このエントリーをはてなブックマークに追加
  • Pocket
  • LINEで送る

SNSでもご購読できます。

コメントを残す

*