AppsScriptPulse

Using Google Apps Script as a Webhook

Google Apps Script projects can be published as web apps …. This is helpful if you’re building a tool with a simple UI (using HTMLService), but they can also be used as webhooks to do work within a user account.

Google provides the option of service accounts, so why use webhooks?

For my project, it was because I needed to send calendar invites to other people. Service accounts are generally the way to handle those kinds of jobs, but it needed to have full account delegation, which means it can act on behalf of any user in the domain. That wasn’t an option in this case, so a webhook became the next best option.

Brian Bennett highlights the benefits of using ContentService and Web Apps as a way to let you interface other services.

Source: Using Google Apps Script as a Webhook

Track Customer Subscriptions with Google Spreadsheet & App Script by sending an Email Automatically

Do you still spend time every day monitoring your sales report, identifying which customer subscription is going to expire soon, and manually draft an email to send out to your customer? Would it be better if this manual task can be automated every day for you? If that’s the case, this article can be a great help for you!

Following on from some recent Google Sheet payment solutions here’s a tutorial that highlights how you can use Sheets and Apps Script as a payment reminder tool. Combine with Stripe or Razorpay and you have a complete subscription management solution.

Source: Track Customer Subscriptions with Google Spreadsheet & App Script by sending an Email Automatically

How to Create Automated Snapshots of your Google Sheet using Google Apps Script

In this video, we’ll show you how to automate snapshots of your Google Sheets and have those logs stored in a separate Google Drive folder so that you can keep your logs nicely organized for future reference. To do this, we’ll be using Google Apps Script to create a script that references the SpreadsheetApp library and the DriveApp library.

There seems to be an increasing number of YouTube channels dedicating content to highlight Google Apps Script solutions. Here’s the latest one we’ve come across from aguycalledjoe at bootstrapping.tools

Bulk convert Google Docs to PDFs

Bulk convert all the Google Docs within a given folder to PDFs and optionally delete the Docs afterwards. Specify the destination folder for the PDFs.

Bulk convert Google Docs to PDFs using Apps Script

Bulk convert Google Docs to PDFs using Apps Script

The following Google Apps Script tool is designed to bulk convert all Google Docs within a given Google Drive folder into PDFs. You can choose the destination folder for the PDFs to be put into and also whether you want the original Docs to be deleted. This tool does work on Shared drives.

Source: The Gift of Script: Bulk convert Google Docs to PDFs

Google Apps Script for Android Translation – Saravana Thiyagaraj

Normally we get strings.xml files from our translation team, but today was one of those days, where (due to time constraints) we got the source Google Sheet file. We had to generate strings.xml files out of these. This gave me the perfect opportunity to explore Google Apps Script which you could run on your Google Sheet.

This post highlights how Google Apps Script can quickly solve those little problems, in this case how to generate language .xml files used in Android. Follow the link to the source post for an explanation and code.

Source: Google Apps Script for Android Translation

Google Apps Script Basics – Variables and getting & setting values

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

Google Apps Script Tutorial: Letterhead in Google Docs

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.

Get the redirect location of a URL using Google Apps Script

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

How to Convert HTML to PDF with Google Script – Digital Inspiration

With Google Apps Script, you can easily convert any HTML content into a PDF file. The converted PDF file can be either saved to a folder in your Google Drive, you can email the file as an attachment or the use the UrlFetchApp service of Apps Script to post the PDF file to an external service like Amazon S3 or Dropbox.

Source: How to Convert HTML to PDF with Google Script – Digital Inspiration

How to Build Custom Functions in Google Sheets with Google Apps Script

Build your own functions in Google Sheets that do anything you need them to in a few lines of code.

This is a great tutorial if you are interested in finding out more about developing custom functions in Google Sheets. The post includes everything you need to get started from handling single cell and range inputs, to documenting your function so that users see inline help.

Source: How to Build Custom Functions in Google Sheets with Google Apps Script

Send data to a Google Form via Apps Script

Use Google Apps Script to create a prefilled URL of data that can be submitted to a Google Form.

Screenshot of Google Form questions

Screenshot of Google Form questions

The following Google Apps Script is designed to submit specific data to a Google Form, by creating a prefilled URL. The reason for this was I needed some way of sending data from a number of individual Google Sheets (that I did not own) to one central location, but crucially it needed to be anonymised.

Sending the data directly to a Google Sheet includes version history both in the file and cell meaning it was not truly anonymous. Whereas sending the data through a Google Form and then on to the Response Sheet did strip away anything identifiable.

The logic behind this code is to create a prefilled URL which contains answers to each of the Forms questions. Use the previous webpage link to learn how to create such a URL first so that you understand each question on a Form has a unique value.

Source: The Gift of Script: Send data to a Google Form via Apps Script

Visually code Google Apps Script with Teacher Blocks

This is a quick discussion of how teacher can respond to student work using block based coding.

This is a very interesting prototype which makes it easier for users to visually develop Google Apps Script code. Developed by Stephen Callahan as part of a Google Innovator Project, TeacherBlocks lets to drag and drop blocks to design your code and automatically create the required script to copy/paste into your Google Doc. Watch the video to see it in action or visit the source link to try it yourself.

Source: Teacher Blocks

Search Google Drive files and extract row data

Search and extract the content of Google Drive files. Collate the row data into a collective Google Sheet.

Search and extract the content of Google Drive files

Search and extract the content of Google Drive files

This was created for a specific Subject Access Request but may be of interest/use to others.

Source: The Gift of Script: Search Google Drive files and extract row data

Tracking Google Sheet Opens with Google Apps Script

The basic principle behind tracking Google Sheet opens is the same as the one behind email tracking with a transparent pixel. In a Google Sheet, we will use the IMAGE()function, which will call a deployed Google Apps Script web app URL with some parameters in the query string. The web app will be able to do whatever you need among the following: record to a spreadsheet, create a message via a messenger, or simply send an email.

Source: Tracking Google Sheet Opens with Google Apps Script

How to Automatically Archive Gmail Messages and update your Gmail signature with Google Apps Script

Chanel Greco at saperis has shared some useful video tutorials that should help beginner Google Apps Script developers with working with Gmail:

In this Google Apps Script tutorial we will learn how to automatically archive Gmail messages.

In this video tutorial you will learn how to create an email signature in Gmail.

Check the domain of an email address by regex

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

Search Google Drive for owned files

Use Google Apps Script to search Google Drive for all files owned by a user and collate their details onto a Google Sheet.

Provide the owner and maximum script runtime for search Drive for files

Provide the owner and maximum script runtime for search Drive for files

It will collate:

  • The filename,
  • The file ID,
  • A direct/clickable link to the file,
  • The type of file eg PDF, Googe Sheet/Doc, etc.

Source: The Gift of Script: Search Google Drive for owned files

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

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

A Google Apps Script powered Google Chat Updates Bot

Keep up to date with any feed by having new posts published to a Google Chat room using Apps Script and Webhooks. Feeds included in this example are various official Google blogs.

This is a nice solution developed by Justin Wexler which provides an easy solution which can monitor a list of blogs and post any updates to a Google Chat room. The source post provides more detail on setup and how to use the code with your own Google Chat rooms. The code also provides a useful pattern for monitoring updates from RSS feeds.

Source: jdw353/google-workspace-apps-script-toolbox

Google Apps Script: A Beginner’s Guide

With Apps Script, you can do cool stuff like automating repetitive tasks, creating documents, emailing people automatically and connecting your Google Sheets to other services you use.

This Google Apps Script introductory post from Ben Collins has been around for a while, but worth keeping in the old link locker as it summarises lots of useful information and links. Click through to the source for more details.

Source: Google Apps Script: A Beginner’s Guide

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

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

Extract text from multiple Google Docs into a Sheet

The following Google Apps Script is designed to iterate through Google Docs in a given Google Drive folder and extract the paragraphs of text along with a link to each file into a Google Sheet. A new column will automatically be appended for each paragraph.

This tool was designed with the vision that you may wish to centrally collate some comments/feedback written by others in Docs, into one central location so you do not need to open each file in turn.

Provide a Google Folder ID to extract text from Docs

Provide a Google Folder ID to extract text from Docs

Source: The Gift of Script: Extract text from multiple Google Docs into a Sheet

Sending emails on a schedule with Google Apps Script

Ever wondered how you would automate tasks in Google Workspace without using a tool developed by an external entity? Google has your back. In this article, we will see how one can send emails on a schedule. As an example, I have used an annual event. A birthday reminder script, if you will.

A simple example but for those just starting out in Google Apps Script a nice example of using time-based triggers.

Source: Sending emails on a schedule with Google Apps Script

TSChatWise : Post lessons and learning resources specified in a Google Sheet to one or more Google Chat rooms — Getting Started

In a previous post I introduced TSChatWise … a Google Sheets and Apps Script powered Google Chat tool for teaching and learning. 🚀 This post is the third post in a TSChatWise “Getting Started” series where I’ll discuss how to get started with TSChatWise.

We’ve feature TSChatWise in AppsScriptPulse in the past but worth revisiting as author Laura Taylor has created some resources to help you get started.

Source: TSChatWise — Getting Started

Creating Colorful Buttons on Google Spreadsheet using Google Apps Script

This is a sample script for creating the colorful buttons on Google Spreadsheet on Google Apps Script.

There are some interesting features in this script example, combining the use of Google Slides to create buttons and then assigning a script function to the result using the .assignScript() method.

Source: Creating Colorful Buttons on Google Spreadsheet using Google Apps Script

Data Enrichment with the Google Tables Apps Script Bot -Ben Collins

Learn how the Google Tables Apps Script Bot works and see an example of data enrichment using the Mattermark API.

In this post, I’ll show you how to use the new Google Tables Apps Script Bot. When something happens in your Google Table – a new row, or a value changes – a bot can be set to trigger an Apps Script function. It opens up a world of new possibilities.

While the Tables beta has been announced as joining Google Cloud it is still limited to US users. In the meantime for those outside the US we are limited to watching from the sidelines. Fortunately we can watch through the expert eyes of community members, in this case the esteemed Ben Collins. In this post Ben highlights how the experimental Tables Apps Script integration can be used to develop bots.

Source: Data Enrichment with the Google Tables Apps Script Bot –

Bulk create Sheets from a Google Sheet

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

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

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

Subscribe to Apps Script Pulse...