Google Apps Scriptで最終行に指定の値をコピーする方法

プログラミング
※このページの内容に広告・PRが含まれます。

特定のシートの最終行の下に指定の値をコピーする方法は、大きく分けると2つあります。それは、

  1. データが入力されている最終行の下に追加する
  2. 特定の列の最終行の下に追加する

です。さらに1. の場合において、コピー先のシートで、

  1. A列(1列目)から値を追加する
  2. A列以外の特定の列から値を追加する

の2つの方法があります。

そこでこの記事では、上記のやり方について網羅的に紹介してきます。

データが入力されている最終行の下に追加する方法

コピー先のシートの、データが入力されている最終行の下に値を追加したい場合、データが存在している最終行を取得するgetLastRow()関数を使います。

 

なおここからは、「シート1」のデータを「シート2」にコピーするコードを紹介します。「シート1」と「シート2」は以下のような状態にしてあります。

A列(1列目)から値を追加する

使用するのはappendRow()という、最後行の下に行を追加する関数です。

ただし、appendRow()関数では列を指定することはできないので、A列(1列目)から値が追加されます。

この場合のコード例は以下の通りです。

// データをコピーするスプレッドシートの指定
var id= "[データをコピーするスプレッドシートのid]"
var File = SpreadsheetApp.openById(id);
var BaseSheet = File.getSheetByName("シート1"); 
var CopyToSheet = File.getSheetByName("シート2");

// シート1の最終行・最終列を取得
var lastRow = BaseSheet.getLastRow();
var lastColumn = BaseSheet.getLastColumn();

// シート2の最終行を取得
var lastRow2 = CopyToSheet.getLastRow();

function MainFrame() {
  for(let n=1; n<=lastRow; n++) {
    // シート1のデータを1行ずつ取得
    // getValuesは2次元配列なので、最初の行のみを指定
    var array = BaseSheet.getRange(n,1,1,lastColumn).getValues()[0];
  
    // シート2の最終行に挿入
    CopyToSheet.appendRow(array);
  }
}

上記のコードを追加して『MainFrame』を起動すれば、以下の様に「シート2」にデータが追加されます。

ただ、このコードの欠点は、実行完了までに時間がかかることです。特に、コピー元のデータ数が多い場合は。

なぜなら、一行ずつ「シート1」と「シート2」を行ったり来たりするからです。「シート1」の5行をコピーするのでさえ、約4秒かかってます。

高速化させたい場合

実行時間を短縮させたいなら、空の配列に「シート1」の値を先に格納しておき、最後に一括で「シート2」にコピーすればOKです。

以下のコードだと、「シート1」の5行をコピーするのに約1秒でした。

// データをコピーするスプレッドシートの指定
var id= "[データをコピーするスプレッドシートのid]"
var File = SpreadsheetApp.openById(id);
var BaseSheet = File.getSheetByName("シート1"); 
var CopyToSheet = File.getSheetByName("シート2");

// シート1の最終行・最終列を取得
var lastRow = BaseSheet.getLastRow();
var lastColumn = BaseSheet.getLastColumn();

// シート2の最終行を取得
var lastRow2 = CopyToSheet.getLastRow();

// 空の配列を用意
var array = []

function MainFrame() {
  for(let n=1; n<=lastRow; n++){
    // シート1のデータを配列に格納
    // getValuesは2次元配列なので、最初の行のみを指定
    array.push(BaseSheet.getRange(n,1,1,lastColumn).getValues()[0]);
  }

  // シート2の最終行に追加
  CopyToSheet.getRange(lastRow2+1,1,array.length,array[0].length).setValues(array);
}

少し補足しておくと、

  • 21行目のpush()関数で1行ずつ「シート1」のデータを格納していきます。
  • 最後にsetValues()関数で、2次元配列のデータをそのまま「シート2」に出力してます。
  • setValues()を使用する際、value.lengthで配列の行数、value[0].lengthで配列の列数を取得して範囲を指定しています。

A列以外の特定の列から値を追加する

例えば、「シート2」のB列(2列目)からデータを追加したい場合もあるかと思います。

そういう場合は、以下のようにするといいでしょう。

// データをコピーするスプレッドシートの指定
var id= "[データをコピーするスプレッドシートのid]"
var File = SpreadsheetApp.openById(id);
var BaseSheet = File.getSheetByName("シート1"); 
var CopyToSheet = File.getSheetByName("シート2");

// シート1の最終行・最終列を取得
var lastRow = BaseSheet.getLastRow();
var lastColumn = BaseSheet.getLastColumn();

// シート2の最終行を取得
var lastRow2 = CopyToSheet.getLastRow();

function MainFrame() {
  // シート1のデータを取得
  var array = BaseSheet.getRange(1,1,lastRow,lastColumn).getValues();

  // シート2のB列以降にデータを追加
  CopyToSheet.getRange(lastRow2+1,2,lastRow,lastColumn).setValues(array);
}

すると、以下のように「シート2」のB列からデータが追加されてます。

setValues()でデータを追加するときの範囲指定で、開始列を「2列目(B列)」に指定する、たったこれだけです。

開始列は、19行目のgetRange()の2番目の引数で指定できます。

もちろん、開始列を「1列目(A列)」に指定すれば、A列からデータを追加できます。

なお、このコードも約1秒でコピーが完了しました。

 

特定の列の最終行の下に追加する

例えば、以下のような「シート1」のデータを、「シート2」のB列に追加する方法を紹介します。

コードを実行すると以下のようになります。

ソースコードは以下の通りです。

// データをコピーするスプレッドシートの指定
var id= "[データをコピーするスプレッドシートのid]"
var File = SpreadsheetApp.openById(id);
var BaseSheet = File.getSheetByName("シート1"); 
var CopyToSheet = File.getSheetByName("シート2");

// シート1の最終行・最終列を取得
var lastRow = BaseSheet.getLastRow();
var lastColumn = BaseSheet.getLastColumn();

// シート2の指定した行の最終行を取得
var targetRowCount = getUsedRowCount(CopyToSheet, 'B');

function MainFrame() {
  // シート1のデータを取得
  var array = BaseSheet.getRange(1,1,lastRow,lastColumn).getValues();

  // シート2にデータを追加
  CopyToSheet.getRange(targetRowCount+1,2,lastRow,lastColumn).setValues(array);
}

function getUsedRowCount(sheet, rowName) {
  var values = sheet.getRange(rowName + ':' + rowName).getValues();
  return values.filter(String).length;
}

getUsedRowCount()という独自の関数を定義して、B列の中でもデータが入っているセル数をカウントしてます。

なので、途中に空のセルがある場合は使えないので、ご注意ください。

 

侍テラコヤならいつでもプログラミングの質問が可能

プログラミングをやっていると、どうしても自分では解決できないことがあると思います。

teratailStack OverflowQiitaなどの無料の掲示板で質問することもできますが、回答までに時間がかかったり、自分が期待していた回答が得られないことも多々あります。

そんなときは、お金はかかりますが、月数千円ぐらいで質問し放題の侍テラコヤを利用するのがオススメです。

侍テラコヤは、サブスクリプション型のプログラミング学習サービスです。

  • 質問し放題、回答率100%のQ&A掲示板が利用できる(しかも平均30分で回答が来る!
  • 月に1回、現役エンジニアのレッスンが受けられる
  • プログラミングやWebアプリ開発、機械学習などの50種類以上の教材が学習し放題

といったサービスが受けられます。

これだけの内容にも関わらず、料金も入学金は無料、月額料金も無料のフリープランがあります(ただし、フリープランだと一部の教材やQ&A、現役エンジニアのレッスンは利用不可)。

すべての教材が利用できる有料プランでも月額2,980円(税込)~という格安で利用できます。

この料金にも関わらず、未経験からエンジニアに転職・就職するための面接対策や職務経歴書の作成サポートも付いています。

 

いつでも退会可能で1ヶ月だけの利用もOK!(有料プランの場合は、途中解約で解約手数料が発生する場合あり)

まずは無料で始められるので、とりあえず登録してみましょう。

タイトルとURLをコピーしました