AppsScriptPulse

Create a book tracker using Apps Script and the Google Books API in 10 minutes

I wanted a quick and easy way for my kids to track the books they read, as well as a way for my wife to catalog all the books in the house. I know there are other services out there like Goodreads that can store your book list…but I just wanted to house the data myself, and not have to create accounts for my young kids.

Nice little project that glues together Google Forms and Sheets and the Google Book API with a little but of Google Apps Script. Follow the link for more information and the source code.

Source: Create a book tracker using Apps Script and the Google Books API in 10 minutes

Create Google Classrooms using Google Apps Script

Creating a Google Classroom and maintaining it can be a bit hectic for so many students in a school. Say you are the moderator of your school and your task is to create 10 Google Classrooms for different teachers. Manually doing this can be a very hectic process, that’s where Google Apps Script comes in.

Using Google Apps Script, you can automatically create Google Classroom with the data in the Google Sheet, and paste the Class code in the Google Sheet.

Source: Create Google Classrooms using Google Apps Script

How to Make Your NPM Package Available in Google App Script

Boboss74, CC BY-SA 4.0, via Wikimedia Commons

I recently came across GAS — Google App Script and immediately the first hurdle I had to overcome to was make my NPM packages available on there. I had to hop through a couple loops to make this happen. I wanted to call out the steps in this blog so it’ll hopefully help someone out there as well.

In Pulse we’ve previously highlighted a couple of approaches for using NPM libraries in Google Apps Script. The linked source post come from, recent guest on Totally Unscripted, Nima Poulad, Senior Software Engineer at DocuSign. Nima highlights a quick and dirty approach of using eval() on hosted NPM libraries, but goes on to show how Browseify can be used to convert NPM libraries, highlighting some considerations for this approach.

Source: How to Make Your NPM Package Available in Google App Script

Multiple Sheets Action. Use Macro Recorder 🔴

Image credit: Max Makhrov

The idea: perform the same action on multiple sheets. The idea is not new, my approach was to use macro each time to see the correct code snippet. It was useful and saved me hours on my current project.

A clever solution from Max Makhrov with some boilerplate script that makes it easy to use a recorded macro across multiple sheets. To make this solution even better I’ve suggested an addition which activates the sheet which makes it easier to reference recorded macro functions. To see in action here is a copy of Max’s Google Sheet with updated code.

Source: Multiple Sheets Action. Use Macro Recorder 🔴

How to call Google Cloud Run (or Cloud Functions) from Google Apps Scripts

I struggled how to make an authenticated call from Apps Scripts to Cloud Run even though I had owner rights on the GCP project. This post describes how to do it without reinventing the wheel.

Some very useful setup tips for using Cloud Run with Google Apps Scripts including additional references if you are keen to explore more.

Source: How to call Google Cloud Run (or Cloud Functions) from Apps Scripts

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

Create personal stock portfolio tracker with Google Sheets and Google Data Studio

Image credit: All Stacks Developer

The idea is to take advantage of what Google Sheets and Google Data Studio offer and pull them together to build a personal stock portfolio tracker.

This post provides an insight into how you can maximise the combination of Google Sheets, Data Studio and Apps Script to create powerful dashboards. The source link provides an overview of the solution and if you are interested in finding out more about how this solution works visit LION stock portfolio tracker guide (from there you can make a copy of the demo and see the code).

Source: Create personal stock portfolio tracker with Google Sheets and Google Data Studio

How to Use Service Accounts and OAuth2 in Google Apps Script

Image: Dmitry Kostyuk

Give your scripts privileges that your users don’t have … In 99% of all cases, authorizations in Google Apps Script are extremely straightforward. When a user executes their script, they run it as themselves with their respective authorization scopes. … However, what if you need to give more rights to your app beyond what your intended users will have? … This is where service accounts come in.

A useful post to find out more about service accounts and how to use them with Google Apps Script. The tutorial put together by Dmitry Kostyuk includes example code for using a service accounts with Firestore, BigQuery and the Admin Directory API.

Source: How to Use Service Accounts and OAuth2 in Google Apps Script

How I manage password encryption for my last Google Workspace add-on onleetransfer | by Stéphane Giron | Aug, 2021 | Medium

Manage password encryption in Firebase Cloud Functions for Apps Script using bcrypt with an example of front-end integration.

Useful post from Stéphane Giron with an overview of how Firebase Cloud Functions can be used in combination with Google Apps Script to provide add-on functionality.

Source: How I manage password encryption for my last Google Workspace add-on onleetransfer | by Stéphane Giron | Aug, 2021 | Medium

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

Bypassing the Maximum Script Runtime in Google Apps Script

Image: Dmitry Kostyuk

Google Apps Script is an amazing language that can automate a lot of your work. However, working with GAS also means that you have to learn to live with its built-in limitations and quotas. One such quota is the total script runtime. … I have experienced that the time required to complete tasks like copying or even simply listing files on a drive or in a directory can be quite long. Merging hundreds or thousands of documents can also take longer than both thresholds. Now let’s look into how we can build a solution

Another community contribution looking at handling script runtime and this one is also worth looking into for tips on structuring your code. The tutorial includes lots of useful information and an explanation of what is going on and might be a useful example to look if you are interested in moving your Apps Script coding abilities to the next level.

Source: Bypassing the Maximum Script Runtime in Google Apps Script

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

Import data from MS SQL Server to Google Sheets using Google Apps Script

Image created by Trang Nguyen Ngoc with the icons by Freepik, Pixel perfect and phatplus on Flaticon

There are many ways to push data from SQL Server to Google Sheets, including using Python or Google Apps Script. While Python is great for a heavy SQL query, I find it a bit of a hassle when it comes to scheduling. Meanwhile, Google Apps Script works perfectly well for middle-size SQL queries and the triggers make it very simple to schedule, there is also no need to use any API.

In this article, I will share with you how to push data from MS SQL Server to Google Sheets using Google Apps Script and the tips I learned when implementing the solution.

Continuing the SQL theme this post from Trang Nguyen Ngoc provides a useful overview and introduction for connecting to a MS SQL databases using JDBC service. The post is particularly useful as it contains some tips if you encounter issues with connection.

[Note: there have been some issues recently with the JDBC service and if you encounter problems we recommend checking the issuetracker]

Source: Import data from MS SQL Server to Google Sheets using Google Apps Script

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

Make your own Office Desk Booking with Apps Script

In this new era post Covid, to come back to work we have to maintain a certain quota of people at the office. At Devoteam G Cloud we had this needs and to be able to manage people at office we build a web app with Apps Script to manage team and people presence on site.

A great example from Stéphane Giron using Google Apps Script to rapidly develop and deploy an office management solution. The post highlights the utility of PropertiesService to store data in the web app. This solution falls into the category of minimum viable product, but given the nature of Apps Script could be a great project to build on with integration with other Google services.

Source: Make your own Office Desk Booking with Apps Script

Create Filters in Google Sheet using Google Apps Script

How to create filters in Google Sheets using Google Apps Script.

There are lots of ways you can filter data in Google Sheets and in this post Aryan Irani uses SpreadsheetApp.newFilterCriteria() to create a filter and then copy the filtered data to a new sheet. The post contains everything you need to know to filter data in this way.

Source: Create Filters in Google Sheet using Google Apps Script

Create a simple Approval process via Email with Google Form and Google Apps Script (Web Apps Deployment)

At the end of this article, you will know how to create a workflow app, based on a Google Form that send an email to be approved or deny including logging of who is approving or denying with Apps Script.

Great post from Jérémy Dessalines who has put together this tutorial for a custom workflow using a Apps Script Web App. The post covers triggers, the publication process and how to generate a unique ID and includes some great tips at the end.

Source: Create a simple Approval process via Email with Google Form and Google Apps Script (Web Apps…

Taking Away the Pain from Unit Testing in Google Apps Script

Unit testing is an extremely important practice. It helps developers avoid bugs easily and lets you know when something is broken immediately. … There are numerous libraries that exist for unit testing in JavaScript, including Jest and Mocha; however, they aren’t necessarily well-adapted to run under the Google Apps Script environment. … For that reason, I built my own library with Google Apps Script in mind.

We’ve featured a couple of community contributions around Google Apps Script Unit Testing and if this is a topic that interests you then the related posts section below will link to additional resources. This post from Dmitry Kostyuk is a great addition and the accompanying UnitTestingApp library that is shared in the post has some very useful Google Apps Script related features such as a dedicated method to check your data is a 2D array (particularly useful in the context of handling spreadsheet values). Follow the source link for more details…

Source: Taking Away the Pain from Unit Testing in Google Apps Script

Replace text in Google Spreadsheets with Apps Script using TextFinder

Google Sheets has a powerful find and replace function. But you may want to use Google Apps Script to do the same job. StackOverflow has a question on this. The accepted answer would require you to loop through all cells, find the text, replace it and post the new text back to the cell. Google Apps Script has a more powerful, and simpler, method called TextFinder .

Usual post highlighting the TextFinder method which you can use to find and replace text in Google Sheets. The post has more information about this method and how to use it.

Source: Replace text in Google Spreadsheets with Apps Script

Create membership expiration in Google Groups for Google Workspace

Manage Google Groups membership expiration with Google Apps Script and Cloud Identity API

As noted by Stéphane Giron Google Group member expiration is not available on all types of Google Workspace accounts, but this post is a useful reminder of what is now possible when it is. The most includes a full explanation and code to get started.

Source: Create membership expiration in Google Groups for Google Workspace

Using Design Patterns in Google Apps Script – Introduction to façade and proxy patterns

A lot of users try and quickly learn GAS and use it to make their lives easier. It’s all great, however the code we sometimes tend to come across on StackOverflow and other sites lacks best practices, hence I thought it was time to start bringing them up and I will start today with design patterns.

As noted by the post author Dmitry Kostyuk the Google Apps Script V8 runtime opens up many new coding opportunities previously not available to Apps Script developers. Using the common use case, fetching data and writing it to a Google Sheet, Dmitry explains how the façade and proxy code patterns can be used to write efficient easy-to-read code.

Source: Using Design Patterns in Google Apps Script

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

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

Modern Angular in Google Workspace Editor Add-ons

I intend this post for established Add-on developers who want to use Angular in Google Workspace editor Add-ons and Google Apps Script webapps. This does not attempt to show how to create Add-ons or Angular apps, but the tooling and build process to use Angular apps in Add-ons. I am also assuming familiarity with installing and using CLI tools in your development workflow.

We recently had the author of this post, Spencer Easton, on an episode of Totally Unscripted to talk about this topic. This companion post provides an overview of using Angular for Add-on development. There is a cost associated with this solution has separate hosting is required for the Webpacks, but as covered by Spencer the cost is low (for a 100K+ users add-on the cost is $8/month).

Source: Modern Angular in Google Workspace Editor Add-ons

Maintain Google Apps Script in an Enterprise environment – Google Apps Script deployment with Cloud Build

In this article I will showcase the way I deploy Google Apps Script code in an Enterprise environment. I will share the actual build file and the necessary steps and tools to reproduce my workflow. This approach does require Google Cloud Platform knowledge and a credit card. The instructions are high level, this article is not a codelab.

For those interested in scaling up their Google Apps Script development Jasper Duizendstra outlines how you can use Google Cloud Build for version control and Continuous Deployment (CD). As Jasper notes:

When the CD pipeline is in place it becomes easy to deploy the script, separate the responsibilities and support multiple versions and implementations of the code … the most important advantage of using CD is the mindset that it enables. It provides an environment where I feel confident and safe to create small incremental changes in my applications.

Source: Google Apps Script deployment with Cloud Build

Tracking Email Opens with Gmail, Sheets, and Apps Script

This walkthrough will let you build a tracker for email opens using Gmail, Google Sheet, and Google Apps Script.

Interesting solution that popped up in the Google Apps Script community for logging email opens using Google Apps Script. The post includes a detailed walkthrough for setting this up as well as some caveats about reliability.

Source: Tracking Email Opens with Gmail, Sheets, and Apps Script

Introducing LASTEDIT(), a Timestamp Formula for Google Sheets

“When was the last time this sheet was uploaded?” Probably one of the biggest requests I get is how do I add a timestamp to check when some particular range was changed. Well, [here] is a simple function you can add to your Google Sheets doc to add the formula LASTEDIT()

The question of recording when a cell/range has been edited has landed in my inbox before. I’ve not tested this solution myself but from the scan of the script worth noting that this solution will only work on one cell array per Google Sheet, so some modification might be required if you have more than one range you’d like to monitor.

Source: Introducing LASTEDIT(), a Timestamp Formula for Google Sheets

Google Apps Scripts Libraries — How to set them up and turn Slack into a real-time logging platform

This post shows how to setup Libraries in Google Apps Scripts to share code and functions. We will use this while using Slack for logging.

Nice post from Matt “Rudy” Benton on using libraries in your Apps Script projects to make it easier to share code/credentials. Matt illustrates this with a Slack app that has also been documented.

Source: Google Apps Scripts Libraries — How to set them up and turn Slack into a real-time logging platform

Subscribe to Apps Script Pulse...