【GAS】Google Apps Script 活用事例　スプレッドシートに記載されたZoomの情報をメール本文に差し込みするスクリプト

1日で内定が出る1DAY選考会なるものが、この月末にありまして.....今週は、その書類選考の合格通知などの作成がキツかった....。

前回も、Zoomの情報をHRMOSのメールエディタにコピペで書き写して、スプレッドシートを穴が開くほど血眼になって、ちゃんと正しい情報が転記されているかチェックし、非常に四苦八苦したのですが、喉元過ぎれば熱さを忘れるというか.....1ヶ月に1回しかないし、自動化しなくてもいいかと思って、自動化しませんでした。

そのツケが今週、倍になって返ってきました。あまりにも神経をすり減らす非生産的な作業なので、「アカン、これは次回までに自動化せねば.....」と思いたち、書きました。

重複の削除については、こちらを参考にしました。

JavaScriptで重複排除を自分で実装してはいけない（Setを使う） - Qiita

2020/12/29　追記

コメントで、passCordではなく、passCodeのスペルミスでは？
Bacalhauさんからご指摘を頂いたため、該当箇所を修正しました。ありがとうございます。noteってコメントが分かりづらい位置にあるので、書いてくれる方が少ないのですが、嬉しいです。ありがとうございます！！

もし、まだ、passCordが残っていたら、温かく、しゃーねぇーな。と見守っていただけると幸いです。

スクリプトはこちら.....長ェ.....

/*
*　Gmailの下書きに、1DAY選考のZoom URLなどをご案内する。
*/

function createDraft2() {
const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet       = spreadSheet.getSheetByName('DB');
const values      = sheet.getDataRange().getValues();

//重複を省いて、候補者だけの配列を作成
const nameArray    = generateArray_(values, 2);
const newNameArray = Array.from(new Set(nameArray));

//見出し行の削除
newNameArray.shift();
//console.log(newNameArray);

//選考当日のZoom URLをスプレッドシートから取得して、差し込みする。
confirmedMailDraft_(values, newNameArray, sheet);
}

