Using Hyperlinks in Dialogs and Sidebars to open a URL in a new Tab with Google Apps Script

If you’ve landed on this page you’re probably wondering why your hyperlinks are not working in your Google Workspace dialogue (dialog for my U.S. friends) box or sidebar.

This affects all locations where you can build a sidebar or dialogue with Google Apps Scripts, Sheets, Docs, Slides and Forms.

You might even hit F12 in your browser to inspect the code and found this dreaded error:

Unsafe attempt to initiate navigation for frame with origin ‘https://docs.google.com’ from frame with URL ‘https://n-yyi3lctp…<<fileID>>…-0lu-script.googleusercontent.com/userCodeAppPanel’. The frame attempting navigation of the top-level window is sandboxed, but the flag of ‘allow-top-navigation’ or ‘allow-top-navigation-by-user-activation’ is not set.

linking from a Google Workspace sidebar or dialog error Unsafe attempt to initiate navigation for frame

Why your Links aren’t working in your Google Workspace Dialogs and Sidebars

So what’s going on?

Dialogues and sidebars in Google Workspace are set in iframes. Essentially, this is a nested webpage on your main page. Take a look at the examples, below. I’m in Chrome here and I have selected the Developer Tools Element Selector (Ctrl + Shift + C for PC) and clicked on the Sidebar and Dialogue box respectively.

For the Sidebar.

Google Workspace Sidebars are in iframes

And for the dialogue box.

Google Workspace Dialogs are in iframes

When you create a simple HTML hyperlink in your anchor tag like this:

<a href="https://yagisanatode.com">Website</a>
You are asking the iframe to open a URL in its parent window, your Sheet, Doc, Slide or Form, and browsers generally don’t like to let you do this.

The Solution

The solution is really easy. Simply add target="_blank" to your anchor element:

<a href="https://yagisanatode.com" target="_blank">Website</a>
The target ‘_blank’  attribute will generally open the link in a new tab for most browsers. Note that a user may change their settings to open in a new window instead of a new tab.
Target ‘_blank’ implicitly adds the rel="noopener" behaviour on most browsers to prevent the destination link from tampering with the original source.

The video

Example Implementation

If you want to see an example implementation you can get your own copy of the sample Sheet with bound Apps Script from the link below:
Easter eggs, a bonus Google Sheet formula for linking images in a cell and a fun way to call object methods from a Ui method are all in the example sheet.
Once you made a copy of the Sheet, click on the ‘More’ custom menu item and select from the Sidebar or Dialogue box implementation to see it in action. You will have to Authorise the scopes the first time though.
To look at the code, go to Extensions > Apps Script.
Have fun!

If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.

Looking to learn more about Google Apps Scripts in a more structured format? Udemy has some great courses that can get you from the basics to a real Google Apps Script pro.

Got a more specific problem you need help with, but don’t have the time to develop the skills? Make an enquiry on my 'Hire me!' page. I occasionally pick up projects. Alternatively, Fiverr’s your best bet to find a skilled Google Apps Script developer to solve your problem quickly and professionally. *

*The above affiliate links have been carefully researched to get you to what you specifically need. If you decide to click on one of these links it will cost you just the same as going to the site. If you decide to sign up, I just get a little pocket money to help pay for the costs of running this website.

~Yagi

How to Automatically Share Teachable Students to Google Drive Files and Folders when they Enroll in your Course

Not only are Google Workspaces, Google Sheets, Docs, Forms and Slide great to work in and look awesome, but the convenience of collaborating and sharing your Google Drive Files and Folders is also super useful. So much so that many course creators share their documents with their students all the time.

The problem that course creators have is that they are generally stuck with two options when sharing their Google Drive files and folders:

  1. Set sharing to Anyone with Link can view. And hope other freeloading students don’t find and share their course material.
  2. Manually share each student as they enrol. Which is time-consuming for the course creator and annoying for the student who needs to wait to be shared before they can get their hands on your awesome course content.

Both options are really terrible.

I reluctantly chose option one for my first Google Sheets Essentials Teachable Course and it really bothered me. I needed to find a way to share my Google Drive course content with only those students who signed up for my course.

In this tutorial, I will guide you through creating a Google Apps Script web app that receives a webhook notification when a student enrols onto one of my Teachable courses. If a student enrolled with a non-Gmail or non-Google Workspace domain email account, they will be sent an email with an attached form to add a Google-friendly email.

If you want a copy of the Google Sheet with the Apps Script attached, without coding it all yourself, plus written-visual tutorials on how to quickly set up your sheet head over to my teachable page now and purchase the sheet and instructions for just $2.95. Yeap not even the price of a cuppa.

The fun thing is that you will experience how the whole process works, because…well…that’s how I am going to share the Google Sheets file with you when you enrol. Neat, hey?

As a part of your purchase you will also get a few other perks:

  • Set files or folders for ‘view’, ‘comment’ or ‘edit’ access. 
  • Add existing students to your selected course Google Drive Files and Folders.
  • Get your full course list from your Teachable site right in your Sheet. 
  • A choice to bulk set your files and folders to:
    • prevent downloads, copying and print.
    • Prevent sharing by any documents you have provided ‘edit’ permission to.

If you want to understand how it all works and build your own, read on, you can always throw a couple of coins at me and enrol to run the workflow just for fun.

Instantly share ONLY Teach:able Students to selected Google Drive Files and Folders

 

If you are looking to build your own Teachable Course you can check out a how-to guide here:

How to launch an online course—and craft your email strategy

Continue reading “How to Automatically Share Teachable Students to Google Drive Files and Folders when they Enroll in your Course”

How to Create a Custom Email Auto-responder for a Google Forms Submission with Google Apps Script (Updated Feb 2022)

A few days before publishing this post, I put a call out for some Beta testers to get free access to my new course Google Sheet: Learn the Essentials through 3 Detailed Projects. 

Yeah, yeah, yeah, I am super excited about finishing this course. It’s been a labour of love for two and a half months. No wonder I am banging on about it.

Anyway back to the tutorial. I needed a way to provide coupon codes for free access to the course in exchange for some feedback and much-needed tutorials.

To do this, I created a Google Form.  This contained some details and expectations, and then some details about the submitter and a consent checkbox at the end. If the submitter consented and hit submit I wanted them to get an email back with the coupon code.

In this tutorial, we will go through how to create a custom auto email response containing:

  • The submitter’s name in the greeting.
  • Your email message.
  • Your primary signature block from your Gmail account.

To me, this seems like a pretty standard task that anyone might be considering doing. So, I thought I would share the process and the code.

Let’s get cracking!

The Google Form

Here we will go over the highlights and setup as they pertain to the context of the Google Apps Script code.

The form starts off with some information about beta testing the course.

Google Forms beta testing instructions
Click to Expand!

Next, we have the input fields.

  1. Email: This is an atypical field. You create this field by going to the Settings taband select  Collect email addresses. Here, I also sent the submitter a copy of their responses and limited the responses from each user to one. This made the user use their Gmail account, which added some extra integrity to the form and… well… the user will need a Gmail account to complete the course anyway. This will become important in our code later.
Google Forms collect email address and limit responses to one
Click to Expand!
  1. Name: The name of the Beta tester. I’ll grab the first name from this and also record it as part of their testimonial that they consented to.
  2. Occupation (optional): This apparently helps with the credibility of the testimonial and it is also interesting to see the occupation of the people taking the course. Testers can opt-out of this one.
  3. Company (optional): This one was a fizzer. I understand why, you really don’t want to be telling people where you work online. This item was added as part of the testimonials that I researched in preparation for this. I have no idea how course creators got these, which makes me one part, a little suspicious and, one part, blown away by how these folk got students to share who they worked for. In hindsight, I would not add this item. If I personally wouldn’t give out these details, I shouldn’t expect others to.
  4. Consent: This is a mandatory field that the submitter needs to check to confirm their expectation in exchange for the free course. If they don’t check the box, they can’t submit the form and get the coupon code.

The code

We want to get our email auto-responder code to run when the Google Form is submitted to us.  To do this we need to connect our script to the form by opening the Google Apps Script editor up in the Google Form.

You can do this by clicking the vertical ellipsis () in the top-right of the Google Form editor and selecting Script editor.

Google Form access to Google Apps Script Editor
Click to expand!

Code.gs

You can copy and paste this now into your project and rename the project to whatever you are working on. I usually name my project the same as my Form or Sheet if it is directly related.

You can update the subject and htmlBody values to your own data here. You can add HTML to your htmlBody within backticks (your html) Lines 27 & 28

Of course, the coupon code is a big faker-Rooney.  🙄

The code grabs the current users email and name and then emails the respondent with a coupon code after they hit submit on the Google Form.

If you run the code right now you will face some errors. First, we need to set up the trigger to run this code when the user submits it. Also, we need to get authorization to use scopes, or permissions to give Google Apps Script certain access to your data to run the code and add in an API to access our Gmail signature block.

Add the Gmail API

First up we need to add an advanced API to get access to be able to display the signature block from our Gmail account. You know, to make it look professional.

To add this API in the Google Apps Script editor:

  1. Ensure you are in the Editor.
  2. Select Services +
  3. Scroll down and select Gmail.
  4. Select Add
Add the Gmail API to a Google Apps Script Project
Click to Expand!

