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.
In this blog, I am going to show you how to get Google Classroom Data into your Google Sheet using Google Apps Script. Using this, you can keep track of all the classes that you have in your Google Classroom.
This is a nice introductory tutorial from Aryan Irani which looks at exporting basic information about your Google Classroom courses to Google Sheets. The example script imports the main details about your courses but if you are interested in finding out what else is available the official documentation for a course resource outlines what else is available.
Google recently announced the general availability of the new Google Forms API. For developers familiar with Google Apps Script for some time it has been possible to create, access, and modify Google Forms through the Forms Service. In this post I will highlight why Google Apps Script developers might want to use the new Forms API and some resources I’ve found useful for working with the new API in Apps Script projects. As part of this I’d like to share my experience of using the Forms API within a Google Forms Add-on and some factors to consider if you are particularly interested in watching for Google Form edits.
The Google Forms REST API provides an alternative method for managing Google Forms and responses. For Apps Script developers it is likely you’ll want to stick with using the Forms Service, but the new API is useful to know about as it opens up some new ways for managing, editing and watching Google Forms.
These code samples show how you can use Google Apps Script to manage and search through the content of shared drives in Google Drive using the Drive API.
Some handy Google Apps Script snippets from Amit Agarwal for interacting with Shared Drives. The post includes solutions for:
If you have tried to seamlessly subscribe a user to a Google Calendar as part of an automation workflow in Google Apps Script and discovered that all that happens is that the user gets an automated email request to join, and then it is up to them to accept the calendar invitation to add it to their live calendar list, you’re in the right place. … How to force subscribe a user in your domain to a Google Calendar using Google Apps Script and Service Accounts.
Scott Donald always crams in lots of very useful tips and guidance in his posts. In this latest piece by Scott you as well as learning about the Google Calendar Advanced Service you can also learn about service accounts and domain wide delegation setup, which enables super powers (and responsibilities). If you are interested in learning more about service accounts in Google Cloud Console Scott has included a selection of other community contributions at the end of his post.
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 :)
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.
There have been a few instances in my work where I need to remove a button (more accurately, a button drawing) or and image from a Google Sheets tab once the associated script has been run.
Perhaps we just want the user to run a process on a Google Sheet workbook, just once but not more times. This would be a good case for removing the button or drawing after use.
Note: This tutorial expects that you know how to create a drawing or a button from the Google Sheets drawing tool.
In this report, I would like to introduce to apply the spread syntax and the destructuring assignment to Google Spreadsheet with Google Apps Script. … I have sometimes gotten the questions related to the spread syntax and the destructuring assignment. So, I thought that I would like to introduce in my blog.
We’ve featured other coding patterns from Kanshi Tanaike and here is some other nice examples of how you can handle 2D arrays returned by .getValues(), in particular, this post shows a nice way of getting non-adjacent columns in Google Sheets.
If you’re a Google Workspace Super Admin, you’ve probably been thinking of ways to inventory and keep a handle on Google Shared Drives. They are a really great addition to the Workspace offering, but there are still some gaps in reporting & oversight that some people have wanted. This solution uses Google Apps Script and the Drive API to pull information about all the Shared Drives in a domain into a Google Sheet. Then, it uses the Drive API again to loop through all the drives and get the top-level permissions.
For Google Workspace admins Nick Young has provided a nice solution for auditing Google Shared Drives within your Workspace domain. This post covers all the steps to setup, a link to the code on GitHub and some notes on limitations to be aware of.
