見出し画像

GASでスプレッドシートを操作する時、R1C1形式(行番号,列番号)と、2次元配列[行配列][列配列]のあいだに生じる「1」のズレを確認するだけのサイトを作りました。

sooz

「社内でちょっとGASが触れる便利な人」として、ひたすらスプレッドシートを転記する仕組みを作っています。シートAからシートBへ…シートBからシートCへ…以下果てしなく続く。


スプレッドシートAから文字列DのみピックアップしてスプレッドシートBに転記する

例えばシートAからシートBへ転記する時、慣れないうちはgetRange()をfor文で回したりしながら、ひとつずつセルの値をgetValue()して、別のシートにsetValue()していました。

//【スプレッドシートBのプロジェクト】
//getRange(行番号,列番号)で、スプレッドシートAからスプレッドシートBに転記する場合


function R1C1_Function() {

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

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

  for(let i = 1;i <= last_row_A; i++){//行番号(初期値は「1」)
    let alphabet = sheet1ofA.getRange(i,1).getValue();  //スプレッドシートAの1列目を取得
    if(alphabet == "D"){ //Dだけを探して
      let last_row_B = sheet1ofB.getLastRow(); //スプレッドシートBの最終行に
      sheet1ofB.getRange(last_row_B + 1,1).setValue(alphabet);  //転記する
    }
  }    
}

GASについて勉強するうちにgetDataRange().getValues()でシートをまるごと2次元配列で取得、必要な配列だけ取り出しsetValues()する、という芸当も、できるようになりました。

//【スプレッドシート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)
    
}

おかげで処理は格段に軽く、早くなりました。

しかし思わぬつまずきもありました。それはgetRange(行番号,列番号)の引数である行番号,列番号がそれぞれ1から始まるのに対して、取得した2次元配列をインデックス(data[行配列][列配列])で取得する時はそれぞれ0から始まることです。行も列も、たった「1」ズレるだけなのですが、これが意外につまずきました。

思えば、配列で取得したシートAの内容を、シートBの行番号,列番号をgetRange()で取得してsetValues()していくわけですから、常にこの「1」のズレとは付き合って行かなくてはならない。単純なうちはまだいいのですが、表自体が複雑になってきて、転記元のシートを数行ずつ、数列ずつ間隔をあけて取得したり、転記先のシートも数行ずつ、数列ずつ間隔をあけてsetValues()したりしていると、こんがらがってきます。目と頭と心が疲労して何度見ても間違いに気づきません。

疲労が限界に達した結果、こんなサイトを作ってしまいました。


行番号・列番号(R1C1)-2次元配列-A1 変換コンバーター

「1」のズレを確かめるサイトです。

ここでは、おなじセル番地を3種類の形式であらわしています。

・R1C1形式…getRange()の引数など行番号,列番号の形式
・2次元配列…getDataRange().getValues()など配列で取得した場合のインデックス
・A1形式…数字(行)とアルファベット(列)の組み合わせ

こんな機能も作りました。


2次元配列で行をfor文で回した時のセルの動き
//スプレッドシートを2次元配列で取得し、行をfor文で回した時

for(let i = "初期値";i < last_row; i = i + "増加値"){
  data[i]["列配列"];
}

・初期値
・増加値
・列配列(配列の列番号)

フォームに3つの値を入力すると、2次元配列で行をfor文で回した時のセルの動きを確認できます。

きてますね。疲れていますね。我が事ながらそう思います。

疲れているのが私だけでなければいいのですが。

そして、このサイトがどなたかのお役に立てばいいのですが。

この記事が気に入ったらサポートをしてみませんか？