【GAS】スプレッドシートにデータを自動入力して新規スプレッドシートとして複製する

はじめに

スプレッドシートのシートを複製するサイトはたくさん見つかるが、スプレッドシート自体を複製する方法があまり見つからなかったので書きました。コードのみ参照したい方は**スクリプト - makeCopy()**を参照してください。

本記事では、「テンプレートとなるスプレッドシートにデータを入力して、新しいスプレッドシートとして作成する」という目的を設定して説明します。
複製する対象はシートではなくスプレッドシートそのものなので、下記のようにスプレッドシートが作成されます。（データは適当に生成しています。）

↑複製されたスプレッドシートたち

生成されたスプレッドシートはせいぜい５０個ほどなので、手作業でコピペしても１時間くらいあればできそうですが、どこかで必ずコピペミスが発生するはずです。また、後から追加や修正が走った場合、さらに削除&コピペ作業が始まります。スクリプトであればコードを微修正するだけですし、後になるにつれて楽になります。

スプレッドシートのテンプレート

下記のようなTEMPLATEシートに、DATAシートが持つデータを１レコードずつ自動入力して、新たなスプレッドシートとして新規作成します。

↑図1 複製元となるテンプレートを持ったスプレッドシート

図2 スプレッドシートに記載するデータ（データの内容はでたらめです）

スクリプト - makeCopy()

[複製したいファイルのID].makeCopy( )

関数	動作
makeCopy( )	IDで指定したファイルをスクリプトを実行したフォルダにコピー
makeCopy(FOLDER_ID)	IDで指定したファイルをFOLDER_IDで指定したフォルダにコピー
makeCopy(NAME)	IDで指定したファイルをNAMEというファイル名でコピー
makeCopy(NAME, FOLDER_ID)	IDで指定したファイルをNAMEというファイル名で指定したフォルダにコピー

//フォルダの取得
const folder = DriveApp.getFolderById('フォルダのID');
//ファイルの取得
const file   = DriveApp.getFileById('ファイル(スプレッドシート)のID');

//ファイルの複製
const new_file = file.makeCopy("コピー", folder)

たった3行でスプレッドシートを複製することができます。以下の流れでシートを複製しています。

1. 保存先のフォルダのIDを取得する
2. 複製したいファイル(スプレッドシート)のIDを取得する
3. makeCopy関数でfile（取得したファイルID）のコピーを作成

スクリプト - makeCopy() + getId()

しかし、できたてほやほやのシートに対して同じスクリプト上で入力操作を行うにはコピーしたファイルのIDが必要ですが、スクリプト実行前には存在しないためあらかじめ用意することができません。

そこで次のようにスクリプトを工夫することで新しく作成したファイルに対しても操作を行うことができます。

//フォルダの取得
const folder = DriveApp.getFolderById('フォルダのID');
//ファイルの取得
const file   = DriveApp.getFileById('ファイル(スプレッドシート)のID');

//ファイルを複製+複製したファイルのIDを取得
let new_spreadsheet_id = file.makeCopy("コピーしたファイル名", folder).getId();
//複製したファイルのIDを使用してスプレッドシートを取得
let new_spreadsheet    = SpreadsheetApp.openById(new_spreadsheet_id);

3行目でファイルを複製すると同時に、そのファイルのIDを**.getId()**で取得することで、そのIDを使ってコピーしたファイルに対して操作を行うことができます.

スクリプト - スプレッドシートの複製

function myFunction() {
  //テンプレートとなるスプレッドシートの取得
  const spreadsheet  = SpreadsheetApp.openById('xxxxxxxxxxxxxxxxxxxxxxxxx');
  //スプレッドシート内にあるシート(TEMPLATE)の取得
  const sht_template = spreadsheet.getSheetByName('TEMPLATE');
  //スプレッドシート内にあるシート(DATA)の取得
  const sht_data     = spreadsheet.getSheetByName('DATA');

  //保存先となるフォルダの取得
  const folder = DriveApp.getFolderById('XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
  //複製するファイル(スプレッドシート)の取得
  const file   = DriveApp.getFileById('xxxxxxxxxxxxxxxxxxxxxxxxx');

  //データがある先頭行
  const head = 3;
  //データがある最終行
  const tail = 52;

  //データ数だけ繰り返す(head → tail)
  for(i=head;i<=tail;i++){
      
      //ファイルを複製+複製したファイルのIDを取得
      let new_spreadsheet_id = file.makeCopy(sht_data.getRange("A"+i).getValue()+"さんのファイル", folder).getId();
      //複製したファイルのIDを使用してスプレッドシートを取得
      let new_spreadsheet    = SpreadsheetApp.openById(new_spreadsheet_id);
      //複製したスプレッドシートの内にあるシート(TEMPLATE)の取得
      let new_sht_template   = new_spreadsheet.getSheetByName('TEMPLATE');
      //複製したスプレッドシートの内にあるシート(DATA)の取得
      let new_sht_data       = new_spreadsheet.getSheetByName('DATA');

      //DATAシートからそれぞれのデータを取得
      let name     =  new_sht_data.getRange("A"+i).getValue();
      let furigana =  new_sht_data.getRange("B"+i).getValue();
      let birth    =  new_sht_data.getRange("C"+i).getValue();
      let sex      =  new_sht_data.getRange("D"+i).getValue();
      let address  =  new_sht_data.getRange("E"+i).getValue();
      let mail     =  new_sht_data.getRange("F"+i).getValue();
      let tel      =  new_sht_data.getRange("G"+i).getValue();
      let bikou    =  new_sht_data.getRange("H"+i).getValue();

      //DATAシートから取得したデータをTEMPLATEシートに入力
      new_sht_template.getRange("B2").setValue(furigana);
      new_sht_template.getRange("B3").setValue(name);
      new_sht_template.getRange("B4").setValue(birth);
      new_sht_template.getRange("E4").setValue(sex);
      new_sht_template.getRange("B5").setValue(address);
      new_sht_template.getRange("B6").setValue(mail);
      new_sht_template.getRange("E6").setValue(tel);
      new_sht_template.getRange("B7").setValue(bikou);

      //複製したスプレッドシートには必要のないDATAシートを削除
      new_spreadsheet.deleteSheet(new_sht_data);

      //作成完了と残数を表示
      console.log(name+"さんのファイル作成完了");
      console.log("残り"+(tail-i)+"枚");
    }
}

※補足
xxxxxxxxxxx テンプレートスプレッドシートのID
XXXXXXXXXXX 保存したいフォルダのID

結果

作成されたスプレッドシートを見ると、スクリプト通りそれぞれのセルに任意の値が入力されています。
今回の例は一つのシートに入力していますが、複数のシートに対してもデータ・書式等を編集することができます。

↑作成されたスプレッドシートのシート内容

まとめ

テンプレートとなるスプレッドシートにデータを自動入力して新規ファイルを作成する方法を紹介しました。

GASが用意する様々な関数を組み合わせれば、思い通りの操作を実現することができるので、繰り返しになりそうな作業があったら一度スクリプトを考えてみると使える時間を増やすことができると思います。

参考文献