【GAS】Google Apps Script 活用事例 列の増減に強いsetValuesの書き方
ウチの会社の基幹システムは、あくまで稼働可能なスタッフとお客様をマッチングさせるもので、人事データベースではない。という厳然たる事実があります。そこで、人事データベースがわりとなるものを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][?]に代入してあげているのが、ポイントです。
const headerRow = values[0];//見出し行
//欲しい列の見出しリスト
var COLUMN_NAME ={
ID: 'ID',
NAME: 'カタカナ（姓・名・ミドル）',
・
・
・
}
//一次元配列の中から、欲しい列の見出しがどこにあるかを検索
var idColumnIdx = headerRow.indexOf(COLUMN_NAME.ID);
//代入
newValues.push([
values[i][idColumnIdx],
・
・
・
])；indexOfの使い方を過去記事で書いています。