Google Apps Scriptでスプレッドシート間のデータコピーやファイル作成を行うスクリプト

はじめに

Google Apps Scriptでおそらく最も頻繁に行う処理の1つであるスプレッドシート間のデータのコピーを行うスクリプトを例を使ってメモします。

スクリプトの動作概要

以下の動画のように、 Databaseという名前のスプレッドシートとその中のシートcustomerのアクティブになっているセルの行のデータを、別に用意しておいたtemplateという名前のスプレッドシートにコピーしてフォルダに保存します。処理自体は至って基礎的ですが、色々応用して使えます。



スクリプトの動作概要は以下の通りになります。

  • コピーしたいデータを含むスプレッドシートDatabaseの中のシートcustomerで特定のセルをアクティブ(選択状態)にします。
  • 上記の動画ではSample Userという値があるセルA4がアクティブになっています。この状態で、メニューのScriptCreate templateを実行します。ここのScriptというメニューは自分で追加したものです。
  • アクティブなセルA4の行、すなわちここでは4行目の情報をtemplateという名前のスプレッドシートにコピー、新規作成して指定したフォルダに保存しています。


スクリプトの前提

ここに載せるスクリプトを実行するにあたって、以下を用意しておく必要があります。

  • コピーしたいデータを持つスプレッドシートが用意済み(上記の動画でのDatabaseというスプレッドシート)
  • データのコピー先となるスプレッドシートが用意済み(上記の動画でのtemplateというスプレッドシート)



用意が必要なスプレッドシートについては次にメモします。

コピーしたいデータを持つスプレッドシート

ここでは、以下の画像のように、Databaseという名前のスプレッドシートの中のcustomerという名前のシートに顧客情報(「お名前」、「メールアドレス」、「客室タイプ」、「ご到着日」、「ご出発日」、「滞在日数」、「宿泊料金」、「備考」の8個)が含まれています。
image_database

そしてここにある顧客情報のうち、指定した行の情報を別のスプレッドシートに挿入します。上記の画像の場合はA4がフォーカスされているので、4行目のデータを取得してそれを別シートにコピーします。

データのコピー先となるスプレッドシート

上記のcustomerシートに含まれる顧客情報のデータを、以下の画像のようなtemplateという名前のスプレッドシートの中のdocumentというシートの指定したセルに対応する情報をコピーします。
template_image1

上記のシートdocumentの「お申込み情報」という表の中の「お名前」、「メールアドレス」、「客室タイプ」、「滞在日数」、「ご到着日」、「ご出発日」、「備考」、別表に「宿泊料金」をスプレッドシートDatabasecustomerシートからコピーします。

なお、このtemplatedocumentというシートに直接データを挿入するのではなく、これと全く同じスプレッドシートをコピーして指定したGoogle Drive上のフォルダに新規作成し、そこにデータをコピーします。

スクリプト解説

以下のスクリプトをスプレッドシートDatabaseのスクリプトエディタ上で保存します。


データのコピー元となるスプレッドシート、シートの指定

上記のスクリプトでは、現在アクティブなスプレッドシート、シート、セルを取得しています。
すなわち冒頭に載せた動画の例だと、スプレッドシートDatabase、シートcustomerのセルA4を取得することになります。


もしアクティブなスプレッドシートやシートではなく、IDなどで指定したい場合には以下のようにします。

上記はスプレッドシートのID、シート名で指定しています。そして取得したいデータが格納されているセルの行番号を取得しています。

指定したセル範囲のデータを取得

以下の部分で行っています。


getRangeでセル範囲を指定して、その後にgetValuesでデータを取得しています。ここのgetRangeの引数は4つ(rowIndexcolIndexnumRowsnumCols)です。
なお、getValuesの戻り値は2次元配列であり、上記の変数valuesには以下のように値が格納されます。もしnumRowsが2以上の値であれば、当然valuesには2行以上分のデータが格納されます。ここではnumRowsを1に指定しているので、1行分のデータしか格納されません。

ここの例の場合では、「お名前」、「メールアドレス」、「客室タイプ」、「ご到着日」、「ご出発日」、「滞在日数」、「宿泊料金」、「備考」の8個。numColsには8が格納されます。

指定したセルにフォーマットを設定する

数値を表示するフォーマット指定できます。フォーマットの指定方法やサンプルについては以下に載せているので参考にしてみてください。

Google Apps Script で数値のフォーマットをカスタム形式で表示する方法と具体例 | virtualiment


メニューからスクリプトを実行できるようにメニュー項目を追加する

スプレッドシートのメニューから任意のスクリプトを実行できるよう、メニューに自由に項目を追加できます。追加方法についてはこちらに載せていますので必要な方がご参照ください。

置換が必要な箇所

なお、上記のスクリプトでは、以下の箇所をそれぞれの環境に合わせて置換して使用してください。

  • データのコピー先となるスプレッドシートID (上記スクリプト内のtemplateSSID)
  • 新規作成するスプレッドシートの格納先フォルダのID (上記スクリプト内のfolderID)
  • データのコピー先となるセル範囲(上記スクリプトの46~53行目のC15など)
  • コピーするデータと配列の対応(上記スクリプトの70~77行目)


まとめ

ここに載せたスクリプトを元にスプレッドシート間でデータを自由にコピーしたり、ファイルを新規作成できるようになれば色々と応用の幅が広がります。例えば以下に載せたGmailを解析してスプレッドシートへ書き込みするスクリプト、PDF化するスクリプトやを組み合わせる色々な用途に使用できると思います。


Google Apps Script で指定したシートをPDF化するスクリプト

このエントリーをはてなブックマークに追加

SPONSORED LINK

この投稿へのコメント

コメントはありません。

コメントを残す

メールアドレスが公開されることはありません。

この投稿へのトラックバック

トラックバックはありません。

トラックバック URL