AppsScriptPulse

NEW COURSE: Mastering the QUERY Function in Google Sheets

Image credit: Ben Collins (benlcollins.com)

Master the QUERY function, the most powerful function in Google Sheets, to become a more effective data analyst

Friend of Pulse and Google Sheets guru, Ben Collins, has a new course ‘The QUERY Function in Google Sheets’. The QUERY function it lets you perform various data manipulations making it easy to reshape, aggregate and explore your data in Google Sheets. The course is designed to be suitable for everyone from beginner to advanced who are interested in ways to work more effectively with your data.

If you are not familiar with the QUERY function Ben provides one example of what is possible in Sheets Tip 204: How To Use Dates In The QUERY Function (check the linked post in this Sheet Tip for an example worksheet).

Bonus: Sheets Tip 204 includes a 50% discount on the course valid until Friday 20 May 2022 at midnight EDT.

Finally, if you are interested in using the QUERY language in Google Apps Script it is possible! Below is some code used in this copy of Ben’s example workbook based on:

// based on https://gist.github.com/tanaikech/053d3ebbe76fa7c0b5e80ea9d6396011#sample-script
function myFunction() {
const doc = SpreadsheetApp.getActive()
const spreadsheetId = doc.getId(); // or set another Spreadsheet ID.
const sheetId = doc.getSheetByName('Data').getSheetId(); // or set another Sheet ID from Spreadsheet ID.
const query = "select C, B where B > date '2000-01-01' and B <= date '2002-12-31'"; // your QUERY
const url = `https://docs.google.com/spreadsheets/d/${spreadsheetId}/gviz/tq?tqx=out:csv&gid=${sheetId}&tq=${encodeURI(query)}&access_token=${ScriptApp.getOAuthToken()}`;
const res = UrlFetchApp.fetch(url,);
console.log(res.getContentText());
const array = Utilities.parseCsv(res.getContentText());
console.log(array);
// SpreadsheetApp.getActiveSpreadsheet(); // This comment line is put for automatically detecting the scopes if directly adding a spreadsheet ID.
}

Source: The QUERY Function in Google Sheets

Tips, tricks and scripts for automating your Gmail inbox with Google Apps Script

Image credit: rixxo.com

One the powerful features of Google Apps Script is with a couple of lines of code you can quickly start automating and organising your inbox. For people just getting started with Apps Script Google provide a introductory codelab ‘Accessing Google Sheets, Maps, and Gmail in 4 lines of code!’ and for those wanting to do a bit more the Google Workspace Developer documentation includes a ‘create a mail merge‘ solution.

Mail merge solutions are bit or a reoccurring theme in the world of Google Apps Script. In 2011 the official G Suite Developers Blog featured 4 ways to do Mail Merge using Google Apps Script with community contributions from James Ferreira, Steve Webster and Romain Vialard. The post references Romain’s ‘Yet another Mail Merge’ script, which he went on to develop as a very successful YAMM add-on before refocusing on the Mergo Mail Merge. Even further back when Google Apps Script was officially launched in 2009 this included a introductory video with a mail merge example.

A well as Google official channels the ability to automate your Gmail inbox has regularly caught the attention of the wider tech press. In 2013, Computerworld highlighted Jonathan Kim’s ‘Gmail No Response’ script which goes through your inbox and finds recent emails where you were the last respondent. Jonathan’s blog post is no longer available but the Gmail No Response’ script is on GitHub where it has been forked 100 times.

One of those forks is a variation by Christopher Gee published in Find emails with no reply automatically in Gmail which:

runs through the emails in your inbox and checks your outgoing messages for a question mark. Once it finds these emails it checks to see if they are in a date range and then sees if you have had a response. If you have not had a reply to your email containing a “?” then it adds the label “No Response”. You can then quickly see all of the threads for which you are awaiting a reply.

If you are interested in more Gmail script solutions then I highly recommend you have a look at content shared by Amit Agarwal. This doesn’t just include Apps Script solutions but also a number of Gmail tips and tricks. As many of these feature the way you can search your Gmail inbox they can also be used with GmailApp.search() or if using the Gmail Advanced Service Gmail.Users.Messages.list . A nice example of where a Gmail user tip can be used in Apps Script is Amit’s post which includes how to Search Emails by Specific Time in Gmail.

