AppsScriptPulse

Highlights from the Google Workspace Developer Summit 2022 – The state of Workspace development is good!

Busy old couple of weeks at CTS and it’s been nice to get back to some face-to-face events. Last week I had the pleasure of joining the Google Workspace DevRel team on stage at the Google Workspace Developer Summit, London co-presenting with some guy called Charles Maxson.

A couple of years ago as part of Totally Unscripted we did a ‘State of Script‘ episode with then Apps Script PM Keith Einstein. This latest clip from the Google Workspace Developer YouTube channel captures not just the state of script, but the wider state of Workspace Dev.

The clip is worth a watch to get a summary of some of the new features coming to Workspace development. For me, not only is there plenty to get excited about my overall feeling from the Summit was Workspace development is now a first class citizen in the Google Cloud family. The state of Workspace development is good!

Source: Highlights from the Google Workspace Developer Summit 2022!

Introducing AppSheet databases: Build data driven apps for Google Workspace

Over the past year, we’ve added more functionality to AppSheet, extending how it can maximize the power of Google Workspace through integrations with products such as Gmail, Google Drive and Apps Script. To improve the experience for app creators and users, we’re excited to introduce in public preview AppSheet databases, a built-in database for citizen developers to easily and securely manage their data.

During public preview, access to AppSheet databases will be enabled by default for everyone but it will not affect existing apps. Use of this feature in public preview will be included at no additional cost in your AppSheet subscription plan, but limited to 10K rows per table, 20 tables per database and 20 databases per user. Please note that these limits may change when the feature is generally available.

For people who tuned in to TU3.5: New workflow solutions with Tables and Google Apps Script back in 2020 AppSheet databases will look very familiar. Since that episode the Area 120 team behind the Tables beta moved into Google Cloud and specifically AppSheet.

The big question for me is whether AppSheet databases can find a sweet spot for citizen developers looking for something with more performance than a Google Sheet and easier to setup than a Google Cloud project. In public preview AppSheet databases are limited to 10,000, which interestingly is the same limit as the old paid version of Tables.

Ultimately this may however be less able size and performance but a data solution that the AppSheet team has full control, particularly removing a reliance on features developed by the Google Sheets team.

Source: Introducing AppSheet databases: Build data driven apps for Google Workspace

How Google Workspace is delivering innovation for the future of hybrid work | Google Cloud Blog

Google Workspace is the most popular productivity tool on the planet — relied on by more than 3 billion users. To help organizations thrive in a hybrid world, we’ve invested heavily in immersive connections, our approach to bringing people closer together through our communication products, and smart canvas, our next-generation collaboration experience, while enhancing our cloud-first security model to help people work safer.

Today is the first day of Google Cloud Next ’22 and this flagship event is being used to announce a number of upcoming features for Google Cloud and Workspace. This post (source link at the end) from the Google Cloud blog gives a summary of some of the key announcements including:

  • Bringing people together with immersive connections – which announces a number of new features being introduced to enhance Google Meet, the much requested feature to include inline threaded conversations in Google Chat as well as broadcast only Chat spaces
  • Collapsing the boundaries between people and apps with smart canvas – which highlights updates, mainly to Google Docs, to enhance productivity with custom building block and user defined variables. Google Sheets is also getting some smart chips enhancements including smart chips data extraction and third party integration as well as a new timeline view rolling out this month
  • Work safer with Google – a reiteration of Google’s commitment to security with updates on data loss prevention being extended to Google Chat, Trust rules in Drive and client side encryption for Gmail and Calendar
  • Extending the power of Workspace – the one perhaps of most interest to Pulse readers are the opportunities to build on Google Workspace with additional APIs for Meet and Chat, a Meet add-on SDK to third party integrations directly in Meet, and an official Google Chat integration from AppSheet apps.

For some of these announcements you’ll have to wait until 2023 at the earliest to get your hands on them. For a wider narrative and to see how the tech press is responding to today’s Google Cloud Next ’22 announcements here are some stories that have caught my eye:

Source: How Google Workspace is delivering innovation for the future of hybrid work | Google Cloud Blog

How to automate Google Apps Script deployments with GitHub Actions

Build an automated system that will be automatically deployed to one of the destination spreadsheets when the code is committed to the GitHub repository.

Managing code particularly in container bound projects can be a real headache for Apps Script developers. The Script REST API has made this a lot easier both is terms of managing scripts but also opening up options for different development environments, including local development using clasp and your preferred IDE.

In terms of version control there are a number of solutions Apps Script developers can now consider. The Google Apps Script GitHub Assistant Chrome Extension is a popular option as it extends the existing online Script Editor with integration with GitHub and several other Source Code Management services (GitHub Enterprise/Bitbucket/GitLab).

For developers interested in developing projects locally there is perhaps even more choice. One solution we’ve featured a couple of times in Pulse is the use of GitHub Actions:

GitHub Actions makes it easy to automate all your software workflows, now with world-class CI/CD. Build, test, and deploy your code right from GitHub. Make code reviews, branch management, and issue triaging work the way you want.

The latest example for using GitHub Actions comes from Goran Kukurin (gorankukurin.com). Goran has shared a setup for developing code in Google Sheets with development and production versions. As well as using GitHub Actions to automatically push code to the correct Google Sheet version a shell script is used to modify the custom menu name as a useful reminder so you can see what version you are testing:

You can visit Goran’s post (linked below) for an example repo and instructions on how to setup. If you are using a UNIX based operating system it should be straightforward. If like me you are on a Windows machine there are some minor tweaks and possibly big node-gyp headaches to overcome, which I’ve highlighted below (in Goran’s website post some commands with -- are displaying as - – this GitHub repo and Medium version are ok).

Windows Tips

You are better using PowerShell rather than CMD so you don’t have to modify things like $HOME to %HOMEPATH% .

If you’ve not already got node-gyp   installed the setup that worked for me on Windows 11 was:

  • Node 14.19.3
  • Python 3.10
  • Visual Studio Build Tools 2017

After installing with npm install -g node-gyp there are Windows specific setup instructions (don’t forget npm config set msvs_version 2017 ).

After creating the spreadsheets I needed to specify the directory by including src (I think this is a nit) e.g.:

mv src\.clasp.json .clasp-prd.json

To encrypt your clasp credentials you might need to install install GnuPG.

If you are running the setup build tasks in VS Code and get:

'.' is not recognized as an internal or external command

I solved this by configuring npm to use bash.exe , some other options are give in this SO answer.

Final thought

There is a lot more you could do with GitHub Actions like pushing to multiple production spreadsheets and much more. This post from Goran Kukurin is a great insight to what is possible and we look forward to seeing where other Apps Script devs go with it.

Source: How to automate Google Apps Script deployments with GitHub Actions (also published on Medium)

Report: Obtaining current and historic stock data from Google Sheets GOOGLEFINANCE function using Google Apps Script

This is a report for obtaining the values from GOOGLEFINANCE using Google Apps Script. When I tested to retrieve the values from GOOGLEFINANCE function on Google Spreadsheet using Google Apps Script, I noticed that the values can be retrieved.

Another interesting report from Kanshi Tanaike where they test the ability to get values from the Google Sheets GOOGLEFINANCE function with Google Apps Script. For context, as highlighted in the report, Google announced in 2016 that historical data from the GOOGLEFINANCE function would no longer be accessible using either Google Apps Script or the Google Sheets API.

The 2016 announcement followed news in 2014 that the Finance Service, which allowed direct access to current and historical stock data, was deprecated (if you are interested in seeing what you are missing out on here is the Internet Archive snapshot of Finance Service from 2013).

So clearly there is a trend here in Google not wanting you to programmatically access stock data with their services and I anticipate the GOOGLEFINANCE function will be updated to prevent access from Google Apps Script, but in the meantime you can have some fun :)

Source: Report: Obtaining Values from GOOGLEFINANCE using Google Apps Script

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:

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

Find precedents of cells with formulas in Google Sheets using Apps Script — part 1

enumerate a range in google sheets using google apps script and use that to find precedents of cells that contain a formula.

Let’s start with the ubiquity of spreadsheets. I’ve long felt that spreadsheets were everywhere but until a post from Hjalmar Gislason I had never seen it in numbers and it made for interesting reading:

  • 1.2 billion people use Microsoft Office (WindowsCentral, March 2016), odds are most of them have at least access to Microsoft Excel.
  • Microsoft believes that 1 in 5 adults in the world use Excel (“What’s new in Microsoft Excel”, Sept 2017)
  • Excel is the number one skill mentioned in job ads, mentioned in approximately 1 in 3 job ads! (Indeed.com Job Trends, June 2017).
  • In 2010, RescueTime found that about 25% of computer users used Excel on a daily basis and that about 2% of all time spent on a computer anywhere was spent using Microsoft Excel.

As useful as spreadsheets are the underlying data structure can cause problems. This is covered in more detail in Patrick Burns’ Spreadsheet Addiction, which amongst other things highlights the ambiguity of value and formula:

Primarily the problem is that some cells have hidden meaning. When you see a number in a cell, you don’t know if that is a pure number or a number that is derived from a formula in the cell. While this distinction is usually immaterial, it can be critical.

The leading example is sorting. When rows are sorted, usually it is desired to sort the numbers as they are. However, it will be the formulas that are sorted. To humans the numbers are the primary thing, and the formulas are just something in the background. To the spreadsheet the formulas are primary, and the numbers are mere results.

This is a long winded way to highlight some work from Sourabh Choraria which is exploring how the relationship between formulas and cells can be extracted in Google Sheets. As part of this Sourabh is looking at how the getFormulasR1C1 method in SpreadsheetApp can be processed to find precedent cells. There are some very funky use of regex to parse formulas which is worth spending some time to digest.

Source: find precedents of cells with formulas in google sheets using apps script — part 1

Getting a Google Calendar event owner using Google Apps Script

In the Google Group Apps Script community there was an interesting question about returning a Google Calendar event owner/organiser. The problem was that using the Calendar Service it is possible to use getGuestList(includeOwner) to return the EventGuest[] array and in theory using getGuestStatus() find the guest with the OWNER GuestStatus Enum :

const event = CalendarApp.getEventById('some_event_id');
const guestList = event.getGuestList(true); // get the guest list including owner  
// Iterate across EventGuest[]
for (let i = 0; i < guestList.length; i++){
let guest = guestList[i].getEmail();
let status = guestList[i].getGuestStatus().toString();
Logger.log(guest + ' ' + status);
}

However, in practice as the organiser status defaults to ‘Yes’ and can change to ‘No’ or ‘Maybe’ the OWNER status is never returned:

How to solve? Well one solution to find the owner is to get the event guest list with and without the owner then filter out the list ignoring any accounts that appear twice:

  const event = CalendarApp.getEventById('some_event_id);
const guestList = event.getGuestList(true); // get the guest list including owner
const guestListWithoutOwner = event.getGuestList(); // get the guest list without the owner
// filter the guest list to ingore duplicate emails leaving only the owner
// based on https://stackoverflow.com/a/42995029/1027723
const owner = guestList.filter(o1 => !guestListWithoutOwner.some(o2 => o1.getEmail() === o2.getEmail()));
Logger.log('The owner is: ' + owner[0].getEmail());

Alternatively if the Calendar Advanced Service is enabled in your Apps Script project the owner email can be returned using:

  // With Calendar Advanced Service enabled
// Note: eventId is different to the one returned by CalendarApp as it doesn't include @google.com
// See https://stackoverflow.com/a/55545091/1027723 for how to handle 
const event = Calendar.Events.get(CalendarApp.getDefaultCalendar().getId(), 'some_event_id');
Logger.log('The owner is: ' + event.organizer.email); 

Clearly the second method is more direct, but which is quicker? I’ll let you tell me :)

Digital Analytics Automation with Google Apps Script – Book Review

One of the benefits of joining the Google Developers Experts (GDE) program was the opportunity to meet and speak to Google Analytics GDEs. As well as an insight into how Google Analytics and related products can be used, it has also been an opportunity to collaborate with experts including Nico Miceli and Simo Ahava to explore how Google Apps Script and Google’s digital marketing products could be used together. I count myself as very fortunate to have been able to collaborate with these experts, particularly the benefits of sitting down and discussing the opportunities afforded by Google Apps Script.

For those in Digital Marketing and Analytics without a Nico or Simo on hand where do you turn? Michele Pisani, for one, has been and continues to be a prolific contributor to the Digital Analytics and Google Apps Script community. Unless you are fluent in Italian you may not have come across Pisani’s work before on AppsScript.it, his associated YouTube channel and curation of “Fatti di Apps Script”, an Italian Facebook group for Apps Script users.