Note! As of writing this, the Gmail API only uses your primary signature block and can’t access any other signatures you might have. 

Make sure the Scopes have been Authenticated

You may find, even if you have set up the triggers, that the script doesn’t run because it is missing the permissions you need to run the script. To find out what happened you can check out the execution log in the Google Apps Script IDE  sidebar -4th one down.

Google Apps Script IDE execution log
Click to Expand!

You can then check the errors of your execution log to see what happened.

To fix this ahead of time we can modify the onFormSubmit(e) function a little to run a dummy version of the script.

Before we start, you need to have a least one response in your Google Form. Go ahead now and add in a dummy form submission. You can always delete it later.

Go back into your editor create a new Google Apps Script file and call it Test.gs.

Copy and paste in this code:

Go ahead and run the code. You should get a warning popup for authentication. This will happen just one time and it will be fine for you and anyone who enters the form in future.

Once you have accepted all the scopes your code should run and you will get an email. If you don’t, check the execution log for any errors.

You can also check to see your script scopes by going to the Overview of your Google Apps Script IDE.

Google Apps Script IDE Scopes overview
Click to Expand!

Now we can go back to our Code.gs file and add our trigger to send the email on submission.

Assign the Trigger on Submit

Next, we need to assign a trigger to run our onFormSubmit(e) function in our Code.gs file.

To do this, navigate to the Code.gs file and then:

    1. In the sidebar of the IDE, select the Triggers button indicated by a clock.
    2. Select Add Trigger at the bottom right of the page.
    3. A popup window will appear. Under Choose which function to run select onFormSubmit.
    4. Select event type: On form Submit.
    5. Click Save.

Google Apps Script trigger on form submitClick to Expand!

Run a practice Form Submission

Go ahead and run a dummy form submission again and you should now get an auto-response email with the email you created.


Looking to learn more about Google Apps Scripts in a more structured format? Udemy has some great courses that can get you from the basics to a real Google Apps Script pro.

Got a more specific problem you need help with, but don’t have the time to develop the skills? Make an enquiry on my 'Hire me!' page. I occasionally pick up projects. Alternatively, Fiverr’s your best bet to find a skilled Google Apps Script developer to solve your problem quickly and professionally. *

*The above affiliate links have been carefully researched to get you to what you specifically need. If you decide to click on one of these links it will cost you just the same as going to the site. If you decide to sign up, I just get a little pocket money to help pay for the costs of running this website.

Code breakdown

Pfaawhh!!! 

If you made it to this bit you really want to learn about the nuts and bolts of the script. That’s awesome.

In this, code breakdown, I will go over some of the highlights of the code to help you understand what is going on.

Get the current submitters data

You will notice the single ‘e‘ as a parameter for the onFormSubmit function. This indicates an event object. When a trigger, like our on form submit trigger, is assigned to a function, you can use the event object to get the data that will be drawn from triggering that event. For us, we will get the response from the person who submitted the trigger.

To do this we use the response method on our event object and assign it to our responses variable. Line 3

This will give us access to the same list of methods as you would when you run the Test.gs testAndAuthScopes function’s response variable on the first response in the form

const responses = formResponses[0];

You can find out more options about the formResponse class here.

Here we can access things like the date and time the response was submitted, the response, the response item, the respondent’s email if that was enabled in the form.

For us, we will grab the current respondent’s email first. Getting to the email is a little different than accessing other responses. If you remember when you set up your form, you can force users to add their email to the form in the settings menu.

The email here will always be at the top of the form. To access it, you need to use the getRespondentEmail() function. Line 4

Next, you can access any form item response in your form by calling the getItemResponses() method that will return an array of responses in your Google Form. You can then count from zero down your form to find the number of the item that you want to draw the response from and grab it with an array call, [n].

For us, we need to get the first item after the email, which is the name. This is the zeroeth item in the list. Line 5

Alternatively, if you have already grabbed the item’s ID, then you could use, getResponseForItem(item).

You would have probably have noticed the trim() method on the end of each email. This JavaScript method allows us to simply take any whitespace that the respondent accidentally added to their response or email – I’m guilty of accidental extra spaces too. 

Get the first name of the respondent

We want to personalise our email response and use the respondent’s name in the email. These days, it’s generally preferable to use a casual first name approach.

We don’t know if the respondent entered their first name or full name. We know with a certain degree of confidence that they will also not add in their title in this input as well. With this in mind, we can extract all the letters up to the first name and safely assume that this will be their first name (No one likes entering a first and last name input when they can do it in one line).

Here’s our code:

Here we create our firstName variable. The first thing we need to check is if the user added just a first name or a first and last name.  This is done with a JavaScript ternary or conditional operator that is basically a single line if statement.

