AppsScriptPulse

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

Snippet, Solutions, Sheets, Intermediate

Create a Zoom meeting via the API using Google Apps Script

Tags:
| |

Create a meeting in Zoom via the API and OAuth, using Google Apps Script and a Web App. The details for the meeting will be collected from a Google Sheet.

Zoom meeting details in a Google Sheet

Zoom meeting details in a Google Sheet

Source: The Gift of Script: Create a Zoom meeting via the API

Snippet, V8, Beginner, Sheets

Setting Alternate Background Colors for Rows in Google Spreadsheet using Google Apps Script

Tags:
| | 0 Comments |

This is a sample script for setting alternate background colors for rows in Google Spreadsheet using Google Apps Script.

It has already been known when the conditional formatting rule and custom function are used, this can be simply achieved. In this report, I would like to introduce the method for using Google Apps Script.

Source: Setting Alternate Background Colors for Rows in Google Spreadsheet using Google Apps Script

Snippet, Intermediate, Sheets, Web App

Get your Zoom meeting settings via the API and storing in a Google Sheet using Google Apps Script

Tags:
| | 0 Comments |

Get your Zoom meeting settings via the Zoom API and OAuth, using a Web App in Google Apps Script. Here we will access user settings in your Zoom account, specifically the meeting settings, and log the results in a Google Sheet. This provides both a way of confirming we have made a successful connection and will allow us to create subsequent Zoom meetings using these settings.

This will log the results in a Google Sheet to help you understand what is happening and the type of data that is returned.

Source: The Gift of Script: Get your Zoom meeting settings via the API

Snippet, Solutions, Web App, Video Tutorial

Connect to Zoom API with Apps Script and OAuth

Tags:
| | 0 Comments |

How to connect to the Zoom API via OAuth. Using Google Apps Script and a Web App. An alternative to JSON Web Token. Includes a tutorial video.

Source: The Gift of Script: Connect to Zoom API with Apps Script and OAuth

Snippet, Solutions, V8, Intermediate

How to Share User Properties between Google Apps Script Projects – Digital Inspiration

Tags:
| | 0 Comments |

The Properties Service of Google Apps Script is used by developers to store app configuration and user specific settings. The properties data is scoped to a specific user, or a specific project, and cannot be shared between different projects. … In some specific cases, we may want to give access to our store data to another user so they may build upon the existing configuration rather than having to build everything from scratch.

Amit Agarwal shares a useful snippet for letting users export and import data from PropertiesService . There are some nice touches in this solution including using the JavaScript File Reader API.

Source: How to Share User Properties between Google Apps Script Projects – Digital Inspiration

Subscribe to Apps Script Pulse...