AppsScriptPulse

Google Apps Script developer notes on using the new Google Forms REST API

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.

Source: Google Apps Script developer notes on using the new Google Forms REST API

Manage Shared Drives in Google Drive with Google Apps Script – Digital Inspiration

Image credit: Amit Agarwal

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:

  • Create a Shared Drive
  • Share a Shared Drive with a User
  • List all Shared Drives
  • List Files in a Shared Drive
  • Move Files in Shared Drives
  • Copy Files in Shared Drives

Source: Manage Shared Drives in Google Drive with Google Apps Script – Digital Inspiration

How to force subscribe a user in your domain to a Google Calendar with Google Apps Script [and Domain Wide Delegation] – Yagisanatode

Image credit: Scott Donald (yagisanatode.com)

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.

Source: How to force subscribe a user in your domain to a Google Calendar with Google Apps Script. – Yagisanatode

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

Use a Google Sheets as a database in Google Apps Script without sharing it

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.

Source: Use a Google Sheets as a database in Apps Script without sharing it

How to remove a Google Sheets button (drawings) or images connected to a Google Apps Script after the script has been run – Yagisanatode

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. 

Some clever Google Sheets pseudo button manipulation shown off here by Scott Donald. If using Apps Script functions assigned to drawings and images is something you are interested by sure to also check out Kanshi Tanaike’s example of ‘Using Google Apps Script to disable custom functions assigned to Google Sheets buttons/images to prevent simultaneous execution‘.

Source: How to remove a Google Sheets button (drawings) or images connected to a Google Apps Script after the script has been run – Yagisanatode

Using spread syntax and destructuring assignment in Google Sheets .getValues() with Google Apps Script

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.

Source: Applicating Spread Syntax and Destructuring assignment to Google Spreadsheet with Google Apps Script

Use Google Apps Script to pull all Google Shared Drives and permissions into 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.

Source: Use Apps Script to pull all Google Shared Drives and permissions into a Google Sheet

Search Google Drive folders for creation/modified dates using Google Apps Script

Search one level of Google Drive folders and extract creation, last updated and folder names into a Google Sheet.

Enter the Parent folder ID and search the folders within

Enter the Parent folder ID and search the folders within

The following Google Apps Script is designed to search through one level of Google Drive folders and extract the following information into a Google Sheet:

  • The folder name as a direct clickable link,
  • The folder creation date,
  • The folder last updated date,
  • The folder ID.

Source: The Gift of Script: Search Google Drive folders for creation/modified dates

NEW! Calling Google Apps Script from an automation in #AppSheet

Add custom integrations to your app using Apps Script. … You can use custom logic from within your AppSheet app by calling Apps Script from an AppSheet automation bot.

Examples of custom logic that you can implement include:

  • Create a calendar appointment when a button is clicked
  • Add a slide to a presentation when a new row is added
  • Save photos to Drive and share with specific individuals when uploaded using a form
  • Create an audit log by generating a Google Docs file with data from a table
  • Call an external service for machine learning prediction using data from a newly added row and write back model prediction

If you are not familiar with AppSheet it’s Google Cloud’s no-code platform that allows users to create custom applications that can be deployed across mobile and web, often setup using Google Sheets as a data source. AppSheet can be customised with workflows and automations, and those automations have recently been extended with a new preview integration to Google Apps Script.  The video in the Call Apps Script from an automation page provides a great summary of how the Apps Script integration works.

Source: Call Apps Script from an automation