【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);
Guestであって、Guestsではありません。なので、カレンダーの出席者を後から追加する場合には、addGuestで一人ずつ登録する必要があります。上記のスクリプトでは、for文を回して、addGuestを複数回、実施しています。
カレンダーの登録
この記事が気に入ったらサポートをしてみませんか?