AppsScriptPulse

Exporting high-resolution PNG images of Google Slides with Google Apps Script

Learn how to convert Google Slides into high-resolution PNG images using Google Apps Script. Choose between the Google Slides API and the Google Drive API based on your requirements.

Here’s a useful snippet from Amit Agarwal exploring two methods exploring how to convert Google Slides to PNG images with Google Apps Script. The first approach uses the Google Slides API to get the thumbnail images of the slides, fetch the blob of the image, and then upload it to Google Drive. However, this method has limitations including predefined fixed widths and requires two URL fetches.

The second approach uses the Google Drive API, which offers advantages like generating images with the same resolution as the original slide and requiring a single URL fetch. For both code snippets follow the source link.

Source: How to Convert Google Slides to PNG Images with Google Script – Digital Inspiration

Harnessing the power of Puppeteer, Cloud Run, and GraphQL with Google Apps Script

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.

Source: Puppeteer, Cloud Run and GraphQL together – Desktop liberation

Automate Google Drive test data creation with Google Apps Script

This post describes a Google Apps Script function to create a dummy folder structure in Google Drive, including nested folders and files (Documents, Sheets, Slides). It highlights some useful features beginners might find useful such as recursion, randomness, and the use of Google Apps Script services for Drive interactions. The post additionally discusses specific challenges encountered, like using Drive.Files.create for file creation and ensuring compatibility with Shared Drives.

Working with Google Workspace Enterprise customers we recommend using a DEV/Test Google Workspace domain. If you purchase your Workspace licences through a Google Partner you should be able to get a free test domain, which admins can use to test features like Data Loss Prevention, Context Aware-Access without fear of breaking things for your live domain.

Unfortunately there are no convenient settings or data duplication so configuring your test environment can be a challenge. To help create some dummy data with a little help from Gemini I was able to create the following  createDummyFoldersWithNesting() function:


To use the script

  1. Copy into the Apps Script Editor
  2. Enable the Google Drive Advanced Service
  3. Configure the settings inside the function for the root folder and the number of files/folders to generate

Gemini suggested I highlighted the following key points:

  • Recursion: The createNestedFolders function calls itself to create folders within folders.
  • Randomness: The script randomly determines the number of folders and files to create, and the types of files.
  • Google Apps Script Services: It uses DriveApp and Drive.Files to interact with Google Drive.
  • File Metadata: When creating files, it uses the mimeType property to specify the Google Apps Script file type.

Whilst Gemini wrote a lot of the code for me (with a little guidance), there were a couple of gotchas I’ll highlight.

Drive.Files.create instead of DriveApp.createFile(name, content, mimeType) – the current documentation would suggest that you can use the .createFile() method and include a MimeType like GOOGLE_SHEETS, but as explained in this Stackoverflow post Google have said “After extensive consideration, we have determined that DriveApp.createFile() should not be used to create MimeType.GOOGLE_* files.” 

I could have asked Gemini to rewrite this to use DocumentAppSpreadsheetApp or SlidesApp .create() methods e.g. SpreadsheetApp.create() but then I would have to move into a folder, use extra scopes, which all felt a bit messy so instead opted for Drive.Files.create.

Drive.Files.create supporting Shared Drives without a blob – when using Advanced Services there is a bit of cross referencing required between the auto-complete in the script editor and the associated API documentation. For my script I wanted to support creating files in Shared Drive. To do this requires adding the supportsAllDrives as optionalArgs in the Drive.Files.create(resource, mediaData, optionalArgs) method. As I only wanted blank Docs, Sheets and Slides I was scratching my head as to what to include for the mediaData blob. Fortunately this issue was discussed in the Google Apps Script Community – Google Group and it was clear I could use null or undefined.

Source: Automate Google Drive test data creation with Google Apps Script

Analyzing Google Drive files and folder structures with Google Apps Script

An illustration which includes the Google Drive logo. The illustration includes a filing cabinet overflowing with files

This report provides a Google Apps Script to retrieve all files, including those within subfolders, for a designated folder. It addresses the challenges of retrieving files within deeply nested folder structures and obtaining complete file paths.

