Google Apps Scriptでスプレッドシート間のデータコピーやファイル作成を行うスクリプト
はじめに
Google Apps Scriptでおそらく最も頻繁に行う処理の1つであるスプレッドシート間のデータのコピーを行うスクリプトを例を使ってメモします。
スクリプトの動作概要
以下の動画のように、 Database
という名前のスプレッドシートとその中のシートcustomer
のアクティブになっているセルの行のデータを、別に用意しておいたtemplate
という名前のスプレッドシートにコピーしてフォルダに保存します。処理自体は至って基礎的ですが、色々応用して使えます。
スクリプトの動作概要は以下の通りになります。
- コピーしたいデータを含むスプレッドシート
Database
の中のシートcustomer
で特定のセルをアクティブ(選択状態)にします。 - 上記の動画では
Sample User
という値があるセルA4
がアクティブになっています。この状態で、メニューのScript
→Create template
を実行します。ここのScript
というメニューは自分で追加したものです。 - アクティブなセル
A4
の行、すなわちここでは4行目の情報をtemplate
という名前のスプレッドシートにコピー、新規作成して指定したフォルダに保存しています。
スクリプトの前提
ここに載せるスクリプトを実行するにあたって、以下を用意しておく必要があります。
- コピーしたいデータを持つスプレッドシートが用意済み(上記の動画での
Database
というスプレッドシート) - データのコピー先となるスプレッドシートが用意済み(上記の動画での
template
というスプレッドシート)
コピーしたいデータを持つスプレッドシート
ここでは、以下の画像のように、Database
という名前のスプレッドシートの中のcustomer
という名前のシートに顧客情報(「お名前」、「メールアドレス」、「客室タイプ」、「ご到着日」、「ご出発日」、「滞在日数」、「宿泊料金」、「備考」の8個)が含まれています。
そしてここにある顧客情報のうち、指定した行の情報を別のスプレッドシートに挿入します。上記の画像の場合はA4
がフォーカスされているので、4行目のデータを取得してそれを別シートにコピーします。
データのコピー先となるスプレッドシート
上記のcustomer
シートに含まれる顧客情報のデータを、以下の画像のようなtemplate
という名前のスプレッドシートの中のdocument
というシートの指定したセルに対応する情報をコピーします。
上記のシートdocument
の「お申込み情報」という表の中の「お名前」、「メールアドレス」、「客室タイプ」、「滞在日数」、「ご到着日」、「ご出発日」、「備考」、別表に「宿泊料金」をスプレッドシートDatabase
のcustomer
シートからコピーします。
なお、このtemplate
のdocument
というシートに直接データを挿入するのではなく、これと全く同じスプレッドシートをコピーして指定したGoogle Drive上のフォルダに新規作成し、そこにデータをコピーします。
スクリプト解説
以下のスクリプトをスプレッドシートDatabase
のスクリプトエディタ上で保存します。
function myFunction() {
//////////////// スプレッドシートDatabase、シートcustomer用の処理 ////////////////
// アクティブなスプレッドシート、シート、セルを取得
var dataSS = SpreadsheetApp.getActiveSpreadsheet();
var dataSheet = dataSS.getActiveSheet();
var targetCell = dataSheet.getActiveCell();
// コピーするデータ範囲の指定
var rowIndex = targetCell.getRow(); // コピー範囲開始の行番号。ここではアクティブなセルの行番号を取得
var colIndex = 1; // コピー範囲開始の列番号
var numRows = 1; // コピー範囲の行数
var numCols = dataSheet.getLastColumn(); // コピー範囲の列数。ここではdataSheetの列数を取得
// ここの例の場合では、「お名前」、「メールアドレス」、「客室タイプ」、「ご到着日」、「ご出発日」、「滞在日数」、「宿泊料金」、「備考」の8個。numColsには8が格納されます。)
// コピー元のデータのセル範囲を指定
var dataRange = dataSheet.getRange( rowIndex, colIndex, numRows, numCols );
// 指定したセル範囲のデータを取得
var values = dataRange.getValues();
//////////////// スプレッドシートtemplate、シートdocument用の処理 ////////////////
// データのコピー先となるスプレッドシート(ここの例のスプレッドシートtemplateに該当)のIDを指定
var templateSSID = "1234567890abcdefghijklmnopqrstuvwxyz"; // 各環境に合わせて置換
// コピー先となるスプレッドシートを開く
var templateSS = DriveApp.getFileById( templateSSID );
// データのコピー先となるスプレッドシートを新規作成する
// 新規作成するスプレッドシートの格納先フォルダのID
var folderID = "mnopqrstuvwxyz"; // 各環境に合わせて置換
var folder = DriveApp.getFolderById( folderID );
// フォーマットからコピーを作成 上のフォルダの中に置く
var newFile = templateSS.makeCopy( values[0][0], folder ); // values[0][0]はお名前
var newID = newFile.getId();
var saveSS = SpreadsheetApp.openById( newID );
// シート名を指定してシートを開く
var saveSheet = saveSS.getSheetByName('document');
// データのコピー先となるセル範囲をそれぞれ取得。各環境に合わせて変更
var nameRange = saveSheet.getRange("C15"); // お名前
var emailRange = saveSheet.getRange("I15"); // メールアドレス
var roomtypeRange = saveSheet.getRange("C16"); // 客室タイプ
var lengthRange = saveSheet.getRange("I16"); // 滞在期間
var startRange = saveSheet.getRange("C17"); // ご到着日
var endRange = saveSheet.getRange("I17"); // ご出発日
var moneyRange = saveSheet.getRange("D23"); // 宿泊料金
var noteRange = saveSheet.getRange("C18"); // 備考
// データのコピー先となるセルのフォーマットも指定可能
// 以下は日付のフォーマットを指定。
var dateFormats = 'yyyy-mm-dd';
// 金額のフォーマット
var moneyFormats = '¥ #,##0;¥ (#,##0);"zero";@[blue]';
// 指定したセル範囲に日付のフォーマットを適用
startRange.setNumberFormat( dateFormats );
endRange.setNumberFormat( dateFormats );
// 指定したセル範囲に金額のフォーマットを適用
moneyRange.setNumberFormat( moneyFormats );
// コピー先となるシートにデータをコピー。以下ではsetValueを使用しているが、処理速度が遅いので基本的にはsetValuesで一気にコピーするのが通常。
nameRange.setValue( values[0][0] );
emailRange.setValue( values[0][1] );
roomtypeRange.setValue( values[0][2] );
lengthRange.setValue( values[0][3] );
startRange.setValue( values[0][4] );
endRange.setValue( values[0][5] );
moneyRange.setValue( values[0][6] );
noteRange.setValue( values[0][7] );
}
// スプレッドシートのメニューから実行できるよう、メニューにスクリプト実行用のメニュー項目追加
function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [
{
name : "Create template",
functionName : "myFunction"
}
];
sheet.addMenu("Script", entries);
};
データのコピー元となるスプレッドシート、シートの指定
上記のスクリプトでは、現在アクティブなスプレッドシート、シート、セルを取得しています。
すなわち冒頭に載せた動画の例だと、スプレッドシートDatabase
、シートcustomer
のセルA4
を取得することになります。
var dataSS = SpreadsheetApp.getActiveSpreadsheet();
var dataSheet = dataSS.getActiveSheet();
var targetCell = dataSheet.getActiveCell();
もしアクティブなスプレッドシートやシートではなく、IDなどで指定したい場合には以下のようにします。
// データのコピー元となるスプレッドシートID
var dataSSID = "abcdefghijklmnopqrstuvwxyz1234567890";
// コピー元となるスプレッドシートを取得
var dataSS = SpreadsheetApp.openById( dataSSID );
// シート名を指定してシートを開く
var dataSheet = dataSS.getSheetByName('customer');
// この場合はgetActiveCell()が使用できないので、行番号は直接指定
var rowIndex = 4; // 4行目を指定
上記はスプレッドシートのID、シート名で指定しています。そして取得したいデータが格納されているセルの行番号を取得しています。
指定したセル範囲のデータを取得
以下の部分で行っています。
// コピーするデータ範囲の指定
var rowIndex = targetCell.getRow(); // コピー範囲開始の行番号。ここではアクティブなセルの行番号を取得
var colIndex = 1; // コピー範囲開始の列番号
var numRows = 1; // コピー範囲の行数
var numCols = dataSheet.getLastColumn(); // コピー範囲の列数。ここではdataSheetの列数を取得
// ここの例の場合では、「お名前」、「メールアドレス」、「客室タイプ」、「ご到着日」、「ご出発日」、「滞在日数」、「宿泊料金」、「備考」の8個。numColsには8が格納されます。)
// コピー元のデータのセル範囲を指定
var dataRange = dataSheet.getRange( rowIndex, colIndex, numRows, numCols );
// 指定したセル範囲のデータを取得
var values = dataRange.getValues();
getRange
でセル範囲を指定して、その後にgetValues
でデータを取得しています。ここのgetRange
の引数は4つ(rowIndex
、colIndex
、numRows
、numCols
)です。
なお、getValues
の戻り値は2次元配列であり、上記の変数values
には以下のように値が格納されます。もしnumRows
が2以上の値であれば、当然values
には2行以上分のデータが格納されます。ここではnumRows
を1に指定しているので、1行分のデータしか格納されません。
// getValuesは二次元配列でデータを格納するため、上記の場合は以下の様にデータが格納される。
// values[0][0] = お名前
// values[0][1] = メールアドレス
// values[0][2] = 客室タイプ
// values[0][3] = ご到着日
// values[0][4] = ご出発日
// values[0][5] = 滞在日数
// values[0][6] = 宿泊料金
// values[0][7] = 備考
ここの例の場合では、「お名前」、「メールアドレス」、「客室タイプ」、「ご到着日」、「ご出発日」、「滞在日数」、「宿泊料金」、「備考」の8個。numColsには8が格納されます。
指定したセルにフォーマットを設定する
数値を表示するフォーマット指定できます。フォーマットの指定方法やサンプルについては以下に載せているので参考にしてみてください。
Google Apps Scriptで扱う数値を色々なフォーマットをカスタム形式で指定して表示することができます。以下に日付や金額などの数値のフォーマットの指定方法と一緒に具体例をメモします。
メニューからスクリプトを実行できるようにメニュー項目を追加する
スプレッドシートのメニューから任意のスクリプトを実行できるよう、メニューに自由に項目を追加できます。追加方法についてはこちらに載せていますので必要な方がご参照ください。
置換が必要な箇所
なお、上記のスクリプトでは、以下の箇所をそれぞれの環境に合わせて置換して使用してください。
- データのコピー先となるスプレッドシートID (上記スクリプト内の
templateSSID
) - 新規作成するスプレッドシートの格納先フォルダのID (上記スクリプト内の
folderID
) - データのコピー先となるセル範囲(上記スクリプトの46~53行目の
C15
など) - コピーするデータと配列の対応(上記スクリプトの70~77行目)
まとめ
ここに載せたスクリプトを元にスプレッドシート間でデータを自由にコピーしたり、ファイルを新規作成できるようになれば色々と応用の幅が広がります。例えば以下に載せたGmailを解析してスプレッドシートへ書き込みするスクリプト、PDF化するスクリプトやを組み合わせる色々な用途に使用できると思います。
Webサイトのお問い合わせフォームから送信されたメールを蓄積して傾向などを調べることは多くの人にとって重要だと思います。ここでは、お問い合わせメールの管理の一歩として、お問い合わせメールの内容をGoogleスプレッドシートに自動で保存するシステムの構築手順をメモします。
Googleスプレッドシートで請求書や納品書などのテンプレートを作成しておいて、データを他のシートから取得して埋め込んでPDF化などすると便利です。ここでは、指定したスプレッドシートのPDF化を行うスクリプトをメモします。
-----2017/11/30 追記 スクリプトを初めて実行する場合は承認作業が必要なりました。この承認作業手順については以下にまとめましたので必要な方はご参照ください。
少し前にGoogle Apps Script実行時に承認作業を行わないと使えないようになりました。スクリプトからスプレッドシートの値を参照したりメールを送信するような場合はそのスクリプトの初回実行時に承認が必要となったようです。この承認手順についてメモします。
関連記事
- 公開日:2019/09/28 更新日:2019/09/28
Google Apps Scriptと連携してAmazon Alexaに次のバスの時間を教えてもらう
この記事では、ユーザーの呼びかけに応答してAlexaからGoogle Apps Scriptで公開しているスクリプトにPOSTリクエストを送信し、その応答をもとにユーザーに返答させる手順をまとめます。実際の例として、Alexaに直近のバス時間を教えてもらうシステムを構築しました。
- 公開日:2019/09/27 更新日:2019/09/27
iOSのショートカットでGoogle Apps ScriptにPOSTリクエストを送信する
iOSのショートカット機能を使って指定したGoogle Apps Scriptに任意のデータをPOSTさせたのでその手順をまとめます。
- 公開日:2019/06/02 更新日:2019/06/02
Google Apps Scriptを使って翻訳作業を自動化する手順
Google Apps Scriptは様々な便利な関数が使用可能であり、その中の1つにLanguageAppというクラスがあります。LanguageAppは、自動翻訳を提供するクラスです。この記事では、Google Apps ScriptとLanguageAppを使用して翻訳作業を自動化する手順をまとめます。
- 公開日:2018/10/31 更新日:2018/10/31
Google Apps Scriptをウェブアプリケーションとして公開する手順
Google Apps Scriptで作成したコードをウェブアプリケーションとして公開する手順をメモします。
- 公開日:2018/10/08 更新日:2018/10/08
ユーザの投稿メッセージに応答するSkypeボットをGoogle Apps Scriptで作成する手順
Skypeにてユーザからボットのへの投稿メッセージに応じて適当なメッセージを送り返すボットをGoogle Apps Scriptで作成します。ここではあらかじめ決めたメッセージを返すだけですが、この記事での内容を元に色々な応用に使えると思います。