【GAS】Google Apps Script 活用事例　Zoomの情報を列ごとに分けて、転記するスクリプト

ZoomのURLなどをスプレッドシートに貼り付ける機会があり、招待のコピーを特定の列に貼り付けると、適切な関数を自動で挿入し、列ごとに分けてくれます。

招待のコピーって何？

下記のスクリーンショットは、Macの個人版で、会社で使用しているのと、やや異なりますが、招待のコピーとは、下の方にちょろっと出ているヤツです。

Zoomミーティングに参加する
https://zoom.us/j/98948807180?pwd=MlA3bDN6Qi9XM0s2RDBIWjlrd3lzUT09

ミーティングID: 989 4880 7180
パスワード: 7kfnFT

上記を表に貼り付けます。サンプルでは、N列にコピーすると、O、P、Qに、自動で列を分けてくれます。

スクリプトは、こんな感じ

function setZoomInfo() {
 const spreadsheet     = SpreadsheetApp.getActiveSpreadsheet();
 const targetSheet     = spreadsheet.getSheetByName('面接日程');
 const activeSheet     = spreadsheet.getActiveSheet();
 
 //シート名の取得
 const targetSheetName = targetSheet.getSheetName();
 const activeSheetName = activeSheet.getSheetName()
 
 
 console.log('指定したシート：%s', targetSheetName);
 console.log('アクティブなシート：%s', activeSheetName);
 
 
 //アクティブセルを取得
 const activeCell   = targetSheet.getActiveCell();
 const activeRow    = activeCell.getRow();
 const activeColumn = activeCell.getColumn();
 const activeValue  = activeCell.getValue();
 
 console.log(`activeRow ${activeRow}`);
 
 
 //1行目の見出しを取得
 const headerRow   = targetSheet.getDataRange().getValues()[0];
 console.log(headerRow);
 
 
 //該当する列を探すために検索をする。
 const COLUMN_NAME ={
   COPY: 'Zoom 招待のコピー貼付け欄',
   URL: 'Zoom URL',
   ID: 'ID',
   PASSWORD: 'PASSWORD',
 }
 
 //return number
 //配列のindexは、0から始まるため、activeColumn()と1つズレるために1を足す
 const pasteColumnIndex     = headerRow.indexOf(COLUMN_NAME.COPY)       + 1;
 const urlColumnIndex       = headerRow.indexOf(COLUMN_NAME.URL)        + 1;
 const idColumnIndex        = headerRow.indexOf(COLUMN_NAME.ID)         + 1;
 const passwordColumnIndex  = headerRow.indexOf(COLUMN_NAME.PASSWORD)   + 1;
 
 console.log(`招待のコピー貼付け欄 ${pasteColumnIndex}`);
 
 
 if(targetSheetName !== activeSheetName){ return }
 if(activeRow < 2){ return }
 if(activeValue === ''){ return }
 if(activeColumn === pasteColumnIndex){
   
   //数式を挿入する。
   targetSheet.getRange(activeRow, urlColumnIndex).setFormula(`=REGEXEXTRACT(${activeCell.getA1Notation()},"https://zoom.us/.*")`);
   targetSheet.getRange(activeRow, idColumnIndex).setFormula( `=SUBSTITUTE(REGEXEXTRACT(${activeCell.getA1Notation()},"ミーティングID: .*"),"ミーティングID: ","")`);
   targetSheet.getRange(activeRow, passwordColumnIndex).setFormula(`=SUBSTITUTE(REGEXEXTRACT(${activeCell.getA1Notation()},"パスワード: .*"),"パスワード: ","")`);
   

 }//if
}//end

見出し行から特定の列を探す

表が横に長かったり、運用上、後から列が挿入される可能性があったりする場合、indexOfで、見出し行を検索して、列を求めるようなスクリプトにしておくと、後々、楽ちんになります。

トリガーを編集時にセット

2021/11/05リライトver

スクリーンショットのように左右で構成が同じ表が存在する場合の工夫について、下記のようなスクリプトで対処しました。同じ表が3つ4つとなると敵いませんが、2つであれば、lastIndexOfで書き込む列の特定が出来ました。

