GoogleAppsScript

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のスクリプトエディタ上で保存します。

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つ(rowIndexcolIndexnumRowsnumCols)です。
なお、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 で数値のフォーマットをカスタム形式で表示する方法と具体例 | virtualiment


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

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

置換が必要な箇所

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

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


まとめ

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


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

 —–2017/11/30 追記
スクリプトを初めて実行する場合は承認作業が必要なりました。この承認作業手順についてはGoogle Apps Scriptの初回実行に必要となる承認手順にまとめましたので必要な方はご参照ください。

SPONSORED LINK

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

コメントを残す

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