【GAS】Google Apps Script 活用事例　外国人スタッフの名前を姓と名で綺麗に分割して、ジョブカン登録用CSVデータを作成する方法

.......全角と半角スペースが入り混じってやがるッ！！クソッたれがァァーッ！！

ジョブカンに限らず勤怠管理システムには、CSVで登録する機能があります。その際、スプレッドシートで作成したデータを作成、CSVに変換という順番で作成する実務があります。

function separateSpace() {
 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 const sheet       = spreadsheet.getSheetByName('シート1');
 console.log(sheet.getName());

 
 /*シート上の半角スペースを全角スペースに変換
   textFinderは、Ctrl + H　で置換をするのと同じ感覚
　　For文を使わなくても良いのがポイント
*/


 const textFinder  = sheet.createTextFinder(' ');
 textFinder.replaceAllWith('　');
 
 let newArray      = [];
 
 const values = sheet.getDataRange().getValues();
 let columnB  = sheet.getRange('B:B').getValues();
 columnB.shift();
 
 
 for(let i = 0; i < columnB.length; i++){
   if(columnB[i][0] === ''){continue}
   
   let strings        = columnB[i].join(',');//文字列化
   const separateName = strings.split('　', 2);//文字列の全角スペースを区切りに、2つに分割する。
   
   newArray.push(separateName);
 }//for
 /*//処理するデータの中に、半角スペースと全角スペースが混戦していると失敗する。*/
 sheet.getRange(2, 3, newArray.length, newArray[0].length).setValues(newArray);
}

シート1のB列に書かれた姓名のデータを分割するスクリプトです。全角と半角が混在していると支障があるので、半角スペースを全角スペースに変換してしまいます。その上で分割します。やはり入力フォームにバリデーションをかけて、元データを綺麗にするのが一番いいかなと思います。spreadsheetに直入力というのは、あまり良くないな。

標準機能　データ　>　テキストを列に分割　がある。

元データが綺麗ならば、実は標準機能だけでも出来ちゃいます。難しいプログラムを組む必要が全くありません。カンマやコロン、もちろん全角スペースでも分離が可能です。エクセルでも似た機能があるみたいですね。

SPLIT関数を使う事も出来ます。

​過去にSPLIT関数で1本、記事を書いています。この頃は、まだコピペが中心で、GASが全然書けなかった.....。

PromptDialogを使用して、臨機応変が効くようにする

function promptDialog() {

 const ui = SpreadsheetApp.getUi();
 const response = ui.prompt(
   'スタッフの情報を取得',
   '取得したいスタッフの配属先を入力してください。（例）新宿',
   ui.ButtonSet.OK
 );
 
 const inputName = response.getResponseText();
 console.log(inputName);
 
 switch (response.getSelectedButton()) {
   case ui.Button.OK:
     console.log('%s と入力され、OKが押されました。',inputName);
     
     break;
     
   case ui.Button.CLOSE:
     console.log('閉じるボタンが押されました。');
     
 }//switch
 
 return inputName;
}

入力内容に応じて、spreadsheetに書き出す条件を変更してあげると汎用性が高いスクリプトになると思います。例えば、新宿支店に所属しているスタッフだけを書き出すとか、そんな使い方です。

正規表現を使って痒いところを何とか出来ないか？

ミドルネームが本当に厄介で、上記のスクリプトだと上手く出来なかったため、上記登録基本情報は、DBから、GASで必要な箇所のみを書き出して、REGEXEXTRACT関数を併用して、姓と名に分類するように変更しました。

全部、GASで出来たら、スマートなんですが、相当時間が掛かりそうだったので、今回は関数で処理しています。後に良い書き方が分かれば追記します。

エレンが名、イエーガーが姓じゃないってツッコミはなしで。とにかく勤怠アカウント登録用データを作成するのが目的です。進撃ファンの皆さん、すみません.....。

G2に次の関数を入れます。
Bの文字列からHの文字列を消すという事をしています。

=ARRAYFORMULA(IFERROR(SUBSTITUTE($B$2:$B,$H$2:$H,""),""))

H2に次の関数を入れます。
スペースから始まる文章を抜き出して、1文字目を削除しています。

