Generate and send PDFs from Google Sheets with Google Apps Script 

Automatically create PDFs with information from sheets in a Google Sheets spreadsheet. Once the PDFs are generated, you can email them out directly from Sheets. This solution focuses on creating custom invoices, but you can update the template and script to fit your needs.

Today as part of a customer Google Apps Script introduction workshop, we covered a basic invoice / PDF example. By coincidence this month’s Google Workspace Developers Newsletter highlighted a solution spotlight from the Apps Script samples catalogue which does something very similar.

A trick I learned today from our Lead Workspace Trainer, Tim McLardy, was rather than using UrlFetchApp to get a PDF version of the Google Sheet with the export/?format=pdf url, is instead getting the Google Sheet as a blob and creating a pdf with DriveApp.createFile(). As this technique will generate a PDF with pages with all your Sheet tabs the trick is to selectively hide all but the tabs you want in your PDF. In the case of the example linked as the source post you can rewrite the createPDF() function as:

 * Creates a PDF for the customer given sheet.
 * @param {string} ssId - Id of the Google Spreadsheet
 * @param {object} sheet - Sheet to be converted as PDF
 * @param {string} pdfName - File name of the PDF being created
 * @return {file object} PDF file as a blob
function createPDF(ssId, sheet, pdfName) {
  // Based on
  const ss = sheet.getParent();
  const sheets = ss.getSheets();
  // Hides all sheets that are not the main one
  sheets.forEach(s => {
    if (s.getSheetName() !== INVOICE_TEMPLATE_SHEET_NAME) {   
  // make a pdf of the sheet
  const folder = getFolderByName_(OUTPUT_FOLDER_NAME);
  const pdfFile = folder.createFile(ss.getBlob()).setName(pdfName);
  // show all the sheets
  sheets.forEach(s => s.showSheet())
  return pdfFile;

Source: Generate & send PDFs from Google Sheets  |  Apps Script  |  Google Developers

Automating the creation of multiple folders in Google Drive with Google Apps Script (with a nice Google Sheet processing code pattern)


you can use the following Apps Script to create folders in Google Drive for students and share those folders with their email addresses based on data from a Google Sheet

Whilst this post from Amit Agarwal looks at the automatic creation of Google Drive folders, it’s also worth having a look at it at for an Apps Script pattern for iterating over and processing data from Google Sheets. In particular there is a getStudentData()function that turns the 2D array return from .getValues() into an object array which includes a rowIndex. This makes it easy to update your Google Sheet when each row is processed.

Source: Automating the Creation of Multiple Folders in Google Drive – Digital Inspiration

Efficiently exporting mulitple Google Docs files in PDF format with batch requests and Google Apps Script

This is a sample script for exporting Google Docs files (Spreadsheets, Documents, and so on) in PDF format with batch requests using Google Apps Script.

As a reference sample situation, in order to export 100 Google Document files as PDF files, when I tested this sample script, the processing time was about 150 seconds and no error occurred. And, I confirmed that 100 valid PDF files were created in my Google Drive.

Kanshi Tanaike has been busy again, this time looking at how you can handle batch exports from Google Docs, Sheets and Slides using Google Apps Script. There is quite a bit of engineering to get your head around but if you are looking for a copy/paste solution everything is well commented for you to drop this code into your own project. If you’d like more context about the solution there is a related post on Stack Overflow.

Source: Exporting Google Docs Files in PDF format with Batch Requests using Google Apps Script

Handling bytes in Google Apps Script with Blobs

If you have ever worked with Google Apps Script (GAS), you may have found yourself in this situation. You want to move some files from some Google service to another, e.g., from Drive to a Google Cloud Storage Bucket. You get the file’s contents using GAS’s built-in service in the form of a Byte array. You pass this byte array to the URLFetchApp.fetch() function in the body.

All is going good and well, but then you notice. Why is it taking 30 seconds to upload a 10MB file? Am I doing something wrong? Is GAS not meant for this?

Don’t panic. I’ve been there, and I decided to take a closer look. Here’s what I found.

In this article Ignacio Lopezosa takes a deep dive into how to handle bytes in Google Apps Script, sharing his journey in finding a solution on how to share (large) files.

Source: Handling bytes in Google Apps Script

🎥 🏉 AppSheet + Apps Script for sports videography highlights tracking

Image credit: Zack Akil

During the summer I was asked to help with some video recording for a rugby tournament, but rather than spending hours rewatching footage to find highlights, I quickly threw together some tech to make things a lot easier.

A slick solution that uses a AppSheet app for data collection with Apps Script to cue up video files hosted on Google Drive. The Apps Script code is hosted on GitHub if you’d like to take a peak. Hopefully this example gives you inspiration for your own little projects.

Source: 🎥 🏉 AppSheet + AppScript for sports videography highlights tracking

FilesApp is a GAS library for retrieving file and folder list in Google Drive

When I create some applications using Google Drive, there are often the case which is required to retrieve the file list and folder list. I had prepared the script each time for each case so far. But recently, I thought that if there is a library for retrieving the file and folder list (as a tree), it will be useful for me and other developers.

The FilesApp library by Kanshi Tanaike has been around since 2018. I have to confess I wasn’t aware of this library until I read a more recent post, Retrieving Total File Sizes in Specific Folder of Google Drive using Google Apps Script. Similar to my own post on Creating a Google Drive report in Google Sheets the FilesApp library directly communicates to the Drive API, this library however goes a step further in terms of performance by using UrlFetchApp.fetchAll to handle some asynchronous Drive API calls.

Source: GAS Library – FilesApp

Creating a Google Drive report in Google Sheets: Making Google Workspace Enterprise solutions with Google Apps Script

Image credit: Martin Hawksey (with the help of DALL·E 2)

Think 10x — supercharging your Google Apps Script solutions by directly calling Google Workspace Enterprise APIs.

The Google Apps Script built-in services like SpreadsheetApp, Maps and GmailApp are a great onramp for users with limited coding experience, the flip side is you can find yourself easily getting results but not in the most efficient way. DriveApp is a great example where Google have made it easy to iterate across folders and files, but when you have lots of folders and files it becomes a time consuming process and you hit execution limits.

An alternative approach is using Google Apps Script to make direct calls to the Drive API. The benefits of this approach is you can be more specific in the data you want back and it gives more flexibility with how you call the API, in some cases with the ability to make batch or asynchronous processes.

In this post I highlight a method ideal for scenarios when you want to index larger volumes of My Drive files and folders to a Google Sheet with calls directly to the Drive API. The post includes some sample code you can use which instead is able to reduce a 4 minute runtime to index 10,000 files and folders to one that can complete in under 40 seconds!

Source: Creating a Google Drive report in Google Sheets: Making Google Workspace Enterprise solutions with Google Apps Script

Get the Creator’s email of a Shared Drive with Google Apps Script


Learn how the access the creator’s email of a Google Shared Drive with Google Apps Script using the Drive Activity API & Admin Directory SDK.

Scott Donald has found a clever way to get the Shared Drive creator using Apps Script and the Google Drive Activity API. Whilst there is a Google Drive ‘Drives’ endpoint the response doesn’t include the creator in the Drives Response object. This post is a nice example of how you can combine data from different Google Workspace APIs. Follow the source link for a detailed explanation.

Source: Get the Creator’s Email of a Shared Drive with Google Apps Script – Yagisanatode

Organise Google Drive files into a JavaScript object for use with Google Apps Script

Organise Google Drive files into a JavaScript Object so that they can be looped through for each named individual.

Files to loop through and organise

Files to loop through and organise

The following Google Apps Script is designed to go through a folder of Google Drive files where a number of tutors have individual files for groups of students they teach. The code will collate all of the relevant files (specifically their IDs) that belong to each tutor, so that at a later date we can loop through the organised data set and create individual Google Sheets for each tutor and compile their student data into it.

What this code ultimately allows us to achieve is the ability to go through the files and get them organised for further coding.

In this example we have a file name pattern of ModuleCode – Tutor Name – Group Number for example ABC – Jane Doe – Grp 02. So we want to collate all of the files that belong to Jane Doe first, then move on to the next tutor (Micky Mouse in this example).

The collated data will look like this:

   "Jane Doe":[
      "FILE ID HERE",
      "FILE ID HERE",
      "FILE ID HERE"
   "Micky Mouse":[
      "FILE ID HERE",
      "FILE ID HERE"

Source: The Gift of Script: Organise files into a JavaScript Object

Bulk add files to existing Google Drive folders using Google Apps Script

Take a Google/Shared Drive folder full of files that you want to make a copy of and place into another set of existing Drive folders.

Bulk add files to existing Google Drive folders

Bulk add files to existing Google Drive folders

The following Google Apps Script tool is designed to take a Google/Shared Drive folder full of files that you want to make a copy of and place into another set of existing Drive folders.You can decide if you want the subfolder name appending/prepending/ignoring in the file name of the new copy to help make it unique.

The tool will iterate through a single level of folders (not sub-subfolders) and for each one place a copy of the files into it.

Source: The Gift of Script: Bulk add files to existing Google Drive folders