Connect to Zoom API with Apps Script and OAuth

The following blog post is about connecting to the Zoom API by creating a Zoom OAuth App and then using a Web App designed in Google Apps Script. Our aim here is to return an Access Token which could then subsequently be used to access Zoom account data (eg your profile, meetings, etc).

Here are some useful links:

• How to create an OAuth App in Zoom.
• Zoom App Marketplace.
  
• How to deploy a Google Apps Script as a Web App.
  

 

Process overview

There are a set of steps that we need to complete in order to achieve successful authentication (connection with our Zoom account via the Zoom API):

1. Have a user visit a dedicated URL (which comes from our Web App).
2. This URL is attached to our Zoom OAuth App and upon a user visiting, it returns an Authorisation Code.
3. Our Web App then uses this Authorisation Code along with the Client ID and Client Secret (generated when we created the Zoom OAuth App) to make another request that finally returns an Access Token, valid for 1 hour.
4. Further blog posts will explore how we then get Zoom account data, create meetings, etc with the Access Token.
   

Bulk manage members of a Google Group - overview

The following Google Apps Script is designed to allow bulk management of members of a Google Group all from a Google Sheet. So with a list of email addresses and roles (owner, manager, member) you can add/remove/edit a large number of users with the click of a button. The tool provides an easier way of doing this as opposed to the web portal for managing members (www.groups.google.com).

Screenshot of the tool in a Google Sheet.

 Features of the tool

• Overcomes some of the web portal limits on bulk adding members.
• Checks that the person running the tool is at least a 'manager' of the group so they do not accidentally lose access.
• Checks there is an 'owner' of the group present to prevent permanent loss of access to the group for everyone.
• Creates a full log of all changes made in the Google Sheet so you have a record of actions.
• Will stop if it encounters and issue with an email address (for example) but will not need to re-sync any members prior to that point.
• Uses toast popups to inform you of the progress as it may take several minutes to run depending on how many members you have.
• Uses an in-house API url to connect to Google Groups, for which the address has been removed from the Apps Script code (you will need to insert your own).
  

Bulk manage members of a Google Group - the code

Following on from the overview blog post here, I have included the code itself from which the tool is built.

Search spreadsheet for multiple criteria

The following Google Apps Script has been extracted from a tool I am developing to manage a Google Group via a Google Sheet. The purpose of this particular script is to enable searching through rows of spreadsheet data and simultaneously looking for matches against 2 pieces of criteria:

1. That the user running the script (their email address) is listed in the sheet (Email address column) and has the role (Role column) of either 'manager' or 'owner'.
2. That there is at least 1 'owner' listed somewhere in the Role column.

Now what we want to avoid is looping through all of the data twice just to look for our 2 pieces of criteria. We also want to avoid unnecessarily scanning all of the rows once our criteria is met. So this script shows a way of doing that by using true/false flags to determine how the single loop should behave.

Screenshot of spreadsheet data to search

Generate Apps Script code for Forms

The following Google Apps Script is designed to automate the generation of further Apps Script code typically used when capturing Google Form submission data (create a Google Doc from a Form post). I tend to find there are 2 lines of code per Form question that I have to write which when scaled up to 60+ questions is a lot of time and chance for human error. They are:

1. var data = formValues['question title'][0];
2. Logger.log('data is: ' + data);
   

So this code is designed to gather all of the column headings from the response sheet attached to the Form, then loop through each one in turn and construct the necessary syntax to automatically produce the 2 above lines for each question into a Google Doc:

Screenshot of auto-generated code

Bulk export Qualtrics survey results

The following tool is designed to connect to your Qualtrics account and export the results data for multiple surveys that you specify, in one go. The results will be appended to the Google Sheet using the survey-name to identify each sheet of data. The data itself is exported as CSV.

Screenshot of instructional sheet requesting Qualtrics information

Bulk create Google Drive folders 2.0

The following Google Apps Script code was developed to bulk create Google Drive folders with data from a given spreadsheet (eg to name the folders and add permissions). It is an improved version of the tool built in April 2019 (original blog link here).

Improvements that have been made include:

• A Log sheet to support troubleshooting problems.
• More use of 'try/catch' to handle errors.
• Adding permissions is optional.
• Some of the column numbers can be tweaked in the Config sheet rather than diving into the code.

Remove Google Form created during copy

The following Google Apps Script is designed to remove the Google Form that is created when copying the Response Sheet file in Google Drive. The script checks to see if the original file has a linked-Form before making a copy, after which it finds the new Form's Url and removes it.

The 'Spreadsheet App' is used to open the new file and get the Url of the Form (if there is one):

var formUrl = SpreadsheetApp.openById(newFileId).getFormUrl();

Next the 'Form App' opens the newly created Google Form and gets its Id:

var form = FormApp.openByUrl(formUrl);
var formId = form.getId();

With the Id we can perform the final 2 steps of un-linking the Google Form from the Response Sheet and then deleting it:

form.removeDestination();
DriveApp.getFileById(formId).setTrashed(true);