AppsScriptPulse

Uploading files without authorizing scopes  with a dialog in Google Sheets using Google Apps Script

Making the shared users input a value and upload a file without authorization of the scopes with a dialog on Google Spreadsheet.

It’s usually unavoidable when you are creating and sharing Apps Script projects that the user will be required to complete an authentication flow to approve access to the services you include in your script such as reading/writing to Google Sheets, Drive etc.

The process is reliant on OAuth scopes, which are identifiers that specify the level of access an application requests from a user’s Google Account data. They are essentially a way for developers to define the specific actions or data their application needs to access. When a user grants an application access to their Google Account, they are agreeing to allow the application to perform the actions or access the data specified by the scopes.

Sometimes you can restrict the ‘scope’, for example, usually for Sheets, Docs, Slides, and Forms where I need only permission for the current doc I will include the following documented comment to only require access to the doc that the script project is bound to:

/**
 * @OnlyCurrentDoc
 */

There are some limitations when defining the scopes you need. For example if you would like a user to upload a document to Drive usually you would require the very broad https://www.googleapis.com/auth/drive scope which will prompt the user to ‘view and manage all of your Drive files’.

Understandably users may become nervous approving such a scope and in some cases Google Workspace Admins may prevent authentication for this type of scope for unverified/unconfigured applications.

There are alternative approaches to allowing users to execute Apps Script projects without having to approve scopes like Google Drive. There are clearly security considerations when you do this, so always proceed with caution.

This post from Kanshi Tanaike has some examples of how users can be prompted to upload files to Google Drive without authorising Drive access. The post includes two approaches, the first using a Web App which is pre authenticated to run as the user who has deployed the Web App, the other using a service account. The source post contains all you need to know include the code.

Source: Uploading Files without Authorizing Scopes by Shared Users with Dialog on Google Spreadsheet using Google Apps Script

Build your own Gmail-based expense tracking solution with Google Sheets and Google Apps Script

Use Google Apps Script to automate email-based expense tracking. Store and track your receipts entirely through Gmail, Drive and Sheets

Here’s a nice tutorial on how to create an email-based expense tracking system using Google Apps Script. The solution allows users to submit expense reports via email, which are then automatically processed and stored in a Google Sheet with attachments stored in Google Drive.

The blog post by Joshua Mustill provides detailed instructions on how to set up the system, including how to create the Gmail labels and filters, the Google Sheet and the Apps Script code. There are some nice features in the code you might want to use in other projects including the creation of date based Google Drive folders for storing Gmail attachments.

Source: Build your own email-based expense tracking with Google Apps Script

How to extract images from Google Docs and Google Slides using Google Apps Script

Learn how extract all the embedded images from a Google Document or Google Slides presentation and save them as individual files in a specified folder in your Google Drive.

Often I’ll use Google Docs for drafting blog posts. With the introduction of Duet AI having a generative assistant in situ helps with the creative process. As well as text Google Docs is a really simple canvas for quickly copy/pasting screenshots. Copying content from Google Docs to WYSIWYG editors can sometimes be challenging, particularly, when it comes to images.

Next time I encounter this problem I’ll be using the script solution from Amit Agarwal, which can extract images from Google Docs and Slides and save them to Google Drive. I’m sure there are many other situations where this snippet could come in handy.

Source: How to Extract Images from Google Docs and Google Slides – Digital Inspiration

PDFApp and many recipes for ‘cooking’ PDFs with Google Apps Script

Unfortunately, there are no built-in methods for directly managing PDF data using Google Apps Script. Fortunately, after the V8 runtime has been released, several raw Javascript libraries could be used with Google Apps Script. pdf-lib is also one of them. When this is used, PDF data can be cooked over Google Apps Script. In this report, I would like to introduce achieving this using a Google Apps Script library.

Google Apps Script is a powerful tool for automating tasks. It can be used to process data, create spreadsheets, and send emails. One of the limitations of Apps Script is that it does not have built-in support for PDF files. However, there are a number of third-party libraries that can be used to work with PDF files and in Pulse we’ve featured a number of posts from Kanshi Tanaike where they have explored the pdf-lib is a JavaScript library.

This work has culminated in PDFApp, a dedicated Apps Script library created by Kanshi Tanaike based on the pdf-lib, but optimised for Apps Script. The source post includes a number of recipes for handling PDF files listed below:

  • Export pages from a PDF
  • Get metadata of a PDF
  • Update metadata of a PDF
  • Reorder pages of a PDF
  • Merge PDF files
  • Convert PDF pages to PNG files
  • Get values from PDF form
  • Set values to PDF form
  • Create PDF form using Google Slide as a template
  • Embed objects into a PDF

