【GAS】Google Apps Script 活用事例　スプレッドシート上の名前を一括で、アカウントIDに変更するスクリプト

上記のようなシートがあって、このシートを見ながら、カレンダーの出席者に、この面接官を加えてくれる？みたいに依頼される機会がありました。

DBを作ります。これが結構面倒だったりするのですが....。

安藤だったら、ando@gmail.comを返すようにします。

turnIntoStringの関数で、最後列に、それぞれのアカウントのIDをまとめたものを貼り付ける事で、カレンダーの登録用の出席者リストを一発で作成する事が出来ます。

これがすぐにパッとは書けなくて、手作業で登録したので、書けてスッキリです。

2020/09/24　indexOfの関数に、ミスがあり、修正しました。

上記のスクリーンショットのように、必ず4名の面接官がいる場合は、問題無かったのですが、行によって、参加する面接官が必ずしも4名ではない場合にエラーが出てしまっていたので、else ifの処理を追加しました。

面接官の名前をSlack のアカウント名に変換するスクリプト

function convertId () {
 const spreadsheet  = SpreadsheetApp.getActiveSpreadsheet();
 const sheet        = spreadsheet.getSheetByName('IMP');
 const values       = sheet.getDataRange().getValues();
 values.shift();
 
 console.log(values);
 
 let newValues      = [];
 
 for (const record of values) {
   if(record === ''){continue}
   newValues.push(record.map(element => indexOf(element)));
   
 }//for
 console.log(newValues);
 const targetSheet = spreadsheet.getSheetByName('convert');
 targetSheet.getRange(2, 1, newValues.length, newValues[0].length).setValues(newValues);
 
}//end


function indexOf(element) {
 const spreadsheet  = SpreadsheetApp.getActiveSpreadsheet();
 const sheet        = spreadsheet.getSheetByName('list');
 const values       = sheet.getDataRange().getValues();
 
 let mailAddress;
 let count = 0;
 
 //表の構成は、社員ID, 名前, アカウントID, SlackID
 for(let i = 0; i < values.length; i++){
   
   //一致しなかったら、-1を足す
   count += values[i][0].indexOf(element);
   
   //elementが空白だったら、空白として出力
   if(element === ''){
     mailAddress = '';
     return
   }
   
   if(values[i][0] === ''){continue}
   if(values[i][0].indexOf(element) !== -1){
     mailAddress = values[i][2];
     console.log(element, mailAddress);
   }//if
   
   //全部エラーだった場合、そのまま出力
   else if(count === -1 * values.length){
     mailAddress = element;
   }
 }//for
 console.log(mailAddress);
 return mailAddress
}


function turnIntoString() {
 const spreadsheet  = SpreadsheetApp.getActiveSpreadsheet();
 const sheet        = spreadsheet.getSheetByName('convert');
 const values       = sheet.getRange('E2:H14').getValues();
 //const values       = sheet.getDataRange().getValues();
 //values.shift();
 
 let newValues      = [];
 
 const targetColumn = sheet.getLastColumn() + 1;
 
 for(const row of values){
 
   //1次元配列を、文字列に変換して、最後の列に追加
   const idList        = row.filter(element => element);
   const convertString = idList.join(',');
   
   console.log(convertString);
   newValues.push([convertString]);
   
 }//for
 console.log(newValues);
 sheet.getRange(2, targetColumn, newValues.length, newValues[0].length).setValues(newValues);
}//end

こちらは、以前、etauさんに書いてもらったスクリプトを応用したものになります。

ContactsAppを使ってメールアドレスを取得するスクリプト

シートに新卒が含まれる日程だけを書き出す

GASのエディタで書いていると、時折、オートインデントが効かなくなる事があります。特にオブジェクトや、アロー関数などを使うと、その行からずれてしまっています。一部、スペースで調整したりしています。すみません.....。

function getCalEvent() {
 
 //今日から1年後の日付
 const startTime = new Date();
 const endTime   = new Date();
 endTime.setFullYear(endTime.getFullYear() + 1);
 
 const calendar  = CalendarApp.getCalendarById('***************');
 const events    = calendar.getEvents(startTime, endTime);
 
 let newValues   = [];
 
 const stringDate  = (date, number, format) => {
   date.setDate(date.getDate() + number);
   return Utilities.formatDate(date, 'JST', format);
 }
   
 for(const event of events){
   
   const eventTitle = event.getTitle();
   const calId      = event.getId();
   const start      = event.getStartTime();
   const end        = event.getEndTime();
   
   console.log(eventTitle);
   
   const startDate  = stringDate(start, 0, 'yyyy/MM/dd HH:mm');
   const endDate    = stringDate(end, 0, 'yyyy/MM/dd HH:mm');
   
   if(eventTitle.indexOf('新卒') !== -1){
     newValues.push([calId, eventTitle, startDate, endDate]);
   
 	}//if
  }//for
 console.log(newValues);
 return newValues
 }//end
   
   
function setValues() {
 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 const sheet       = spreadsheet.getSheetByName('書き出し');
 const values      = getCalEvent();
 sheet.getRange(2, 1, values.length, values[0].length).setValues(values)
   
}

準備が整ったシート経由で、カレンダーのイベントに、面接官を出席者として追加する。

//上記のスクリプト、setValuesを実施した上で、この関数を使用すること
function addEmailList() {
 const spreadsheet  = SpreadsheetApp.getActiveSpreadsheet();
 const sheet        = spreadsheet.getSheetByName('書き出し');
 const values       = sheet.getDataRange().getValues();
 values.shift();
 
 
 const calendar  = CalendarApp.getCalendarById('***************');
 
 
 for(let i = 0; i < values.length; i++){
   if(values[i][0] === ''){continue};
   
   const calId      = values[i][0];
   const event      = calendar.getEventById(calId);
   const eventTitle = event.getTitle();
   
   
   //for文を回すために、カンマ区切りのメールアドレスを配列に変換
   const emailList  = values[i][4];
   const emailArray = emailList.split(',');
   
   console.log(calId, eventTitle);
   console.log(emailList);
   console.log(typeof emailArray);
   console.log(emailArray);
   
   
   //出席者を追加
   for(const email of emailArray){
     event.addGuest(email);
     console.log(`for of ${eventTitle} ${email}`);
   }//for of
 }//for
}//end

該当するイベントをIDで取得する

//calIdには、前述のスクリプトでシートに書き出した、カレンダーのイベントIDが入ります。
//そうする事で、該当するイベントだけを修正出来ます。

const event = calendar.getEventById(calId);

【GAS】addGuestメソッドを完全マスター【GAS】addGuestメソッドを完全マスター

Guestであって、Guestsではありません。なので、カレンダーの出席者を後から追加する場合には、addGuestで一人ずつ登録する必要があります。上記のスクリプトでは、for文を回して、addGuestを複数回、実施しています。

カレンダーの登録