/**
* 1DAY選考会のZoomの招待を特定の列に張り付けると、自動で列を分ける
* 
* 
*/
function setZoomInfo() {
 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 const sheet       = spreadsheet.getSheetByName('1DAY選考会');
 const activeSheet = spreadsheet.getActiveSheet();
 
 //シート名の取得
 const sheetName       = sheet.getSheetName();
 const activeSheetName = activeSheet.getSheetName();

 console.log(`指定したシート　${sheetName}`);
 console.log(`アクティブなシート ${activeSheetName}`);

 if(sheetName !== activeSheetName) return;
 
 //アクティブセルを取得
 const activeCell  = getActiveCell_(sheet);
 const headerRange = sheet.getRange(2, 1, 1, sheet.getLastColumn());
 const headerRow   = headerRange.getValues()[0];

 console.log(`ヘッダー行の範囲：　${headerRange.getA1Notation()}`);
 //console.log(headerRow);
 
 //配列のindexは、0から始まるため、activeColumn()と1つズレるために1を足す
 let column = {
   name:       headerRow.indexOf('学生名'),
   confluence: headerRow.indexOf('confluence'),
   hrmos:      headerRow.indexOf('HRMOS\nURL'),
   copy:       headerRow.indexOf('招待のコピー'),
   account:    headerRow.indexOf('使用Zoom'),
   id:         headerRow.indexOf('ID'),
   passCode:   headerRow.indexOf('PASS'),
   url:        headerRow.indexOf('URL'),
   memo:       headerRow.indexOf('備考'),

 }
 
 //全ての要素に1を足す
 modifyObject_(column);
 console.log(column);

 //左側の表の最終列　備考が**列目　アクティブな列が**以上なら右側の表　
 if(activeCell.column < column.memo){
   console.log('アクティブセルは、左側の表内にあります。');

   //Zoomの情報 [[アカウントの発行者, ID, PASS, URL]] を列ごとに分けて記入する
   splitZoomInfo_(sheet, activeCell, column);
  
 }//if 左側の表かどうかを判定
 else{
   console.log('アクティブセルは、右側の表内にあります。');

   column = {
     name:       headerRow.lastIndexOf('学生名'),
     confluence: headerRow.lastIndexOf('confluence'),
     hrmos:      headerRow.lastIndexOf('HRMOS\nURL'),
     copy:       headerRow.lastIndexOf('招待のコピー'),
     account:    headerRow.lastIndexOf('使用Zoom'),
     id:         headerRow.lastIndexOf('ID'),
     passCode:   headerRow.lastIndexOf('PASS'),
     url:        headerRow.lastIndexOf('URL'),
     memo:       headerRow.lastIndexOf('備考'),
   }

   console.log(`右側の表のcolumn　lastIndexOfで検索した値を、columnに再代入しています`);
   modifyObject_(column);
   console.log(column);

   //Zoomの情報 [[アカウントの発行者, ID, PASS, URL]] を列ごとに分けて記入する
   splitZoomInfo_(sheet, activeCell, column);
   
 }
 return
}


/**
* アカウントの発行者を取得する
* '〇〇さんがあなたを予約されたZoomミーティングに招待しています。'
* 実際には、社員の名前　→ ニックネームへの変換に使用しています。
* 
*/
function getZoomAccount_(invitation){
 let string    = '';
 const account = invitation.match(/野比のび太|ジャイアン|静香|スネ夫|ドラミ|ドラえもん/)[0];
 console.log(`matchの結果：　${account}`);
 
 //発行者の名前　→ 発行アカウント
 switch(account){
   case '野比のび太': string = 'Zoom1';
     break;
   case 'ジャイアン': string = 'Zoom2';
     break;
   case '静香': string = 'Zoom3';
     break;
   case 'スネ夫': string = 'Zoom4';
     break;
   case 'ドラミ': string = 'Zoom5';
     break;
   case 'ドラえもん': string = 'Zoom6';
     break;
 }
console.log(`Zoom URLの発行者：　${string}`);
return string
}


