「神エクセル」をGASで自動転記！のための3つの関数

2023年8月30日 GAS活用法

お久しぶりです。
実は、最近はあまりGoogle Apps Script（GAS）を新しく書いてませんでした。

しかし、ある信販会社のクレジットカード手数料の明細を会計で記録する際、証憑データがCSVではなく、いわゆる神エクセルでした。そのため、この面倒な転記作業をGASで自動化できないかと試行錯誤していました。

その過程で生まれたいくつかの関数たちを供養のためご紹介します。

指定されたフォルダ内のExcelファイル（.xlsx）の一覧を取得する関数

/**
 * 指定されたフォルダ内のExcelファイル（.xlsx）の一覧を取得する関数
 * 
 * @param {string} folderId - Excelファイルを検索するフォルダのID。
 * @returns {Object} - ファイルIDをキーとし、ファイル名を値としたオブジェクト。
 * 
 * @example
 * const folderId = "あなたのフォルダID";
 * const xlsxFiles = getXlsxFilesInFolder_(folderId);
 * // xlsxFilesは、{ "fileId1": "fileName1", "fileId2": "fileName2", ... } の形式のオブジェクトです。
 */

function getXlsxFilesInFolder_(folderId) {
  const folder = DriveApp.getFolderById(folderId);
  const filesIterator = folder.getFilesByType(MimeType.MICROSOFT_EXCEL);
  const objFiles = {};

  while (filesIterator.hasNext()) {
    const file = filesIterator.next();
    const fileName = file.getName();
    const fileId = file.getId();
    objFiles[fileId] = fileName;
  }
  return objFiles;
}

文字通りの関数ですね。

手数料のデータを決まったフォルダに保存しておいて、スプレッドシートに転記できるようにファイルIDとファイル名をオブジェクトで出力させる関数です。

IDをキー、ファイル名を値にして、いわゆる連想配列っぽく作っています。

指定されたExcelファイル（.xlsx）をGoogleスプレッドシートに変換し、指定されたフォルダに保存する関数

/**
 * 指定されたExcelファイル（.xlsx）をGoogleスプレッドシートに変換し、指定されたフォルダに保存する関数
 * 
 * @param {string} xlsxFileId - 変換するExcelファイルのID。
 * @param {string} saveFolderId - 変換後のGoogleスプレッドシートを保存するフォルダのID。
 * @returns {Spreadsheet} - 生成されたGoogleスプレッドシートのオブジェクト。
 * 
 * @example
 * const excelFileId = "あなたのExcelファイルID";
 * const destinationFolderId = "あなたの保存先フォルダID";
 * const newSheet = xlsx2Sheet_(excelFileId, destinationFolderId);
 * // newSheetは新しく生成されたGoogleスプレッドシートのオブジェクトです。
 */

function xlsx2Sheet_(xlsxFileId, saveFolderId) {
  const file = DriveApp.getFileById(xlsxFileId);

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

  // https://developers.google.com/drive/api/reference/rest/v2
  const fileNew = Drive.Files.insert(options, file.getBlob());
  const idFileNew = fileNew.id;
  return SpreadsheetApp.openById(idFileNew);
}

このコードを使用すると、指定したExcelファイルがGoogleスプレッドシートに変換され、指定のフォルダに保存されます。

ちょっとややこしいのが、GASの標準の機能だけではこの変換ができなくて Drive APIというのを有効にしないといけない点。

詳しくはノンプロ研仲間のカワムラさんのnote参照。

[GAS]Excelファイルをスプレッドシートに転記｜カワムラ

与えられたスプレッドシート内のすべてのシートでセルの結合を解除する関数

/**
 * 与えられたスプレッドシート内のすべてのシートでセルの結合を解除する関数
 * 
 * @param {GoogleAppsScript.Spreadsheet.Spreadsheet} ss - セル結合を解除する対象のスプレッドシート。
 * 
 * @example
 * const ss = SpreadsheetApp.getActiveSpreadsheet();
 * breakApartAllCells_(ss);
 */

function breakApartAllCells_(ss) {

  // すべてのシートを取得
  const sheets = ss.getSheets();

  // 各シートのセル結合を解除
  sheets.forEach(sheet => {
    const range = sheet.getDataRange();
    range.breakApart(); // セル結合を解除するメソッド
  });
}

そして神エクセル対策で必須なのがこれ。

後でスプレッドシート関数でデータを参照するにしても、セル結合していると参照先のセル指定が面倒でしょうがない。

あんまり使いたくないけれど、神に出会ってしまったらしょうがないとあきらめて関数化しました。