AppsScriptPulse

Bulk save emails from Gmail

Screenshot of setup sheet for tool.

Screenshot of setup sheet for tool.

This tool is designed to bulk save emails (and associated attachments) from a specified Gmail label into a Google Drive folder, with relevant threads combined into a single PDF document.

Source: The Gift of Script: Bulk save emails from Gmail – overview

Log actions performed by a user running a Google Apps Script

Screenshot of spreadsheet with logs from script

Example log output from script to spreadsheet

A standalone function that you could include in your scripting projects to help determine which user ran a script and what actions it has performed. It is designed to output the information into a Google sheet – Date/Time, User, Action.

Source: The Gift of Script: Log actions performed by a user running a script

Convert Doc to PDF and move into a new folder with Google Apps Script

Create a PDF version of a Google Doc, move it into a new folder, remove its parents so it only exists in the new folder (typically also exists in My Drive) and then trash the Doc.

Source: The Gift of Script: Convert Doc to PDF and move into a new folder

Check if a date is more than a month ago

Recently during a consultation I was asked if it would be possible to check if the date submitted on a Google form was over a month ago, to which I responded “sure …”.

Admittedly this was a lot more difficulty than I thought it would (and should) be – I was bamboozled by online forums and posts suggesting to convert dates into numbers and perform other incoherent functions to achieve this. Eventually however I came across the single JavaScript function that I would need to achieve this feat …… ‘getMonth’.

Source: The Gift of Script: Check if a date is more than a month ago

Protect a named range in a Google sheet

The following Google Apps Script code is from some recent learning I have been doing when asked about locking-down certain areas of a sheet. I knew of named ranges and protecting cells but not quite that it could be done with apps script.

Source: The Gift of Script: Protect a named range in a Google sheet

SpreadAPI: add REST API to any spreadsheet in Google Sheets

SpreadAPI

Image: spreadapi.com

SpreadAPI is a free Google Apps Script that allows you to add REST API to any spreadsheet in Google Sheets in a few minutes. The API can be accessed over HTTPS from back-end as well as front-end apps.

It’s currently possible to publish a Google Sheet as JSON to get the data in a third-party application, but it’s harder to allow for posting new data back to that sheet. SpreadAPI is a script that creates a full-function REST API for a Google Sheet. Each implementation is set up individually and provides multiple authentication methods for specific users or even anonymous users.

Source: spreadapi.com

Stripe payment code integrated into Google Apps Script

Stripe Payment Example

Regular Google Apps Script community contributor Alan Wells has provided example Apps Script code with different examples of Stripe payment integration.

Update: Alan had published the source code to GitHub

Source: Stripe payment system Рclient version v3 РApps Script integration 

Unit Testing in GAS Part 4: Error Handling | ohhey[blog]

Up until now, our Calcs class has handled errors with simple true and false flags. That’s not helpful to the user. At this point, we’re ready to begin defining and testing custom errors in our functions.

QUnit allows a throws method which can check for errors in your application. This tutorial post adds some error checking to a simple Apps Script project.

Source: Unit Testing in GAS Part 4: Error Handling | ohhey[blog]

Get weekday name from a date

Take a date and extract the day of the week from it. By making use of ‘formatDate’ and the pattern ‘EEEE’ within a few lines of code we have a workable week-day name that could be used in an if statement as discussed in the previous post.

Source: The Gift of Script: Get weekday name from a date – alternative

Copy template sheet to Google Sheets and reorder

Template sheet with signatures to copy to other files

Screenshot of template sheet with signatures to copy to other files

Take a template Google Sheet (containing fields for 3 signatures) and copy it into other Google Sheet files within a given Google Drive folder and then reorder those sheets so that it was the first one.

The idea was based around needing users to authorise student marks with their signature from multiple Google Sheet reports produced from a third party system.

Source: The Gift of Script: Copy template sheet to Google Sheets and reorder

Dynamically remove Google Form options

Screenshot of Google Form appointment slots
Google Form appointment slots

This was developed as I wanted to learn about removing Google Form option choices as part of exploring its capabilities for use with appointment slots. I am aware there are a number of Google add-ons that achieve this but they come with potential data protection issues when accessing your Google Account, hence an in-house approach.

The principle is simply that a user would select their desired appointment slot on the Form and then that option would be unavailable for the next user accessing the Form.

Source: The Gift of Script: Dynamically remove Google Form options

Batch get existing Google Drive folder IDs & names

Get the folder IDs of a large number of current Google drive folders along with parts of their name – for capturing in a Google Sheet.

Source: The Gift of Script: Batch get existing folder IDs & names – version 2.0

Fixing dates from a Google Form

For those getting a ‘date’ from a Google Form and finding that it flips the day/month the wrong way around. This small piece of code can be inserted to create a correct date format.

Source: The Gift of Script: Fixing dates from a Google Form

Unit Testing GAS Part 3: Adding and Testing Functions | ohhey[blog]

We’ve looked at how to install and configure QUnit and just finished writing some simple tests. In this post, we’re going to write a new method, add(a, b) in Calcs which will add the two passed parameters.

Part 3 in the unit testing series, this post looks at writing tests for new methods and some considerations for when (or when not) to use private functions in Javascript when it comes to testing.

Source: Unit Testing GAS Part 3: Adding and Testing Functions | ohhey[blog]

Unit Testing GAS Part 2: Simple Tests | ohhey[blog]

Unit tests are for single units of code. They test a specific function for a specific result…

Part two in a series on unit testing, this configures the QUnit web application and has you write simple tests against an Apps Script class.

Source: Unit Testing GAS Part 2: Simple Tests | ohhey[blog]

Unit Testing GAS Part 1: QUnit Setup | ohhey[blog]

I’m not good at writing testable code. I’m more of a ‘figure it out when it breaks’ kind of hobby programmer. The problem with this is that I am constantly making my own bugs and not really finding them until a bad time.Unit testing is the process of running automated tests against your code…

The first post in a series, this will eventually be an in-depth look at unit testing both as a habit of writing code and through the lens of large Apps Script projects.

Source: Unit Testing GAS Part 1: QUnit Setup | ohhey[blog]

Adding data from spreadsheet to custom fields in Google Contacts with Google Apps Script

At my work data of clients, opposing parties, courts, third parties and cases (among which case names) are stored in a spreadsheet. The spreadsheet is the source for contacts in Google Contacts.

For some reason names of cases are used as subject in Gmail. I use(d) Google Apps Script to add case names to Google Contacts

Nice little script example to navigate around and add data to Google Contacts.

Source: /**There must be a better way to do it but I do not yet know how*/: Adding data from spreadsheet to custom fields in Google Contacts with GAS

‘While’ loop with a ‘Sleep’ and ‘Toast’

Screenshot of toast notification in Google Sheet
Screenshot of toast notification in Google Sheet

Understanding how to use a simple ‘while’ loop that pops up a ‘toast’ message every 5 seconds by making use of ‘Utilities.sleep’ to pause the script.

This was created as part of some testing for another script designed to bulk move files between Google Drive folders. Without the toast notification it was difficult to determine the progress being made, so adding it in allowed for the name of the file last copied to be displayed to the user. This script removes all of the extra features and shows the basic while loop and toast.

Source: The Gift of Script: While loop with a Sleep and Toast

Clear cell contents on Google Sheet edit

Screenshot of coloured columns to highlight actions
Screenshot of coloured columns to highlight actions

This is designed to clear the adjacent cell to the right of the one that has just been edited – so long as it is within the column we specify.

It was developed as I was testing the available options for a Stock Inventory system where an item that was to be marked as back in stock would automatically clear an email timestamp in the adjacent cell (ready for the whole ordering process to start again).

Source: The Gift of Script: Clear cell content on Google Sheet edit

Create alphabetised folders in Google Drive

Screenshot of Google Drive folder layout

The script is designed to bulk create folders from a Google Sheet and to arrange them into alphabetised folders within Google Drive, by extracting the first letter of a user’s surname.

If a suitable alphabetised folder does not exist in Google Drive, then one is created. The script also adds the created folder to an ‘Everybody’ folder so that there is a single area with all the folders listed in.

Source: The Gift of Script: Create alphabetised folders in Google Drive

Subscribe to Apps Script Pulse...