AppsScriptPulse

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

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

Apps Script: solutions to the issue “installable triggers failing when created from another trigger function in V8”

There’s a long standing issue with triggers installed programmatically by other triggers, that was kind of fixed last year, but users are still reporting problems, at least in certain locations (Japan, India and others). The issue in short: triggers installed programmatically would not be created, or fail systematically.

In many cases this affects add-ons (and, to be noted, the issue still persists when testing them, see Testing details); I had a slightly different scenario: a webapp that, when executed (doGet), would install a time based trigger for the user executing the script.

This article provides two solutions to an annoying issue and unlocks many possibilities for programmatically installed triggers.

Source: Apps Script: solutions to the issue “installable triggers failing when created from another trigger function in V8”

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

Putting values from of all Google Sheets in Google Drive folder into a master Sheet with low process cost using Google Apps Script

 

This is a sample script for putting the values of all Spreadsheets in a folder to the master Spreadsheet with a low process cost using Google Apps Script.

Some clever scripting from Kanshi Tanaike to combine individual Google Sheets in a Google Drive folder into a single master sheet. To achieve this the solution uses the Google Sheets Advanced Service combined with UrlFetchApp.fetchAll() to asynchronous process the source Google Sheets. It’s reported that this method was able to process 50 source sheets in 10 seconds!!! There are some limitations to be aware of highlighted in the source post, but for a method to quickly process a lot of data this solution is definitely one to keep in mind.

Source: Putting Values of All Spreadsheets in Folder to Master Spreadsheet with Low Process cost using 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

Hiding/deleting non-consecutive rows and columns in Google Sheets using Google Apps Script

These are the sample scripts for hiding and deleting rows and columns on Google Spreadsheet [sic] using Google Apps Script. I sometimes see the questions for hiding and deleting rows and columns on Spreadsheet at Stackoverflow. So here, I would like to introduce the sample scripts for this.

In SpreadsheetApp there is the .deleteRow() and .deleteRows() , but when you have a lot of data or non-consecutive rows it can really eat into your Apps Script runtime limit. In searching for a better way of doing this I found this great solution from Kanshi Tanaike on Stack Overflow which uses the Google Sheets Advanced Service to delete multiple rows using one API call!

Using Sheets.Spreadsheets.batchUpdate isn’t just limited to deleting rows and for more examples see the source post which includes examples for hiding and deleting both rows and columns.

Source: Hiding and Deleting Rows and Columns on Google Spreadsheet using Google Apps Script

Using ChatGPT to Generate Fake Data in Your Spreadsheet with Google Apps Script

ChatGPT is pretty much a universal API: one endpoint to get any data.

Fake data is useful for testing purposes. At times, real data is too sensitive, and sometimes you do not have access to real data before production, but you need to begin working with something. Here is where fake data comes in.

We used to have a great library for fake data called Faker.js; however, Marak, the author, became so frustrated that he could not monetize the solution that he pushed an update that broke everything, which led to him being blocked on GitHub and NPM. As a result, the library is no longer maintained. ChatGPT, however, is not going anywhere.

Source: Using ChatGPT to Generate Fake Data in Your Spreadsheet with Google Apps Script

Merging multiple PDF files as a single PDF and converting all the pages in a PDF to PNG images using Google Apps Script

This is a sample script for merging multiple PDF files as a single PDF file using Google Apps Script. [and] This is a sample script for converting all pages in a PDF file to PNG images using Google Apps Script.

Kanshi Tanaike has recently been exploring and sharing some Apps Script solutions for handling PDF Documents using the PDF-LIB JavaScript library. So far they have looked at merging PDF files as well as converting PDF pages into PNG images.

The solution uses fetch and eval to load PDF-LIB, but with a minor modification hoisting the declaration of setTimeout you can also copy the source code into the script editor and avoid the evils of evals (a modified example here).

The Apps Script execution runtime limit will be a factor in the size of PDF Documents you can handle, but for smaller jobs a great solution to keep in mind.

Source: Merging Multiple PDF Files as a Single PDF File using Google Apps Script and Converting All Pages in PDF File to PNG Images using Google Apps Script

Three pitfalls to avoid when using the onEdit trigger in Google Apps Script

Illustration by ahmiruddinhidayat111198 on freepik.com https://www.freepik.com/author/fahmiruddinhidayat111198

  1. Making a Single Function Do Everything
  2. Expecting onEdit to Catch All Changes by Default

Source: Three Pitfalls to Avoid When Using the onEdit Trigger in Google Apps Script