スプレッドシートでCSVファイルを一括インポート！GASで効率化する方法

2024年12月8日

GoogleスプレッドシートではCSVファイルをインポートすることができますが、ファイルを一つずつ操作する必要があります。

インポートするファイルが複数がある場合は手間と時間がかかります。

そこで、本記事ではGoogle Apps Script（GAS）を活用して複数のCSVファイルを一括でインポートする方法を紹介します。

このGASを使うことで、インポートしたいファイルを選択してまとめて実行できるため効率的です。

完成イメージ

フォルダ選択画面を表示：フォルダIDまたはフォルダ名を入力
ファイルリストの表示：フォルダ内のCSVファイルを選択
インポート：選択したファイルをGoogleスプレッドシートに取り込み

選択したCSVファイルをまとめてインポートし、一つのスプレッドシートファイルに集約します。

手順

STEP

スプレッドシートを開く

CSVをインポートさせるスプレッドシートファイルを開きます。

STEP

GASエディタを開く

Googleスプレッドシートを開き、メニューの「拡張機能」から「Apps Script」をクリックしてGASエディタを開きます。

STEP

GASスクリプトの作成

function myFunction(){

}

が最初から入っているため、消去して以下のスクリプトを貼り付けます。

function showFolderAndFileSelection() {
  const html = HtmlService.createHtmlOutputFromFile("FileSelectionDialog")
    .setWidth(500)
    .setHeight(400);
  SpreadsheetApp.getUi().showModalDialog(html, "フォルダとファイルを選択");
}

function getFilesInFolder(folderInput) {
  let folder;
  try {
    // フォルダIDで取得を試みる
    folder = DriveApp.getFolderById(folderInput);
  } catch (e) {
    // フォルダ名で検索
    const folders = DriveApp.getFoldersByName(folderInput);
    if (!folders.hasNext()) {
      throw new Error("指定されたフォルダが見つかりません: " + folderInput);
    }
    folder = folders.next(); // 最初に見つかったフォルダを使用
  }

  // フォルダ内のファイルを取得
  const files = folder.getFiles();
  const fileList = [];
  while (files.hasNext()) {
    const file = files.next();
    fileList.push({ id: file.getId(), name: file.getName() });
  }
  return fileList;
}

function importSelectedCSVFiles(fileIds) {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const results = [];

  fileIds.forEach(fileId => {
    try {
      const file = DriveApp.getFileById(fileId);
      const content = file.getBlob().getDataAsString("UTF-8"); // UTF-8でデータを読み込む
      
      // 区切り文字を自動検出
      const delimiter = detectDelimiter(content);

      // 区切り文字に基づいてデータを分割
      const rows = splitContentByDelimiter(content, delimiter)
        .filter(row => row.some(cell => cell.trim() !== "")); // 空行を除外

      // 最大列数を取得して列数を揃える
      const maxColumns = Math.max(...rows.map(row => row.length));
      const normalizedRows = rows.map(row => {
        while (row.length < maxColumns) row.push("");
        return row;
      });

      const sheetName = file.getName().replace(/\.[^/.]+$/, ""); // 拡張子を除いた名前
      let sheet = spreadsheet.getSheetByName(sheetName);
      if (!sheet) {
        sheet = spreadsheet.insertSheet(sheetName);
      } else {
        sheet.clear(); // 既存データをクリア
      }

      // データを書き込む
      sheet.getRange(1, 1, normalizedRows.length, normalizedRows[0].length).setValues(normalizedRows);
      sheet.autoResizeColumns(1, sheet.getLastColumn());

      results.push(`成功: ${file.getName()}`);
    } catch (e) {
      results.push(`失敗: ${fileId} (${e.message})`);
    }
  });

  return results.join("\n");
}

// 区切り文字を自動検出
function detectDelimiter(content) {
  const delimiters = [",", "\t", ";"]; // サポートする区切り文字
  const lines = content.split("\n").filter(line => line.trim() !== ""); // 空行を除外

  let bestDelimiter = ",";
  let maxFields = 0;

  delimiters.forEach(delimiter => {
    const fields = lines.map(line => line.split(delimiter).length);
    const avgFields = fields.reduce((a, b) => a + b, 0) / fields.length;

    if (avgFields > maxFields) {
      maxFields = avgFields;
      bestDelimiter = delimiter;
    }
  });

  return bestDelimiter;
}

// 指定された区切り文字でコンテンツを分割
function splitContentByDelimiter(content, delimiter) {
  const lines = content.split("\n").filter(line => line.trim() !== ""); // 空行を除外
  return lines.map(line => line.split(delimiter));
}

スクリプトの説明

showFolderAndFileSelection()

function showFolderAndFileSelection() {
  const html = HtmlService.createHtmlOutputFromFile("FileSelectionDialog")
    .setWidth(500)
    .setHeight(400);
  SpreadsheetApp.getUi().showModalDialog(html, "フォルダとファイルを選択");
}

フォルダとファイルを選択するダイアログを表示します。

getFilesInFolder(folderInput)