The list of examples could go on (and I’ve not even mentioned Stonian’s recent post Keep your Gmail inbox size in check with google app scripts :). Do you have a favourite ‘tip, trick or script’ for Gmail and Google Apps Script?

Sources:

“This app is blocked” error on Google Apps Script [solution]

In this post, we’ll be going through a quick workaround so that you can get back to running your scripts. Note that this issue is still not entirely resolved, but you can follow any developments in Google’s issue tracker.

If you are a Google Apps Script developer using a consumer @gmail.com account for development/testing or sharing script projects for other users to use with their gmail.com account you may have encountered the “This app is blocked” issue. This issue appears to prevent a Google account from completing the Apps Script authentication flow even when using limited scopes.

This post from Aiman Fikri provides a solution for getting around this issue by associating an Apps Script project to a Google Cloud Platform (GCP) project. Google also provide documentation on setting up Standard Cloud Platform projects, but if you are supporting novice users directing them to Aiman’s post might be less daunting for them.

There are some benefits of using Standard GCP project particularly when you are developing scripts as it gives access to Cloud logs and Error Reporting. If you encounter “This app is blocked” on all your script projects you can group multiple scripts with a single Cloud Platform project to save having to go through the full setup process.

Source: “This app is blocked” error on Google Apps Script [solution]

New YouTube series ‘GWAOw! Google Workspace Add-on Walkthroughs’ from yagisanatode.com

GWAOw! is a YouTube series that explores the best Google Workspace Add Ons in the Google Marketplace to help you learn what is out there.

This new YouTube series from Scott Donald at yagisanatode.com will hopefully be a great way to see and learn how other developers have implemented Google Workspace Add-ons, picking up UI tips and tricks. For the first episode Scott takes a look at Workbook Statistics by Sourabh Choraria. Scott is planning one/two episodes a month and if you have published your own add-ons you can find out about getting it featured in GWAOw!.

Source: GWAOw! Google Workspace Add-on Walkthroughs – Yagisanatode

Create Spaces and add members with the Google Chat API (and introducing the Google Workspace Developer Preview program)

Image credit: Google

In Google Chat, Spaces serve as a central place for team collaboration—instead of starting an email chain or scheduling a meeting, teams can move conversations and collaboration into a space, giving everybody the ability to stay connected, reference team or project info and revisit work asynchronously.

We are pleased to announce that you can programmatically create new Spaces and add members on behalf of users, through the Google Workspace Developer Preview Program via the Google Chat API.

Besides the new ability to programmatically create and populate Google Chat spaces outlined in the source post, if you are a member of the Google Cloud Partner Advantage program you may want to apply for the new Google Workspace Developer Preview Program. This program will give you access to this new Chat API functionality and other Google Workspace Developer preview developments.

Not a Cloud Partner Advantage member? You can apply to join

If you are interested in building on the Google Chat platform there is the What’s new in the world of Google Chat apps session at Google I/O (this session will be available on-demand from 12 May 2022).

Source: Now in Developer Preview: Create Spaces and Add Members with the Google Chat API

Formatting date/time values using the user’s timezone in Google Apps Script

Working with dates, times and time zones can often be a bit of a headache. If you’d like to learn more about some of the challenges of dealing with ‘big balls of wibbly-wobbly, timey-wimey… stuff’ I recommend watching Comptuerphile’s Problem with Time & Timezones.

For displaying dates/time values for users in different time zones you can use Utilities.formatDate(date, timeZone, format) , the challenge however is working out the user’s timeZone . For Google Workspace Add-ons developers can configure the manifest option to include user locale information in event objects included as part of action callback functions [See Accessing user locale and timezone].

For web apps and editor add-ons using HTML Service it is possible to get the user’s browser time zone setting with the JavaScript code Intl.DateTimeFormat().resolvedOptions().timeZone (at time of writing this works in 93.85% of browsers). This can be passed back to your server script and used to format date/times for the user’s time zone. This forked gist provides an example of how this can be used in Google Sheets (props to Eric Koleda for the original solution, which uses .getTimezoneOffset() to get the users time zone offset).

The merits of Intl.DateTimeFormat() and .getTimezoneOffset() are discussed in Getting the client’s time zone (and offset) in JavaScript.

