Google Apps Scriptで列の増減に対応する読み書き【GAS】

現場の事務員の人ってルールを守らず己のルールでスプレッドシートを作る傾向があります。そしてそのデータ構造はプログラムで扱うには問題がおおありの構造ってだけでなく、勝手に列を追加したり、ID列を左端に配置してるのに移動させてしまったり。。こういう問題のある行動が目立ちます。

さて、そんなデータ構造であった場合、Google Apps Scriptで配列でデータを取り他のシートへ加工して転記する場合、確実にデータが壊れます。そこで、列の増減や列の位置を移動に対応できる構造にして備えておくことも必要になります。

1 今回使用するスプレッドシート
2 コードとJSON出力結果
- 2.1 ソースコード
- 2.2 backjsonの出力結果
3 関連リンク

今回使用するスプレッドシート

列の増減に対応するスプレッドシート

Query関数を使って列の入れ替えをしてからデータを取得する方法も有効です。このシートの使い方は、メニューにある「取り出す」⇒「列増減対応抽出」を実行すると、増減シートから貼り付け先に指定の列のデータだけ、またID列を左端として抽出します。

コードとJSON出力結果

ソースコード

//シートID
var sheetid = "ここにスプレッドシートのIDを入れる";

//JSONデータから書き込み用データを作ってはりつける
function copysheet(){
  //JSONデータで取得する
  let json = backjson();
  let length = json.length;
  
  //書き込み用配列を用意する
  let array = [];
  
  //配列にデータをpushする
  for(let i = 0;i<length;i++){
    //一時配列を用意
    let tempArray = [];
    
    //特定の列だけを順番に取り出す
    tempArray.push(json[i]["ID"]);
    tempArray.push(json[i]["販売先コード"]);
    tempArray.push(json[i]["商品名"]);
    tempArray.push(json[i]["売上個数"]);
    
    //書き込み用配列にpush
    array.push(tempArray);
  
  }

  //一括で貼り付ける
  let ss = SpreadsheetApp.openById(sheetid).getSheetByName("貼り付け先");
  var lastColumn = array[0].length;　　//カラムの数を取得する
  var lastRow = array.length;  　　　　//行の数を取得する
  ss.getRange(2,1,lastRow,lastColumn).setValues(array);  

}

//JSONでデータを返す関数
function backjson() {
  //スプレッドシートデータを取得する
  let sheet = SpreadsheetApp.openById(sheetid).getSheetByName("増減シート");
  let ss = sheet.getDataRange().getValues();
  
  //タイトル行を取得する
  let title = ss.splice(0, 1)[0];
  
  //JSONデータを生成する
  return ss.map(function(row) {
    let json = {}
    row.map(function(item, index) {
      json[title[index]] = item;
    });
    return json;
  });
 
}

backjson関数でスプレッドシートデータをタイトル行を含めてJSON化する。ここがポイントです。
copysheet関数でjson化されたデータから欲しい列だけを取り出して、配列に組み直す
最後に一括でシートに書き込みする

backjsonの出力結果

backjson関数がスプレッドシートのデータをどのようにJSON化して出力するか？console.logで出してみるとこんな感じで出力されます。配列で位置を指定してやり取りをするわけじゃないので、列名を変更するようなことをやられなければ、列の位置が変わろうが、間に新しい列が増えようが対応可能です。

[ 
  { '販売先コード': 101,
    '支店名': '渋谷支店',
    '日付': Thu Jan 01 2015 00:00:00 GMT+0900 (日本標準時),
    ID: 1,
    '商品名': 'ハエトリソウ',
    '売上個数': 2 },
  { '販売先コード': 101,
    '支店名': '新宿支店',
    '日付': Fri Jan 02 2015 00:00:00 GMT+0900 (日本標準時),
    ID: 2,
    '商品名': 'ハエトリソウ',
    '売上個数': 4 },
  { '販売先コード': 101,
    '支店名': '池袋支店',
    '日付': Sat Jan 03 2015 00:00:00 GMT+0900 (日本標準時),
    ID: 3,
    '商品名': 'ムシトリスミレ',
    '売上個数': 3 },
  { '販売先コード': 101,
    '支店名': '新宿支店',
    '日付': Sun Jan 04 2015 00:00:00 GMT+0900 (日本標準時),
    ID: 4,
    '商品名': 'ハエトリソウ',
    '売上個数': 5 },
  { '販売先コード': 101,
    '支店名': '池袋支店',
    '日付': Mon Jan 05 2015 00:00:00 GMT+0900 (日本標準時),
    ID: 5,
    '商品名': 'ハエトリソウ',
    '売上個数': 7 }
]