スプレッドシートのデータからExcelを大量生産しよう

概要

こんな話があったとしましょう・・・

毎月この請求リストのデータを

このテンプレートに転記して請求書を作っているが、手間なので楽に作れないだろうか。

というリクエスト。
いいでしょう、夕方までにつくって御覧に見せましょう。

GASで作ろう

GASでスプレッドシートからExcelへの変換APIを叩いて、それをGoogle Driveに保存する作戦です。

GASでAPIアクセスするにはこの関数を使います。
Class UrlFetchApp  |  Apps Script  |  Google Developers

コード

function generateSpreadsheet() {
  // 出力元データとテンプレートがあるスプレッドシート
  const sourceSpreadsheet = SpreadsheetApp.openById('SPREADSHEET_ID');
  const sourceSheet = sourceSpreadsheet.getSheetByName('データ');
  const templateSheet = sourceSpreadsheet.getSheetByName('テンプレート');

  // 出力先フォルダ
  const outputFolder = DriveApp.getFolderById('SPREADSHEET_OUTPUT_ID');

  // 出力対象データ
  const sourceDataArray = sourceSheet
    .getRange(2, 1, sourceSheet.getLastRow() - 1, sourceSheet.getLastColumn())
    .getValues();
  for (let i = 0; i < sourceDataArray.length; i++) {
    // 請求先名
    const companyName = sourceDataArray[i][0];

    // 出力スプレッドシートの名称、シート名
    const newSpreadSheetName = companyName + '様_請求書';
    const newSheetName = '請求書';

    // スプレッドシートの生成
    // 新規作成して出力対象のフォルダに移動する
    const tempSpreadSheet = SpreadsheetApp.create(newSpreadSheetName);
    const originalFile = DriveApp.getFileById(tempSpreadSheet.getId());
    const copiedFile = originalFile.makeCopy(newSpreadSheetName, outputFolder);
    DriveApp.getRootFolder().removeFile(originalFile);

    // データの転記
    const newSpreadsheet = SpreadsheetApp.openById(copiedFile.getId());
    const newSheet = templateSheet.copyTo(newSpreadsheet).setName(newSheetName);

    newSheet.getRange(3, 1).setValue(sourceDataArray[i][0]); // 請求先
    newSheet.getRange(5, 3).setValue(sourceDataArray[i][1]); // 請求金額
    newSheet.getRange(7, 4).setValue(sourceDataArray[i][2]); // 支払期限

    // スプレッドシート作成時の空白の新規シートを削除
    const deleteSheets = newSpreadsheet.getSheets();
    newSpreadsheet.deleteSheet(deleteSheets[0]);
  }
}

// フォルダ内のスプレッドシートをExcelに変更します
function convertToExcel() {
  // 変換元取得フォルダ
  const sourceSpreadsheetFolder = DriveApp.getFolderById('SPREADSHEET_OUTPUT_ID');
  // エクセル出力先
  const excelFolder = DriveApp.getFolderById('EXCEL_OUTPUT_ID');

  const spreadsheetFileArray = sourceSpreadsheetFolder.getFiles();

  while (spreadsheetFileArray.hasNext()) {
    // fetch先の構成
    const id = spreadsheetFileArray.next().getId();
    const url = 'https://docs.google.com/feeds/download/spreadsheets/Export?key=' + id + '&amp;exportFormat=xlsx';
    const params = {
      headers: { Authorization: 'Bearer ' + ScriptApp.getOAuthToken() },
      muteHttpExceptions: true,
    };

    // Excelファイル変換して元のスプレッドシートを削除
    const excelFileName = SpreadsheetApp.openById(id).getName();
    const excelFile = UrlFetchApp.fetch(url, params).getBlob().setName(excelFileName);
    excelFolder.createFile(excelFile);
    sourceSpreadsheetFolder.removeFile(DriveApp.getFileById(id));
  }
}

シートの構成

請求一覧。
この一行ずつで請求書を作成します。

転記する先のフォーマット。
GASで請求一覧のデータを転記して、個別のスプレッドシートを作ります。

あらかじめ、Excelの出力先と一時的にスプレッドシートが生成されるフォルダを作っておきます。

請求先ごとのスプレッドシート生成

function generateSpreadsheet()

ここで請求一覧と転記する先のフォーマットを読み込んでおきます。

// 出力元データとテンプレートがあるスプレッドシート
const sourceSpreadsheet = SpreadsheetApp.openById('SPREADSHEET_ID');
const sourceSheet = sourceSpreadsheet.getSheetByName('データ');
const templateSheet = sourceSpreadsheet.getSheetByName('テンプレート');

請求一覧のヘッダを抜いた、データ部分を取得して行数分ループします。

// 出力対象データ
const sourceDataArray = sourceSheet.getRange(2, 1, sourceSheet.getLastRow() - 1, sourceSheet.getLastColumn()).getValues();
for (let i = 0; i < sourceDataArray.length; i++) {

スプレッドシートを新規作成して、指定のGoogle Driveに保存するためにコピーして、元を削除します。

// スプレッドシートの生成
// 新規作成して出力対象のフォルダに移動する
const tempSpreadSheet = SpreadsheetApp.create(newSpreadSheetName);
const originalFile = DriveApp.getFileById(tempSpreadSheet.getId());
const copiedFile = originalFile.makeCopy(newSpreadSheetName, outputFolder);
DriveApp.getRootFolder().removeFile(originalFile);

新しく作成したスプレッドシートのテンプレートに各種データを転記します。
テンプレートの位置を直指定です。

// データの転記
const newSpreadsheet = SpreadsheetApp.openById(copiedFile.getId());
const newSheet = templateSheet.copyTo(newSpreadsheet).setName(newSheetName);

newSheet.getRange(3, 1).setValue(sourceDataArray[i][0]); // 請求先
newSheet.getRange(5, 3).setValue(sourceDataArray[i][1]); // 請求金額
newSheet.getRange(7, 4).setValue(sourceDataArray[i][2]); // 支払期限

スプレッドシートを新規作成したときの シート1 な不要シートを削除します。
シートの一番左が不要シートな前提です。

// スプレッドシート作成時の空白の新規シートを削除
const deleteSheets = newSpreadsheet.getSheets();
newSpreadsheet.deleteSheet(deleteSheets[0]);

こんな感じでデータの行ごとにスプレッドシートが作成されます。

中身はこんな感じ。
無事、転記されてますね。

Excelの生成

function convertToExcel()

先ほど生成した個別のスプレッドシートが格納されたフォルダと出力先のフォルダを読み込んでおきます。

// 変換元取得フォルダ
const sourceSpreadsheetFolder = DriveApp.getFolderById('SPREADSHEET_OUTPUT_ID');
// エクセル出力先
const excelFolder = DriveApp.getFolderById('EXCEL_OUTPUT_ID');

個別スプレッドシートのファイルを取得して、whileですべて変換処理をしていきます。

const spreadsheetFileArray = sourceSpreadsheetFolder.getFiles();

while (spreadsheetFileArray.hasNext()) {

UrlFetchAppのfetchに渡す値を作っておきます。
APIのURL末尾のxlsxをpdfに変えるとPDFで生成できます。
これは便利。

// fetch先の構成
const id = spreadsheetFileArray.next().getId();
const url = 'https://docs.google.com/feeds/download/spreadsheets/Export?key=' + id + '&exportFormat=xlsx';
const params = {
  headers: { Authorization: 'Bearer ' + ScriptApp.getOAuthToken() },
  muteHttpExceptions: true,
};

最後にAPIを叩いてファイルをフォルダに格納していきます。
元のスプレッドシートを削除して処理は完了です。

// Excelファイル変換して元のスプレッドシートを削除
const excelFileName = SpreadsheetApp.openById(id).getName();
const excelFile = UrlFetchApp.fetch(url, params).getBlob().setName(excelFileName);
excelFolder.createFile(excelFile);
sourceSpreadsheetFolder.removeFile(DriveApp.getFileById(id));

こんな感じでExcelが生成されますので、ダウンロードして請求先に送りましょう。

さらに、送信業務もGASで効率化しておくと喜ばれますね。

takeda-san.hatenablog.com

考慮事項

最低限の例なので、もうちょっと考えるべきことがあります。

• 処理の再実行を考慮する(同名のファイルは削除してから再度作成する)
• 個別の作成を考慮する(あとから滑り込みで請求したいものがよくある)
• 6分の壁対応(一回の実行で処理時間6分が上限なので、1Excelに対して1実行にする)

などなど。

請求書のSaaSを使いなさいよ

それはそうですが、ユーザの心境的には今困ってるので今解決してほしいでしょう。
そんなときにGASでサクッと作れると喜ばれますね。
欲しいものを欲しい時に提供していきましょう。
(この仕組みが好評で要件が複雑になるようなら、SaaSなりに置き換えも検討しましょう)