/**
* Zoomの情報 [[アカウントの発行者, ID, PASS, URL]] を列ごとに分けて記入する
* 
* @param sheet       1DAY選考会のシートオブジェクト
* @param activeCell  現在アクティブなセルの情報
* 
*/
function splitZoomInfo_(sheet, activeCell, column){
 if(activeCell.value.includes('https://abcdefg.zoom.us') && activeCell.column === column.copy){

   //ミーティングID: 974 3784 4623 3桁-4桁-4桁　になっている　ゼロ落ちがないか確認
   //パスコード: 276551 6桁

   const zoom = [{
     account:  getZoomAccount_(activeCell.value),
     id:       activeCell.value.match(/ミーティングID: .*/)[0].replace('ミーティングID: ', ''),
     passCode: activeCell.value.match(/パスコード: .*/)[0].replace('パスコード: ', ''),
     url:      activeCell.value.match(/https:\/\/abcdefg.zoom.us\/.*/)[0]
   }];

   console.log(zoom);

   //オブジェクトを2次元配列に変換する
   const keys   = Object.keys(zoom[0]);
   const values = zoom.map(value => keys.map(key => value[key]));
   const range  = sheet.getRange(activeCell.row, column.account, 1, values[0].length);

   console.log(keys);
   console.log(values);
   console.log(`Zoom情報を張り付ける範囲：　${range.getA1Notation()}`);

   range.setValues(values);

 }//Zoomの情報が含まれていたら
 else{
   console.log('Zoom情報は含まれていなかったので処理を終了します。');
   return
 }
}



/**
* 連想配列の全ての要素に1を足す。
* 
* values[0].indexOf('名前');　//expected output: 2
* 上記のように見出し行から列の位置を特定するスクリプト
* getRangeでも使いたい場合に使用
* 
*/

function modifyObject_(original) {
  const object = Object.keys(original).reduce(
    (after, key) => ({...after, [key]: original[key] += 1}),{}
  );
  console.log(object);
  return object
}

面接官の名前を入力したら、Googleアカウントを返す。

function activeCell() {
 const spreadsheet     = SpreadsheetApp.getActiveSpreadsheet();
 const targetSheet     = spreadsheet.getSheetByName('面接日程');
 const activeSheet     = spreadsheet.getActiveSheet();
 
 //シート名の取得
 const targetSheetName = targetSheet.getSheetName();
 const activeSheetName = activeSheet.getSheetName()
 
 
 console.log('指定したシート：%s', targetSheetName);
 console.log('アクティブなシート：%s', activeSheetName);
 
 
 //アクティブセルを取得
 const activeCell   = targetSheet.getActiveCell();
 const activeRow    = activeCell.getRow();
 const activeColumn = activeCell.getColumn();
 const activeValue  = activeCell.getValue();
 
 const columnL      = targetSheet.getRange('L1').getColumn();
 
 //１行目以降で、列が、9,10,11列目がアクティブだったら、処理を実行する。
 if(targetSheetName !== activeSheetName){ return }
 if(activeRow < 2){ return }
 if(activeValue === ''){ return }
 if(9 <= activeColumn && activeColumn <= 11){


   //面接日程シートに入力された面接官の名前から、カレンダーIDを取得する。
   const calId = getCalId(activeValue);
   const range = targetSheet.getRange(activeRow, columnL);
   
   
   if(range.isBlank() === true){
     range.setValue(calId);
     console.log(`${range.getA1Notation()} は、空白です。`);
     console.log(`セルの値 ${range.getValue()}`);
     
   }else{
     const existValue = range.getValue();
     range.setValue(`${existValue},${calId}`);
     console.log(`${range.getA1Notation()} は、空白では、ありません。`);
     console.log(`セルの値 ${range.getValue()}`);
   }
 }//if
}//end

//面接日程シートに入力された面接官の名前から、カレンダーIDを取得する。
function getCalId(personName) {
 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 const sheet       = spreadsheet.getSheetByName('名簿');
 const values      = sheet.getDataRange().getValues();
 
 let targetCalId;
 
 for(let i = 0; i < values.length; i++){
   
   //B列の名前が一致したら、A列のカレンダーIDを取得する。
   
   if(values[i][1].indexOf(personName) !== -1){
     targetCalId = values[i][0];
     console.log(targetCalId);
     
   }//if
 }//for
 return targetCalId
}

今回の記事は、こちらの記事で紹介していたものを、GASで関数の挿入が出来るようにしました。

管理表では、こちらのスクリプトも役立っています！！

面接官の名前を、Googleアカウントで返すスクリプトです。