AppsScriptPulse

Email scheduler for Gmail – overview

Email scheduler spreadsheet columns image

Email scheduler spreadsheet columns

Over the next few blog posts I look to explore my development of an email scheduler for Gmail that is designed to let you schedule when you want an email composed in your drafts to be sent (by specifying the date/time).

This was written prior to Google introducing an email scheduler themselves.

Source: The Gift of Script: Email scheduler for Gmail – overview

Free Web-Based Paging System with Google Apps Script and Hangouts Chat

If you use G Suite for Education or Business and have five minutes to spare then you’ve got everything you need to make a web-based paging system for FREE.

In essence, it’s a doorbell. But one that can reach you wherever you are.

Source: Free Web-Based Paging System with Google Apps Script and Hangouts Chat

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

Storing data from the Flutter app → Google Sheets 📊

In this article, we’ll create a sample app which stores Feedback of user from the Flutter app into Google Sheets using Apps Script.

Source: Storing data from the Flutter app → Google Sheets 📊

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

Loop delete specific rows with counter

Example table with specific rows to delete

Example table with specific rows to delete

Table with row 3 now deleted and data shifted up

Table with row 3 now deleted and data shifted up

The following Google Apps Script has been created to help with a recent difficulty I was having with deleting rows containing a specific value, where the loop was losing the row position due to the spreadsheet data all shifting up a row each time one was deleted. Some searching online suggested looping through a spreadsheet in reverse (from bottom up) to avoid the shift, but that seems more like a workaround than a direct solution …

Source: The Gift of Script: Loop delete specific rows with counter

Data Validation – check for blank cells

The following Google Apps Script code was developed as a tool to check that all the necessary data existed in a spreadsheet before another function was triggered. The reason for this was that if any data was missing it would affect the student Group files I was creating (eg no email address or name) that would eventually prevent further functions from running at a later date.

Source: The Gift of Script: Data Validation – check for blank cells

Debugging in Google Apps Script – Jeff Everhart

Most of the comments I get on this site have to do with someone trying something I’ve written about that isn’t working for them. In most cases, error messages can be your friends since they can point to the particular place your script failed and give you additional insight into why it failed.

This post will describe some of the ways you can use the built-in tools of the Google Apps Script environment to debug or troubleshoot your own scripts. My goal here is start from the easiest to implement and work towards more difficult, but more efficient, methods of debugging.

Source: Debugging in Google Apps Script – Jeff Everhart

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.

Get updated with 2019-nCoV (or any) real-time statistics with Google Sheets

What if you want to catch up with the numbers regularly, say, in particular areas? Of course you can keep scrolling your phone for it, but there are chiller ways to do so.

In this 3-part series, Claire Chung explores the spread of pneumonia outbreak and explains how you can import & track data with Google Sheets, organize it & send notifications.

Source: Get updated with 2019-nCoV (or any) real-time statistics with Google Sheets.

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

Manage Gmail with Google Apps Script

Ever had a messy inbox full of mails? Does it bother you to skip the inbox? Luckily, Google has provided many services that we can harness its utilities to organize the mail using personal preferences or workflow.

You will able to make an auto email archiver/remover after following the steps. This will be done by using Google Apps Script and setting the schedule trigger. The code is pretty simple and straightforward since Google has already provided us with ready-to-use objects for managing Gmail.

Source: Manage Gmail with Google Script

GGvulnz — How to check your domain and groups settings

This article will highlight how you have to be careful about settings when you make some content available publicly, like Groups or Drive file.

The original article describe how an external person of the domain can subscribe to your Slack company channel by using a Google Groups that is publicly accessible. Stéphane Giron’s article helps answer the question – how to prevent and how to identify if some groups are concerned.

Source: GGvulnz — How to check your domain and groups settings

How to get that inactive Twitter handle you really want using Google Apps Script

You may have seen in the news that Twitter will begin deleting inactive accounts of users who hadn’t logged in within the past 6 months before Dec. 11, 2019.

This is bad news if you happen to be @qwerty123, but great news if — like me — you’ve found the perfect username but some clueless bovine has been accidentally hoarding it for the past 8 years.

In this post Sean Cann shows you how you can use Google Apps Script to detect when your preferred new Twitter handle becomes available.

Source: How to get that inactive Twitter handle you really want

TSDynamicUrls (#1) — Leverage the URL Power of Google Docs, Sheets, Slides, Drawings & Forms 💥

In my previous post, I outlined the power of Google document URLs to perform “Simple, Powerful, Work” and promised to share several tools I’ve developed to aid in building them.

Source: TSDynamicUrls (#1) — Leverage the URL Power of Google Docs, Sheets, Slides, Drawings & Forms 💥

5 Google Sheets Script Functions You Need to Know

Google Apps scripting is a background scripting tool that works not only in Google Sheets but also Google Docs, Gmail, Google Analytics, and nearly every other Google cloud service. It lets you automate those individual apps, and integrate each of those apps with each other.

Source: 5 Google Sheets Script Functions You Need to Know

Google Apps Script Development – Best Practices – Andrew Roberts

This is an overview of the various techniques and best practices I have evolved in developing Google Apps Scripts over the years. Of course Google have got a few suggestions of their own, and there are plenty of more general …

This includes some good design pattern considerations for Apps Script projects as well as links to testing libraries, boilerplate frameworks, and extensions that help manage Apps Script work.

Source: Google Apps Script Development – Best Practices – Andrew Roberts

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

Subscribe to Apps Script Pulse...