function confirmedMailDraft_(values, nameArray, sheet){

//Google Documentのテンプレート
const url      = 'https://docs.google.com/document/d/***************/edit';
const document = DocumentApp.openByUrl(url);
const body     = document.getBody();
const text     = body.getText();

//console.log(text);

//シートの見出し行　列の挿入対策
const headerRow         = values[0];
const typeIndex         = headerRow.indexOf('タイプ');
const nameIndex         = headerRow.indexOf('応募者名');
const dateIndex         = headerRow.indexOf('日付');
const startTimeIndex    = headerRow.indexOf('開始時刻');
const endTimeIndex      = headerRow.indexOf('終了時刻');
const zoomUrlIndex      = headerRow.indexOf('Zoom URL（自動）');

//getValues()だと、ゼロ落ちするため、getDisplayValue()で取得する
const zoomMeetingIndex  = headerRow.indexOf('ミーティングID（自動）') + 1;
const zoomPassCodeIndex = headerRow.indexOf('パスコード（自動）') + 1;


//1DAY選考の実施日は、一律なので、for文内で定義しない。
const day        = stringDayOfTheWeek_(values[1][dateIndex]);
const startDate  = createStringDate_(values[1][dateIndex], 0, 'yyyy年MM月dd日');
const targetDate = startDate + ' (' + day + ')';

console.log(targetDate);


//nameArrayの要素数は応募者の重複を省いた人数、メールの下書きを作成する数
for(let i = 0; i < nameArray.length; i++){
  
  //全角スペースから半角スペースへ変換
  const person   = nameArray[i].replace('　',' '); 
  const lastName = person.slice(0, 2);
  
  //初期化
  let startTime   = '';
  let endTime     = '';
  
  //接続確認の時間と、1次選考の時間
  let stringTime  = '';
  let stringTime2 = '';
  
  //Zoom URL
  let zoomUrl1       = '';
  let zoomUrl2       = '';
  
  //Zoom Meeting ID
  let zoomMeetingId1 = '';
  let zoomMeetingId2 = '';
  
  //Zoom passCode
  let zoomPassCode1  = '';
  let zoomPassCode2  = '';
  
  
  //シートの内容
  for(let j = 1; j < values.length; j++){
    
    let row = j + 1;
    if(values[j][nameIndex] === nameArray[i]){
      
      //接続確認時のそれぞれの開始時刻
      if(values[j][typeIndex].includes('接続確認') === true){
        startTime  = createStringDate_(values[j][startTimeIndex], 0, 'HH:mm');
        endTime    = createStringDate_(values[j][endTimeIndex], 0, 'HH:mm');
        
        //選考当日の動作確認用のZoom URL
        zoomUrl1             = values[j][zoomUrlIndex];
        const meetingIdRange = sheet.getRange(row, zoomMeetingIndex);
        zoomMeetingId1       = meetingIdRange.getDisplayValue();
        
        const passCodeRange  = sheet.getRange(row, zoomPassCodeIndex);
        zoomPassCode1        = passCodeRange.getDisplayValue();
        
        
        //10:30 ～ 11:20
        stringTime = startTime + ' ～ ' + endTime;
        
        console.log(meetingIdRange.getA1Notation(), zoomMeetingId1);
        console.log(stringTime);
      }else if(values[j][typeIndex].includes('1次選考') === true){
        startTime  = createStringDate_(values[j][startTimeIndex], 0, 'HH:mm');
        
        //面接時間には、面接官の評価記入のための時間が含まれているので、15分引く。
        const fifteenMinutesAgo = values[j][endTimeIndex];
        fifteenMinutesAgo.setMinutes(fifteenMinutesAgo.getMinutes() -15);
        endTime = createStringDate_(fifteenMinutesAgo, 0, 'HH:mm');
        
        
        //選考本番用のZoom URL
        zoomUrl2              = values[j][zoomUrlIndex];
        const meetingIdRange2 = sheet.getRange(row, zoomMeetingIndex);
        zoomMeetingId2        = meetingIdRange2.getDisplayValue();
        
        const passCodeRange2  = sheet.getRange(row, zoomPassCodeIndex);
        zoomPassCode2         = passCodeRange2.getDisplayValue();
        
        //10:30 ～ 11:20
        stringTime2 = startTime + ' ～ ' + endTime;
        
        console.log(meetingIdRange2.getA1Notation(), zoomMeetingId2);
        console.log(stringTime2);
      }//else if
    }//if
  }//for_j
  
  const mailAddress = 'sample@co.jp';
  const mailTitle   = '面接日程についてのご連絡';
  
  //1が接続確認　2が1次選考
  //Google Documentから取得したメール本文を、変数で置き換える
  const mailBody    = text
  .replace('{name}', person)
  .replace('{date1}', targetDate + ' ' + stringTime)
  .replace('{date2}', targetDate + ' ' + stringTime2)
  .replace('{Zoom URL1}', zoomUrl1)
  .replace('{Zoom URL2}', zoomUrl2)
  .replace('{passCode1}', zoomPassCode1)
  .replace('{passCode2}', zoomPassCode2)
  .replace('{Zoom Meeting ID1}', zoomMeetingId1)
  .replace('{Zoom Meeting ID2}', zoomMeetingId2);
  
  GmailApp.createDraft(mailAddress, mailTitle, mailBody);
  
}//for_i
}//end

//空白行をすべて省いた上で、1次元配列を生成する。
function generateArray(values, column){
 return values.map(record => record[column]).filter(value => value);
}

※シート構成上の都合

動作確認用ZoomのURL、Meeting ID、PassCode
選考本番で使用するZoomのURL、Meeting ID、PassCode

後述しますが、上記のZoomの情報ごとに、応募者情報があるため、まずはじめに重複を省く必要があります。

この長ったらしいスクリプトで、何をやっているか？

