【GAS】Google Apps Script 活用事例　非構造化データの表から、情報を取得・集約するスクリプト

データ処理しやすい表と、そうでない表がある

1つのシートの上下や横に表が存在すると、スプレッドシートの効率性が下がります。セルの結合も然り.....。ただ多いんですよねぇ......。明らかな悪癖なのに、あたかもこれがスプレッドシートの使い方の常識みたいな感じで、はびこっています。本日は、自分の職場での奮闘ぶりと苦肉の策についてご紹介したいと思います。

元となるシートはこんな感じ

それぞれの日数ごとに15人ずつ登録されています。1行目はセルの結合がされています。閲覧するだけなら、これでも困らないのですが、データ処理という観点からすると、......めちゃくちゃ扱いづらい。今回は、説明会開催期日ごとに名前を取得して、新たな2次元配列を作成します。

この表は、上下に表が分かれているのが、なかなかクセありで苦労しました。そんな時に役立ったのが、こちら

Class TextFinder  |  Apps Script  |  Google Developers

sheet.createTextFinder('No').useRegularExpression(true);

シートから No を含むセルを取得できます。今回のスクリプトはこちらを上手く活用して、名前をピックアアップして2次元配列を作成します。

・　spreadsheet（シート全体）
・　sheet（特定シート）
・　range（特定の範囲）

こういう表を作らせないに限るんですが、立場が弱いとねぇ.....。

//[['名前A', '2021/10/13'],['名前B', '2021/10/13']]

ソースコードはこんな感じ

/**
* 横に広がった表から、2次元配列を作り直すスクリプト
* 
*/

function myFunction() {

 //シート全体からNoという単語を探す　その1行上が、取得したい行
 //createTextFinderは、spreadsheet(全てのシート), sheet(特定のシート), range(特定の範囲)
 //対象範囲を限定することができる。

 const sheet   = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シート1');
 const finder  = sheet.createTextFinder('No').useRegularExpression(true);
 const results = finder.findAll();
 console.log(results);

 let newValues = [];

 for(const result of results){

   //ex: A25 → 25のように文字列から数値に変換
   const cell      = result.getA1Notation().replace(/[A-Z]/,'');
   const headerRow = Number(cell) - 1;

   //console.log(`行：　${cell}`);  //2
   console.log('型： ', typeof headerRow, headerRow); //1

   //説明会期日
   const headerRange = sheet.getRange(headerRow, 1, 1, sheet.getLastColumn());
   const header      = headerRange.getDisplayValues().flat();
   const columns     = header.map((value, index) => index +=1);

   console.log(`範囲：${headerRange.getA1Notation()}`);
   console.log(header);
   console.log(`列：${columns}`);
   //console.log(`配列の長さ：${header.length}`);

   //説明会期日
   for(let i = 0; i < header.length; i++){
     if(header[i] !== ''){
       const date   = header[i];
       const column = columns[i];
       const row    = Number(cell) + 1;

       console.log(`説明会期日：${date}`);
       console.log(`行：${row}, 列：${column}`);

       const nameArray = sheet.getRange(row, column, 15, 1).getValues().flat();
       console.log(nameArray);
       nameArray.map(value => newValues.push([value, date]));
     }
   }
 }
 console.log(newValues);
 return newValues
}

実行時のログ

やめろやーやめろや

流石にやりにくいので、シートを作り直したいとお願いしました。作り直した表を元に実現した自動化がこちらです。