=ARRAYFORMULA(IFERROR(IF($B$2:$B<>"", REPLACE(REGEXEXTRACT($B$2:$B,"　.*"),1,1,""),REPLACE(REGEXEXTRACT($B$2:$B," .*"),1,1,"")),""))

ジョブカンの仕様と戦う。UTF-8形式だと、上手くいかない場合がある。

spreadsheetでは、CSVが、UTF-8形式で保存されます。それを当然、Excelで開こうとすると文字化けします。ジョブカンサポートに聞くと、UTF-8のCSVもサポートしているという回答が返ってくるのですが、たまに失敗する事があります。時給の登録などが失敗した事があります。給与ミスは怖いのと事後処理が色々と大変なので、CSVをShift-JIS形式で保存するGASを書きました。

function promptDialog2() {
 /*UIでシート名を入力させる*/
 const ui = SpreadsheetApp.getUi();
 const response = ui.prompt(
   '入力フォーム',
   'Google Driveのフォルダーのリンクを入力してください。',
   ui.ButtonSet.OK
 );
 
 const folderLink = response.getResponseText().replace('https://drive.google.com/drive/folders/','');
 console.log(folderLink);
 
 switch (response.getSelectedButton()) {
   case ui.Button.OK:
     console.log('%s と入力され、OKが押されました。',folderLink);
     
     break;
     
   case ui.Button.CLOSE:
     console.log('閉じるボタンが押されました。');
     
 }//switch
 
 return folderLink;
}

function createCSV() {
 const spreadsheet  = SpreadsheetApp.getActiveSpreadsheet();
 const sheet        = spreadsheet.getSheetByName('CSV：ユーザー登録');
 const values       = sheet.getDataRange().getDisplayValues();
 
 var csvString = values.reduce(function(str, row) {
   return str + '\r' + row;
 });
 
 const date         = new Date(); //現在日時のDateオブジェクトを作る
 const today        = Utilities.formatDate(date, 'JST', 'yyyy/MM/dd_');
 
 const fileName    = today + 'ジョブカン登録';
 const blob        = Utilities
 .newBlob('', MimeType.CSV, fileName + '.csv')
 .setDataFromString(csvString, 'Shift-JIS');
 const folderId    = promptDialog2();
 const folder      = DriveApp.getFolderById(folderId);
 folder.createFile(blob);
}

CSVを保存するフォルダだけユーザーに入力してもらい、.replace('https://drive.google.com/drive/folders/','');この部分を消して、FolderIdを取得します。

CSVを作成するスクリプトはGAS本にも確か、掲載されていたかと思います。詳しく知りたい場合は確認されると良いと思います。

ジョブカンWFをspreadsheetに書き出すスクリプト

個人的には、下記のエントリーはもっと、ハートが付いてもいいのにって思っています。ジョブカン使っている方、ぜひ読んでみてください。

別件で、こんなスクリプト書きました。

テキストを分割するスクリプト
noteに乗せるための動画 pic.twitter.com/PEk8JRvlS3

— ⅡBASA (@nepia_infinity) March 27, 2020

function separateColumn() {
 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 const sheet       = spreadsheet.getSheetByName('シート1');
 
 /*数式をセットする*/
 const setFormula  = sheet.getRange(1, 1).setFormula('=ARRAYFORMULA(IF(B1:B<>"",REGEXEXTRACT(B1:B,"[0-9]{5,6}"),""))')
 
 /*セットした関数を値貼り付けにしてしまう。*/
 const lastRow    = sheet.getRange(1, 1, sheet.getLastRow(), 1).getValues().filter(String).length;
 const range      = sheet.getRange(1, 1, lastRow, 1);
 range.copyTo(range, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
 
 console.log(lastRow);
 console.log(range.getA1Notation());
 
 /*B列の ID　nameからIDを削除*/
 const range2      = sheet.getRange(1, 2, sheet.getLastRow(),1)
 const textFinder  = range2.createTextFinder('[0-9]{5,6}.').useRegularExpression(true);
 textFinder.replaceAllWith('');
}

function onOpen() {
 SpreadsheetApp.getUi()
 .createMenu('追加メニュー') // メニューの追加
 .addItem('分割する','separateColumn')
 .addToUi();
}

['11111　岡部倫太郎']　こういう、どうしようもないセルを正規表現が使えるREGなんとか関数で抽出後、値貼り付けからの〜置換で、元データからIDと空白スペース削除をしています。