Pisani has recently published a new book “Digital Analytics Automation with Google Apps Script” (or if you prefer in Italian “Google Apps Script nella Digital Analytics: Risparmia Tempo Risparmia Denaro”), which is an introduction to Google Apps Script for Digital Marketers. As the title suggests the book is about ways products in the Google Marketing Platform, including Google Analytics and Google Tag Manager, as well as BigQuery can be automated with Google Apps Script to save time and money.

The book is aimed at all those who work or enjoy in the field of data analysis, SEO, ADV, Social Media and in general who are looking for a productive advantage to support their activities, with significant savings in time and at zero cost.

I refer to configurations, management and governance in one click for Google Analytics and Google Tag Manager, support for Ecommerce, access control and data according to the GDPR law, connectors with third-party services, add-ons, Web Apps, tracking, automated reporting and simplified data visualization.

All with a few lines of JavaScript, using only Google Apps Script, without the need for a domain, hosting and knowledge to manage them. The tool offers a simple environment accessible from the Web to overcome the technical and psychological gap due to the need to use complicated external paid tools.

As you can imagine the book includes information on tasks like report automation, however, even with this Pisani often goes beyond ‘basic’ solutions to explore creative and clever ways Apps Script can be used to enhance Digital Marketing activities. Examples of this include consolidating all payments and refunds in Google Analytics, monitoring version changes to Google Tag Manager, and opportunities with adding some Machine Learning to Google Sheets.

Throughout Pisani’s book there are also clear explanations of not just how you can automate processes, but the clear benefits of why you should be doing it in the first place. This included best practices to ensure data integrity as well as legal and service requirements around data protection.

In the field of Digital Analytics I would classify myself as an amateur enthusiast rather than a seasoned professional, but from what I’ve seen in Pisani’s book I can imagine it will be a very useful addition for anyone with a little coding experience and desire to learn more about digital marketing automation.     

[Michele kindly provided a free copy of “Digital Analytics Automation with Google Apps Script” for review.]

Want to become a recognised Google Developer Expert in Google Workspace/Apps Script? – About Road to GDE

Image credit: Google

“Road to GDE” is a 3-month mentoring program focused on supporting people from historically underrepresented groups in tech on their path to becoming a Google Developer Expert.

This site is hopefully proof that there is an incredibly rich Google Apps Script / Google Workspace developer community. If you are one of those contributors based in EMEA, LATAM and India from an underrepresented group ‘Road to GDE’ is a great opportunity to help you become recognised as a Google Developer Expert in the field that you are already passionate about. The source link contains more information about requirements and how to start your ‘Road to GDE’

Source: Road to GDE – About Road to GDE

Trend of the google-apps-script tag on Stack Overflow for 2021. Apps Script usage in decline?

Stack Overflow is a question and answer website for professional and enthusiast programmers. It is the flagship site of the Stack Exchange Network, created in 2008 by Jeff Atwood and Joel Spolsky. It features questions and answers on a wide range of topics in computer programming.

Wikipedia

Kanshi Tanaike has recently published the 4th annual Stack Overflow trend report for the Google Apps Script question tag. The full report, including how the data is collected, is published here and there is some further discussion on the Google Apps Script Google Group community.

From the headline data it is interesting to see the total number of Google Apps Script questions posted on Stack Overflow has declined following previous years of continued growth. It’s important to remember that this decline is unlikely to directly correlated with general App Script usage. Factors that might be influencing the number of questions asked include like better developer documentation, larger corpus of existing Stack Overflow answers, growing number of other community resources and spaces.

Fig. 1. Year vs. Total questions, answered, solved and closed questions. These all questions include the tag of “google-apps-script” in the tags. Image credit: Kanshi Tanaike

Another consideration is Google Apps Script is increasingly positioned as a platform for citizen developers and low coders. This community may be more hesitant to ask questions on a site that has a reputation as being for ‘professional programmers’. My conclusion is the annual Stack Overflow report for Google Apps Script is always very useful to see, particularly in terms of see if there are enough people answering questions.

Finally, as a little easter egg here is a Google Sheet I’ve created which includes a little Apps Script to fetch my own Stack Overflow annual report data. Copy and open the script editor to add a monthly trigger to get the data. Next year I’m planning to look at changes in post viewcount :)