A Google Cloud run hosted puppeteer with a graphQL interface … In this article I’ll show you how to use this (I’ve called it gql-puppet ) with Apps Script
One of Apps Scripts superpowers is the ability to make HTTP requests to external services. These can be requests to other product APIs, or as in the following example, your own services. In this post from Bruce Mcpherson you can learn how you can use Google Apps Script to interact with a service he has created on Cloud Run which has a deployment of the headless browser, Puppeteer.
To interact with Puppeteer, Bruce has added GraphQL as an application interface. GraphQL is a query language that allows developers to request specific data from an API, making it more efficient and flexible than REST, which often requires multiple requests to retrieve the same data.
As Puppeteer is just a browser there are lots of things you can do like take a screenshot, print a PDF or get the page content. With GraphQL there is an interface layer to do this as well as returning specific data from a webpage.
The article has various code examples for different use cases, such as taking screenshots, extracting table data, and creating PDFs from web pages using Google Apps Script and saving the data in Google Drive. Bruce is providing access to his combined Puppeteer/Cloud Run/GraphQL solution, gql-puppet but you can also find out how you can deploy it as your own service.
Shared cached data between multiple scripts with this enhanced Apps Script Cacheservice library
Bruce Mcpherson has shared a new Apps Script library, bmCacheSharer, which enables sharing of cache data between multiple Google Workspace projects. The library was originally created to solve the problem of sharing configuration data stored in a Google Sheet with multiple script projects.
The library includes a number of nice features to overcome some of the limitations of the Apps Script CacheService, such as the 100k maximum item size and the 6-hour expiration limit by incorporating features like compression, key digestion, and automatic refresh.
It also provides options for sharing cached values by using community keys. The library is designed to be flexible, allowing users to either use its built-in cache service or provide their own. Additionally, it offers a memory cache for faster access within the same script instance. The document includes code examples and explanations to illustrate the library’s usage and benefits.
Apps Script library to convert between file types, including OCR for image to documents, with a huge repertoire of conversion combinations.
The Drive API offers a whole range of conversions between mimeTypes, but it’s a little fiddly to figure out exactly how. This library takes a file and an a desired output format and converts it for you. Sometimes, there’s not a direct route – for example if you need to convert a word file to a pdf, it first needs to get converted to a Google Doc, then to a Pdf. This library automatically works out and actions any intermediate conversions required.
For those who are unfamiliar MIME types (mimeTypes) is a standard way of identifying a file’s content type, such as text, image, or audio. The MIME type for a file is usually used by applications to determine how to open and display the file. Google Drive is able to convert various files, such as a MS Word document to Google Docs.
Sometimes there isn’t a direct route and this clever library from Bruce Mcpherson can help alleviate the pain. The library works by first checking if there is a direct route between the input and output file formats. If there is, it simply converts the file. If there is no direct route, the library will use the Drive API to first convert the file to a format that can be converted to the output format. For example, if you need to convert a Word file to a PDF, the library will first convert the Word file to a Google Doc, and then convert the Google Doc to a PDF.
Knowing what mimeType to use can be a bit of a challenge but you can also use the library to give a list of known types, or if you prefer a more readable list of Google Drive export MIME types.
A useful library to create prettified HTML, XML GraphML and other markups directly from Apps Script or JavaScript.
A long, long, long time ago Apps Script had a Xml class which was deprecated in favour of the current XML Service. One of the nice features of the old Xml class was the Xml.parseJS() method, which “given a JavaScript array … returns an XmlDocument representation”.
The current XML Service doesn’t have a JavaScript to XML parser, but Bruce Mcpherson has recently shared a library that can be used to easily convert JSON objects into XML and it’s many recognised formats like HTML. Bruce’s post shows how to can create various HTML page elements including head sections and tables. It’s also worth checking out Bruce’s other post on “Create GraphML markups from Apps Script” which is included in the source link.
If you are playing around with Sheet colors with Apps Script, you sometimes find yourself with font colors that don’t go well with the background colors you’ve chosen. However, we can use Yiq values to decide whether the luminance of the background color would be best with a light or a dark foreground font color. Here’s a small Apps Script library to figure it out for you.
Nice little helper library from Bruce Mcpherson if you’ve like to automatically apply contrasting colours to Google Sheets ranges. The source post from Bruce provides the background to the YIQ colour system as well as how to use the library. Follow the source link for more details.
How to fix Apps Script file loading order and defintion visibility problems with an Exports object.
It’s good practice to keep class and namespace definitions in separate files and avoid defining functions or variables in the global space. However, App Script doesn’t give you control over the order in which it loads files. If you reference a class or a namespace from one script file, it may not yet be defined. This is where an Exports object comes in.
As your script projects get larger and you start splitting out across script files you may find you need a little more structure. Class and namespace definitions are a good way to structure your code. Even when you do this you can still encounter problems with parts of your script trying to run before they are fully loaded.
This was a particular issue when the V8 runtime launched in 2020. This was fixed in June 2022, but it can still be an issue depending on how declarations are made in your code. To find out more about why this happens and how to fix it this post by Bruce Mcpherson shows how an Exports object can be used to structure your code.
I’ve been running this site for about 12 years ago. with over 1000 pages of content, here’s some of the high (and low) lights. I came to Apps Script not long after it was available, my first foray into it was probably around 2010, and I started writing about it not long afterwards.
I’m sure many Google Workspace developers are familiar with the work of Bruce Mcpherson. Regardless of whether or not you have, this is a nice summary of the last 12+ years of work published by Bruce last year but well worth revisiting. It covers everything from his move from VBA to focus on Apps Script, useful script libraries and code as well as explorations into other Google Cloud products.
Tank and Drv are SuperFetch plugins to emulate streaming and use the Drive REST API with Apps Script … This article will cover how to copy very large files using Tank to stream and Drv to upload and download partial content. The Apps Script Drive services have a limit on the size of files you can write, and very large memory usage can potentially cause Apps Script to fall over mysteriously
Clever stuff as always from Bruce Mcpherson, this time looking at how you can handle large files with Google Apps Script. We’ve featured some of Bruce’s other SuperFetch posts on Pulse, but developers can benefit from exploring the entire series so far on Bruce’s website. As well as SuperFetch plugins for Firebase and Twitter I’m personally interested in the evolution of the Google Drive client (Drv).
I’m using the JSON API for BigQuery rather than the Apps Script advanced service, since I have centralized all my BigQuery datasets in one project, and want to use a Service Account for authorization
Google Apps Script includes an Advanced service to interact with BigQuery. A limitation with both the Workspace services and Advanced Services don’t play nice if you need to use a service account. This post from Bruce Mcpherson provides details of how you can setup your Google Apps Script project to use a service account with BigQuery.
Another in the SuperFetch (a proxy for Apps Script UrlFetchApp) plugins series, Frb is a plugin to access a Firebase Real time database.
If you want to take your use of APIs a little further Bruce Mcpherson is continuing his series exploring his recently published SuperFetch library showing how a client can be setup to interact with Firebase. As Bruce highlights: “Firebase is pretty fast, so there’s not a huge speed benefit from caching, but if you’re on a pay as go plan, SuperFertch caching can reduce your Firebase costs.”
The source post provided by Bruce provides everything to need to set up the SuperFetch client and Firebase project.
