GoogleドライブのEXCELファイルから任意セル内容を抽出する

Web・プログラミング

GoogleドライブにはEXCELファイルを保存できるので、同じフォーマットで作成したEXCELファイルを、Googleドライブのあるフォルダの中に大量に保存しています。

この大量のEXCELファイルから、必要な情報だけ取り出して整理したいと思い、同じフォーマットで記載された大量のEXCELデータから、自動的に情報を集めて整理する仕組みを作り、全体像を把握しやすくしました。

その過程で得た、GAS(GoogleAppScript)でGoogleDriveのExcelデータの読み取りと、読み取ったデータのスプレッドシートへの書き込み方法について、やり方やハマったことの対策をここに整理しておこうと思います。

この記事でわかること

  • gasを使って任意のフォルダにあるEXCELファイルにアクセスする方法
  • EXCELファイルの指定した箇所のセル内容を取り出す方法
  • スプレッドシートの任意の箇所のセルにデータを入力する方法

GASスクリプトの作り方については、下記ページの「マイドライブにGoogleスプレッドシートを新規作成する」を参照下さい。

GoogleドライブのファイルURL取得
GAS(GoogleAppScript)を使って、Googleドライブにあるフォルダ内のファイルに対するリンク集を作成する
miti-haku-un.com
2024.06.30

準備

DriveAPI V2の追加

EXCELファイルのセル情報をGASで取得するためには、GASの関数で情報を抽出できるようにするために、EXCELファイルをGoogleスプレッドシートに変換する必要があります。

EXCELファイルをGoogleスプレッドシートに変換するために、DriveAPIを使用します。今回の解説ではバージョンはv2を選択してください。エディタ左上側の「サービス」の「＋」をクリックすると、「サービスの追加」という画面が出るので、「Drive API」を選択して、追加ボタンを押下します。

フォルダIDの確認

また、今回は任意のフォルダにある複数のEXCELファイルが存在する場合の手法を紹介しております。GASで任意のフォルダにアクセスするためにはフォルダIDが必要です。フォルダIDについては、下記記事の「URLを取得したいファイルがあるフォルダの「フォルダID」をメモしておく」を御覧ください。

GoogleドライブのファイルURL取得
GAS(GoogleAppScript)を使って、Googleドライブにあるフォルダ内のファイルに対するリンク集を作成する
miti-haku-un.com
2024.06.30

フォルダ内の全EXCELファイルに対するデータ抽出

あるGoogleドライブのフォルダ内に、複数のEXCELが存在する（EXCELファイル以外は存在しない）場合を例として説明します。

下記はEXCELファイルをスプレッドシートに変換し、任意のセル情報を取得するまでのサンプルコードです。セルF9と、セル範囲D20:D39を抽出して、GASのスプレッドシートの最終行に追記します。

function readExcelData() {
  //①任意のフォルダにあるEXCELファイルを取得し、スプレッドシートに変換
  const FOLDERID = "★★★ExcelファイルがあるGoogleドライブのフォルダID★★★"
  const folder = DriveApp.getFolderById(FOLDERID);
  files = folder.getFiles();

  //②ループのなかでイテレータをつかって、データ取得を繰り返す
  while (files.hasNext()) {
    const file = files.next();
    //ExcelをSpreadSheetに変換し取得
    let fileobj = 
      Drive.Files.insert({
                          title:file.getName(),
                          mimeType: MimeType.GOOGLE_SHEETS,
                          parents:[{ id: folder.getId() }] 
                          }
                          ,file.getBlob());
    //③変換したスプレッドシートから、指定した箇所のセル内容を取り出す
    const newSs = SpreadsheetApp.openById(fileobj.id);
    let sheet = newSs.getSheetByName('★抽出したいデータがあるシート名★');

    const CELLADDR='F9';
    let tmp1 = sheet.getRange(CELLADDR).getDisplayValue();
    const RANGEADDR = 'D20:D39';
    let tmp2 = sheet.getRange(RANGEADDR).getDisplayValues();

    //④取得したセル内容を、このGASが実装されたスプレッドシートに出力する。
    var wb = SpreadsheetApp.getActive();
    let gsws = wb.getSheetByName('シート1');
    let editrow = gsws.getLastRow()+1;
    let writeArray = [
      ['', '', '', '', '', '', '', '', '', '','', '', '', '', '', '', '', '', '', '']
    ]
    writeArray[0][0] = tmp1;
    writeArray[0][1] = tmp2[0];    
    gsws.getRange(editrow , 1,1,20).setValues(writeArray);

    //⑤変換したスプレッドシートを削除する
    DriveApp.getFileById(newSs.getId()).setTrashed(true);
  }
}

