How can I generate and open pdf as a link with google sheet script?

Question

I'm doing an invoice program in google spreadsheets, and in the program I'm using drawings as buttons (create new invoice, increase/decrease invoice number and clear data). Those functions works well, but I also want to add a button that will create a PDF file and open as a tab in the web browser. So far I had to duplicate the Invoice-sheet as a tempSheet and then remove the buttons, and then download pdf of the tempSheet. I've found this code below which I've tried to change so it will fit with my program (the original program is only for the active sheet). I think maybe the problem is when I use getId() and getSheetId().

If I use ss.getId() in the url I only get the active sheet (invoice-sheet) as the pdf.
If I use tempSheet.getId(), I get an error (ERROR TypeError: tempSheet.getId is not a function)

Do you know what I'm doing wrong? If I understand it correctly it is the same Id for all the sheets and then the different sheets also have its own id which I can get from getSheetId().

getId()- the number after docs.google.com/spreadsheets/d/

getSheetId() - #gid=xxxxxxxxx

function createPDF(){
  //create a temporary sheet

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  var tempSpreadsheet = SpreadsheetApp.getActiveSpreadsheet().duplicateActiveSheet();

  SpreadsheetApp.getActiveSpreadsheet().renameActiveSheet('temp');
  
  var tempSheet = SpreadsheetApp.getActive().getSheetByName('temp');
  var invoiceNbr = tempSheet.getRange('C3').getValue().toString();
  var tempId = tempSheet.getId(); //ERROR TypeError: tempSheet.getId is not a function
  var tempSheetId = tempSheet.getSheetId();
  var drawings = tempSheet.getDrawings();
  for (var i = 0; i < drawings.length; i++) {
    drawings[i].remove();
  }

  const url = 'https://docs.google.com/spreadsheets/d/{ID}/export?'.replace('{ID}', tempId);
  
  const exportOptions =
    'exportFormat=pdf&format=pdf' + // export as pdf
    '&size=letter' + // paper size letter / You can use A4 or legal
    '&portrait=true' + // orientation portal, use false for landscape
    '&fitw=false' + // fit to page width false, to get the actual size
    '&sheetnames=false&printtitle=false' + // hide optional headers and footers
    '&pagenumbers=false&gridlines=false' + // hide page numbers and gridlines
    '&fzr=false' + // do not repeat row headers (frozen rows) on each page
    '&gid=' + tempSheetId; // the sheet's Id. Change it to your sheet ID.
  // You can find the sheet ID in the link bar. 
  // Select the sheet that you want to print and check the link,
  // the gid  number of the sheet is on the end of your link.
  
  var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};
  var blob = UrlFetchApp.fetch(url + exportOptions, params).getBlob().setName(invoiceNbr+'.pdf');
  

  var pdfFile = DriveApp.createFile(blob);

  var downloadLink = HtmlService
    .createHtmlOutput('<p>Download your file <a href="' + pdfFile.getUrl() + '" target="_blank">here</a>.</p>')
    .setWidth(200)
    .setHeight(100);

  SpreadsheetApp.getUi().showModalDialog(downloadLink, "Download PDF");
  

  ss.deleteSheet(tempSheet);

On the tempSheet var you have get active ` var tempSheet = SpreadsheetApp.getActive().getSheetByName('temp');` didn't you mean to use getActiveSheet() or getSheetByName(name) — Kessy

Rubén Rubén · Accepted Answer · 2020-10-21T16:54:27

Add SpreadsheetApp.flush() before getSheetByName('temp'):

SpreadsheetApp.getActiveSpreadsheet().renameActiveSheet('temp');

SpreadsheetApp.flush();
  
var tempSheet = SpreadsheetApp.getActive().getSheetByName('temp');

How can I generate and open pdf as a link with google sheet script?

1 Answers