Last active
August 2, 2024 09:45
-
-
Save baku89/5a53ad6d41737c1371f947e9536c35d1 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| const sheetInfo = new Map([ | |
| ['見積書', { range: 'A1:I38' }], | |
| ['選定理由書', { range: 'A1:H40' }], | |
| ['発注書', { range: 'A1:I38' }], | |
| ['注文請書', { range: 'A1:I38' }], | |
| ['出張予定表', { range: 'A1:H51' }], | |
| ['旅費計算書', { range: 'A1:L23' }], | |
| ['納品書', { range: 'A1:I39' }], | |
| ['検収書', { range: 'A1:I38' }], | |
| ['検収書', { range: 'A1:I38' }], | |
| ['出張報告書', { range: 'A1:H51' }], | |
| ['請求書', { range: 'A1:I38' }], | |
| ['領収書', { range: 'A1:I38' }], | |
| ]) | |
| function exportPDFs() { | |
| const doc = SpreadsheetApp.getActiveSpreadsheet() | |
| const descriptorSheet = doc.getSheetByName('採択者情報') | |
| const documentListSheet = doc.getSheetByName('証憑書類整理一覧表') | |
| // Retrieve the selected rows | |
| const rows = selectedRows(documentListSheet) | |
| // Collect the PDF information to export | |
| const pdfInfos = rows | |
| .flatMap((row) => { | |
| // B行: 通番 | |
| const index = documentListSheet.getRange(row, 2).getValue() | |
| // F行: 費用種別 | |
| const kind = documentListSheet.getRange(row, 6).getValue() | |
| // F行: 費用No. | |
| const kindNo = documentListSheet.getRange(row, 7).getValue() | |
| // I行: 内訳枝番 | |
| const branch = documentListSheet.getRange(row, 9).getValue() | |
| //AD行: 必要書類リストを元に、PDFの枚数だけ情報を生成 | |
| return documentListSheet | |
| .getRange(row, 30) | |
| .getValue() | |
| .split(',') | |
| .map((name) => name.trim()) | |
| .filter((name) => sheetInfo.has(name)) | |
| .map((sheetName) => { | |
| const { range } = sheetInfo.get(sheetName) | |
| const filename = `${kind}_${branch}_${sheetName}.pdf` | |
| const month = parseInt(index.split('-')[0]) | |
| const year = month >= 6 ? 2024 : 2025 | |
| const monthFolder = `${year}年${month}月` | |
| const kindFolder = kindNo.toString().padStart(2, '0') + '_' + kind | |
| return { | |
| index, | |
| sheetName, | |
| range, | |
| filename, | |
| monthFolder, | |
| kindFolder, | |
| } | |
| }) | |
| }) | |
| .flat() | |
| // Retrieve the folder instance by the id | |
| const folderId = descriptorSheet.getRange('B9').getValue() | |
| const folder = DriveApp.getFolderById(folderId) | |
| // Generate the PDF blobs | |
| pdfInfos.forEach((info, i) => { | |
| const path = `${info.monthFolder}/${info.kindFolder}/${info.filename}` | |
| Logger.log(`Exporting ${path}... (${i + 1} / ${pdfInfos.length})`) | |
| const pdf = getPdfBlob(info.index, info.sheetName, info.range) | |
| pdf.setName(info.filename) | |
| // Create a directory if it does not exist | |
| const monthFolder = getOrCreateFolder(folder, info.monthFolder) | |
| const kindFolder = getOrCreateFolder(monthFolder, info.kindFolder) | |
| // Delete the existing file | |
| const files = kindFolder.getFilesByName(info.filename) | |
| while (files.hasNext()) { | |
| files.next().setTrashed(true) | |
| } | |
| kindFolder.createFile(pdf) | |
| }) | |
| // const zipBlob = Utilities.zip(pdfBlobs, 'all.zip') | |
| // const downloadURL = DriveApp.createFile(zipBlob).getDownloadUrl() | |
| // const htmlOutput = HtmlService.createHtmlOutput(` | |
| // <html><body> | |
| // <a href="${downloadURL}" target="_blank">Download All ZIP</a> | |
| // </body></html> | |
| // `) | |
| // const ui = SpreadsheetApp.getUi() | |
| // ui.showModalDialog(htmlOutput, 'Download ZIP') | |
| function getOrCreateFolder(parent, name) { | |
| var folders = parent.getFoldersByName(name) | |
| if (folders.hasNext()) { | |
| return folders.next() | |
| } else { | |
| return parent.createFolder(name) | |
| } | |
| } | |
| function selectedRows(sheet) { | |
| const range = sheet.getActiveRange() | |
| // 行番号のリストを取得 | |
| const startRow = range.getRow() | |
| const numRows = range.getNumRows() | |
| const rows = [] | |
| for (let i = 0; i < numRows; i++) { | |
| rows.push(startRow + i) | |
| } | |
| return rows | |
| } | |
| function encodeQueryParams(params) { | |
| return Object.keys(params) | |
| .map(function (key) { | |
| return encodeURIComponent(key) + '=' + encodeURIComponent(params[key]) | |
| }) | |
| .join('&') | |
| } | |
| function getPdfBlob(index, sheetName, rangeNotation) { | |
| // 通番をセット | |
| descriptorSheet.getRange('B1').setValue(index) | |
| SpreadsheetApp.flush() | |
| const sheet = doc.getSheetByName(sheetName) | |
| const range = sheet.getRange(rangeNotation) | |
| const url = `https://docs.google.com/spreadsheets/d/${doc.getId()}/export?` | |
| const queryString = encodeQueryParams({ | |
| exportFormat: 'pdf', | |
| format: 'pdf', | |
| size: 'A4', | |
| portrait: true, | |
| fith: true, | |
| top_margin: '0.50', | |
| bottom_margin: '0.50', | |
| left_margin: '0.50', | |
| right_margin: '0.50', | |
| sheetnames: false, | |
| printtitle: false, | |
| pagenumbers: false, | |
| gridlines: false, | |
| fzr: false, | |
| gid: sheet.getSheetId(), | |
| range: range.getA1Notation(), | |
| }) | |
| const token = ScriptApp.getOAuthToken() | |
| const response = UrlFetchApp.fetch(url + queryString, { | |
| headers: { | |
| Authorization: 'Bearer ' + token, | |
| }, | |
| }) | |
| const pdfBlob = response.getBlob() | |
| // Wait for a while to avoid the error "Exception: Service invoked too many times for one day: urlfetch." | |
| Utilities.sleep(6000) | |
| return pdfBlob | |
| } | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment