One Approach to Encourage Users to Run Google Sheet-Bound Apps Script When They First Make a Copy of the File

I quite often get called upon by clients to create Google Sheet templates that have Google Apps Script Automations bound to them. Sometimes these Google Sheets require an automated setup process to run things like gathering initial data, setting up time triggers, approving scopes connected to onEdit() or onOpen() triggers or renaming connected forms and their contents.

One of the challenges is getting new owners of the duplicated template Google Sheet to run the bound script before they dive into working on the Google Sheet (Often, only to discover that things aren’t working how they want them to). It can be a frustrating step for both the user and the developer.

Another issue that also arises is when a user runs a script for the first time. The user will need to give permission to the scopes that will be used to run the bound script. After authorising the script to run, the script will not continue to run and will require the script to be run a second time to execute the process.

After quite a lot of trial and error, I have devised a pretty solid approach that seems to have the most success in getting users to run through the authorisation of scopes and then run the startup script.

Here are the basic steps:

  1. User makes a copy of the Google Sheet template
  2. User only sees a ‘Setup’ sheet tab containing two buttons and instructions. All other tabs are hidden for now.

    Run Script before exposing the rest of a template Google Sheet
    Click to Expand!
  3. User clicks the first button to ‘Authorise’ the script scopes.
  4. User clicks the second button to run the startup script.
  5. The ‘Setup’ tab will be deleted and all selected sheet tabs will be displayed.

The Setup Google Sheet Tab

You don’t have to reinvent the wheel. You can grab a copy of the ‘Setup’ Google Sheet tab mentioned above and insert it into your own project. I’ll explain why its features seem to make it so effective in a moment.

Alternative you can make your own version based on the discussion about its features below.

Here is the link to the sheet:

Setup Google Sheet

To add the Sheet Tab to your own Google Sheets template:

  1. Select the ‘Template Setup’ sheet tab dropdown (dropup? 🤔) menu.
  2. Select ‘Copy to’.
  3. Select ‘Existing spreadsheet’.
  4. A dialogue box will appear.
  5. Select the file you want to add the sheet tab to.
  6. Note! The Sheet tab name will have ‘Copy’ added to it in your target Google Sheet. Simply double click the sheet tab name to rename it.

Runs Script before exposing the rest of a template Google Sheet_copy setup sheet tab to another sheet

Setup Sheet Tab Features

Whether you are creating your own ‘Setup’ sheet tab or reviewing the one provided to make your own, here are some of the features that make it successful.

Runs Script before exposing the rest of a template Google Sheet_breakdown

  1. Clear title: Users might be surprised that they are not seeing the full spreadsheet they intend to work in. So a clear title explaining what is going on is a must.
  2. Brief explanation: A brief explanation that the Sheet the user made a copy of contains scripts that will need to be run before the Sheet will work as expected along with instructions that it will take two steps to complete the process.
  3. Clearly identify the steps: Both in the basic instructions and then reinforced in bold on lines 7 and 23 for step one (authorisation) and step 2 (running the script)
  4. Keep it lean and viewable without scrolling: People don’t like to read. Yeah, yeah, I know, ironic coming from the guy who writes the wordy tutorials. But when it comes to these types of tutorials keeping it lean is key. If a user has gone through this type of process before, then they don’t need a full walkthrough of what to expect and can just quickly follow the basic instructions and get on with it, but…
  5. Also, provide noob instructions: Not everyone is a pro, so it is good to provide more details. The best compromise here, I found was to add further details in a Group that is hidden with a ‘+’ button. Here is what they look like expanded.
    1. Authorisation
      Runs Script before exposing the rest of a template Google Sheet_authorisation detailed instructions

      1. Link to video tutorial: There is a link to a quick explainer video about authorising scripts for the user to come to grips with giving permissions to script scopes.
      2. Step-by-step instructions: that the user can follow to give them confidence that they are following the process correctly.
    2. Run Startup Script: This one is pretty basic and instructions seem to be enough for anyone who is met with this startup page for the first time. You could add more detailed instructions here for your specific needs.
      Runs Script before exposing the rest of a template Google Sheet_script run detailed instructions