The source code for PDFApp is also on GitHub if you want to dig further.

Source: Cooking PDF over Google Apps Script

How to programmatically enable push notifications/watches for file changes in Google Drive with Apps Script

Are you looking for a way to receive notifications in real-time when an important spreadsheet in your Google Drive get modified or is accidently deleted by sometimes? Well, Google Drive offers an API to help you set up a watch on any file in your Google Drive be it a document, presentation or even a PDF file. This means that you can receive instant notifications whenever the content or even permissions of that file changes.

This tutorial explains how you can setup watch notifications on any file in your Google Drive with the help of Google Apps Script.

As part of the Google Drive API you can set up watch notifications on any file in your Google Drive. This means you can receive instant notifications whenever the content or permissions of that file changes. This feature can be useful for a number of scenarios, for example,  if you have sensitive Drive files that you want to closely monitor, or a workflow where you’d like to trigger additional events when a file is updated.

This post from Amit Agarwal explains how you can create a push notification for a Google Drive file using Google Apps Script. As noted in the post you can use Google Apps Script to handle the push notification by creating a doPost Web App, however, not all the response data/headers are available using Google Apps Script. There is a related ticket which had some activity earlier in the year and I would encourage you to star the request to get updates and encourage Google to fix – https://issuetracker.google.com/issues/67764685.

Google Workspace Admins looking for domain/user activity on Drive files might want to look at the Reports API activities endpoint, which can be configured to setup similar watch notifications. You can read more in the Reports API: Drive Activity Report overview.

Source: How to Enable Push Notifications for File Changes in Google Drive with Apps Script – Digital Inspiration

Automatically save attachments for a Google Google to Google Drive using Gmail and Google Apps Script

 

Imagine you’re managing a Google Group, where important attachments are regularly sent. Manually saving these attachments to your Google Drive can be time-consuming and prone to errors. Is there a way to automate this process and ensure that you don’t miss any crucial documents?

It’s quite easy to tie yourself in knots when it comes to scheduling tasks in Google Apps Script to handle data created since the last run. This Medium post from Pablo Pallocchi shows a nice way your can structure Gmail searches using the after: operator combined with a ‘last execution date’ stored in Apps Script Properties Service. The result is a nice solution design to backup attachments sent to a Google Group to Google Drive. There’s lots of scope for extending and/or modifying this solution. All the details are in the source post.

Source: Automatically Save Email Attachments to Google Drive Using Google Apps Script

Bulk create Drive folders with subfolders with Google Apps Script

Bulk create Google Drive folders with multiple subfolders. Control the naming of the folders and how many you want.

Use this tool to bulk create folders with subfolders

Use this tool to bulk create folders with subfolders

The following Google Apps Script is designed to bulk create Google Drive folders with multiple subfolders. You control the naming convention of each folder and exactly how many you want.

Features include:

  1. Runtime control – currently set to 5 minutes 30 seconds. Will ignore rows that have a ‘Folder Link’ so you can continue from where you left off and append further folders should you require.
  2. More subfolders – the Google Sheet has columns for 10 subfolders but you can technically add more (columns) and the code will account for this automatically.
  3. Toast popups to inform you of the progress as folders are created.
  4. ‘Log’ sheet and popup error messages if something goes wrong.
  5. Concatenation – create those useful descriptive folder/file names so items are easier to search for in the future. Concatenate will let you combine values that may exist in different columns in another spreadsheet for example, or take those folder names and prepend/append words around them.

Source: The Gift of Script: Bulk create Drive folders with subfolders

List all files and folders in a selected folder’s directory tree in Google Drive using Google Apps Script

A fast approach to get all items in a directory tree in Google Drive with Google Apps Script. Contains video and edge case handling.

This is an excellent detailed step-by-step tutorial from Scott Donald all about how to efficiently list all files and folders in a folder’s directory tree in Google Drive using Google Apps Script. The in parents method is one we’ve covered before in Pulse, but Scott provides a very comprehensive explanation of what’s going on, plus all the code is available if you would like to reuse in your own projects.

Source: List all files and folders in a selected folder’s directory tree in Google Drive: Apps Script – Yagisanatode

Bulk duplicate Google Drive files using Google Apps Script

The following Google Apps Script tool is designed to take a single Google Drive file e.g. a Doc / Sheet / Slide and make duplicates/copies of it with unique file names. The tool will also create a clickable link within the Google Sheet to each new file copy.

Screenshot of the tool for duplicating Drive files

Screenshot of the tool for duplicating Drive files

Source: The Gift of Script: Bulk duplicate Google Drive files

New Google Apps Script library to convert files with the Drive API (including OCRing image file formats)

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.

Source: Convert any file with Apps Script – Desktop Liberation