We’ve shared a couple of approaches to Google Drive reporting using Apps Script in Pulse, this latest comes from Kanshi Tanaike. There are actually two approaches covered in the post one using DriveApp and the other using the Drive API.

Hopefully, the related posts list on Pulse will highlight some of the alternative approaches you can use. I’m slightly obsessed with the .getFileNameWithPath() methods. This isn’t something that is provided in the Google Drive File resource data so it has to be constructed using only the file and parent information so it is interesting to see how this has been approached.

If you are looking at these code examples and struggling to understand them, Gemini (including implementations on gemini.google.com or aistudio.google.com), is a great way to copy the code (even the entire blog post) and start asking questions.

Source: Analyzing Folder Structures with Google Apps Script

Move files uploaded with Google Forms to specific folders in Google Drive with Google Apps Script

Learn how to move uploaded files from Google Forms to a specific folder in Google Drive. You can also rename the files based on the form responses

Amit Agarwal, the brains behind Digital Inspiration’s Document Studio must have one of the most feature full Google Workspace Marketplace Add-ons. I also like Amit’s approach of sharing how you can solve problems like moving Google Form file uploads using Document Studio as well as providing an Apps Script snippet if you would like to manually code it.

In this example some setup is required to specify a folder ID and create an onFormSubmit trigger. After this the script then retrieves uploaded files from the form response, creates a subfolder named after the unique Response ID, and moves the files into that subfolder within the specified parent folder. This provides a structured and organized way to manage file uploads from Google Forms.

Follow the source link to read more about this script solution and about Document Studio.

Source: How to Move Files Uploads from Google Forms to Specific Folders in Google Drive – Digital Inspiration

Find out who has access to your Google Drive files using Google Apps Script

In this blog we are going to find out who exactly has access to my Google Drive files, be it a Google Sheet, Google Doc, Form and more. To do this we are going to be using the DriveApp and Google Apps Script.

Recently Aryan Irani shared this post which shows how you can get the file permissions on a Google Drive file using DriveApp. This uses the DriveApp methods for .getEditors() and .getViewers(), which left me wondering about commenters???

The answer is file commenters are included in the .getViewers() response and as pointed out by TheMaster you can filter out commenters with .getAccess().

Another approach is to use the Advanced Drive Service:

The advanced Drive service lets you use the Google Drive API in Apps Script. Much like Apps Script’s built-in Drive service, this API allows scripts to create, find, and modify files and folders in Google Drive. In most cases, the built-in service is easier to use, but this advanced service provides a few extra features, including access to custom file properties as well as revisions for files and folders.

Learning about the Advanced Drive Service can be useful as it open up more opportunities to interact with Google Drive content and can also help you get file properties.

In the case of permissions there is a dedicated Permissions Resource that allows access to all the file permissions. For example, if I wanted to see what accounts had access to a file in MyDrive you can use:

// Requires Drive Advanced Service v3
const fileP = Drive.Permissions.list(fileId, {
  fields: "*" // all fields
});

fileP.permissions.map(perm => {
  console.log(`${perm.role} - ${perm.emailAddress}`)
});

Using the Advanced Drive Service does require a step up in understanding how to call the Drive API and the response you get but once you begin understanding it can come with huge benefits with more efficient code.

Source: Find out who has access to your Google Drive Files using Google Apps Script

How to audit Google Shared Drive permissions in Google Sheets with Apps Script

Are you looking for an efficient way to get an overview of all shared drives and their access permissions within your organization? Whether you’re navigating a company reorganization or implementing security procedures, accessing this information can be challenging.

This post from Niek Waarbroek highlights the important of auditing Google Shared Drive permissions. This can be a challenge to do using the Google Workspace Admin Console, especially if you have lots of Shared Drives.

To help Niek has shared a Google Sheet with Apps Script code that automatically generates a list of all shared drives and their associated root level permissions.

Shared Drive auditing is bit of a niche subject, but I encourage you to have a look at the post and script as it has some nice features which could be applicable to other projects. For example, there is a gaspTimeManager to make sure the script doesn’t go beyond the script execution limit.

