Google appscript to convert worksheets to PDF

Question

I am new to google appscript and I am facing an issue. I have a script that converts the current google sheet to PDF. However I want to convert all the sheets to PDF except the first two sheets. Below is my code.

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Create PDF')
    .addItem('Create PDF', 'exportCurrentSheetAsPDF')
    
    .addToUi()
  SpreadsheetApp.getUi()
    .createMenu('Create all PDF')
    .addItem('Create all PDF', 'exportAllSheetsAsSeparatePDFs')
    
    .addToUi()
}
function _exportBlob(blob, fileName) {
  blob = blob.setName(fileName)
  var pdfFile = DriveApp.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 _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()
  }
  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=false'
      + '&gridlines=true'
      + '&fzr=FALSE'      
      + sheetParam
      + rangeParam
      
  Logger.log('exportUrl=' + exportUrl)
  var response = UrlFetchApp.fetch(exportUrl, {
    headers: { 
      Authorization: 'Bearer ' +  ScriptApp.getOAuthToken(),
    },
  })
  
  return response.getBlob()
}



function exportCurrentSheetAsPDF() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  var currentSheet = SpreadsheetApp.getActiveSheet()
  
  var blob = _getAsBlob(spreadsheet.getUrl(), currentSheet)
  _exportBlob(blob, currentSheet.getName())
}

function exportAllSheetsAsSeparatePDFs() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  var files = []
  spreadsheet.getSheets().forEach(function (sheet) {
    spreadsheet.setActiveSheet(sheet)
    
    var blob = _getAsBlob(spreadsheet.getUrl(), sheet)
    var fileName = sheet.getName()
    blob = blob.setName(fileName)
    var pdfFile = DriveApp.createFile(blob)
    
    files.push({
      url: pdfFile.getUrl(),
      name: fileName,
    })
  })
    const htmlOutput = HtmlService
    .createHtmlOutput('<p>Click to open PDF files</p>'
      + '<ul>'
      + files.reduce(function (prev, file) {
        prev += '<li><a href="' + file.url + '" target="_blank">' + file.name + '</a></li>'
        return prev
      }, '')
      + '</ul>')
    .setWidth(300)
    .setHeight(150)
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Export Successful')
}

Does this answer your question? [Can no longer produce PDF from Google Sheets spreadsheet for some of the users](https://stackoverflow.com/questions/63413712/can-no-longer-produce-pdf-from-google-sheets-spreadsheet-for-some-of-the-users) — Marios, Sep 02 '20 at 17:24

score 0 · Answer 1 · answered Sep 03 '20 at 10:43

Solution

The forEach function callback can take as an input the array index as well.

Wrap your pdf generation routine in an if-statement that checks the index of the array:

spreadsheet.getSheets().forEach(function (sheet, index) {
    if ( index > 1 ) {
        spreadsheet.setActiveSheet(sheet)

        var blob = _getAsBlob(spreadsheet.getUrl(), sheet)
        var fileName = sheet.getName()
        blob = blob.setName(fileName)
        var pdfFile = DriveApp.createFile(blob)
    
        files.push({
          url: pdfFile.getUrl(),
          name: fileName,
        })
    }
})

Reference

Array forEach

Google appscript to convert worksheets to PDF

1 Answers1

Solution

Reference