function getFilesInFolder(folderInput) {
  let folder;
  try {
    // フォルダIDで取得を試みる
    folder = DriveApp.getFolderById(folderInput);
  } catch (e) {
    // フォルダ名で検索
    const folders = DriveApp.getFoldersByName(folderInput);
    if (!folders.hasNext()) {
      throw new Error("指定されたフォルダが見つかりません: " + folderInput);
    }
    folder = folders.next(); // 最初に見つかったフォルダを使用
  }

入力されたフォルダIDまたはフォルダ名をもとに、フォルダ内のファイルを取得します。

importSelectedCSVFiles(fileIds)

function importSelectedCSVFiles(fileIds) {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const results = [];

  fileIds.forEach(fileId => {
    try {
      const file = DriveApp.getFileById(fileId);
      const content = file.getBlob().getDataAsString("UTF-8"); // UTF-8でデータを読み込む
      
      // 区切り文字を自動検出
      const delimiter = detectDelimiter(content);

      // 区切り文字に基づいてデータを分割
      const rows = splitContentByDelimiter(content, delimiter)
        .filter(row => row.some(cell => cell.trim() !== "")); // 空行を除外

      // 最大列数を取得して列数を揃える
      const maxColumns = Math.max(...rows.map(row => row.length));
      const normalizedRows = rows.map(row => {
        while (row.length < maxColumns) row.push("");
        return row;
      });

      const sheetName = file.getName().replace(/\.[^/.]+$/, ""); // 拡張子を除いた名前
      let sheet = spreadsheet.getSheetByName(sheetName);
      if (!sheet) {
        sheet = spreadsheet.insertSheet(sheetName);
      } else {
        sheet.clear(); // 既存データをクリア
      }

      // データを書き込む
      sheet.getRange(1, 1, normalizedRows.length, normalizedRows[0].length).setValues(normalizedRows);
      sheet.autoResizeColumns(1, sheet.getLastColumn());

      results.push(`成功: ${file.getName()}`);
    } catch (e) {
      results.push(`失敗: ${fileId} (${e.message})`);
    }
  });

  return results.join("\n");
}

選択されたCSVファイルをGoogleスプレッドシートに取り込みます。
区切り文字（カンマやタブ）を自動検出し、シートにデータを整形して書き込みます。

detectDelimiter(content)

// 区切り文字を自動検出
function detectDelimiter(content) {
  const delimiters = [",", "\t", ";"]; // サポートする区切り文字
  const lines = content.split("\n").filter(line => line.trim() !== ""); // 空行を除外

  let bestDelimiter = ",";
  let maxFields = 0;

  delimiters.forEach(delimiter => {
    const fields = lines.map(line => line.split(delimiter).length);
    const avgFields = fields.reduce((a, b) => a + b, 0) / fields.length;

    if (avgFields > maxFields) {
      maxFields = avgFields;
      bestDelimiter = delimiter;
    }
  });

  return bestDelimiter;
}

CSVの区切り文字を自動で検出します。

splitContentByDelimiter(content, delimiter)

// 指定された区切り文字でコンテンツを分割
function splitContentByDelimiter(content, delimiter) {
  const lines = content.split("\n").filter(line => line.trim() !== ""); // 空行を除外
  return lines.map(line => line.split(delimiter));
}

STEP

スクリプトを保存

スクリプトを書いたら、名前を付けて保存します。

（例：「CSVインポート」）

STEP

HTMLファイルを作成

このHTMLファイルは、フォルダ名やIDの入力を受け付け、CSVファイルのリストを表示するダイアログを作成するために使用します。

メニューの「+」をクリックし、「HTML」を選択します。

STEP

HTMLコードを貼り付け

以下のHTMLコードを貼り付けます。

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <style>
      /* フォルダ入力ボックスのスタイルを調整 */
      #folderInput {
        width: 95%; /* 幅を広げる */
        height: 40px; /* 高さを調整 */
        font-size: 16px; /* フォントサイズを大きくする */
        padding: 5px; /* 内側の余白を追加 */
      }

      /* その他のボタンとファイルリストの調整 */
      button {
        font-size: 14px; /* ボタンのフォントサイズを少し大きくする */
        margin-top: 10px; /* ボタン間に余白を追加 */
      }

      #fileList {
        margin-top: 20px; /* ファイルリストの上部に余白を追加 */
        max-height: 300px; /* リストの最大高さを設定してスクロール可能に */
        overflow-y: auto; /* スクロールを有効にする */
      }
    </style>
  </head>
  <body>
    <p>フォルダIDまたはフォルダ名を入力してください:</p>
    <input type="text" id="folderInput" placeholder="フォルダIDまたはフォルダ名">
    <button onclick="fetchFiles()">フォルダ内のファイルを表示</button>
    <div id="fileList"></div>
    <button onclick="importFiles()">選択したファイルをインポート</button>

    <script>
      let selectedFiles = []; // 選択されたファイルのIDを格納

      function fetchFiles() {
        const folderInput = document.getElementById("folderInput").value;
        google.script.run
          .withSuccessHandler(displayFiles)
          .withFailureHandler(showError)
          .getFilesInFolder(folderInput);
      }

      function displayFiles(files) {
        const fileListDiv = document.getElementById("fileList");
        fileListDiv.innerHTML = ""; // リストをクリア
        files.forEach(file => {
          const checkbox = document.createElement("input");
          checkbox.type = "checkbox";
          checkbox.value = file.id;
          checkbox.id = `file-${file.id}`;

          const label = document.createElement("label");
          label.textContent = file.name;
          label.htmlFor = `file-${file.id}`;

          const lineBreak = document.createElement("br");

          fileListDiv.appendChild(checkbox);
          fileListDiv.appendChild(label);
          fileListDiv.appendChild(lineBreak);
        });
      }

      function importFiles() {
        const checkboxes = document.querySelectorAll("input[type=checkbox]:checked");
        selectedFiles = Array.from(checkboxes).map(checkbox => checkbox.value);

        if (selectedFiles.length === 0) {
          alert("ファイルを選択してください！");
          return;
        }

        google.script.run
          .withSuccessHandler(showMessage)
          .withFailureHandler(showError)
          .importSelectedCSVFiles(selectedFiles);
      }

      function showMessage(message) {
        alert(message);
        google.script.host.close();
      }

      function showError(error) {
        alert("エラー: " + error.message);
      }
    </script>
  </body>
</html>

STEP

HTMLコードを保存

HTMLファイルの名前をFileSelectionDialog.htmlに設定して保存します。

スクリプト実行時にこの名前でHTMLファイルを参照します。

STEP

スクリプトを実行

先ほど作成した.gs ファイルを開き、実行をクリックします。

STEP

フォルダIDまたはフォルダ名を入力

スクリプトを実行すると、ダイアログボックスが表示されます。

フォルダIDかフォルダ名を入力して「フォルダ内のファイルを表示」をクリックします。

STEP

インポートするファイルを選択

フォルダ内からインポートするファイルを選択します。

