【GAS】スプレッドシート⇒エクセル変換時短術

表計算といえば、Googleスプレッドシートが主流になりつつあります。

しかし、取引先への報告など、どうしてもエクセル形式が必要な場面も少なくありません。

そんな時、GASを使えば必要なデータだけカンタンにエクセルに変換してメールで送ることができます。

＼GASをつかって効率化！！／

Excel形式が必要な理由

スプレッドシートはアクセス制限の設定により、データの閲覧制限がかけられます。

一方、エクセルはパスワード設定が可能なので、外部への共有にも安心です。

また、組織内の共有はスムーズですが、外部への共有においては、エクセルの場合が有効。

エクセル出力も可能だが問題アリ

スプレッドシートからエクセルへの変換はGASを使わずともカンタンにおこなうことができます。

ファイルタブからクリックひとつでエクセルでエクスポートが可能。ただ、これだと一つ問題が発生。

不要なシートもエクセルに

スプレッドシート内、各シート間を関数を使って連携させてる場合があります。

共有したいシートはこのシート、っていう場合に余分なシートまでくっついてきます。

スクリプトを実行させるための準備

実際のスクリプトはこちら

2種類のスクリプトを紹介します。

エクセルに変換してドライブへ保存

1. 1ヶ月前の日付を生成
2. 新しいスプレッドシートを作成
3. 既存のシートを新しいスプレッドシートにコピー
4. 不要なシートを削除
5. Excel形式に変換
6. Googleドライブに保存
7. 不要になったスプレッドシートを削除

function sheets_Excel_convert() {
  // 現在のスプレッドシートを取得
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  // シート「sheetName」を取得
  let sheet = ss.getSheetByName('sheetName');

  // 処理内容をログに出力
  Logger.log(sheet.getName());

  // 1ヶ月前の日付を生成
  let date = new Date();
  let month = date.getMonth();
  date.setMonth(month - 1);
  let lastMonth = Utilities.formatDate(date, 'JST', 'YYYY-MM_');
  let fileName = lastMonth + '〇〇リスト';

  // **GASからDrive APIを利用するための設定**
  // 1. プロジェクトを開いて、上部の「リソース」 - 「Googleの拡張サービス」 - 「Drive API」をONにする。
  // 2. Googleデベロッパーコンソールのライブラリにて「Google Drive API」を検索して有効にする。

  // 新しいスプレッドシートを作成
  let new_ss = Drive.Files.insert({
    "title": fileName,
    "mimeType": "application/vnd.google-apps.spreadsheet",
    "parents": [{"id": 'FOLDER_ID'}]
  });

  // 新しいスプレッドシートのIDを取得
  let ssid = SpreadsheetApp.openById(new_ss.id);
  let getid = ssid.getId();
  Logger.log(ssid.getName());

  // 現在のシートから新しいシートに特定のシートをコピー
  sheet.copyTo(ssid);

  // シート名の変更と不要なシートの削除
  let export_sheet = ssid.getSheetByName('sheetName のコピー');
  export_sheet.setName('sheetName');
  let deletesheet = ssid.getSheets();
  ssid.deleteSheet(deletesheet[0]);

  // Excelに変換後、不要になったスプレッドシートは削除
  let fldid = 'FOLDER_ID'; // ブランクの場合はマイドライブ直下に
  let fetchUrl = 'https://docs.google.com/feeds/download/spreadsheets/Export?key=' + getid + '&exportFormat=xlsx';
  let fetchOpt = {
    'headers': {
      Authorization: 'Bearer ' + ScriptApp.getOAuthToken()
    },
    'muteHttpExceptions': true
  };

  // ファイルダウンロードと保存
  let file = UrlFetchApp.fetch(fetchUrl, fetchOpt).getBlob().setName(fileName);
  DriveApp.getFolderById(fldid).createFile(file);
  DriveApp.getFolderById(fldid).removeFile(DriveApp.getFileById(getid));
}

Googleドライブ経由せずエクセルをメール添付

1. 1ヶ月前の日付を生成し、ファイル名に含める
2. アクティブなシートを別のスプレッドシートにコピー
3. コピーしたシート名を変更
4. 特定のシートのデータを別のシートに貼り付け
5. 特定のシートをExcel形式に変換
6. Gmailの下書きに添付ファイルとして送信

function sheetCopy_anotherfile() {
  // 1ヶ月前の日付を生成
  const date = new Date();
  const month = date.getMonth();
  date.setMonth(month - 1);
  const lastMonth = Utilities.formatDate(date, 'JST', 'YYYY-MM_');
  const fileName = lastMonth + '◯◯リスト';

  // コピー元のシートを取得
  const copySheet = SpreadsheetApp.getActiveSheet();

  // コピー先のスプレッドシートを取得
  const destSpreadsheet = SpreadsheetApp.openById('SPREADSHEET_ID');

  // コピー元のシートをコピー先のスプレッドシートにコピー
  const newCopySheet = copySheet.copyTo(destSpreadsheet);

  // コピーしたシート名を変更
  newCopySheet.setName(fileName);

  // データ貼り付け処理
  const targetSheetName = fileName;
  const targetSheet = destSpreadsheet.getSheetByName(targetSheetName);

  if (targetSheet) {
    // 最終行・列を取得
    const rows = targetSheet.getLastRow();
    const columns = targetSheet.getLastColumn();

    // 全範囲を取得
    const range = targetSheet.getRange(1, 1, rows, columns);

    // ソースの値と書式を取得
    const sourceValues = range.getValues();
    const sourceFormats = range.getBackgrounds();

    // 値を貼り付け
    range.setValues(sourceValues);

    // 書式を貼り付け
    range.setBackgrounds(sourceFormats);
  } else {
    Logger.log('指定された名前のシートが見つかりませんでした。');
  }

  // Excel変換処理
  const sheetName = fileName;
  const excelBlob = spreadsheetToExcelBlob(destSpreadsheet, sheetName);

  // Gmail下書き作成処理
  createDraftWithAttachment(excelBlob);
}

// Googleスプレッドシートの特定のシートをエクセル形式に変換する関数
function spreadsheetToExcelBlob(spreadsheet, sheetName) {
  const sheet = spreadsheet.getSheetByName(sheetName);
  const url = `https://docs.google.com/spreadsheets/d/${spreadsheet.getId()}/export?format=xlsx&gid=${sheet.getSheetId()}`;
  const params = {
    method: 'GET',
    headers: {
      Authorization: `Bearer ${ScriptApp.getOAuthToken()}`,
    },
    muteHttpExceptions: true,
  };
  const blob = UrlFetchApp.fetch(url, params).getBlob();
  return blob;
}

// Gmailの下書きに添付ファイルを含むメールを作成する関数
function createDraftWithAttachment(blob) {
  const recipient = 'googlepixel@example.com'; // 宛先のメールアドレス
  const subject = 'Subject'; // メールの件名
  const body = 'Body'; // メールの本文

  // 添付ファイルの名前
  const attachmentName = '○○リスト';

  // Blob オブジェクトにファイル名を設定
  blob.setName(attachmentName);

  // 添付ファイル付きのメールを作成
  const emailOptions = {
    attachments: [blob],
    name: Session.getActiveUser().getEmail(),
    htmlBody: body,
  };

  // 下書きを作成
  const draftBody = `To: ${recipient}\nSubject: ${subject}\n\n${body}`;
  GmailApp.createDraft('', '', '', { htmlBody: draftBody, attachments: [blob] });
}

作成したスクリプトを試してみて

今回は2つのスクリプトを試してみました。

1. エクセルにしてドライブ保存
2. 下書きメールにエクセル添付

エクセルに変換する際は、

• 値貼付けをしてリンクは外しておく
• パスワード保護が必要なシートは避ける

定例報告などを、組織外に向けて効率よく連絡する際は、このスクリプトは有効活用できますね！！

＼GASは学ぶと超便利！！／

Google Apps Script～初心者のための完全ガイド～ローコード開発の手法とGASの具体的な利用方法について初心者向けにわかりやすく解説します。ローコード開発のメリットや実際のビジネス事例を紹介し、今後の展望も探ります。初心者向けに始め方と学習リソースも提供します。...