GASでスプレッドシートを取得、読み込みをしたり、
idやurlや名前を指定して開くのは、
GASでスプレッドシートを使用する上で、一番最初に行う処理です。
アクティブなシートだけでなく、別のスプレッドシートの取得もできますが、
エラーが出ることもあるので、まとめて解説しています。
サンプルシートとコードを公開しているので、
確認用に使用してみてください。
この記事で解説している関数・メソッド
SpreadsheetApp
getActiveSpreadsheet()
getActiveSheet()
openById()
openByUrl()
getSheetByName()
getSheets()[n]
GASでスプレッドシートを取得・読み込む方法
基本的にGASでスプレッドシートを読み込むには、
2つの方法があります。
1、現在のアクティブなシートを取得
2、idやurl、シート名や番号を指定してシートを取得
アクティブなスプレッドシートを取得・読み込みをする
アクティブなシートは、今開いているGoogle Apps Scriptが紐付けられているシートのことです。
この取得は一番簡単で、一番使用される方法で、
GASでスプレッドシートを扱う基本的なメソッドです。
使用するメソッド
SpreadsheetApp
getActiveSpreadsheet()
getActiveSheet()
function myFunction() {
//アクティブシートを取得して変数sheetに格納
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
}
スプレッドシートidからシートを取得・読み込みをする
シートIDは、スプレッドシートのURL「/d/」と「/edit#」の間にあります。
URL https://docs.google.com/spreadsheets/d/sample012345/edit#gid=0
↑このURLのスプレッドシートIDは、
"sample0123457"です。
ちなみに、「#gid=0」の「0」は開いているシートのIDです。
使用するメソッド
SpreadsheetApp
openById(id)
function sitid() {
//シートIDで取得して変数「ss」に格納
var ss = SpreadsheetApp.openById('シートIDをここに入力');
//取得したシートIDのシート名「シート1」で取得して変数「sheet」に格納
var sheet = ss.getSheetByName('シート1');
}
スプレッドシートurlからシートを取得・読み込みをする
スプレッドシートのURLで読み込む場合は、
ブラウザから取得した直リンクURL、共有リンクから取得したURL、
どちらを指定しても開くことができます。
使用するメソッド
SpreadsheetApp
openByUrl(url)
function shturl() {
//シートURLで取得して変数「ss」に格納
var ss = SpreadsheetApp
.openByUrl('https://docs.google.com/spreadsheets/d/1yFUhEnHn2OuNmuHmwbm3IQWGwdVX_HftB3JOsHEtTUQ/edit?usp=sharing');
//取得したシートURLのシート0番目でシートを取得
var sheet = ss.getSheets()[0];
}
スプレッドシートを名前で指定して取得・開く
スプレッドシートのシート名で指定して開くには、
先にスプレッドシート本体（ブック）を取得しておく必要があります。
順番としては、
1、スプレッドシートを取得
2、シート名を指定して取得
使用するメソッド
getSheetByName(シート名)
function sitid() {
//シートIDで取得して変数「ss」に格納
var ss = SpreadsheetApp.openById('シートIDをここに入力');
//取得したシートIDのシート名「シート1」で取得して変数「sheet」に格納
var sheet = ss.getSheetByName('シート1');
}
スプレッドシートを何番目のシートか指定して取得
シート番号での取得は、シート名取得と同じように、
まずスプレッドシートを取得してから、シート番号を取得、
という順番で取得していきます。
シートの番号は一番左から0、1、2・・・と、0から順番に数えます。
一番左のシートを指定する時は「0」となります。
使用メソッド
getSheets()[シート番号]
function shturl() {
//シートURLで取得して変数「ss」に格納
var ss = SpreadsheetApp
.openByUrl('https://docs.google.com/spreadsheets/d/1yFUhEnHn2OuNmuHmwbm3IQWGwdVX_HftB3JOsHEtTUQ/edit?usp=sharing');
//取得したシートURLのシート0番目でシートを取得
var sheet = ss.getSheets()[0];
}
呼び出す権限がありません。というエラー
スプレッドシートでメソッドを使用するには、
権限の許可をしておく必要がありますが、
使用メソッドの許可がされていない場合、エラーとして、
「呼び出す権限がありません。」とログに出力されます。
今回はopenByIdで出たものですが、
「呼び出す権限がありません。」というエラーが出た場合は、
基本的にはやることは同じで、使用しているメソッドを実行して、許可をするだけです。
シンプルトリガーのonEdit(e)で許可が必要なメソッドが入っていると、
実行してもエラーになってしまうので、
別で該当のメソッドを使用したGASを作成して手動実行する必要があります。
※別のスプレッドシートの参照メソッドはonEditでは使用できずエラーになります。
（メソッドによっては「appsscript.json」を書き足す必要もありますが、
これはまた記事にしていこうと思います！）
また、シンプルトリガーのonEdit(e)では、
他のスプレッドシートの参照はできないため、
時間起動のトリガーにするなど、他の対処が必要になります。
onEditについての詳細記事はこちら
【GAS】onEditで特定のシートやセルの変更で実行する方法まとめ【Google Apps Script】
エラーを回避する権限の取得方法
権限許可のないメソッドを実行すると、許可取得のためのダイアログが表示されるので、
クリックして、アカウントを選択
アカウントを選択すると、「このアプリはGoogleで確認されていません」という表示が出ますが、
Googleが作ったコードではなく、自作のコードでGASを実行しようとしているからです。
左下の詳細をクリック
詳細をクリックすると、
「実行しようとしているGASの名前（安全ではないページ）に移動」
という表示が出ます。
安全ではないと書いていますが、大丈夫です。
（むしろこれやらないとGASが使えない）
こちらをクリック
「GASの名前」がGoogleアカウントへのアクセスをリクエストしています
という画面が出るので、「許可」をクリック
この時に表示される許可の内容はメソッドによって異なります。
これで、GASで該当のメソッドが使えるようになりました！
【応用編】スプレッドシートの一覧から開く
私がよくやっている方法で、メンテナンス用のシートを用意しておいて、
そこからシートのIDなどを取得して処理していくということをよくやります。
このサンプルデータの公開もしているので、
そちらも併せて確認してみてください。
メンテナンス用のシートを準備する
メンテナンス用のシートには、
取得して処理をしたいシートURLやシートID、シート名などを入力して作成します。
作成したら、GASからそれぞれのデータを読み込んで処理を行なっていきます。
シート取得、読み込みGASの応用、実例コード
function ouyou() {
//アクティブシート（【Copori】スプレッドシート取得サンプル）の「シート1」を取得
let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
//アクティブシート（【Copori】スプレッドシート取得サンプル）のB23からサンプル2シートIDを取得
let sample1 = sheet.getRange('B23').getValue();
Logger.log('サンプル2のシートID取得');
//アクティブシート（【Copori】スプレッドシート取得サンプル）のB24からサンプル3シートIDを取得
let sample2 = sheet.getRange('B24').getValue();
Logger.log('サンプル2のシートID取得');
//アクティブシート（【Copori】スプレッドシート取得サンプル）のD22〜D24からサンプル1のシート名を取得
let sample1shtname1 = sheet.getRange('D22').getValue();
let sample1shtname2 = sheet.getRange('D23').getValue();
let sample1shtname3 = sheet.getRange('D24').getValue();
Logger.log('サンプル1のシート名を取得')
//取得した変数からサンプル1のシート名でそれぞれを取得
let sample1sht1 = SpreadsheetApp.openById(sample1).getSheetByName(sample1shtname1);
let sample1sht2 = SpreadsheetApp.openById(sample1).getSheetByName(sample1shtname2);
let sample1sht3 = SpreadsheetApp.openById(sample1).getSheetByName(sample1shtname3);
Logger.log('サンプル2シート、3枚を取得')
//取得した変数からサンプル2のシート名で取得
let sample2sht1 = SpreadsheetApp.openById(sample2).getSheetByName(sample1shtname1);
Logger.log('サンプル2シートを取得')
//サンプルシートの実行結果に「OK」をセット
sheet.getRange('C3').setValue('OK');
Logger.log('サンプルシート実行結果「OK」')
//サンプルシート1の実行結果に「OK」をセット
sample1sht1.getRange('A8').setValue('OK');
sample1sht2.getRange('A2').setValue('OK');
sample1sht3.getRange('A2').setValue('OK');
Logger.log('サンプル2 シート3枚、実行結果「OK」')
//サンプルシート2の実行結果に「OK」をセット
sample2sht1.getRange('A8').setValue('OK');
Logger.log('サンプル2シート1 実行結果「OK」')
}
無駄にコードが長くなっていますが、
わかりやすいように注釈やログ書き出しを行なっているからです。
内容は難しくないので、サンプルデータを実行しながら確認してみてください。
この処理がわかれば、応用もできるようになりますよ！
【サンプルデータ】取得・読み込みのスプレッドシートとGASのサンプル
ここでは、その内容とサンプルデータをそのまま公開しています。
サンプルデータの使い方は、スプレッドシートにも記載していますので、
確認してみてください！
サンプルスプレッドシートとGAS
【Copori】スプレッドシート取得サンプルのシートでは、
シートURLからMIDとSEARCH関数を使用してIDを取得し、
そこからGASでIDやシート名などを取得して開いて処理を行うということを行なっています。
処理は実行結果のセルに「OK」と記入するだけのシンプルなものです。
応用編で行なっているデータも入っているので、
実行して確認してみてください。
【使用方法】シート読み込みサンプルデータ
1、【Copori】スプレッドシート取得サンプルと、
その中に記載してある、サンプルシート1、2を開いてそれぞれコピーしてください。
2、開きたいスプレッドシートのURLに、コピーしたシートのURLをそれぞれコピペしてください。
URLを入力すると、MIDとSEARCH関数を使用してIDを取得できるようになっています。
3、メニューから「拡張機能」⇨「Apps Script」を開きます
※複数アカウントでログインしているとエラーで開けないので注意してください
4、GASの実行をして確認してみてください
このサンプルコードでは、スプレッドシートを取得・開いて実行結果に「OK」と入力するようになっています。
実行後、それぞれのサンプルシートの実行結果に「OK」が表示されたら正常に動作しています。
5、再度実行する時は、GASの初期化用（del）を実行してください。
（実行完了すると、実行結果のセル（サンプル2、サンプル3も含む）が全て空欄になります）
わかりにくいところやご質問があれば、
コメントやお問い合わせよりご連絡ください！
修正・追記などさせていただきます。
また、GASでやりたいことができない！という場合も、
一緒に考えて作成させていただきますので、ご連絡くださいね。
作成したコードはブログで公開していきます。
（GASはネットのみで完全独学なので、全てが対応できるかはわかりませんが・・・）
それでは、ありがとうございました！
コメント