【GAS】Google Apps Script 活用事例　採用イベント後に配布されるExcelファイルから情報を読み取り、VLOOKUPの結果をスプレッドシートに転記するスクリプト

2022年4月16日 10:55

今回は、ドライブに保存されているExcelファイルを一度、スプレッドシートに変換し転記するだけではなく、VLOOKUP関数的な動きをして、結果を返すスクリプトを書いてみました。

Google DriveにあるExcelファイルの内容をGASで読み取れないのかな？
Service Spreadsheets failed while accessing document with id　*******

ってエラーが出る
— ⅡBASA (@nepia_infinity) April 13, 2022

以前、書いたスクリプト

今回参考にしたスクリプト

採用イベント後に、参加企業の中で、どの企業への志望度が高いかをまとめたファイルが配布されます。Excelを開いて、VLOOKUP関数を書くなり、コピペするなり事後処理をやっていたワケですが、今なら、8割くらい自動化出来そうだなと思って踏み切りました。

function searchDbSheet_(values, id)

if(values[i][0] === id)
Excelシートの1列目がID、転記先のシートから取得したIDが一致したら、その行を返す。

参加人数はイベントにもよりますが、Max40人くらいです。
参加しない人がいたりする場合は、IFERROR的な処理を加えなければいけません。これは大変…..。

どんなことをやっているか

・Excelファイルを自動でスプレッドシートに変換
・変換したスプレッドシートの配列を取得
・イベント名のみの1次元配列を取得
・結果を転記する位置を取得
　└（毎回、シート下部に情報が蓄積されていくため）
・イベント参加人数を取得
・学生IDを付き合わせて、一致した行を返す

スクリプト実例

/**
 * 採用イベント後に配布されるExcelファイルをスプレッドシートに変換し、内容を取得し
 * 学生IDと一致した行の内容を特定のシートに張り付ける
 * 
 * 【スクリプト実行前に必要な準備】
 * 採用イベント参加学生 連絡先一覧（Excel）をGoogle Driveにアップロードする。
 * 
 */
function insertVlookupResults(){
  const url    = 'https://docs.google.com/spreadsheets/d/**************/edit#gid=************';
  const sheet  = getSheetByUrl_(url);
  const values = sheet.getDataRange().getDisplayValues();

　//見出し行から列を特定する
  const column = {
   applicantId: values[0].indexOf('学生ID'),
   name:        values[0].indexOf('氏名'), 
   event:       values[0].indexOf('イベント名'),
　　priority:    values[0].indexOf('第一志望'),
  }
　
　//処理開始行の結果を返す
  const startRow = getStartRow_(values, column);


  //Driveに保存したExcelシートから2次元配列を取得
  //チームドライブのフォルダIDだと認証が通らない。
  const file        = findExcelFile_();
  const excelValues = getValuesFromExcelSheet_(file, '***********'); //Google DriveのフォルダID

  //前回のイベント参加者の人数を取得
  const eventNameArray = values.map(record => record[column.event]);
  const eventName      = sheet.getRange(startRow, column.event + 1).getValue();

  //console.log(eventNameArray);
  console.log(`イベント名：　${eventName}`);


  //2次元配列から前回のイベント参加者のIDを取り除く
  const filtered = values.filter(row => row[column.event] === eventName);
  console.log(filtered);

  //学生IDを1次元配列で取得
  const applicantIdArray = filtered.map(record => record[column.applicantId]);
  console.log(`${eventName} 参加人数: ${applicantIdArray.length}`);
  

  const max     = numberOfParticipants_(eventNameArray, eventName);
  let count     = 0;
  let targetRow = startRow;

  for(let i = 0; i < applicantIdArray.length; i++){
    //見出し行と空白行だったら省く
    if(!applicantIdArray[i] || applicantIdArray[i] === '学生ID'){continue}

    //イベント名、名前、ID
    console.log(`${eventName},学生ID: ${filtered[i][column.applicantId]}, ${filtered[i][column.name]}`);
    const newValues = searchDbSheet_(excelValues, filtered[i][column.applicantId]);

    else if(!newValues){
      console.log(`学生ID: ${filtered[i][column.applicantId]}　${filtered[i][column.name]}　さんは欠席の可能性があります。`);
      console.log(`そのため、newValuesが、undefinedになっています。`);
    }    
    else if(0 < newValues.length){
      const range = sheet.getRange(targetRow, column.priority + 1, newValues.length, newValues[0].length);
      range.setValues(newValues);

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

    count     += 1;
    targetRow += 1;

    //参加人数に達したら処理を終了
    else if(count === max){
      break
    }
  }//for
}


/**
 * Google Driveのフォルダから名前と合致した特定のファイルを探す
 * 
 */
function findExcelFile_(){
  const input    = showPrompt_('Google DriveのフォルダIDを取得', 'https://drive.google.com/drive.....');
  const folderId = input.replace('https://drive.google.com/drive/folders/','');
  const folder   = DriveApp.getFolderById(folderId);
  const files    = folder.getFiles();

  console.log(`実行中の関数名：　findExcelFile_()`);
  console.log(`フォルダID: ${folderId}`);
  console.log(`取得したフォルダ名：　${folder.getName()}`);

  while (files.hasNext()) {
    const file     = files.next();
    const fileName = file.getName();
    console.log(`ファイル名：　${fileName}`);

    //ファイル名に参加後、連絡先が含まれていたら処理を続行する
    if(fileName.includes('連絡先')||fileName.includes('参加後')){
      const fileId = file.getId();
      console.log(`ファイルID: ${fileId}`);

      return file
      
    }
  }
}


/**
 * Excelファイルをスプレッドシートに変換、マイドライブに保存。valuesを取得
 * チームドライブのフォルダだと認証が通らず、エラーになってしまう。
 * 
 * @param  {object} fileオブジェクト
 * @param  {string} Google DriveのファイルID
 * @return {object} Excelファイルの内容
 */
function getValuesFromExcelSheet_(file, destinationId){

  options = {
   title   : file.getName(),
   mimeType: MimeType.GOOGLE_SHEETS,
   parents : [{ id: destinationId}]
 }

  const spreadsheet = SpreadsheetApp.openById(Drive.Files.insert(options, file.getBlob()).id); //ExcelをSSに変換し取得
  const sheet       = spreadsheet.getSheetByName('連絡先一覧');
  const values      = sheet.getDataRange().getDisplayValues();

  console.log(`実行中の関数名：　getValuesFromExcelSheet_()`);
  console.log(values);
  return values

}


/**
 * 処理を開始する行を取得する
 * 第一志望企業が入力されていない行 = 処理開始行とみなす
 * 
 *　@param  {object} 転記先の2次元配列
 *　@param  {object} 見出し行の情報
 *　@return {number} 処理開始行を返す
 *
 */
function getStartRow_(values, column){
  let startRow = 0;
  console.log(`実行中の関数名：　getStartRow_()`);

  //イベント名の1次元配列を取得
  for(let i = 2; i < values.length; i++){
    if(!values[i][column.priority]){
      startRow = i + 1;
      console.log(`処理開始行：　${startRow}`);
      return startRow
    }
  }//for
}


/**
 * 参加人数を取得
 *　@param  {object} イベント名が含まれた1次元配列
 *　@param  {string} 対象となるイベント名
 *　@return {number} 参加人数を返す
 */
function numberOfParticipants_(array, eventName){
  let count = 0;
  for(let i = 0; i < array.length; i++){
    if(array[i] === eventName){
      count += 1;
    }
  }//for
  console.log(`イベント参加者人数：　${count}`);
  return count
}


/**
 * 取得した2次元配列から学生の第一志望や第二志望の企業などを取得する
 * 大学名や学部など、イベント前に用意した情報を省く
 *
 *　@param  {object} 2次元配列
 *　@param  {string} 学生ID getDisplayValuesで取得しているため、型は文字列
 *　@return {object} 学生の第一志望や第二志望の情報
 */
function searchDbSheet_(values, id){
  const headerRow = values[2]; 
  const column    = {
    number1: headerRow.indexOf('第一志望の企業'),
    reason1: headerRow.indexOf('第一志望の理由'),
    number2: headerRow.indexOf('第二志望の企業'),
    reason2: headerRow.indexOf('第二志望の理由'),
    number3: headerRow.indexOf('第三志望の企業'),
    reason3: headerRow.indexOf('第三志望の理由'),

  }
  for(let i = 0; i < values.length; i++){
    if(values[i][0] === id){
      const newValues = selectColumn_([values[i]], column);
      return newValues
    }
  }//for
}


/**
 * getDataRange()などで取得した2次元配列から必要な列だけを抽出し、新しい2次元配列を作成する
 * 
 * @param  {object} 元の2次元配列
 * @param  {object} 見出し行のオブジェクト
 * @param  {string} 2次元配列から情報を取捨選択するためのキーワード
 * @return {object} 新しい配列
 */
function selectColumn_(values, column, keyWord){
  const keys    = Object.keys(column);
  const numbers = keys.map(key => column[key]);

  console.log(`selectColumn_()　を実行中`);
  console.log(`00.2_commonsに記載`);

  console.log(numbers);//1次元配列

  //indexに該当する列だけを残して2次元配列を作成する
  const newValues = values.map(array => array.reduce((accumulator, current, index) =>{
      if(numbers.includes(index)){
        accumulator.push(current);
      }
    return accumulator
    }, [])//reduce
  );//map

  //console.log(newValues);
  
  if(!keyWord){
   //keyWordが省略されており、定義されていない場合、空白行の配列を取り除く
    const filtered = newValues.filter(row => row[0] !== '');
    console.log(filtered);

    return filtered

  }else if(keyWord){
    //newValuesから、さらに特定の単語が含まれている配列のみを残す
    const filtered = newValues.filter(row => row.indexOf(keyWord) !== -1);

    console.log(filtered);
    return filtered
  }
}

ログないし、スクリーンショットが欲しいですね…..。
正直、使う場面が限定的過ぎて、読者の方に役立つかと言われると微妙なのですが、何かの参考になれば幸いです。

この記事が気に入ったらサポートをしてみませんか？