In our condition inside the braces, we use the indexOf method on our name variable. This method takes one variable, the searchable value. In our case, this is an empty space, (" "). If a space is found it will return the index or numerical location of the space. If no space exists, it will return -1.

We check to see if there is no space, which will equal -1. If this is the case, then we just report the name variable, because the user only entered their first name on the form.

However, if there is a space, then we want to create a new string with all the characters up to the first space. We do this by using JavaScript’s substring method. This method takes a start and end index inside the string. For us, our start is the first value which is zero. To get the end index we can use indexOf() again on name to get the location of the space.

Use our signature block on the email

We want to look professional here and add our Gmail signature block to the end of our email like we normally do when we send an email. You would think this would be a pretty simple process, but actually, it is a little challenging.

To access the signature, we have to use the Gmail API Advanced Service we installed earlier.

Here’s the code:

Google Advanced APIs are written a little differently than what is shown in the doc’s when we use them in Google Apps Script. This can make using them a little confusing to get your head around. Let’s take a look at the Gmail API documentation for this method:

GET https://gmail.googleapis.com/gmail/v1/users/{userId}/settings/sendAs.list

In Google Apps Script, this boils down to:

Gmail.Users.Settings.SendAs.list({userId}).sendAs

We replace the {userId} hint with the special "me" input to reference your own account.

Running this on its own will give you a list of all of your emails and their data that are assigned to your account. kind of like this:

 

For me, I have a number of domain accounts along with my primary Gmail account assigned. As you can see in the sample data above, you can see a signature object in the first array. This object contains all the HTML we need to generate our signature block at the end of the email.

We can access this by using the JavaScript filter method. This method creates a new array based on parameters you intend to use to filter. For us, we want to filter down to just keep our default Gmail account. Line 7 

This will give our list of objects and we can then select the signature object (Line 7) which we store in our signature variable (Line 4).

Note, that as of writing this email, there is no way to access alternate emails.

We can then use this at the end of our email message.

Emailing the respondent

Finally, we can email the respondent with our custom message. We do this with the MailApp class and call the sendEmail() method. This method can take a number of possible variations of parameters, but I like to use the object parameter because it gives me more flexibility.

With this approach, you can modify a number of different objects. For our project we will use the following:

  • to: This is the email of the respondent that we will send our message to.
  • subject: Just like in a normal email, you can add your subject text here.
  • htmlBody: You can add your HTML for your text here. You can always use one of the numerous email template builders to do this without hard coding HTML for most of your task. However, you will need to wrap your code in backticks and then at the very bottom add in the signature paragraph.

Here is a link to more emails on MailApp for you to get familiar with it:

Conclusion

In this tutorial, we looked at creating a custom email responder that is triggered when a Google Form is sent. We looked at how to add triggers to run our code when the form is submitted. Then we made sure all of our permissions and scopes were added so that our code would run. We also had to add the Gmail API advanced service to get our signature block.

So where to next?

You might want to run different custom email responses based on the respondent’s response. Here you could look at the user’s response and then perhaps use an if statement or switch to send the code to a specific set of htmlBody that relates to that particular response.

What do you think you would use this for? I would love to hear in the comments below.

If you’ve liked this tutorial and want to get regular updates on what I am working on next, you can subscribe down below this post.

Happy coding!

~Yagi

Create a seat booking form with Google Forms, Google Sheets and Google Apps Script

Google Forms, Google Sheets, Google Apps Script: SpreadsheetApp, FormApp

In this tutorial, we are going to build a simple seat booking Google Form. It will contain a registeree’s name and the session that they wish to attend.

Each time a registeree submits a request to book a seat for a session, that seat is taken from the list displaying only the remaining seats for that session for when the next user submits the form.

Take a look at the example below (click to expand the image):

Basic Seat Booking Form Google Forms

If all the seats have been booked for a seminar, then that seminar will not appear on the form. If all seats have been booked for all seminars, then the Google Form will close.

We will even create a  live list of attendees that we can embed on our website using Google Sheets.

Google Forms can’t do this for you out of the box. We will need to use Google Sheets to store our bookings and do some manipulation of the data. Then, we will use Google Apps Script to update the Google Form with the number of seats each time the form is submitted.

The Google Apps Script script has been prepared in a way that someone with limited coding skills can use it as a template by plugging in a few global variables. Alternatively, a more advanced user can incorporate the code into a larger project.

I’ll assume you have the basic abilities to create a simple Google Form and have used Google Sheets.

This tutorial can be followed as a useful step-by-step guide or if you are more confident, you can simply jump to the code using the contents below.

Continue reading “Create a seat booking form with Google Forms, Google Sheets and Google Apps Script”

%d bloggers like this: