見出し画像

【GAS】Google Apps Script 活用事例　列の増減に強いsetValuesの書き方

nepia_infinity

ウチの会社の基幹システムは、あくまで稼働可能なスタッフとお客様をマッチングさせるもので、人事データベースではない。という厳然たる事実があります。そこで、人事データベースがわりとなるものをspreadsheetで作っています。

しかし、関数やプログラムを全く理解していない人がデータベースを作ると、IDが左端にないため、VLOOKUPで必要なデータが拾えない。セルの結合によって、nullが返ってきてしまう、おまけに多くの人にとって不要な情報が多過ぎて、見辛いという問題が起こります。

僕が上のポジションだったら、間違いなく、「この見辛く不要な情報が多いDBをなんとかせい。」と言うでしょうね。

2020.06.06 データベースの正規化について、学ぶと得られるものが多いよとアドバイスを受けました。1冊くらい読んでみようかな。

必殺ッ！！QUERY関数

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/＊＊＊＊＊＊＊＊＊＊＊/edit#gid=＊＊＊＊＊＊＊＊＊＊＊","DB!A:BF"),"SELECT Col8,Col9,Col13 WHERE NOT Col13 CONTAINS '退職' AND Col8 IS NOT NULL")

QUERY関数、spreadsheetにしかないオリジナルの関数です。IMPORTRANGE関数を入れ子にする事が出来て、SELECT Col2,Col5 のように書けば、必要な列だけを抽出することが出来ます。※ColはColumn　列の事です。

1行で書けて、他のシートから情報を読み取れるので、凄まじく便利な関数です。ただし、列の増減に弱いのです。上記の例題コードには、Col13があります。13列目が謝って削除されたり、中間列のどこかしらが削除された途端に、エラーになったり、意図しない列のデータが読み込まれる危険性が高まります。

いつかは、変更に強い（列の増減に強い）仕組みをGASで書けないかなと思っていたのです。

Google Cloudのブログに答えがあった。

先日、読んだGoogle Cloudのサンプルコードをザァーと眺めていて、「おっ」と思うものがあって、それを参考に仕上げました。全部が完璧に理解できる訳ではないのですが・・・ちょいちょい確認したいと思います。

実は、Google Cloud Natural Language APIと連携して、クレーム内容を自然言語解析を掛けたらどうなるだろうか？と思っています。demoを設定なしで使う事が出来ます。ぜひやってみてください。

列の増減に強いsetValuesの書き方

function readDB() {
 const url         = '＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊';//spreadsheetのURL
 const dataSheet   = SpreadsheetApp.openByUrl(url).getSheetByName('DB');
 const rows        = dataSheet.getDataRange();
 const numRows     = rows.getNumRows();
 const values      = rows.getValues();
 const headerRow   = values[0];
 
 console.log(headerRow);
 
 const COLUMN_NAME ={
   ID: 'ID',
   NAME: 'カタカナ（姓・名・ミドル）',
   SECTION: '配属',
   FLIGHT:'便',
   SHAREHOUSE:'入居先',
   BIRTH:'生年月日',
   ENTRY:'入国日',
   YEAR:'経過年数',
   RETIRE:'退職'
 }
 
 const idColumnIdx          = headerRow.indexOf(COLUMN_NAME.ID);
 const nameColumnIdx        = headerRow.indexOf(COLUMN_NAME.NAME);
 const sectionColumnIdx     = headerRow.indexOf(COLUMN_NAME.SECTION);
 const flightColumnIdx      = headerRow.indexOf(COLUMN_NAME.FLIGHT);
 const shareHouseColumnIdx  = headerRow.indexOf(COLUMN_NAME.SHAREHOUSE);
 const birthDateIdx         = headerRow.indexOf(COLUMN_NAME.BIRTH);
 const entryDateIdx         = headerRow.indexOf(COLUMN_NAME.ENTRY);
 const yearIdx              = headerRow.indexOf(COLUMN_NAME.YEAR);
 const retireDateIdx        = headerRow.indexOf(COLUMN_NAME.RETIRE);
 
//  console.log('ID values[i][%s] カタカナ（姓・名・ミドル） values[i][%s]	配属 values[i][%s] 便 values[i][%s] 入居先 values[i][%s] 生年月日 values[i][%s] 入国日 values[i][%s] 経過年数 values[i][%s]',idColumnIdx,nameColumnIdx,sectionColumnIdx,flightColumnIdx,shareHouseColumnIdx,birthDateIdx,entryDateIdx,yearIdx);
 
 let newValues = [];
 
 for(let i = 0; i < values.length; i++){
   if(values[i][idColumnIdx] === '' || values[i][retireDateIdx]){continue}
   
   newValues.push([
   values[i][idColumnIdx],
   values[i][nameColumnIdx],
   values[i][sectionColumnIdx],
   values[i][flightColumnIdx],
   values[i][shareHouseColumnIdx],
   values[i][birthDateIdx],
   values[i][entryDateIdx],
   values[i][yearIdx]
   ])；//push
 }//for
 
 console.log(newValues);
 
 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 const sheet       = spreadsheet.getSheetByName('readDB');

 /*シートが存在しない場合、作成する。*/
 if (sheet == null) {
   spreadsheet.insertSheet('readDB');
   const sheet       = spreadsheet.getSheetByName('readDB');
 }
 
 /*範囲に値があれば、消してから貼り付けをする。*/
 const range       = sheet.getRange(2, 1, newValues.length, newValues[0].length);
 if(range.isBlank() == false){
   range.clearContent();
 }
 
 range.setValues(newValues);
}//end

見出し行をindexOfで検索して、その位置を返す。そして、values[i][?]に代入してあげているのが、ポイントです。

スクリーンショット 2020-03-14 10.15.10

const headerRow   = values[0];//見出し行

//欲しい列の見出しリスト
var COLUMN_NAME   ={
   ID: 'ID',
   NAME: 'カタカナ（姓・名・ミドル）',
    ・
    ・
    ・
}

//一次元配列の中から、欲しい列の見出しがどこにあるかを検索
var idColumnIdx   = headerRow.indexOf(COLUMN_NAME.ID);


//代入
newValues.push([
   values[i][idColumnIdx],
    ・
    ・
    ・
])；

indexOfの使い方を過去記事で書いています。

GASのそのほかの効率化や、業務改善事例

そのほかの参考URL


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