I am currently new to the google sheet script. I'm doing a simple report generator in google sheet using script. I already have a script that saves a generated pdf file to the google drive. In my google sheet there is a cell referenced to the main data source with dropdown data validation selecting the email of the person and will generate the other details (it a reference also based from that email in the same sheet). what I want is, when I run the script to save pdf file, it should automatically generate pdf file for each item in the dropdown validation list. Is this possible? Anyone who know this please.
this is my code that generate pdf file to the drive (i got this also from the net and made some changes)... thank you so much in advance.
var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheet2 = ss.getSheetByName('ID');
var gdid1 = sheet2.getRange('B1').getValue();
var gdid2 = sheet2.getRange('B4').getValue();
function onOpen() {
var ui = SpreadsheetApp.getUi()
ui.createMenu('ExportGDrive')
.addItem('Export BasicED', 'exportBasicED')
.addItem('Export College', 'exportCollege')
.addToUi()
}
function _exportBasic(blob, fileName, spreadsheet) {
blob = blob.setName(fileName)
var folder = DriveApp.getFolderById(gdid1)
var pdfFile = folder.createFile(blob)
// Display a modal dialog box with custom HtmlService content.
const htmlOutput = HtmlService
.createHtmlOutput('<p>Click to open <a href="' + pdfFile.getUrl() + '" target="_blank">' + fileName + '</a></p>')
.setWidth(300)
.setHeight(80)
SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Export Successful')
}
function _exportCollege(blob, fileName, spreadsheet) {
blob = blob.setName(fileName)
var folder = DriveApp.getFolderById(gdid2)
var pdfFile = folder.createFile(blob)
// Display a modal dialog box with custom HtmlService content.
const htmlOutput = HtmlService
.createHtmlOutput('<p>Click to open <a href="' + pdfFile.getUrl() + '" target="_blank">' + fileName + '</a></p>')
.setWidth(300)
.setHeight(80)
SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Export Successful')
}
function exportAsPDF() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
var blob = _getAsBlob(spreadsheet.getUrl())
_exportBlob(blob, spreadsheet.getName(), spreadsheet)
}
function _getAsBlob(url, sheet, range) {
var rangeParam = ''
var sheetParam = ''
if (range) {
rangeParam =
'&r1=' + (range.getRow() - 1)
+ '&r2=' + range.getLastRow()
+ '&c1=' + (range.getColumn() - 1)
+ '&c2=' + range.getLastColumn()
}
if (sheet) {
sheetParam = '&gid=' + sheet.getSheetId()
}
// A credit to https://gist.github.com/Spencer-Easton/78f9867a691e549c9c70
// these parameters are reverse-engineered (not officially documented by Google)
// they may break overtime.
var exportUrl = url.replace(/\/edit.*$/, '')
+ '/export?exportFormat=pdf&format=pdf'
+ '&size=LETTER'
+ '&portrait=true'
+ '&fitw=true'
+ '&top_margin=0.75'
+ '&bottom_margin=0.75'
+ '&left_margin=0.7'
+ '&right_margin=0.7'
+ '&sheetnames=false&printtitle=false'
+ '&pagenum=UNDEFINED' // change it to CENTER to print page numbers
+ '&gridlines=FALSE'
+ '&fzr=FALSE'
+ sheetParam
+ rangeParam
Logger.log('exportUrl=' + exportUrl)
var response
var i = 0
for (; i < 5; i += 1) {
response = UrlFetchApp.fetch(exportUrl, {
muteHttpExceptions: true,
headers: {
Authorization: 'Bearer ' + ScriptApp.getOAuthToken(),
},
})
if (response.getResponseCode() === 429) {
// printing too fast, retrying
Utilities.sleep(3000)
} else {
break
}
}
if (i === 5) {
throw new Error('Printing failed. Too many sheets to print.')
}
return response.getBlob()
}
function exportCurrentSheetAsPDF() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
var currentSheet = SpreadsheetApp.getActiveSheet()
// Change the Cell for the name of the file (example: B3 for current sheet)
var blob = _getAsBlob(spreadsheet.getUrl(), currentSheet)
_exportBlob(blob, SpreadsheetApp.getActiveSheet().getRange('B3').getValue(), spreadsheet)
}
function exportBasicED() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
var sheet1 = spreadsheet.getSheetByName('Generate-Basic Ed');
var name = sheet1.getRange('B3').getValue();
// Change the Cell for the name of the file (example: B3 for current sheet)
var blob = _getAsBlob(spreadsheet.getUrl(), sheet1)
_exportBasic(blob, name, spreadsheet)
}
function exportCollege() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
var sheet1 = spreadsheet.getSheetByName('Generate-College');
var name = sheet1.getRange('B3').getValue();
// Change the Cell for the name of the file (example: B3 for current sheet)
var blob = _getAsBlob(spreadsheet.getUrl(), sheet1)
_exportCollege(blob, name, spreadsheet)
}