【応用編】GAS(Google Apps Script)でスプレッドシートAからBに文字を転記できるようになるまで【二次元配列】

3回目、最終回となる今回は【応用編】として、ちょっと手間のかかる方法を紹介します。

手間がかかりますが、便利です。何百行もあるスプレッドシートを短時間で処理できるようになります。【基礎編】で学んだ方法も、スキルアップするにつれて「最大処理時間6分」の壁に突き当たるはず。そんな時にこの【応用編】もぜひ、参考にしてみてください。

メンドクセェ！っていう人はココナラで注文してください。GASで検索すれば3,000円〜5,000円で作ってくれるフリーランスの人たちがたくさん出てきます。

2次元配列を使ったGASコードの解説

では、さっそくコードの紹介から。

やりたいことは前回の【基礎編】と同じ。スプレッドシートAの文字列から、文字列”D”だけを選んで、スプレッドシートBに転記することです。

変わるのはそのやり方。この【応用編】と、【基礎編】のコードを見比べてみれば、それぞれのやり方の違いがよくわかるはずです。

//【スプレッドシートBのプロジェクト】
//getDataRange().getValues()で2次元配列化して、スプレッドシートAからスプレッドシートBに転記する場合

function array_Function() {

  //スプレッドシートAのシート1を取得
  const spreadsheetA = SpreadsheetApp.openById('※※スプレッドシートAのID※※')
  const sheet1ofA = spreadsheetA.getSheetByName('シート1'); 
  const last_row_A = sheet1ofA.getLastRow()

  //スプレッドシートAを2次元配列として取得
  const data  = sheet1ofA.getDataRange().getValues();

  //スプレッドシートBのシート1を取得
  const spreadsheetB = SpreadsheetApp.getActiveSpreadsheet(); 
  const sheet1ofB = spreadsheetB.getSheetByName('シート1'); 

  //配列を設定
  let array = [];

  for(let i = 0;i < last_row_A; i++){//行配列の番号(初期値は「0」)
    let alphabet = data[i][0];
    if(alphabet.match('D')){
      array.push([alphabet]); //条件Dにmatchする情報を配列に追加     
    }
  }
  //スプレッドシートBにsetValues()で配列をまとめて追加
  sheet1ofB.getRange(1,1,array.length).setValues(array)
    
}

まずは、スプレッドシートAのシート1を取得していきます。このへんの流れはほとんど【基礎編】でやったことと一緒です。違うのは最後の1行だけ。

  //スプレッドシートAのシート1を取得
  const spreadsheetA = SpreadsheetApp.openById('※※スプレッドシートAのID※※')
  const sheet1ofA = spreadsheetA.getSheetByName('シート1'); 
  const last_row_A = sheet1ofA.getLastRow()

  //スプレッドシートAを2次元配列として取得
  const data  = sheet1ofA.getDataRange().getValues();

コード内のコメントにも書いてありますが、最後の1行で、getDataRange().getValues()というメソッドを使って、スプレッドシートAのシート1を「2次元配列」で取得しています。dataという名前で。

では、2次元配列とは何か。そもそも配列とは。

//1次元配列
let array = ["test1", "test2", "test3"];

//2次元配列
let array = [["test1-1","test1-2"],["test2-1","test2-2"],["test3-1","test3-2"]];

こんな形で、[ ]のカッコの中に文字や数字が入ったデータ型のことを配列といいます。この[ ]の囲みがひとつだけなら、1次元配列。[[ ],[ ]]こんな感じで2重の入れ子構造になっていたら、2次元配列です。

では、「スプレッドシートを2次元配列で取得する」とは、どういうことでしょうか。

上の画像のようなシートを、2次元配列で取得してみると、以下のようなかたちになります。

[["A",,,,,,,……],
["B",,,,,,,……],
["C",,,,,,,……],
["D",,,,,,,……],
["E",,,,,,,……],
["D",,,,,,,……],
["C",,,,,,,……],
["A",,,,,,,……]]

お分かりいただけたでしょうか…入れ子になった[[ ]]の中に、シートの1行1行が、格納されているのです。「,」で区切られた部分が、セルごとの区切りです。各行のA列に文字が入っている状態、というのは、2次元配列では各[[ ]]内の先頭に文字が入っている状態、と解釈することができます。

