GASで見積書をテンプレートから自動作成する方法

const HEADER_SHEET_NAME = '見積依頼';
const PRICE_SHEET_NAME = '単価マスタ';
const DETAIL_SHEET_NAME = '見積明細';
const TEMPLATE_SHEET_NAME = '見積書';
const OUTPUT_FOLDER_ID = 'ここにドライブの保存先フォルダIDを入れる';

const TEMPLATE_ITEM_START_ROW = 10; // 見積書シートの明細1行目
const TEMPLATE_ITEM_MAX_ROWS = 15; // 明細欄に確保している最大行数
const TAX_RATE = 0.1;

// 見積依頼シートの列（A列から順に0,1,2...）
const HEADER_COL = {
  quoteNumber: 0,
  customerName: 1,
  status: 2,
};

// 見積明細シートの列
const DETAIL_COL = {
  quoteNumber: 0,
  itemCode: 1,
  quantity: 2,
};

// 単価マスタシートの列
const PRICE_COL = {
  itemCode: 0,
  itemName: 1,
  unitPrice: 2,
};

function createQuotesFromRequests() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const headerSheet = ss.getSheetByName(HEADER_SHEET_NAME);
  const priceMap = buildPriceMap(ss.getSheetByName(PRICE_SHEET_NAME));
  const detailRows = ss.getSheetByName(DETAIL_SHEET_NAME).getDataRange().getValues();
  const headerRows = headerSheet.getDataRange().getValues();

  for (let i = 1; i < headerRows.length; i++) {
    const row = headerRows[i];
    if (row[HEADER_COL.status] === '発行済') {
      continue;
    }

    const items = pickItemsForQuote(detailRows, row[HEADER_COL.quoteNumber], priceMap);
    generateQuotePdf(ss, row[HEADER_COL.quoteNumber], row[HEADER_COL.customerName], items);
    headerSheet.getRange(i + 1, HEADER_COL.status + 1).setValue('発行済');
  }
}

function buildPriceMap(priceSheet) {
  const rows = priceSheet.getDataRange().getValues();
  const map = {};
  for (let i = 1; i < rows.length; i++) {
    const row = rows[i];
    map[row[PRICE_COL.itemCode]] = {
      itemName: row[PRICE_COL.itemName],
      unitPrice: row[PRICE_COL.unitPrice],
    };
  }
  return map;
}

function pickItemsForQuote(detailRows, quoteNumber, priceMap) {
  const items = [];
  for (let i = 1; i < detailRows.length; i++) {
    const row = detailRows[i];
    if (row[DETAIL_COL.quoteNumber] !== quoteNumber) {
      continue;
    }

    const price = priceMap[row[DETAIL_COL.itemCode]];
    items.push({
      itemName: price.itemName,
      quantity: row[DETAIL_COL.quantity],
      unitPrice: price.unitPrice,
    });
  }
  return items;
}

function generateQuotePdf(ss, quoteNumber, customerName, items) {
  if (items.length > TEMPLATE_ITEM_MAX_ROWS) {
    throw new Error(`見積番号 ${quoteNumber} の明細行数がテンプレートの上限 ${TEMPLATE_ITEM_MAX_ROWS} 行を超えています`);
  }

  const templateSheet = ss.getSheetByName(TEMPLATE_SHEET_NAME);
  templateSheet.getRange(3, 2).setValue(customerName);
  templateSheet.getRange(4, 2).setValue(quoteNumber);
  writeItemRows(templateSheet, items);
  writeTaxSummary(templateSheet, items);
  SpreadsheetApp.flush();

  const pdfBlob = exportSheetAsPdf(ss, templateSheet);
  const folder = DriveApp.getFolderById(OUTPUT_FOLDER_ID);
  folder.createFile(pdfBlob).setName(`見積書_${quoteNumber}.pdf`);
}

function writeItemRows(templateSheet, items) {
  for (let i = 0; i < TEMPLATE_ITEM_MAX_ROWS; i++) {
    const row = TEMPLATE_ITEM_START_ROW + i;
    if (i < items.length) {
      const item = items[i];
      const amount = item.quantity * item.unitPrice;
      templateSheet.getRange(row, 2, 1, 4).setValues([[item.itemName, item.quantity, item.unitPrice, amount]]);
    } else {
      templateSheet.getRange(row, 2, 1, 4).clearContent();
    }
  }
}

function writeTaxSummary(templateSheet, items) {
  const subtotal = items.reduce((sum, item) => sum + item.quantity * item.unitPrice, 0);
  // 端数処理は切り捨てで実装。切り上げ・四捨五入にする場合は社内ルールに合わせてここを変更する
  const tax = Math.floor(subtotal * TAX_RATE);
  const summaryRow = TEMPLATE_ITEM_START_ROW + TEMPLATE_ITEM_MAX_ROWS + 1;
  templateSheet.getRange(summaryRow, 5).setValue(subtotal);
  templateSheet.getRange(summaryRow + 1, 5).setValue(tax);
  templateSheet.getRange(summaryRow + 2, 5).setValue(subtotal + tax);
}

function exportSheetAsPdf(ss, sheet) {
  const url = `https://docs.google.com/spreadsheets/d/${ss.getId()}/export`
    + `?format=pdf&gid=${sheet.getSheetId()}`
    + '&size=A4&portrait=true&fitw=true&gridlines=false&printtitle=false';
  const response = UrlFetchApp.fetch(url, {
    headers: { Authorization: `Bearer ${ScriptApp.getOAuthToken()}` },
  });
  return response.getBlob();
}