その後、「選択したファイルをインポート」をクリックします。

CSVファイルのデータ容量によってインポートに時間がかかることがあります。

STEP

CSVファイルがインポート

スクリプトが正常に実行されると、成功のメッセージが表示されるとともにCSVファイルがインポートされます。

注意事項

フォルダIDとフォルダ名の使い分け

同じフォルダ名が複数存在する場合、最初に見つかったフォルダが使用されます。

スクリプト実行時間

Google Apps Scriptの制限により、スクリプトの実行時間が6分を超えると実行が中断されます。

データの大きいCSVファイルをまとめてインポートする際はご注意ください。

文字コード

スクリプトはUTF-8でデータを読み込みます。

異なる文字コードの場合、読み込みエラーが発生する可能性があります。

文字コードについて

UTF-8は、Unicode文字（世界中の文字を一意に表現するための標準規格）をエンコードする標準的な形式で、英語や日本語などの多言語に対応した文字コードです。

この形式は広く使用されており、異なるシステム間での互換性が高いのが特徴です。

異なる文字コードの場合、例えばShift_JISやISO-8859-1などで保存されたファイルは正しく読み込めない可能性があります。

この場合、以下の手順でUTF-8に変換することをお勧めします：

オンラインツール：文字コード変換をサポートするオンラインツールを利用して変換
スプレッドシートを使用：ExcelやGoogleスプレッドシートでファイルを開き、「名前を付けて保存」または「ダウンロード」でUTF-8形式を指定して保存。
テキストエディタを使用:メモ帳でファイルを開き、「名前を付けて保存」でエンコードをUTF-8に指定して保存。

GASを使った文字コードの変換方法もありますが、スクリプトを変更する必要があり、やや複雑になります。

GASを使ってShift_JISに対応する：Encoding.jsライブラリを導入してUTF-8に変換。

手順

STEP

Encoding.jsをダウンロード

ライブラリ「Encoding.js」をダウンロードします。

STEP

Google Apps Scriptプロジェクトに追加

Google Apps Scriptのエディタで「ファイル」 > 「新しいスクリプトファイル」を選択し、ファイル名を Encoding.js にします。

Encoding.jsのコードをそのファイルに貼り付けて保存します。

STEP

スクリプトを修正

importSelectedCSVFiles(fileIds)のスクリプトを修正します。

function importSelectedCSV(fileId) {
  try {
    const file = DriveApp.getFileById(fileId);
    const blob = file.getBlob();
    const content = convertShiftJISToUTF8(blob);

    Logger.log("CSV内容:\n" + content); // デバッグ用

    const rows = Utilities.parseCsv(content).filter(row => row.some(cell => cell.trim() !== "")); // 空行を除外

    // 最大列数を取得して列数を揃える
    const maxColumns = Math.max(...rows.map(row => row.length));
    const normalizedRows = rows.map(row => {
      while (row.length < maxColumns) row.push("");
      return row;
    });

    const sheetName = file.getName().replace(/\.[^/.]+$/, ""); // 拡張子を除いた名前
    const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spreadsheet.getSheetByName(sheetName);
    if (!sheet) {
      sheet = spreadsheet.insertSheet(sheetName);
    } else {
      sheet.clear(); // 既存データをクリア
    }

    // データを書き込む
    sheet.getRange(1, 1, normalizedRows.length, normalizedRows[0].length).setValues(normalizedRows);
    sheet.autoResizeColumns(1, sheet.getLastColumn());

    return `CSVファイル「${file.getName()}」を正常にインポートしました。`;
  } catch (e) {
    Logger.log(`エラー: ${e.message}`);
    throw new Error("ファイルのインポート中にエラーが発生しました: " + e.message);
  }
}

// Shift_JISをUTF-8に変換する関数
function convertShiftJISToUTF8(blob) {
  const encodedBytes = blob.getBytes(); // バイナリデータを取得
  const decodedText = Encoding.convert(encodedBytes, {
    to: 'UNICODE',
    from: 'SJIS',
    type: 'string'
  });
  return decodedText;
}