次の行ではもうひとつ、空の配列を設定しています。

  //配列を設定
  let array = [];

今からやることを説明します。2次元配列として取得したスプレッドシートAのシート1から、たった今設定した配列arrayに、中身(文字)を移していきます。そしてそのarrayの中身をスプレッドシートBのシート1に転記する、という流れです。

それではfor文で、配列の中身をチェックしていきます。

  for(let i = 0;i < last_row_A; i++){//行配列の番号(初期値は「0」)
    let alphabet = data[i][0];
    if(alphabet.match('D')){
      array.push([alphabet]); //条件Dにmatchする情報を配列に追加     
    }
  }

セルをひとつずつ取得→転記していた【基礎編】の内容とは、細かい、けれど重要な違いがあるのでよく見比べてみてください。

//【基礎編】
 for(let i = 1;i <= last_row_A; i++){//行番号(初期値は「1」)

//【応用編】
for(let i = 0;i < last_row_A; i++){//行配列の番号(初期値は「0」)

【基礎編】では初期値が「1」でしたが、【応用編】では初期値が「0」です。これは【基礎編】でセルを取得する時、シートの行数が「1」から始まっていたのに対して、【応用編】の場合配列は「0」からスタートするため。最終値last_row_Aも、【基礎編】では「<=」の不等号ですが、【応用編】では「<」。1つずれる仕組みになっているんですね。

次はfor文の中身です。

  let alphabet = data[i][0];

2次元配列dataに、[i][0]という記号がついています。これは配列のインデックスといって、配列のどの部分かを指定して取り出すための記号です。最初の[i]が2次元配列の[[“A”,,],[“B”,,],[“C”,,]]など[ ]内の要素をfor文で順番に指定して、さらに後の[0]の部分で[“A”,,]内部の順番(ここではいちばん先頭[0]の要素)を指定します。

【基礎編】ではスプレッドシートAのシート1の、A列をいちばん上の行から順番に読み込んでいました。それがこの【応用編】で2次元配列の内部を検索しようとすると、こんな風になるわけです。

if(alphabet.match('D')){
    array.push([alphabet]); //条件Dにmatchする情報を配列に追加     
 }

2次元配列dataの中身をひとつずつ、変数alphabetに格納すると、今度はその中に文字列’D’が含まれているかどうかを「alphabet.match(‘D’)」というメソッドでチェックします。そして該当した場合は、別の配列arrayに追加pushしていきます。これで配列arrayには’D’だけが追加されていきます。

  //スプレッドシートBにsetValues()で配列をまとめて追加
  sheet1ofB.getRange(1,1,array.length).setValues(array)

そして最後、for文のループを抜けたタイミングで、配列arrayの内容を、スプレッドシートBのシート1に転記します。この時、getRangeで転記する範囲を指定するわけですが、この範囲は配列の長さによってどんどん変わります。だから範囲を指定する時は、配列の長さ(array.length)としています。

ちなみに今回は、1列だけの配列をgetRangeしてsetValuesしていました。これが2列以上の配列だと以下のようになります。

|------|
|------|
|------|
|------|

//1列だけの配列→引数が3つ
getRange(始まりの行の数, 始まりの列の数, +何行)
↓
(例)
getRange(1,1,array.length)

|------|------|
|------|------|
|------|------|
|------|------|

//2列以上の配列→引数が4つ
getRange(始まりの行の数, 始まりの列の数, +何行,+何列)
↓
(例)
getRange(1,1,array.length,array[0].length)

おわかりでしょうか。…わかりませんね。この部分は、実際にやってみながら調整してください。

進めているといろんなエラーに突き当たると思います。それをいろいろと試しながら処理しているうちに学んでいくものと思うので。

こういう学びも昔は①教えてくれる人を探す ②自分でぐぐって何とかする、の2択しかありませんでしたが、今は幸い③chatGPTに聞いてみる、という選択肢まで出てきました。普段スプレッドシートを使っている方なら、身に付けると時短できることは間違いない、便利なテクニックかと思いますので、ぜひとも挑戦してみてください。