【GAS】Google Apps Script 活用事例　たまには失敗する事もある....理解して使おう、indexOf

自分が理解していないスクリプトを使うと痛い目にあう

前回の記事で、紹介した、TSUJI KENZO　さんのスクリプトを流用しようとした所、上手くいかずハマってしまったので、その原因と対策をメモ。indexOfは苦手意識があったので、今回ハマった事から学んで、より使いこなせるように頑張りたいと思います。

上手くいかなかった時のスクリプト

//pkにIDが空白以外だったら追加して、1次元配列を作成する
var pk = [];
for(var i=1; i < values.length; i++){
  pk.push(values[i][0]);
}

//pkをprimary keyとしてシート[VLOOKUP:参照]のフィールド[氏名]を参照してnewValuesに格納してsetValuesする
var rfSheet  = spreadsheet.getSheetByName('VLOOKUP:参照');
var rfValues = rfSheet.getDataRange().getValues();

var newValues = [];

for(var i=0; i<pk.length; i++){
  for(var j=1; j< rfValues.length; j++){
    if(rfValues[j][0].indexOf(pk[i]) !== -1 ){
      newValues.push([rfValues[j][1]]);
    }
  }
}
sheet.getRange(3,2,newValues.length, newValues[0].length).setValues(newValues);

return

この記事の初稿は、2020年、GASを始めて間もない頃でした。indexOfが文字列か1次元配列の検索でしか使えないことを理解しないで使っていました。
そこで数値で検索しようとしたところ、下記のようなエラーが出たという事のようです。

また、他人が書いたというコードを理解不十分のまま、使っていたという点がエラーの原因でした。読み返すとpkが1次元配列かどうか分かりにくいので、keysとかidArrayにするかな・・・

TypeError: オブジェクト 59183 で関数 indexOf が見つかりません。

pk は           1次元配列
rfValues　  2次元配列
rfValues[j]  1次元配列
rfValues[j][0] 数値

const number = 59183; //rfValues[j][0]
console.log(number.indexOf(59183));

エラーを至極簡単に再現すると、上記のような感じになっていたのではないかなと思う。ちゃんとログを見て確認しなきゃね。

解決方法：一次元配列（行全体）から特定の値を含んだセルの検索を行う

for(var i = 0; i < pk.length; i++){
  for(var j = 1; j < rfValues.length; j++){
    if( rfValues[j].indexOf(pk[i]) !== -1 ){
      newValues.push([rfValues[j][1]]);
    }
  }
}

rfValues[j][0]　だとA2とかA35とか特定のセルを検索している状態。しかし、そこに入っている値が数値データの時に検索が出来ません。

rfValues[j]　と書くと1行目、2行目みたいな感じで一次元配列全体から特定の値があるかどうかを検索するスクリプトになるようです。....難しい。VLOOKUP関数でも表示形式が文字列か数値のどちらかに一致していないとエラーが出てしまうのと、同じ感覚かもしれません。

いや、そもそも値の検索ならindexOf要らないよ。なんで使ってるの？

  for( var i = 0; i < idArray.length; i++){
   for( var j = 1; j < rfValues.length; j++){

       if(rfValues[j][0] === idArray[i]){
       newValues.push(rfValues[j][2])
     }
   }//for_j
 }//for_i

普通に　===　って書けばOKだったようです。結果だけ聞くと、「あぁ、そんな事」って感じなのですが、見聞きした事が自身の体験や気づきと結び付いて知識に変わるまでは結構時間が掛かるのかなと思います。

リライト済み　スクリプト

/**
* スタッフIDから、名前を求めるスクリプト
* 
*/

function vlookup() {

 //formsで送信された最新情報が、最終行に追加される。3列目のスタッフIDを取得する
 const spreadsheet  = SpreadsheetApp.getActiveSpreadsheet();
 const formsRawData = spreadsheet.getSheetByName('Forms');
 const staffId      = formsRawData.getRange(formsRawData.getLastRow(), 3).getDisplayValue();

 //従業員情報が記載されているシート
 const dbSheet = spreadsheet.getSheetByName('DB');
 const values  = dbSheet.getDataRange().getDisplayValues();

 //IDが1列目にある場合　2次元配列に格納されている各1次元配列の0番目の要素が空白以外だったら、配列に入れる
 const idArray = values.map(record => record[0]).filter(value => value);
 const index   = idArray.indexOf(staffId);
 const array   = values[index];
 const name    = values[index][1];

 //ID, 名前, 所属部署
 //['12345', 'へのへのもへじ', '営業部1課']
 console.log(`スタッフID:　${staffId}, 名前:　${name}`);
 console.log(array);

 return name;
}

Formsの回答、最終行からスタッフIDを拾って、それをDBシートで参照して名前なり必要なデータを取得するスクリプトです。

 Array.prototype.concat.apply([], values);

リライト前のスクリプトでは、V8ではなかったので、一次元配列化に変換するために、こんな面倒くさいことやっていました。

ハマった時は、型を調べよう。

console.log(typeof value[i][0]); 

構造が同じ表から任意の列を特定する

2021/11/02　追記
このような実質2枚の表がセットになったような表から列を特定したいと思うことがありました。lastIndexOfを使って簡単に解決出来たので、備忘録として、こちらに転記しておこうと思います。

function myFunction() {
 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 const sheet       = spreadsheet.getSheetByName('シート1');
 const header      = sheet.getDataRange().getValues()[0];

 console.log(header);

 let column = {
   name:       header.lastIndexOf('学生名'),
   university: header.lastIndexOf('大学'),
   section:    header.lastIndexOf('学部')
 }

 //indexOf expected output: { name: 0, university: 1, section: 2 }
 //lastIndexOf expected output: { name: 3, university: 4, section: 5 }
 
 console.log(column);
}