①～⑤の順に、処理を説明していきます。

①任意のフォルダにあるEXCELファイルを取得し、スプレッドシートに変換

下記のコードにより、EXCELファイルがあるフォルダのIDから、そのフォルダ内にある全ファイルを取得します。

const FOLDERID = "★★★ExcelファイルがあるGoogleドライブのフォルダID★★★";
const folder = DriveApp.getFolderById(FOLDERID);
files = folder.getFiles();

②ループのなかでイテレータをつかって、データ取得を繰り返す

ルーブとイテレータを使って一つのEXCELファイルに対する処理を繰り返します。（※フォルダ内にはEXCELファイル以外は存在しないことを前提としています。ある場合はEXCELファイル以外は処理から除外する仕組みが必要です）

イテレーター（Iterator）とは、プログラム内で順番に要素にアクセスするための仕組みです。特に、データの集合やコレクションに対して一つずつ順番に要素にアクセスするときに利用されます。

下記コードで、フォルダ内のファイルに一つずつアクセスしてスプレッドシートに変換しています。

  while (files.hasNext()) {
    const file = files.next();
    //ExcelをSpreadSheetに変換し取得
    let fileobj = 
      Drive.Files.insert({
                          title:file.getName(),
                          mimeType: MimeType.GOOGLE_SHEETS,
                          parents:[{ id: folder.getId() }] 
                          }
                          ,file.getBlob());

③変換したスプレッドシートから、指定した箇所のセル内容を取り出す

下記の例は、変換したスプレッドシート「newSs」の任意のシート（シート名は、抽出したいデータがあるシート名を設定して下さい）のセルC17と、セル範囲「D20:D39」を抽出しています。

    const newSs = SpreadsheetApp.openById(fileobj.id);
    let sheet = newSs.getSheetByName('★抽出したいデータがあるシート名★');
    const CELLADDR='F9';
    let tmp1 = sheet.getRange(CELLADDR).getDisplayValue();
    const RANGEADDR = 'D20:D39';
    let tmp2 = sheet.getRange(RANGEADDR).getDisplayValues();

ここで、セル情報の読み込みには、関数「getdisplayvalue」を使います。セル情報は、「getvalue」でも抽出できますがEXCELのセル書式設定によって失敗したことがあります。

どういう失敗かというと、EXCELで入力された日付の「セルの書式設定」によって、欲しいデータが取得できなかった、という事例です。

　右記の「8月15日」が記入されていたセルの「セルの書式設定」が「ユーザー定義」に設定されていたため、GASで取得したデータが「8月15日」ではなく、「2024/08/15 16:00:00」になっており、この後に続く日付の文字列処理（「月」と「日」で区切って月日を取得する処理）でエラーが発生し、理由がわからずハマったことがあります。

　セルに表示された文字をそのまま取得したいため、getDisplayValueを使用しています。

④取得したセル内容を、このGASが実装されたスプレッドシートに出力する。

    var wb = SpreadsheetApp.getActive();
    let gsws = wb.getSheetByName('シート1');
    let editrow = gsws.getLastRow()+1;
    let writeArray = [
      ['', '', '', '', '', '', '', '', '', '','', '', '', '', '', '', '', '', '', '']
    ]
    writeArray[0][0] = tmp1;
    writeArray[0][1] = tmp2[0];    
    gsws.getRange(editrow , 1,1,20).setValues(writeArray);

GASが実装されたスプレッドシートのオブジェクトを「SpreadsheetApp.getActive」で取得します。

スプレッドシートオブジェクトから、「getSheetByName」を使って、シート名「シート1」のシートオブジェクトを取得します。

シート1にセルの情報を追記していきます。一つのEXCELファイルの情報を書き込んだら改行して次のEXCELファイルの情報を追記していきたいので、「getLastRow」を使って「シート1」の最終行の次の行数を取得し、そこにデータを書き込みます。

シートへの書き込みは関数「getRange」と「setValues」を使用します。getRangeは、第一引数が開始行番号、第二引数が開始列番号、第三引数が行数範囲、第四引数が列数範囲です。setValuesの引数で指定した配列を、getRangeで指定した範囲に書き込みます。今回は書き込むデータを1☓20の2次元配列writeArrayにまとめて設定おります。

⑤変換したスプレッドシートを削除する

変換したスプレッドシートは不要なので、下記のコードで削除します。

    DriveApp.getFileById(newSs.getId()).setTrashed(true);
  }//ループ最終

コメント

タイトルとURLをコピーしました