AppsScriptPulse

Validate postal address with the new Google Maps Address Validation API and Google Apps Script

 

Validate addresses using Google Maps Address Validation and Apps Script. Discover onleeaddress the add-on for Google Workspace.

I missed the announcement in November 2022 that there is a new Google Maps Address Validation API. I also missed this post from Stéphane Giron showing how you can use the Address Validation API in Google Apps Script.

The concept is simple, you provide the Address Validation API with what you think is a correct address and the API returns information on each component of the address and additional metadata. Visit the source post for more details on setup and some example code.

Source: Validate postal address with the new Google Maps Address Validation API and Apps Script

Quick backup solution for Google Apps Script by creating versions in Google Docs

Get security and peace of mind by backing up your Google Apps Script files to Google Docs and trigger for continuous backup.

We’ve featured a couple of methods for restoring Google Apps Script code in Pulse, but most of these rely on recovering a deployed version of the script. Stéphane Giron wields his Apps Script hammer and skilfully demonstrates how you can use Google Docs and it’s built-in version history as a way of keeping a backup copy of your code.

Source: Quick and not so Dirty backup solution for Google Apps Script Code

Create a room availability dashboard for Google Workspace with onleetable and Apps Script

Make a Room availability dasboard for your Google Workspace domain wihtin a minute with onleetable and Google Apps Script

This post from Stéphane Giron caught my eye for a couple of reasons. Besides a quick and easy solution for displaying room availability in an attractive table as a bonus you can see/copy the script Stéphane uses to extract calendar data into a Google Sheet. Follow the source link for the code and more details.

Source: Create a room availability dashboard for Google Workspace with onleetable and Apps Script

Automatically backup Google Drive folders to Cloud Storage with Google Apps Script

Image credit: Stéphane Giron

Few days ago, with the launch of French region ‘europe-west9’ in GCP, I made an apps script to backup a Drive folder to Cloud Storage. It is a cool script and works nicely but after some exchange, we can make it better.

So here I come back with onleebackup an open source code to backup multiple Google Drive folders to cloud storage with synchronisation.

A very interesting open source project from Stéphane Giron which lets you backup Google Drive folders to Google Cloud Storage. An important caveat is with Google Apps Script limitations like script runtime and URL Fetch POST size this won’t work if you have gigabytes of data. The code has some nice features like handling Google Docs/Sheets/Slides file types, converting them to equivalent MS Office formats as well as management of Google Drive shortcuts, which requires calls to v3 of the Google Drive API. The source post provides details for setting up onleebackup, which also includes a link to a previous post with code highlights.

Source: Automatically backup Google Drive folders to Cloud Storage

Use a Google Sheets as a database in Google Apps Script without sharing it

Make Google Sheets as your database for your web app or add-on developed in Apps Script without sharing the file. … In order to keep our database secret to end users we will use a Service Account (SA) … This Service Account is not a real account, no Gmail or Drive attached but a service account can access a spreadsheet. We will use this property to manage our data.

As the post author Stéphane Giron indicates before using Google Sheets as a database you need to fully scope out if it’s the right solution for you. For ‘small’ data you can often get away with using Sheets and in this post from Stéphane you can learn how to do it in a way where you can have access control by setting up and using a Service Account.

Source: Use a Google Sheets as a database in Apps Script without sharing it

Code preparation for Hash Code 2022 with Apps Script [snippets for working with .CSV data files]

Apps script code to manage CSV files input and output for hash code 2022 of Google.

This post from Stéphane Giron provides some very useful Google Apps Script snippets for working with .CSV data files. Included in the post are ways to:

  • Retrieve CSV files with Apps Script and read data;
  • Treating CSV files; and
  • Create output CSV files

As a bonus the post shows you how you can create a backup text file of your apps script file.

Source: Code preparation for Hash Code 2022 with 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

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 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

Use the Google Cloud Identity API for Google Groups with some Google Apps Script helper functions

Use the new Google Cloud Identity API to manage your Google Groups on your G Suite domain. Find in this article some REST and Apps Script code to query the Cloud Identity API.

Stéphane Giron summaries some helpful Google Apps Script code for interacting with the Google Cloud Identity API via REST. In this post you’ll find functions for listing both Google Groups in your domain and the members of each of those groups.

Source: Use the Google Cloud Identity API for Google Groups

React on Google Calendar change with Apps Script and EventUpdated trigger

It is really amazing how it is simple to make this script and have an app that run on each change perform in your Google Calendar.

A quick post to deal with a script that run when a new event or an event is updated in my Google Calendar. Hopefully in Apps Script we have an EventUpdated trigger.

Source: React on Google Calendar change with Apps Script and EventUpdated trigger

Manage event registration with Apps Script, Google Calendar and Google Forms

Manage training or event registration with Apps Script, Google Forms and Google Calendar.

Using Google Calendar to setup a meeting is really easy and works like a charm for business or personal needs. But sometimes you want to manage event registration for an event, a training and in this case you need to share a form and register the participant after the submission. Hopefully Google Apps Script is at the rescue.

Source: Manage event registration with Apps Script, Google Calendar and Google Forms

BigQuery create partitioned table and load CSV with Apps Script

Work with partitioned table and upload a csv file in BigQuery with Apps Script.

BigQuery is the famous Google database to store huge amount of data and query them fastly. For a recent a project I had to use it for storing some logs so here the code used with Google Apps Script to create the table and load a csv file from Google Drive.

Source: BigQuery create partitioned table and load CSV with Apps Script

Change Google Drive Folder color with Apps Script – Stéphane Giron – Medium

In Apps Script you can use the DriveApp service to manage your files and folders, but the methods getColor and setColor do not exist. You can use the drive advanced service to query the API directly but with recent change you will have to activate the Drive API in GCP and will need a GCP project. To have a smooth integration and skip the Google Cloud project creation here is a function to manage Folder color in Apps Script. These functions queries the API directly with UrlFetchApp() service.

Source: Change Google Drive Folder color with Apps Script – Stéphane Giron – Medium