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! ( 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
  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
  // See for how to handle 
  const event = Calendar.Events.get(CalendarApp.getDefaultCalendar().getId(), 'some_event_id');
  Logger.log('The owner is: ' +; 

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


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

r/GMail – Has anybody here tried to improve Martin Hawksey’s free Mail Merge system? [adding custom attachments to mailing] 

Has anybody here tried to improve Martin Hawksey’s free Mail Merge system? from GMail

I’m not really a reddit user but this question about adding custom attachments my Google Workspace Solutions Gallery – Mail Merge example landed in my inbox. The solution is, in Google Apps Script tradition, one line of code! The line is:

if(row['Attachment'] != '') emailTemplate.attachments.push(DriveApp.getFileById(row['Attachment']).getBlob());

The logic is:

  1. check if the row value in the column named Attachment 
  2. if true call Drive to open file with attachment id and push to the emailTemplate object.

The line is added just after:

const msgObj = fillInTemplateFromObject_(emailTemplate.message, row);

If you prefer you can also copy this sheet for a working example. To attach different files the Attachment row cell needs to include a Google Drive file id. There are a number of ways you can get Drive file id’s. For example, here is a Getting all files’ file-id from a folder in Google Drive snippet.

If you are interested in more of what is happening in this solution, the emailTemplate object is returned by getGmailTemplateFromDrafts_(). This function gets the Gmail draft message and as well as any attachments including inline images. If you are interested in handling attachments and inline images you can read more in Everything you ever wanted to know about Gmail draft inline images and Google Apps Script but were afraid to ask.

How to get Google Lighthouse reports using PageSpeedInsights API and Google Apps Script


Image: CC-BY Google

Lighthouse is an open-source, automated tool for improving the quality of web pages. You can run it against any web page, public or requiring authentication. It has audits for performance, accessibility, progressive web apps, SEO and more.

On StackOverflow there is a nice question/answer from Mert Dökümcü on How to send Google Lighthouse reports to Slack using PageSpeedInsights API and Google Apps Script. You can read the full solution there and below is an excerpt from Mert’s post (copied here CC-BY-SA Mert Dökümcü) of the bit that really interested me, getting results from PageSpeedInsights.

First and foremost, get…

Once you have these, go to and create a new script. The following code should do the trick:

var mobileData = fetchDataFromPSI('mobile');
var desktopData = fetchDataFromPSI('desktop');

function pageSpeedApiEndpointUrl(strategy) {
  const apiBaseUrl = '';
  const websiteHomepageUrl = ''; // Your website
  const apikey = ''; // Your API key
  const apiEndpointUrl = apiBaseUrl + '?url=' + websiteHomepageUrl + '&key=' + apikey + '&strategy=' + strategy;
  return apiEndpointUrl;
function fetchDataFromPSI(strategy) {
  const pageSpeedEndpointUrl = pageSpeedApiEndpointUrl(strategy);
  const response = UrlFetchApp.fetch(pageSpeedEndpointUrl);
  const json = response.getContentText();
  const parsedJson = JSON.parse(json);
  const lighthouse = parsedJson['lighthouseResult']
  const originLoadingExperience = parsedJson['originLoadingExperience']

  const result = {
    'overall_performance': originLoadingExperience['overall_category'],
    'score': lighthouse['categories']['performance']['score']*100,
    'firstContentfulPaint': lighthouse['audits']['first-contentful-paint']['displayValue'],
    'speedIndex': lighthouse['audits']['speed-index']['displayValue'],
    'timeToInteractive': lighthouse['audits']['interactive']['displayValue'],
    'firstMeaningfulPaint': lighthouse['audits']['first-meaningful-paint']['displayValue'],
  return result; 

Structure and simplify your Google Apps Script (with) V8

Apps Script now supports the V8 runtime. Time for an update to the story “Structure and simplify your Google Apps Script Apps script”. The V8 runtime brings modern ECMAScript syntax to Apps Script. So we can now use arrow functions and destructuring assignments. No more hoisting with let and const and my personal favorite, template literals.

In this story Jasper Duizendstra shows the added value of the changes made in the constructor pattern using the factory functions. It also shows how to implement chaining and a builder pattern in Apps Script.

Source: Structure and simplify your Google Apps Script (with) V8

Reliable, Secure & Scalable use of Google Apps Script | Google Developers Experts

Most conversations around solutions or automations created using Apps Script eventually get to a point where being able to address concerns around its reliability, security and scalability becomes a challenge — more so if compared to having similar applications being built on other platforms (say: AWS, GCP, Firebase etc.).

In this post, we’ll see how to navigate our way through said constructs by addressing parts of these problems and also explore ways in which we can bake some solutions right from the get-go!

In this post Sourabh Choraria provides some very useful answers for common questions organisations have when using Google Apps Script as part of their infrastructure. Points covered in this post include: managing concurrent executions; being able to securely store, access & manage API keys, tokens; and considerations around quotas.

Source: Reliable, Secure & Scalable use of Apps Script | Google Developers Experts

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