AppsScriptPulse

Snippet, V8, Beginner

Google Apps Script Basics – Variables and getting & setting values

Tags:
| | 0 Comments |

Image: Baz Roberts

In this post, we’re going look at how variables are set up and how they can store various pieces of information. We’re also going to look at reading and writing data from a spreadsheet, which is one of the most common tasks when working with one.

We offer a wide range of community contributions here on AppsScriptPulse. This one might be useful if you are just learning to code and want to understand some of the fundamentals or if you’ve been using Apps Script for a while learn about variable types available in the V8 runtime.

Source: Apps Script Basics – Variables and getting & setting values

Snippet, V8, Video Tutorial, Beginner, Docs

Google Apps Script Tutorial: Letterhead in Google Docs

Tags:
| | 0 Comments |

In this Google Apps Script tutorial you will learn how to automatically create a letterhead in Google Docs.

This is a nice short video tutorial from Chanel Greco highlighting how you can manipulate a Google Doc header using Google Apps Script.

Snippet, V8, Beginner, Sheets

Get the redirect location of a URL using Google Apps Script

Tags:
| | 0 Comments |

Have a list of shortened urls and need their final destination? Use this custom function in google sheets to extract them all at once.

Handy little snippet that can make testing redirects on URLs a lot easier, which can be useful for expanding short links from social media sites like Twitter.

Source: get the redirect location of a url using google apps script

Snippet, Solutions, Docs, V8, Intermediate

How to Find and Replace Text in Google Docs with RegEx Search Patterns – Digital Inspiration

Tags:
| | 0 Comments |

It is easy to search and replace text in Google Documents with the DocumentApp service of Google Apps Script. You can use use findText method with simple regular expressions to find text elements in the document that match a pattern and replace them with the specified text. All well and good but in some cases, this simple search and replace function may fail if the search text does not transform into a valid regular expression.

Some more regex goodness this time from Amit Agarwal who provides some very useful tips on escaping characters when using the DocumentApp service and findText method. Click through to the source link for more details.

Source: How to Find and Replace Text in Google Docs with RegEx Search Patterns – Digital Inspiration

Snippet, Solutions, Sheets, Beginner

Check the domain of an email address by regex

Tags:
| | 1 Comment |

Compare a number of email addresses and check their domains against one you are filtering for via a regular expression.

Screenshot of email addresses in a Google Sheet cell

Screenshot of email addresses in a Google Sheet cell

The following Google Apps Script is designed to use a regular expression (regex) to confirm if an email address domain matches one we specify eg: @hotmail.co.uk, @gmail.com, @outlook.com

I developed the code so I could screen email addresses to make sure they only came from within the institution – otherwise it would have broken the tool I was developing for creating Zoom meetings. The email addresses in this example are all stored in one cell in a Google Sheet and are separated via a comma and a single space.

Source: The Gift of Script: Check the domain of an email address by regex

Snippet, Solutions, Sheets, V8, Beginner

Sort by Random – How to Randomize the Order of Rows in Google Sheets – Digital Inspiration

Tags:
| | 0 Comments |

Learn how to sort your data in a Google Sheet in randomized order using Excel formulas and Google Apps Script.

Nice little snippet from Amit Agarwal which includes some Google Apps Script code for randomising the order of rows in Google Sheets. The code has some clever use of .autoFillToNeighbor() , .offset() and .sort() .

Source: Sort by Random – How to Randomize the Order of Rows in Google Sheets – Digital Inspiration

Snippet, Solutions, Admin SDK, V8, Beginner

Using Google Apps Script to find/print inactive users in Google Workspace – xFanatical

Tags:
| | 0 Comments |

In large organizations, it’s overwhelming for admins to find inactive users in the User List of Google Admin Console. These inactive users have not been logged in their Google accounts for a long period. It imposes a potential security risk to the organizational data if these accounts are not taken care of. A free apps script to print all inactive users in Google Workspace. The following open source Google Apps Script can automate the process of printing inactive users in your domain.

Nice little snippet from xFanatical. Check out the source link for all the code and instructions for using.

Source: Print inactive users in Google Workspace – xFanatical

Snippet, V8, Docs, Intermediate

Check/uncheck the entire checklist in Google Docs with Google Apps Script

Tags:
| | 0 Comments |

Currently it is unknown if the checklist can be manipulated in Docs. Clark Lind suggests to replace a list item with a copy of one of them (which is in expected state – check/uncheck)

A nice example of the Google Apps Script developer community helping each other out. In this case Alexander Ivanov was looking for a way to interact with the new Google Docs checklist feature. A direct way to interact with these isn’t possible but some clever coding from Alex demonstrates what is currently possible.

Source: Uncheck a checklist

Snippet, Solutions, Sheets, Web App, V8, Intermediate

XPath Tester using Web Apps Created by Google Apps Script (working with existing Google Sheets functions in Apps Script)

Tags:
| | 0 Comments |

In this post, I would like to introduce the xpath tester using Web Apps created by Google Apps Script.

This is an interesting little Apps Script solution from Kanshi Tanaike which uses the existing Google Sheets =IMPORTXML() in a Google Apps Script  Web App to test an XPath. As there is a delay in loading some Google Sheets function results this post includes a nice little coding pattern using a do...while loop to wait for results to render.

Source: XPath Tester using Web Apps Created by Google Apps Script

Snippet, Solutions, Sheets, Beginner

Bulk create Sheets from a Google Sheet

Tags:
| | 0 Comments |

Use Google Apps Script to bulk create Sheets from a Google Sheet of data. Loop through each row and copy the data to the new file.

Bulk create Sheets from Google Sheet

Bulk create Sheets from Google Sheet

Source: The Gift of Script: Bulk create Sheets from a Google Sheet

Snippet, V8, Beginner, Docs

How to Change the Font in your Google Documents with Apps Script – Digital Inspiration

Tags:
| | 0 Comments |

How to change the font family and font styles of multiple Word documents in your Google Drive with Apps Script. This example show how to replace the font family of your Google Documents of specific sections – the heading titles are rendered in a different font while the tables, list items, body and table of contents are formatted with a separate font.

Source: How to Change the Font in your Google Documents with Apps Script – Digital Inspiration

Snippet, Solutions, Drive, Beginner

Bulk create Shared drives with permissions

Tags:
| |

Bulk create Google Shared drives with permissions all from a Google Sheet.

Bulk create Shared drives from a Google Sheet

Bulk create Shared drives from a Google Sheet

  • Provide the name of the Shared drive on each row in column A.
  • Provide the email address(es) of the Google Account(s) under the relevant Access level column (Manager, Content Manager, Contributor, Commenter, Viewer). Use a comma and a space to separate multiple email addresses, eg: [email protected], [email protected], [email protected].
  • Ensure you include at least one Manager – the script will check for this – so as to prevent creating a Shared drive that you are then unable to access.
  • On the ‘Config’ sheet provide the column numbers – leave the default values unless you change the structure of the ‘Data’ sheet and move columns around.
  • The ‘Log’ sheet should help to troubleshoot any errors you experience, but there will also be a popup message should the script fail at some point.

Source: The Gift of Script: Bulk create Shared drives with permissions

Snippet, Sheets

Get the difference between dates in minutes

Tags:
| |

The following Google Apps Script is designed to get the difference (in minutes) between 2 dates from a Google Sheet. This was part of a tool used to create events from data in a Google Sheet where I needed to get the duration of the meeting for Zoom. The actual date, hour and minute values are separated in columns as it was easier to control user input in that format, so we will need to piece them together.

Start and End date values in a Google Sheet

Start and End date values in a Google Sheet

Source: The Gift of Script: Get the difference between dates in minutes

Snippet, Drive

Set permissions on a Shared drive with Google Apps Script

Tags:
| | 0 Comments |

The following Google Apps Script is designed to bulk set permissions on a Shared drive. It has been created as a standalone Function that requires the ID of the Shared drive and manually entered email addresses for the relevant permissions.

This is how I first worked with it to learn what information was required and how to structure the content, before combining it all into a Google Sheet tool which will be blogged about in the near future.

Source: The Gift of Script: Set permissions on a Shared drive

Snippet, Beginner

Generate a quick unique(ish) value

Tags:
| | 0 Comments |

Generate a quick, random and fairly unique value in Google Apps Script. Found this useful for random strings to use as Meeting IDs for example.

Source: The Gift of Script: Generate a quick unique(ish) value

Snippet, Solutions, Drive

Get permissions of a Shared drive

Tags:
| | 0 Comments |

The following Google Apps Script is designed to get permissions of a given Shared drive. It uses the Shared drive ID to then list a users email address and role they have. This page on Google Drive permissions is useful for what we need to put together to make the call.

Source: The Gift of Script: Get permissions of a Shared drive

Snippet, Solutions, Sheets, Beginner

Find and replace in a Google Sheets with Google Apps Script – whole data search

Tags:
| |

This solution cleanse Google Sheet data by searching for specific words and replacing them with an alternative. As part of this the solution highlights cell changes with an alternative colour. This example uses ‘TextFinder’ rather than ‘For Loops’.

Use the 'Welcome' sheet for input to run the tool

Use the ‘Welcome’ sheet for input to run the tool

Source: The Gift of Script: Find and replace in a Google Sheet – whole data search

Snippet, Solutions, Sheets, Intermediate

Parse 16K URLs and Put the Result to Google Sheet in 3 Minutes

Tags:
| | 3 Comments |

Photo by Joel Mott on Unsplash

Sometimes my best option is to write code on the go rather than using libraries or searching for ready-to-use code. I’ve made a short script for parsing URLs and putting the result into my Google Sheet.

As a developer it is always interesting to see how other people approach problems. This little snippet from Max Makhrov caught my eye because the approach to parsing data in a Google Sheet wasn’t one I’d seen before. Follow the source link to have a look yourself…

Source: Parse 16K URLs and Put the Result to Google Sheet in 3 Minutes

Snippet, Solutions, Maps, Intermediate

Generating custom travel advice links for Nederlandse Spoorwegen using Google Apps Script

Tags:
| | 0 Comments |

I would like to send clients a travel advice (url) but I don’t know how to do that with the Maps Service. Luckely the website of the ‘Nederlandse Spoorwegen’ offers a possibility to do what I want to do (with some Google Apps Script).

For our fans in the Netherlands a nice little snippet from Mariette Timmer that builds a custom url to use with the Nederlandse Spoorwegen travel advice site. To solve this Mariette uses the Google Maps geocoder to convert an address into lat/long for needed for Nederlandse Spoorwegen.

Source: Travel advice #GoogleAppsScript

Snippet, Solutions, Slides, V8, Beginner

Adding Slide Page Link to Shape using Google Apps Script

Tags:
| | 0 Comments |

This is a sample script for adding the slide page link to the shape using Google Apps Script

Nice little snippet that might be useful to look at just for an example for adding shapes to Google Slides.

Source: Adding Slide Page Link to Shape using Google Apps Script

Snippet, Solutions, Groups, Sheets

Extract a list of Google Group members into a Sheet with Google Apps Script

Tags:
| | 0 Comments |

Google Groups

Image: Google

Extract a list of members of a Google Group (email address and role) into a Google Sheet. You simply enter the email address of the Google Group that you belong to and then run the Function.

Source: The Gift of Script: Extract a list of Google Group members into a Sheet

Snippet, Beginner, Forms

Submit a Google Form to a Slack channel via a Webhook

Tags:
| | 0 Comments |

Send a Google Form submission to a specific Slack channel as a message, via a Webhook.

Messages can be posted in to a Slack channel via the Google Form

Screenshot of message when posted in Slack channel

Source: The Gift of Script: Submit a Google Form to a Slack channel via a Webhook

Snippet, Video Tutorial, Calendar

Create a Google Calendar Event via the Calendar API

Tags:
| | 0 Comments |

Use the Calendar API to create an Event via Google Apps Script. Includes a tutorial video.

This is a simple Google Apps Script file that may look lengthy but in actual fact is just each possible item of an event that a user may wish to modify. Some of the items have default values which means you could remove them from the script and the Calendar API would use these defaults instead. My hope is that laying out the format like this allows you to then manipulate as you require and learn as I did.

Source: The Gift of Script: Create a Google Calendar Event via the Calendar API

Snippet, Solutions, Drive, Beginner

Save PDF from URL directly to Google Drive using Google Apps Script

Tags:
| | 0 Comments |

There is a PDF file on the Internet. Say this one. And you want to save it to your Google Drive. The tedious way is to download it to your computer. … and then upload it to your Google Drive. (Seems like too much work.) If only you could send the PDF straight from where it is stored on the Internet, to your Google Drive…

Nice little snippet which shows how you can add .pdf files to Google Drive.

Source: Save PDF from URL directly to Google Drive using Google Apps Script

Snippet, V8, Beginner

Download Gmail Messages as EML Files in Google Drive – Digital Inspiration

Tags:
| | 0 Comments |

This Google Script will help you download your email messages from Gmail to your Google Drive in the EML format.

Nice little snippet from Amit Agarwal for generating .eml files from Gmail messages using Google Apps Script. The post has all the code you need for this.

Source: Download Gmail Messages as EML Files in Google Drive – Digital Inspiration

Snippet, Beginner, Gmail

[Solved] Inline images break when embedded from the htmlBody of an existing Gmail draft

Tags:
| | 0 Comments |

Handy snippet from Andras Sztrokay to handle inline images when using Gmail draft messages as part of your script projects. As highlighted by Romain Vialard in the Google Apps Script community:

Google added the ability to easily retrieve the inline images in an existing draft via the getAttachments() method on October 30, 2018 (see Apps Script release notes)

Source: Inline images break when embedded from the htmlBody of an existing draft [GmailApp] [Apps Script]

Snippet, V8, Beginner, Drive

How to Make your Documents Read-only in Google Drive (using Google Apps Script) – Digital Inspiration

Tags:
| | 0 Comments |

Your documents and files in Google Drive can be modified by anyone who has edit access to the file. Learn how to freeze a document and prevent anyone from editing your files.

Handy little snippet from Amit Agarwal that highlights the Google Drive Locking API to restrict edits on files. The post includes all the code and instructions on how this worked (also shared with a MIT license for reuse).

Source: How to Make your Documents Read-only in Google Drive – Digital Inspiration

Snippet, V8, Intermediate, Slides

Inserting Text on Image using Google Apps Script

Tags:
| | 0 Comments |

This is a sample script for inserting a text on an image using Google Apps Script. The flow of this sample script is as follows:

  1. Retrieve the image size using ImgApp.
  2. Create new Google Slides with the custom page size using DocsServiceApp.
  3. Put the image and text.
  4. Export the result image

Source: Inserting Text on Image using Google Apps Script

Subscribe to Apps Script Pulse...