Google Sheets: Script to Print As PDF, Save as CSV, XLSX

Hyperlinks can do more than just link documents and sheet pages.  They can also help you to quickly download the content as a CSV, TSV, XLSX or PDF.  Best of all, we can limit the range to be downloaded.

Problem:

If using a Google Sheet, exporting specific ranges as a PDF, CSV, etc., is difficult or impossible using Google's preselected menu bar.

Solution & Skills:

We will create one Google Spreadsheet where we utilize the hyperlink function to create download links.  This solution helps with the creation of invoices, grabbing CSV data that can then be uploaded to other programs/web applications.

Skills:

✔ Write hyperlink function;

✔ Link to pages within spreadsheet;

✔ Modify link to create CSV, XLSX or PDF files by range.

Hyperlink Function:

Here's the breakdown of the hyperlink function in Google Sheet:

=hyperlink("https://www.google.com","Google")

The equal sign tells Google Spreadsheets that we are using the hyperlink function.  Inside the first quote, we have the URL we want to visit.  We then create another "object" by using a comma and then use quotes to provide a string name. You can get silly here use any Unicode character, including emojis. 👍

But, for our example, we want to create a link that with grab the CSV or create a PDF when clicked.  To do this we need to grab the URL of the Google Sheet we desire.

For example, if we want create a link to a sheet within the Google Spreadsheet.  Here's a breakdown of the link's components:

Google Application: document, presentation, spreadsheets, drive

SpreadSheet ID:

Action: edit, copy, template/copy, preview

Sheet ID: gid= (number representing the sheet or slide)

Modify the Link - CSV & TSV:

To download a sheet as a CSV (or TSV), we will modify the action part of the URL.  Remove "edit#" and replace with:

export?format=cvs&

keep your: gid=xxxxxxxxx

Add:

&range=B9:C"

Think of the action as a series of questions.  Example:

What would you like to do with this file? 

Export it.

 What format?

Format should be CSV, from this sheet AND I only want range B9:C.

The new ending of the URL will look like:

/export?format=csv&gid=1909726178&range=B9:C

Modify the Link - PDF:

To download a sheet as a PDF, it is important for you to know that you can still select the range, the printout will only be for regions that are visible. So, our hidden column B is a no go.  However, let's say I only wanted the Group Names. And, we know this is a wide list, so we want it printed horizontally. The range we want is:  I3:R6.  Here is what we want typed:

export?format=pdf&portrait=false&gid=1909726178&range=I3:R6

Here is the question format:

What would you like to do with this file? 

Export it.

What format?

Format should be PDF.

Do you want that as a portrait? 

No.  AND, I only want this sheet AND range I3:R6.

What happens when your forget to tell Google you don't want portrait?  You get a portrait. 

There are a number of other format options we can add.  Spencer Easton produced a small script that gives an overview of some of the other features available.

 

What can you do with this?

If you keep your class roster in a Google Spreadsheet, you can create your roster and provide a link to download the PDF for printing or emailing.  This also works for district reimbursement forms or invoices. I built a "Group Creator" Worksheet and use the hyperlink feature to print the CSV required for Zoom.  What's great with Apps Script is that you can automate this process to insert the hyperlink formula on whatever sheet you desire.

/** 
 * Generates CSV download link
 * @Constructor
 * @parma {string} newSheet - Name of Sheet
 */
function getSheetCSV(newSheet) {
  const SS = SpreadsheetApp.getActiveSpreadsheet();
  const id = SS.getId();
  const ss = SS.getSheetByName(newSheet);
  let url = '';
  url += 'https://docs.google.com/spreadsheets/d/'+id;
  url += '/export?format=csv&gid=';
  url += ss.getSheetId();
  url += '&range=B9:C' 
  return url;
}