If using HTML Service is not an option an alternative workaround is getting the user’s Google Calendar time zone using the Calendar Service and .getTimeZone() . Here is an example script shared on Stack Overflow:

function getUserTimeZone() {
var userTimeZone = CalendarApp.getDefaultCalendar().getTimeZone();
Logger.log(userTimeZone)
}

The downside of this approach is it potentially adds an additional authorisation scope the user will need to approve before the script can run. As noted by Google best practice is always to limit the scopes in your Apps Script projects to the ones you need.

If you’ve got other tips for handling user time zones feel free to pop them in the comments.

Extract and visualize your own Twitter data using Google Apps Script and Google Sheets

Use Apps Script to normalize your tweet data into tabular format for easy visualization & analysis.

I’ve a personal interest in Twitter data, in particular, how it can be collected analysed in Google Sheets so it was nice to see this example from Nick Young (@techupover). The solution shared by Nick uses Google Apps Script to parse a downloaded archive from a Twitter account and write it to a Google Sheet. A nice weekend project if you are looking for something to do :)

Source: Extract & visualize your own Twitter data using Google Apps Script & Google Sheets

Must see Google Workspace Platform sessions at Google I/O

In the latest Google Workspace Developers Newsletter – April 2022 (if not already you can subscribe here), there is a handy summary of ‘Must see Google Workspace Platform sessions’ at Google I/O, 11-12 May 2022. Google I/O is being run as a hybrid event and anyone online can register for free and the program is worth exploring:

Join us for Google I/O 2022. Connect with experts, get inspired, and expand your knowledge. From no-code platforms to automated Chat applications, the team has put together an informative and educational set of sessions to get you up to date with the Google Workspace Platform.

Keynote

The cloud built for developers: Learn how Google Cloud and Workspace teams are building cloud services to help developers and technologists create transformative applications.

Sessions

Learn how to enable shared experiences across platforms: Explore how to enable shared experiences across platforms (Android, iOS, web).

What’s new in the world of Google Chat apps: Integrate services with Google Chat, explore visual improvements to Google Chat apps, and discover new Google Chat API features.

Extending Google Workspace with AppSheet’s no-code platform and Apps Script: Learn how to configure the new Apps Script Connector in your AppSheet apps.

Conversational AI for business messaging: Create messaging experiences for consumers on Google Search and Maps using Google’s Business Messages.

Workshop

Building AppSheet apps with the new Apps Script connector: Building connected AppSheet and Apps Script apps using AppSheet’s new Apps Script connector.

Register now

How to Auto-Download Podcasts to Google Drive with Google Sheets [and Google Apps Script] – Digital Inspiration

You use Google Sheets as your own Podcast Manager that will automatically download your favorite podcasts to Google Drive and instantly sync across all your devices.

Amit Agarwal is always coming up with creative uses for Google Apps Script. His latest project is a great example of what can be achieved with a little code and a lot of know-how. Even if you aren’t a podcast fan this project is worth checking out as Amit is expert at writing concise and efficient code.

Some highlights to check out once you make a copy of the ‘Podcast Manager’ Google Sheet are use of CacheService for getting/putting a last update time and using the .filter(Boolean) trick for ignoring blank cells when using .getValues() on Google Sheets data. There is plenty more going on and worth spending some time using the Script Editor debugger and breakpoints to learn from a master.

Source: How to Auto-Download Podcasts to Google Drive with Google Sheets – Digital Inspiration

Report: Handling 10 million cells in Google Sheets using Google Apps Script

In this report, I would like to introduce the important points for handling 10,000,000 cells in Google Spreadsheet using Google Apps Script.

In March 2022 Google announced that the Google Sheets cell limit is doubled from 5 million to 10 million cells. The increased capacity has implications for Google Workspace developers as you now may encounter scenarios where you have users with lots of data.

Fortunately, Kanshi Tanaike has been exploring the impact the increased volume of data in Google Sheets has when using Google Apps Script and both SpreadsheetApp and Sheets API. The linked report contains a number of useful findings and strategies for handling large Google Sheets with Apps Script.

Source: Report: Handling 10,000,000 cells in Google Spreadsheet using Google Apps Script

Subscribe to Apps Script Pulse...