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.

Limitations

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

Conclusion

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 force subscribe a user in your domain to a Google Calendar with Google Apps Script.

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.

While providing an invitation gateway makes a lot of sense for Google Workspace consumer(free) accounts where you don’t want strangers to subscribe you to calendars that you don’t want to be a member of, it can be a little frustrating for Google Workspace business and organisations that need users to see a particular calendar.

You’ve probably experienced this, but you can’t always rely on your users to accept these invitations to add a calendar. Then down the track, the user is left scratching their head wondering why they are not getting the calendar events like everyone else.

Continue reading “How to force subscribe a user in your domain to a Google Calendar with Google Apps Script.”

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”

Append List Items, Paragraphs and table cell items with a date-time stamp in Google Docs using Google Apps Script

Recently, I thought it would be a cool idea to add a date-time stamp to the end of a Google Doc checklist item with Google Apps Script. So I knew when I completed a task.

I often share a project Google Doc with clients and then add my tasks to the document list. With Google’s new check box list item, I wanted to add the date and time that I completed the task when I checked the box.

The bad news is that there is no onEdit() trigger (like in Google Sheets) for the DocumentApp class that would listen for an edit of the document and see a change of the checked box from unchecked to checked and then apply the date-time stamp. 😢

All good, I settled for the next best thing! A menu item.

Take a quick look at the results.

Continue reading “Append List Items, Paragraphs and table cell items with a date-time stamp in Google Docs using Google Apps Script”

%d bloggers like this: