みなさんこんにちは！GASおじさんです。
「GASでスプレッドシートを自由自在に操るためのスキル習得講座」の第5回です。
前回の記事はこちら。
前回までで、「セルの値を取得して、別のセルに入力する」という基本的な操作ができるようになりました。
しかし、それはあくまで同じシート内での操作でした。
今回は、「取得した値を別のシートに入力するにはどうしたらいいか？」という部分をやっていきます。
getValuesした値を別のシートにsetValuesしよう
下準備
まずは下準備として以下のような2つのシートを用意してみてください。
シート1
という文字を入力しています。
つづいて、画面左下の「＋」から「シート2」を追加します。
シート2
シート2はまっさらな状態で構いません。
では、「シート1のA1からE3をgetValuesして、シート2のA1からE3にsetValuesする」ということをしていきます。
A1記法による範囲指定
まずはA1記法による範囲指定です。以下のコードをエディタにコピペしてみてください。
function myFunction() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('シート1');
const values = sheet.getRange('A1:E3').getValues();
const sheet2 = ss.getSheetByName('シート2');
sheet2.getRange('A1:E3').setValues(values);
}こちらは、
- 4〜5行目でシート1からgetValuesして、
- 7〜8行目でシート2にsetValuesする
というコードですね。
ポイントは、
- 4行目では
sheetという変数にss.getSheetByName('シート1')を代入し、 - 7行目では
sheet2という変数にss.getSheetByName('シート2')を代入している
という部分ですね。
関数の中で同じ変数名は使えないので、新たに「sheet2」という変数を用意しました。
では、これを実行してスプレッドシートを確認してみましょう。
無事、シート2にsetValuesできていることが確認できました。
番地記法による範囲指定
次に、番地記法による範囲指定で書いてみましょう。以下のコードをエディタにコピペしてみてください。
function myFunction() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('シート1');
const values = sheet.getRange(1, 1, 3, 5).getValues();
const sheet2 = ss.getSheetByName('シート2');
sheet2.getRange(5, 1, values.length, values[0].length).setValues(values);
}こちらは、
- 4〜5行目で、シート1の1行1列目から3行5列分(つまりA1からE3)をgetValuesして、
- 7〜8行目で、シート2の5行1列目から3行5列分(つまりA5からE7)にsetValuesする
というコードですね。（ここがよくわからない場合は第2回の記事を読み返してきてください。）
それでは、これを実行してスプレッドシートを確認してみましょう。
無事、シート2のA5からE7にsetValuesできていることが確認できました！
まったく別のスプレッドシートにsetValuesする場合は？
新たな問題点
さて、無事に別のシートへsetValuesすることができました。
しかし、ここまではあくまで「同じスプレッドシート内で、別のシートにsetValuesするにはどうしたらいいか」という話です。
ここでもし、以下のような「GASをはじめよう！その2」という新しいスプレッドシートを新規作成して、その「シート1」にsetValuesしたいです、という場合はどうしたらいいでしょうか？
結論: openByIdを使う
もったいぶらずに結論からいうと、openByIdメソッドを使って新しいスプレッドシートを取得します。
まずは以下のコードをエディタにコピペしてみてください。
function myFunction() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('シート1');
const values = sheet.getRange('A1:E3').getValues();
const ss2 = SpreadsheetApp.openById('xxxxxxxxxxxxxxxxxxxxxxx');
const sheet2 = ss2.getSheetByName('シート1');
sheet2.getRange('A1:E3').setValues(values);
}エディタに貼り付けたら、6行目のxxxxxxxxxxxxxxxxxxxxxxxの部分を、新しいスプレッドシートのIDに書き換えます。
スプレッドシートIDはURLのspreadsheets/d/と/editの間にある文字列です。
この文字列をコピーして、6行目のopenByIdのカッコの中に貼り付けます。
こうすることによって、新しいスプレッドシート（GASをはじめよう！その2）を取得することができます。
新しいスプレッドシートはss2という変数に代入しておくことにします。
このss2に対してgetSheetByName('シート1')をすることによって、「GASをはじめよう！その2」の「シート1」を取得し、それをsheet2という変数に代入します。
では、実際にプログラムを実行してみましょう。
無事、新しいスプレッドシートのほうにsetValuesできていることが確認できました！
getActiveSpreadsheet()とopenById(‘xxxxxx’)の違い
ではここであらためて、以下のコードの2行目と6行目に注目ください。
function myFunction() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('シート1');
const values = sheet.getRange('A1:E3').getValues();
const ss2 = SpreadsheetApp.openById('xxxxxxxxxxxxxxxxxxxxxxx');
const sheet2 = ss2.getSheetByName('シート1');
sheet2.getRange('A1:E3').setValues(values);
}2行目ではSpreadsheetApp.getActiveSpreadsheet();でスプレッドシートを取得し、
6行目ではSpreadsheetApp.openById('xxxxxxxxxxx');でスプレッドシートを取得しています。
どちらもスプレッドシートを取得するためのメソッドですが、この2つの違いは一体なんなのでしょうか？
これは、
- スクリプトエディタがスプレッドシートに紐づいている場合は
getActiveSpreadsheet()を使う - スクリプトエディタがスプレッドシートに紐づいていない場合は
openById('xxxxxxxx')を使う
というのが答えになります。
現在利用中のスクリプトエディタで、左メニューの一番上のアイコンをクリックして「概要」を開いてみてください。
すると、「コンテナ」という項目に「GASをはじめよう！」というスプレッドシートのタイトルがついているのが確認できると思います（スプレッドシートのタイトルは人によって異なるかもしれませんが...）。
これはつまり、現在利用中のスクリプトエディタと、「GASをはじめよう！」というスプレッドシートが紐づいていることを意味しています。
なぜこの2つが紐づいているのかというと、最初にこの「GASをはじめよう！」のスプレッドシートのメニューから、スクリプトエディタを開いたからですね。
このようにスプレッドシートに紐づいたスクリプトエディタの場合は、
SpreadsheetApp.getActiveSpreadsheet();
とすることでスプレッドシートを取得することができます。
一方、今回新しく作成したスプレッドシート「GASをはじめよう！その2」に関しては、現在利用中のスクリプトエディタとは紐づいていないので、
SpreadsheetApp.openById('xxxxxxxxx');
とすることでしか、スプレッドシートを取得することができない、というわけですね。
この2つのメソッドの違いも重要なので、よく理解しておくようにしましょう。
「コンテナバインドスクリプト」と「スタンドアロンスクリプト」
ちなみに、このようにスプレッドシート（コンテナ）に紐づいたスクリプトエディタのことを「コンテナバインドスクリプト」と言います。
コンテナバインドスクリプトは、スプレッドシートだけでなく、GoogleドキュメントやGoogleフォームからも開くことができます。
Googleドキュメントから開く場合
Googleフォームから開く場合
このように、さまざまな種類のコンテナと紐づいたコンテナバインドスクリプトが存在します。
逆に、どのコンテナとも紐づいていないスクリプトエディタのことを、「スタンドアロンスクリプト」と言います。
スタンドアロンスクリプトは、Googleドライブから開くことができます。
Googleドライブからスクリプトエディタを起動すると、どのコンテナとも紐づいていない独立した（スタンドアロンな）スクリプトとなります。
スタンドアロンスクリプトの「概要」を確認すると、「コンテナ」の項目がありません。
つまり、どのコンテナとも紐づいていない、独立したスクリプトエディタになっているということですね。
「コンテナバインドスクリプト」と「スタンドアロンスクリプト」。
この2つの用語は今後も使う可能性があるので、丁寧に説明しておきました。
まとめ
以上、getValuesした値を別のシートにsetValuesする方法について解説してきました。
ひとくちに「別のシート」といっても、「同じスプレッドシート内の別のシート」なのか、はたまた「まったく別のスプレッドシートなのか」によって、コードの内容が変わってきます。
現在扱っているスクリプトエディタが「コンテナバインドスクリプト」なのか、「スタンドアロンスクリプト」なのかという点を意識することが求められます。
それではまた次回！
コメント