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.
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.
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 :)
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.
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.]
“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’
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 :)
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:
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.
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.
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.
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:
