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

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

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

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

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

Excel形式が必要な理由

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

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

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

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

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

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

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

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

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

Google Apps Script目的別リファレンス　実践サンプルコード付き

created by Rinker

¥3,080 (2024/05/09 19:25:08時点 楽天市場調べ-詳細)

• Amazon
• 楽天市場
• Yahooショッピング

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

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

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] });
}

Google Apps Script目的別リファレンス　実践サンプルコード付き

created by Rinker

¥3,080 (2024/05/09 19:25:08時点 楽天市場調べ-詳細)

• Amazon
• 楽天市場
• Yahooショッピング

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

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

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

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

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

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

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

【ローコード開発ツール】GASでプログラミング体験！！ iPhoneからGoogle Pixel6aに変更してかから3ヶ月。めっきりGoogleのさまざまなサービスを使おうと必死になっており...