The Code

The code for the setup page is run when the first button is clicked.

Copy and paste the runScriptBeforeReveal() function below into your project.

Rows to update

Line 7. Add your own start-up function here.  You can see my example myStarterFunction as a guide.

Line 10. Add your list of sheets to reveal. These are all the Google Sheet tabs that you want to be displayed after the startup process is complete. You could also change this to the sheets to be kept hidden and change line 27 to if(!sheetsToKeptHidden.includes(sheet.getName())){ .

Lines 14 & 15. This is the location you want to show your user once the setup is complete. Provide the sheet name and the cell location.

Line 20. Setup sheet name. If you change the setup sheet tab name to something other than the one provided in the example, please update this line.

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.

What’s going on?

Both buttons in the template setup reference the function runScriptBeforeReveal().

Google Apps Script: How to Connect a Button to a Function in Google Sheets [Updated 08 Apr 2022]

When the first button is clicked, Google Sheets will prompt the user to run through giving permission to the scopes needed to run the script. When the second button is clicked the actual script is run to complete the setup process using your setup function then:

  1. All sheets that you want to be displayed are unhidden.
  2. The setup sheet tab is deleted.
  3. The user is sent to your selected sheet at your desired cell.

But wait a second, Yagi. Aren’t both buttons referencing the same function? Can’t someone just click the first or second button to run authorisation and then click the same button again to run the script?

Yeah, yeah, I know. There is a bit of subterfuge going on here. To be honest it won’t matter what button the user clicks so long as they do it twice.

So why the two buttons? 

I tried it with one button to ask users to click it twice and then trialled it with two buttons. The success rate with two buttons was much higher. Two buttons just seem to be a better psychological tool to get the user to complete the process.


You might have a nagging feeling that this is not going to be a 100% accurate way to ensure that the user completes authentication and runs our startup code, and you would be correct. We are still putting our trust in the hands of the user. However, in circumstances where we have templates like these that a user makes a copy of, then I have found that this is the best possible process.

Another limitation is that if users who are not the owner of the Googe Sheet need to run code, then they will still need to run the Authorisation process. In this case, they would not need to be activating startup scripts but just scripts that would help them complete their workflow in the Google Sheet. It might be a good idea here to have an ‘Instructions’ sheet tab for these users with their own button that would prompt them to run the authorisation process should it be required.

In the end, the best approach would be to transform your templates into Google Workspace Add-ons that can enforce the authorisation process and run setup code more fluidly, but that can be quite an unnecessarily daunting process.

The Video Tutorial


I’ve used this template without alteration on dozens of template sheets now and it seems to be the most successful. However, there is always room for improvement. How would you tweak the setup sheet tab? Do you have another alternative that you like to use? I would love to hear in the comments below.

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

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

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. 

Google Apps Script: How to Connect a Button to a Function in Google Sheets

Continue reading “How to remove a Google Sheets button (drawings) or images connected to a Google Apps Script after the script has been run”

Copy and Paste Range Values from one Google Sheet into another with Google Apps Script

You can easily use Google Apps Script to copy a range of data from one Google Sheet to another Google Sheet, just like you would with using the IMPORTRANGE function in Google Sheets. However, there are some clear advantages to importing ranges with Google Apps Script.

In this beginner-friendly tutorial, we’ll create an importRange() Google Apps Script function that you can quickly duplicate and even expand on in your own projects. We’ll also show you how to apply certain formatting and a time trigger to your code.

Note! This tutorial covers how to replace a range with existing data using Google Apps Script. If you wish to append data please head to the ‘Further reading’ section for more tutorials on this topic.

As usual, read what you need and skip the rest. 

Continue reading “Copy and Paste Range Values from one Google Sheet into another with Google Apps Script”

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”

Create custom prefilled Google Forms links in custom emails with Google Apps Script (Updated Feb 2022)

Recently I raised a support ticket with a tech company I was subscribed to where we were trying to resolve an integration issue I had with their service. Once we had it all resolved they followed up with a feedback form. That feedback form just happened to be a Google Form.

Great, that’s cool. But that wasn’t what got me excited. They had exposed the raw URL link to the form in the email and I noticed that there were some references to my name, my support number and a few other things in the URL query parameters.

I clicked the link to the Google Form and, as expected, the Google Form appeared with these values prefilled into my form.

We this is a pretty cool convenience, I thought. How did they get all the query paths to each form item?

A couple of days passed and I had a chance to figure it all out.

In this tutorial, I’ll walk you through accessing the prefill tool in Google Forms. Then, if you are keen on doing some coding, we’ll create a little custom feedback form for unique users that we will deliver via email.

Let’s play!

Google Forms prefill tool

Accessing the Google Forms prefill tool

First, take a look at my example Google Form:

Go ahead and type in your Chrome browser address bar and create a few form items so you can play along.

Once you are done, got to the top right next to your avatar and you will see a vertical ellipsis. Give it a good old click.

A popup window will appear. Three items down and you will see the menu item, Get a pre-filled link. Go on, you know you want to click it. I won’t judge.

Google Forms menu buttons ot Get pre-filled link
Click to Expand!

A new window will appear in your browser with a sample of your form. Go ahead and fill out any part of the form that you want to have prefilled.

We’ll fill out the first three items in our form. Here, take a look:

Google Forms prefill screen
Click to Expand!

As you can see above I have added my name (Yagi the Goat), a ticket number (6047) and issue (Login – Passwords).

You might have noticed down the bottom left of the screen a grey box with the prompt, Prefill responses, and then ‘Get link’.

Go ahead and scroll down to the bottom of your form and click the Get link button (1).

Google Forms prefill get link & copy link
Click to Expand!

Then click the COPY LINK button in the grey bar (2).

Paste your link in a new browser tab and hit enter to check that the pre-fill is what you wanted.

If you are happy with the prefill results, then paste the pre-fill link somewhere safe for you to use later.

You should end up with a URL a little like this:

You should be able to see some of the pre-fill items in your URL that you added earlier. We’ll go onto this later if you are following along to the Google Apps Script portion of this tutorial.


Why would you use a pre-fill in a Google Form?

At first, I was a little lost at the usefulness of using a standard static pre-fill for your Google Form. Surely not all people on your form will need to choose the same thing. I mean, you may as well leave it out of the form, right.

However, after a bit of noggin scratching, I thought that maybe you could use a static prefill like this for a standard response to help most users skip filling in unnecessary parts of the form while still making it flexible enough for the user to change the form if they need to.

When it does become an awesome tool is when you can use the URL generated and update fields to customise it for each user.

In the next part of this tutorial, we will do just that with the help of some Google Apps Script and then add our form to a custom email.


Create a custom prefilled form link and email it

In this portion of the tutorial, we are going to create a custom pre-filled form link by altering our copied pre-filled form link and then send a custom email to a user with their name and their own unique Google Form link.

The example

Let’s assume we have our very own tech support team. After we complete each ticket, our team are eager (yeah right!) to find out how well they performed in their support of the client.

The team stores each completed ticket details in a Google Sheet like below:

Support ticket Google Sheets for Google Forms prefill V2
Click to Expand!

Looking at the image of the Google Sheet above, we only want to send an email to those clients whose checkbox in column I is unchecked – indicating that they haven’t received and email yet.

We then want to send an email to our users with a message and a link to our unique pre-filled Google form.

For example, our last user, Andrew Bynum, would get an email like this:

Custom feedback email with link to prefilled Google Form
Click to Expand!

Then when Andrew clicked on the form link he would be navigated to his own pre-filled Google Form with the first 3 items filled in like below :

Custom Google Form pre-fill for specific user
Click to Expand!

The anatomy of the pre-fill URL bar

That was generated with this bespoke URL:

If you look carefully, you will see some of the input we put in our form when we were using the Google Forms pre-fill tool.

This portion of the URL directs the user to the Google Form, with the ID of the form in blue above between the last two forward slashes.


Next, you can see 3 occurrences of entry followed by a number (in red) then equals to the pre-fill input we added (in green). Note that if a prefill item has a space, it is replaced with a plus (+) symbol.

We start to write out our code we can replace these pre-filled inputs with a variable that can update for each user we send our form to.

Time to check out the code to see how we do this.

The Code

This is a pretty basic procedural code so we will simply pack it into one function. No need to go crazy here:

Main variables

Variables to update

We need to first set up some main variables that we will reference in our project. First, we will get access to the Google Sheet that contains the ticket data for our clients – the Tickets file we mentioned earlier – using the SpreadsheetApp class.

We then call the openById() method which takes one argument, the file id. This can be found in the URL and should look similar to the one in the example. This is then put in the SS variable. Line 10

Next, we need to get to the sheet tab our data is in. For us, this is Ticket. So we reference this sheet tab name with our getSheetByName() method and store it in our SHEET variable. Line 11

We will want to indicate what row our user data starts because we don’t want to include our headers. Here we set our ROW_START variable to 2 because our first user is in row 2.

Getting data range and values

Our next task is to get the range of all the data we need to add our pre-fill values, emails and client name data along with our checkbox to see if we need to email that user. We may as well select all the columns and grab the last row.

To grab the full range of our data we use the getRange() method. Which can take many types of arguments, but for us, we want to give it 4 number values:

  • Row start
  • Column start
  • Number of rows
  • Number of columns

We’ll add our ROW_START in our…um…row start argument. Our column start in the first column. Then we grab the last row, which will likely change often by using the getLastRow(). This will update as new entries come in.  We then subtract this by the row start and add 1 to exclude the header. Line 13

To then get the values of the range we use our new range variable and call the getValues() method. This will produce a 2d array of all the data on our sheet. Line 14

Keeping track of emails sent.

Our checkboxes in column keeps track of who we have sent our feedback form to. We will update the checkbox with a tick if we have sent the form using some code.

Before we jump into our loop through each ticket we need to keep track of where the boxes are unticked and where the row of unticked boxes finish. We do this by setting up an object to store untick start and end rows that we will preset as false and update as we loop through the rows.

If you wanted to speed things up in a bigger Google Sheet you could store the start row in a Properties Service like in the post below, but that’s beyond the scope of this tutorial.

How to Automatically Navigate to the First Empty Row in a Google Sheet using Google Apps Script

Looping through our data and setting up our column variables

Now that we have the values of our Google Sheet in our VALUES variable, we want to loop through the 2d array and set some variables to each column of data we want to use in our script. We use the forEach method for our loop here with the first argument being the array containing all the cell data in the row and the second one, the row index:

Next, we need to assign some variables to each relevant row item that we will use in either our email or our pre-fill. To do this we will use some destructuring to cleanly build our variables:

The columns in our sheet contain the following:

  • Date
  • Name
  • Email
  • Ticket #
  • Issue
  • Details
  • Response
  • Status
  • Feedback Sent

The bolded items are the only columns we want to use. In our destructured variable assignment, we create an array of all the variables we want to use and put a blank comma space between the variables we don’t want to use.

Creating the first name variable

It’s kinda weird these days to address someone by their first and last name in an email greeting. Some people even find it a little insincere or annoying. So we might want to just stick to the more popular informal first name.

To get our first name, or fname, we use the Javascript substring method to just get the first part of our string up to just before the first space. The substring method takes 2 arguments. The start position and end position. We find out the end position by using the indexof method that searches a string of text and if it finds the corresponding value, it will report the position of the value, but if the value does not exist it will report -1.

The resulting code would look like this:

Now, we are not certain if our users have put in a second name, or even have one for that matter. So if we just created our fname varaiable with this code we would get a weird error if we had a single name.

To fix that, we are going to use a ternary operator that we will first use to check if the name variable is a single name or not. Here again, we use the indexof method to check if there is a positive number. If so we will use the code above to generate our name. Otherwise, we will use just the name. Check out the full line of code:

Swapping spaces between words for “+”

When we create our custom pre-fills we noticed that spaces were repaced with plus symbols “+” in the URL. We want to keep the full name and the issues in our prefill and we know that both items potentially contain spaces in the text. To change the spaces to plus symbols, we will use the Javascript replace method with the help of a little bit of regular expressions.

The replace method takes two arguments, the item to search for and the item you want to replace it with. Because the item we are searching for is a space it’s good practice to use a regular expression rather that ” ” to be certain you catch it. Our regular expression looks like this:

The \s is the symbol for spaces. The two / mean anything between. The g is the symbol for global. So essentially this expression is saying that is is looking for any occurrence of a space all over (globally) in the string.

We’ll update the two original variables (which will upset the functional programming purists, but hey, it’s only a small bit of code) so our two lines will look like this:

Sending off our email

In the next section of our function (Lines 33-46), we check to see if we need to send an email, and if we do, we send it away with our pre-filled link to our form.

First, we use an if statement to check if the current feedback cell is false, then we are good to send the email.


Next, we invoke the GmailApp Google Apps Script class and then use the sendEmail method. The sendEmail() method can take a few different argument structures, but I like to use the full method approach that takes the following:

  1. Recipient: The email of the person you are sending your email to.
  2. Subject: What your email is about.
  3. Body: We’ll put in a placeholder here, “see HTML body” because we want to use HTML to make our email look fancy.
  4. Options: The are a lot of options you can put inside the curly braces {} of this object, but for us, we just want to add htmlBody. Which allows us to add HTML to our email.

Let’s have a look at the sendEmail() method so far:

The HMTL Email

We will use template literals to create our string of HTML text. Template literals start and end with backticks (`). If you want to add a variable into the string all you need to do is add ${your variable}. The other bonus is that you can happily put your string on new lines of your code without having to close and concatenate your string each time.

Let’s take a look at our htmlBody value:

You can see that it all looks like pretty standard HTML text separated by paragraph tags <p> and breaks </br>. We’ve added in the first name (fname) in the greeting at the start and then created a link to our pre-filled form that we have customised with our variables.

Here is what each entry looks like:

  • entry.1046214884=${name}
  • entry.2009896212=${ticket}
  • entry.415477766=${issue}

Once this part is complete the emails are all sent off. Time to update our Google Sheet to show we have done this job.

Updating the checkboxes

The checkbox process occurs at the end in two stages here. First as we are iterating through our forEach loop we need to keep a record of the first unchecked box and the last one.

Remember earlier that we had set up the variable, uncheckedBoxRange, before we started the loop. Now we want to check if this is the first time we have found an unchecked box. If it is we want to update uncheckedBoxRange.start with the current index plus the ROW_START value to get the row number and also update the uncheckedBoxRange.end.

If we have already found the first occurrence of an unchecked box, we skip updating the start value and just update the end value.

Outside our loop, we then need to use our uncheckedBoxRange object values to update our checkbox columns in our Google Sheet.

First, we need to get the total number of emails we sent. We do this by subtracting the uncheckedBoxRange.end from the start and add 1.

We then want to create a string of true values equal to the uncheckedCount. This can be done fairly cleanly by the new Array constructor that can take an argument to generate amount of values in an array.

Next, we use the fill method to identify what we want to fill each array value with. For us, this is a child array with the value true in each. Why a new array inside our main array? Because each row of a sheet is its own array.

We then use the Google Apps Script getRange() method again to select our range referencing our start row of unchecked boxes, column nine, the total number of unchecked boxes. We don’t have any other columns to worry about so we don’t need a fourth argument.

Finally, we use the setValues() method inserting our newly created array of true (or ticks) into our checkboxes.


To run your code from the Google Apps Script IDE simply click on run and follow the prompts:

Alternatively, you could set a time trigger to run your code daily or weekly or when the Google Sheet changes, or have a button or menu item that you click in your sheet to run the code.

Here are a few tutorials on the topic:

So what do you think? Would you use pre-fill in your own project? I would love to hear how you applied custom pre-fill. It’s always interesting to see what creative things people develop.

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.

%d bloggers like this: