AppsScriptPulse

trigger automated meeting invite reminders using apps script

send a follow-up email to all undecided participants of a meeting that you’ve scheduled via google calendar using apps script.

here’s a piece of code that you could schedule for every morning (preferably before your official work hours start) to be triggered, such that the script could iterate through every single meeting event that you’ve scheduled (i.e. where you’re the “organiser”) for the day and send an email to all those invitees who’ve neither accepted nor declined (even excluding the maybe’d ones too) to said invites.

Nice post from Sourabh Choraria which covers interacting and using data from your Google Calendar.

Source: trigger automated meeting invite reminders using apps script

Benchmark: Loop for Array Processing using Google Apps Script with V8

Benchmark: Loop for Array Processing using Google Apps Script with V8 – submit.md

Kanshi Tanaike has published some useful benchmarks looking at the process time for various loop methods comparing the old Google Apps Script runtime with V8. Something to keep in mind that while there are performance improvements there is a cost as calls to G Suite services:

Source: Benchmark: Loop for Array Processing using Google Apps Script with V8

TeslaGAS is a Google Apps Script library that helps writing scripts for your Tesla car

Photo by Afif Kusuma on Unsplash

Photo by Afif Kusuma on Unsplash

TeslaGAS is a library that helps writing scripts that communicate with your Tesla – Zzapps/teslagas

Riël Notermans from Zzapps has published the beginnings of a nice little Google Apps Script library that you can use to communicate with your Tesla car.

Source: Zzapps/teslagas

V8 Runtime Overview  |  Apps Script  |  Google Developers

Historically, Apps Script has been powered by Mozilla’s Rhino JavaScript interpreter. While Rhino provided a convenient way for Apps Script to execute developer scripts, it also tied Apps Script to a specific JavaScript version (ES5). Apps Script developers can’t use more modern JavaScript syntax and features in scripts using the Rhino runtime.

To address this concern, Apps Script is now supported by the V8 runtime that powers Chrome and Node.js. You can migrate existing scripts to V8 in order to take advantage of the modern JavaScript syntax and features.

This page describes the new features enabled by V8 and how you can enable V8 for use in your scripts. Migrating scripts to V8 describes steps for migrating existing scripts to use the V8 runtime.

Developers have been waiting a while for the new Google Apps Script runtime, since 2018 in fact, but it’s now here. The new runtime not only allows developers to use modern JavaScript syntax but when announced in 2018 also comes with a a tenfold (x10) faster execution. Follow the link to the developer docs for the full migration guide.

Source: V8 Runtime Overview  |  Apps Script  |  Google Developers

SHEETSCON 2020 – Free Online Conference for Google Sheets Users, 11-12 March, 2020 

SHEETSCON 2020

Come and celebrate Google Sheets at SheetsCon 2020, the world’s first, dedicated online conference for Google Sheets users.

Listen to world-class experts talk about how they craft solutions using Google Sheets and G Suite.

Over the course of two jam-packed days, you’ll hear everything from deep-dive tutorials on specific topics to cutting-edge solutions across different industries. You’ll have the chance to network with other professionals and meet vendors at the forefront of the Google Sheets movement.

Looks like a great event being hosted by Ben Collins for Google Sheets users. Click through to the SheetsCon 2020 website for the full agenda and free registration

Source: SheetsCon 2020

 

Gmail Mail Merge For A Specific Label With Apps Script – Ben Collins

Every Monday I send out a Google Sheets tip email and occasionally I’ll include a formula challenge…. It would have been too time consuming to reply to all 150 responses manually from my inbox. Since 95% of all my replies would be the same … it was a perfect case for automation.

Ben Collins expertly shares how he automates part of his Gmail inbox to review emails in Google Sheets and prepare draft replies to be sent as responses. As always Ben provides details of each step and the code.

PS Don’t miss Ben’s free Automation With Apps Script course  – enrolment closes 31 January 2020.

Source: Gmail Mail Merge For A Specific Label With Apps Script – Ben Collins

conversational stats – a way to talk to your data

leverage the power of dialogflow & connect your data sources with simple apps script functions to experience the next-gen approach towards consuming data and shaping insights.

Nice proof of concept from AppsScriptPulse Contributor (and now Editor :)
Sourabh Choraria of the results of a recent hackathon to use Google Sheets as a data source for a Dialogflow agent/bot.

Source: conversational stats – a way to talk to your data

How to use GCP service accounts with Google Apps Script projects to automate actions in G Suite

A Google Apps Script and Google Cloud Platform tutorial to learn how to use GCP Service Accounts to scale and protect automations

Examples of Google Apps Script being used in an enterprise setting tend to be few and far between which means this tutorial by Jeffrey Daube is an interesting insight to how script projects can be setup to serve the entire company. In particular, Jeffrey details how the use of service accounts can be used so that G Suite documents can be created with a non-human user allowing you to make calls to Google APIs without the usual authentication flow.

Source: How to use GCP service accounts with Google Apps Script projects to automate actions in G Suite

App Maker will be shut down on January 19, 2021

Source image credit: Dana Fried @leftoblique

G Suite Admins have started to be notified that App Maker will be shutdown on January 19, 2021. In the turndown notification Google cited ‘low usage’ as the reason for the shutdown. This news comes shortly after the announcement of Google acquisition of no code platform AppSheet and AppSheet is identified as one of the alternative platforms:

Due to the specific source code used for App Maker, you can’t directly migrate your apps to another platform. Depending on your use case, we recommend these alternative tools:

  • If you use App Maker to automate business processes: Use AppSheet, a new addition to our app development portfolio that has capabilities similar to App Maker. App Maker data is stored in Cloud SQL, and App Sheet supports Cloud SQL databases. This allows you to build an application on the existing database tied to your App Maker app.
  • If you use App Maker to develop apps: Use App Engine to build and deploy applications on a fully managed platform. App Maker data is stored in Cloud SQL, allowing you to build an App Engine application on the existing Cloud SQL database tied to your App Maker app.
  • If you use App Maker for data collection: Use Google Forms, which has many new features that were not available when App Maker launched.

Google included the following schedule for turndown:

  • Today, existing apps continue to work. Though App Maker is no longer under active development, the service will continue to be maintained.
  • Starting April 15, 2020, you will no longer be able to create new App Maker apps. You will still be able to edit and deploy existing apps.
  • Starting January 19, 2021, App Maker apps will stop working and you will no longer have access to them. App Maker data stored in Cloud SQL will remain unchanged and continue to follow the policies established by your Google Cloud Platform (GCP) account.

Source: Google App Maker will be shut down on January 19, 2021

Create a mail merge using Gmail and Google Sheets | Sheets to Apps

Whether you are in sales, marketing, education, project management, data analysis, etc., it’s common for people to want to send emails to multiple recipients with information that is customized to each. In this episode, learn about Martin Hawksey’s mail merge solution, which helps anyone send emails with customized content from a Google spreadsheet, and Gmail.

See the YouTube description for all the links you need.

Release Notes  |  Apps Script  |  Google Developers – January 21, 2020

The first release notes in 2020 for Google Apps Script are mostly related to the announcement of general availability of G Suite Add-ons and cover:

  • Manifest file structure changes (Note: “Manifest fields that previously supported Gmail add-ons still exist, but are now deprecated”)
  • Extension of the Card service
  • A Conference Data service has been added to sync Google Calendar with 3rd part conferencing applications

Visit the source link for more details.

Source: Release Notes  |  Apps Script  |  Google Developers

Google Apps Script snippet for Google Sheets to view json strings in a modal dialog

View json strings in a modal dialog

View json strings in a modal dialog. Activate the cell that contains a JSON string Click menu item A foldable JSON will be shown in the modal dialog

Handy little Google Apps Script snippet from Riël Notermans (Zzapps) that makes it easier to view any JSON you are storing in a Google Sheets cell.

Source: Zzapps/google_sheets_json_viewer

G Suite Updates Blog: G Suite Add-ons now generally available in Calendar, Gmail, and Google Drive

Take action without leaving G Suite

Image credit: Google – Take action without leaving G Suite 

Last year we announced the beta of G Suite Add-ons, a new cross-suite platform that connects G Suite to your favorite workplace apps. Beginning today, G Suite Add-ons will begin rolling out to all users.

Whilst the Add-on ecosystem is being extended making them generally available to end users developers will have to wait until you can start developing new add-ons for G Suite:

Organizations can also build their own add-ons using Apps Script. Note that the developer feature will be fully available in early February

Source: G Suite Updates Blog: G Suite Add-ons now generally available in Calendar, Gmail, and Google Drive

AppSheet purchased by Google Cloud – News – Google Groups

Google Cloud has acquired AppSheet. Do I care? At first I thought, “Not really, it’s just interesting news.”

I guess that I do care in the sense that I want to be valued as a programmer, and no-code development feels like programmers are being thrown away in favor of something else. The driving force behind this is development costs. Even if an independent programmer only charged $5 dollars an hour, in two hours the customer would have paid $10 dollars, which is twice as much money as the $5 a month AppSheet plan.

It was recently announced that Google has acquired AppSheet a low-code platform for business users that lets you build applications that can integrate into a variety of sources including Google Sheets. This has resulted in an interesting thread on the Google Apps Script Community Google Group linked to from the source link at the end of this post, in particular, impact on Apps Script developers. On the one hand you can argue that products like AppSheet, with there low/no code environments remove the need for developers. However, the general consensus on the thread is rather than removing developer opportunities it may in fact create them with opportunities to extend the basic functionality of AppSheets. This comment from Alan Wells in particular caught my eye:

What AppsSheet does, is increase the number of apps and code being created, as opposed to lots of business creating no apps at all.  It’s kind of like, you need a lot more mechanics if everyone can afford a car.  If only the rich can afford a car, then there aren’t going to be many car mechanic jobs.  So, I think that AppSheet is filling a need, which increases the number of apps.  Some of those apps will crash, and they’ll need the equivalent of an auto body technician to repair the damage.  This is part of the evolution of the computing industry.

You can join in on this discuss using the following link:

Source: AppSheet purchased by Google Cloud – News – Google Groups

Google Apps Script growth analysis based on Stackoverflow analysis

Source: Kanshi Tanaike

An interesting analysis prepared by Kanshi Tanaike of the ‘google-apps-script’ tag usage on Stackoverflow which shows that since 2016 there has been an increase in questions related to the tag with a bigger increase in 2019. Clicking through to the source post you’ll also see the data shows that new users asking questions about Google Apps Script is growing quicker than those who are answering them.

Source: Trend of google-apps-script Tag on Stackoverflow 2020

iterate ~80x faster through spreadsheet using map function in apps script

use array map method instead of “for” loops to iterate faster through 2-dimensional spreadsheet data.

Sourabh Choraria shares his experience of using Array.map to find values in a Google Sheet and the performance benefit of using this approach.

Source: iterate ~80x faster through spreadsheet using map function in apps script

G Suite Training & Automation 20-21 February 2020 F2F and Online organized by SW gApps LLC

G Suite Training & Automation Conference Speakers

SW gApps LLC is hosting a G Suite and automaton training conference for organizations who have invested in Google’s productivity software known as G Suite. Send your associates to be more equipped and productive!

SW gApps LLC which is run by long time Google Apps Script contributor Steve Webster is running a two day training event for anyone interested in G Suite. Speakers and trainers include James Ferreira (Google, G Suite Specialist), Alexandrina Garcia-Verdin (Google, G Suite Developer Advocate [remote]) Ben Collins (Google Developer Expert and Google Sheets Developer & Data Analytics Instructor) as well as Steve Webster and Eric Schwartz from SW gApps LLC. There is an early bird registration available until February for either attending in-person or a reduced fee for remote attendees. You can follow the source link for more details.

Source: G Suite Training & Automation

Google Sheets Sort By Color And Google Sheets Filter By Color, With Apps Script

Use Google Apps Script to create Google Sheets sort-by-color and filter-by-color features and replicate the functionality that Excel has.

Love the clever workaround Ben Collins uses to let you filter and/or sort a Google Sheet by cell colour. Ben’s post includes all the code you need and an explanation of how it works … or you can just make a copy of the sheet and start using yourself.

Source: Google Sheets Sort By Color And Google Sheets Filter By Color, With Apps Script

How I Email: Ben Collins, Google Sheets Developer & Data Analytics Instructor – The Gmail Genius

Google Sheets expert Ben Collins shares tips and tricks for getting the most out of G Suite for your personal or business email.

In this interview Ben also shares a number of Google Apps Script based solutions he has developed and shared for managing his inbox including “I wrote a small program that automatically creates a draft reply from each row of my Google Sheet. Based on the sentiment of the response, the program adds a generic thank you message. Now my workflow is to go directly to my draft Gmail folder, review these draft emails (which include the feedback so I don’t even have to look at the Google Sheet) and add a custom response before hitting send”. You can view the interview for a link to the example script Ben shared for this.

Source: How I Email: Ben Collins, Google Sheets Developer & Data Analytics Instructor – The Gmail Genius

List of over 130 shared Google Apps Script libraries developed by Bruce Mcpherson

Regular Google Apps Script Community contributor Bruce Mcpherson has published a list of all the Google Apps Script libraries he’s developed since 2012. The list includes links to the source repositories and if you spot a library you like the sound of you can search for more information on Bruce’s Desktop Liberation site .

Source: List of shared Apps Script libraries · Issue #31 · brucemcpherson/desktopliberation

Subscribe to Apps Script Pulse...