1.　特定のシートから応募者の重複を省いた一次元配列を作成
2.　メール本文のテンプレートを読み込む
3.　シートの見出し行の検索・取得する列の決定
4.　日付をyyyy年MM月dd日 (曜日)　になるように直す。
5.　時刻をHH:mm　になるように直す。
6.　シート上のZoomの情報を取得
7.　取得した情報をメール本文の特定箇所に差し込みしていく
8.　メールの下書きに人数分作成。

getValue()だと、数値型で取得した場合、ゼロ落ちする。

スクリプトの挙動確認時、Zoomのパスワードで、ゼロから始まるものが、数値型で取得されたがために、ゼロ落ちしていたという事がありました。そこで、数字の羅列である、Meeting IDと、PassCodeだけは、getDisplayValue()　文字列として取得するようにしています。

getRange()で取得するために、 +1をしています。配列は0から始まりますが、行と列は、それぞれ1から始まるためです。

headerRow.indexOf('ミーティングID（自動）') + 1;

HRMOSの問題点

・HRMOSでは、現状、2つの面談日程を自動挿入する事が出来ない。
・会社説明会日時と、選考当日とか、合否の出ないカジュアル面談と、選考本番などなど、2つの面接日程を入れたいケースって割とある印象。
・自動挿入は、yyyy/M/d　形式。
・2020年11月01日のように、表示形式を変更できない。

会社説明会ZoomのURL、Meeting ID、PassCode
動作確認用ZoomのURL、Meeting ID、PassCode
選考本番で使用するZoomのURL、Meeting ID、PassCode

をそれぞれ3つずつをご案内する必要があり、めちゃくちゃ大変.....。どうして、こんな面倒な運用しているんだよ。絶対非効率やん。しかも、それやるの俺。ぶっちゃけ、メンドいんだよ。「もう、Zoomじゃなくて、Google Meet使おうよ。カレンダーに登録しただけで終わるんだしさ。」......って喧嘩腰にリクエストしましたが、無理、却下。で一蹴されました。

一応、会社説明会は、1人1人、個別で対応するのは限界があるねって話になり、ビデオ撮影した説明会動画をご案内する方向で動いているようです。やった！！

動作確認用のZoomは非効率だなとは思うものの、ビデオ会議ツールに慣れない応募者もいるとは思います。それが合否に影響したら、流石に不公平なので、必要なのかもしれないなと思っています。

V8に準拠した書き方で、インデントがずれる？

console.log(`範囲　${range.getA1Notation()}`);

上記のような、V8に準拠した書き方で、インデントがずれてしまうようです。上のようなログの書き方や、アロー関数みたいのでも効かなくなります。

長いスクリプトの場合、インデントがきちんとしていないと読みにくいので、気をつけようと思います。GASのエディタが対応してくれる事が分かるまで、%sとか使ったほうがいいかもしれませんね。

結局.....見当違いでした。

GASのエディタは、｛｝の数が一定以上になると、インデントが効かなくなるのかもしれない…

130行くらいまではインデントが合ってる

— ⅡBASA (@nepia_infinity) October 30, 2020

GASって、100行超えるとインデント効かなくなるかも。

— ⅡBASA (@nepia_infinity) October 30, 2020

GASを書き始めて、1年。　成長した点

実は、過去にも同じような内容のスクリプトを書いています。

var shareHouse = values[k][4];//シェアハウス
var realEstate = values[k][6];//管理会社
var brokenPart = values[k][7];//不具合・内容

こんな感じで、書いているのですが、列の順序を変えたくなったり、列を挿入したくなったりする場面って、よくあります。これだと、列の順序を決め打ちしているので、挿入されるたびに直さなければいけません。

const headerRow = values[0];
const typeIndex = headerRow.indexOf('タイプ');

これは見出し行を、1次元配列として取得して、その中から特定の列がどこにあるかを探すスクリプトです。とても重宝しています。

他にも日付や時刻の扱い、sliceなど、1年前ではわからなかったであろうこともバリバリ使っています。

過去のスクリプト

今回、ご紹介したスクリプトは、これら上記3つのスクリプトを書いた際に得られた知見を元に書いています。