Source: How to audit shared drive permissions in Google Drive

Replace text in a Google Doc with an image from Google Drive with Google Apps Script

Search the body of a Google Doc for a specific string/pattern and insert an image in place of it.

In this example the code is designed to sit behind the Google Doc so it is bound to it. There are 4 pieces of information to complete in order to setup the script:

  1. searchText – this is the unique string/pattern in the Doc that you want to replace with an image e.g. “<<keyword>>
  2. imageURL – this is the direct link to the image in Google Drive that you wish to use in the Doc.
  3. size – a numerical value representing the number of pixels for the image’s width/height.
  4. hyperlinkURL – if you want the image to be clickable then provide a link for it.

Source: The Gift of Script: Replace text in a Google Doc with an image

Managing Google Cloud Storage files with Google Apps Script without using a service account

Using Google Apps Script scoped authentication to interact with Google Cloud resources without a service account

A key feature of Google Apps Script is its integration into Google Cloud. The default behaviour when any Apps Script project is created is that an associated Google Cloud project is created and configured. This default project is not accessible to the user and for most scripts, the user doesn’t need to worry about any of the configurations such as enabling APIs and configuring authentication settings.

Other key aspects are identity and authentication. The default behaviour for scripts is usually to run as the account executing the script, Apps Script automatically determining what authorisation is required for different Google services based on an automatic scan of your code or from what scopes have been set explicitly in the Apps Script manifest file.

The last piece in the puzzle is the .getOAuthToken() method which is part of the ScriptApp Service:

Gets the OAuth 2.0 access token for the effective user. … The token returned by this method only includes scopes that the script currently needs. Scopes that were previously authorized but are no longer used by the script are not included in the returned token. If additional OAuth scopes are needed beyond what the script itself requires, they can be specified in the script’s manifest file.

What this means is in script projects we can borrow an access token to use other services that the effective user has access to and have been declared in the script project scopes. For example, if my Google account [email protected] has been added to another Google Cloud project with the Google Cloud Storage service enabled, I can use Apps Script to generate a token to use the Cloud Storage service in that project.

To help illustrate this, here are two examples for interacting with Google Cloud Storage buckets to upload and download files to Google Drive.

Source: Managing Google Cloud Storage files with Google Apps Script without using a service account

ReDriveApp: A new Google Apps Script library to replace DriveApp and restricted scopes 

Apps Script class that provides equivalent methods offered by the built-in DriveApp, but that does not require use of full ‘/drive’ OAuth scope (which is a “Restricted” scope”). Instead, uses only these Recommended (non-sensitive) and/or Sensitive scopes

When you use OAuth 2.0 to get permission from a Google Account to access their data, you use strings called scopes to specify the type of data you want to access on their behalf. For Google Workspace Add-on developers wanting to distribute your app in the Google Workspace Marketplace one consideration is only using the scopes required for your app. In the case of Google Drive a number of the scopes are classified by Google as “restricted”. To use “restricted” scopes there is an enhanced verification process, which requires Cloud Application Security Assessment (CASA) by an independent security assessor, which come at a cost and can be time consuming.

ReDriveApp is a new Apps Script community library published by Dave Abouav which makes it easier for developers to use reduced recommended scopes for integrating with Google Drive. As explained by Dave:

The built-in DriveApp service is an easy and powerful way to interact with Google Drive in your Google Apps Script projects, which is why so many developers make use of it. It’s one downside is that it forces your project to use the full ‘/drive’ OAuth scope, which is a “Restricted” scope.

In many cases though, projects don’t really need the full /drive scope for common tasks, and the Recommended /drive.file scope is sufficient. This allows your project to create new files, and open existing files if authorized by the end-user via the Drive Picker. Using it also avoids the aforementioned security review, and is less alarming to users when authorizing your app.

ReDriveApp is still work-in-progress and not an official Google project. There are a number of methods that still need to be implemented, but the project is open source and can be contributed to on GitHub. If you are planning or have already developed a Marketplace Add-on that has stalled due to enhanced verification for restricted scopes it could be worth looking at and contributing to this library.

Source: GitHub – ReDriveApp