Google sheets: How to save a single sheet in a spreadsheet file as PDF, where the file name is based on a certain cell content?

Question

I feel like this is a simple question that has been answered quite a lot before, yet despite the couple of hours I wasted searching this site (and other sites) for a simply, clear answer to my question, I could not find one; hence my question.

Context

I constantly need to issue invoices that provide details for each production/manufacturing order. I have recently transferred pretty much all of our database content from MS Excel to Google Sheets for all the obvious benefits (especially those that come with being cloud-based). The aforementioned invoices are created using a spreadsheet. This spreadsheet is consisting of several sheets, each one responsible for gathering the relevant data from different source spreadsheet. The main sheet, which contains the main invoice interface, is exported as PDF file with a certain name that is derived a certain cell (typically, B6). This cell contains a formula that displays a combination of several other cells, in other words, this cell is constantly changing and is linked to other cells.

Back when I used to use Excel, I searched the internet and was able to create VBA code and link it to macro command which allowed me to export the invoice sheet with the name being derived from the aforementioned cell without problems. As I activate the macro, the file would instantly gets exported as PDF file where the name is right what I wanted (i.e. name is based on the current content of the cell B6).

The Problem

I couldn't get any of the sources I found on the internet (like this one) to work or to replicate what they had, and I could not create one from scratch by myself.

What I Need

How can I pull this off in Google Sheets? I'm really sorry if this is too easy or posted elsewhere, but as I said before, my experience is quite limited and I could not find what I needed anywhere else.

score 0 · Answer 1 · answered Jul 15 '21 at 13:42

Is something like this what you're looking for?

function exportSheet() {    
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var mainSheetName = 'Main Sheet'; //Change 'Main Sheet' to the name of your main sheet
  var mainSheet = ss.getSheetByName(mainSheetName);
  var name = mainSheet.getRange('B6').getValue(); //Gets the pdf name from cell B6
  var sheets = ss.getSheets(); //Pulls all sheets as an array

  for (var i = 0; i < sheets.length; i++) { //Iterates through all sheets
    if (sheets[i].getSheetName() !== mainSheetName) { //Hides all sheets that are not the main one
      sheets[i].hideSheet()
    }
  }
  
  DriveApp.createFile(ss.getBlob().getAs('application/pdf').setName(name)); //Exports the main sheet

  for (var i = 0; i < sheets.length; i++) { //Brings back all sheets that were hidden
    sheets[i].showSheet()
  }
}

I tested this in a mock sheet and it worked for me. Please let me know if you have any issues with it, or if I misinterpreted something.

Google sheets: How to save a single sheet in a spreadsheet file as PDF, where the file name is based on a certain cell content?

1 Answers1

Reference: Export Single Sheet to PDF in Apps Script

Linked