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:

User makes a copy of the Google Sheet template
User only sees a ‘Setup’ sheet tab containing two buttons and instructions. All other tabs are hidden for now.
Click to Expand!
User clicks the first button to ‘Authorise’ the script scopes.
User clicks the second button to run the startup script.
The ‘Setup’ tab will be deleted and all selected sheet tabs will be displayed.

Table of Contents

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:

Select the ‘Template Setup’ sheet tab dropdown (dropup? 🤔) menu.
Select ‘Copy to’.
Select ‘Existing spreadsheet’.
A dialogue box will appear.
Select the file you want to add the sheet tab to.
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.

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.

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.
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.
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)
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…
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
  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.

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.

/**
 * Runs the startup script from the buttons 'Click me to run authorisation' 
 * and 'Now click me to run the starter script'.
 */
function runScriptBeforeReveal(){
  // Add your starter function here (Optional)
  myStarterFunction()

  // Selected tabs to unhide.
  const sheetsToReveal = ["Display1","Display2","Display3","Display4"]

  // Sheet and cell to navigate to when process is complete. 
  const focus = {
    sheet: "Display2",
    cell: "D4"
  }
  

  // Setup sheet name
  const setupSheetName = "Template Setup";


  // Get List of sheets
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  
  ss.getSheets().forEach(sheet => {
    if(sheetsToReveal.includes(sheet.getName())){
      sheet.showSheet();
    }
  })


  // Remove Setup sheet
  ss.deleteSheet(ss.getSheetByName(setupSheetName));

  // Focus on new sheet.
  const activeSheet = ss.getSheetByName(focus.sheet)
  activeSheet.setActiveRange(activeSheet.getRange(focus.cell))

}




function myStarterFunction(){
  // Share access with some editors. 

  // Add a time trigger.

  // Rename connected form titles.
}

/**

* Runs the startup script from the buttons 'Click me to run authorisation'

* and 'Now click me to run the starter script'.

function runScriptBeforeReveal(){

// Add your starter function here (Optional)

myStarterFunction()

// Selected tabs to unhide.

const sheetsToReveal = ["Display1","Display2","Display3","Display4"]

// Sheet and cell to navigate to when process is complete.

const focus = {

sheet: "Display2",

cell: "D4"

}

// Setup sheet name

const setupSheetName = "Template Setup";

// Get List of sheets

const ss = SpreadsheetApp.getActiveSpreadsheet()

ss.getSheets().forEach(sheet => {

if(sheetsToReveal.includes(sheet.getName())){

sheet.showSheet();

}

})

// Remove Setup sheet

ss.deleteSheet(ss.getSheetByName(setupSheetName));

// Focus on new sheet.

const activeSheet = ss.getSheetByName(focus.sheet)

activeSheet.setActiveRange(activeSheet.getRange(focus.cell))

}

function myStarterFunction(){

// Share access with some editors.

// Add a time trigger.

// Rename connected form titles.

}

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.

Need help with Google Workspace development?

Go something to solve bigger than Chat GPT?

I can help you with all of your Google Workspace development needs, from custom app development to integrations and security. I have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.

Schedule a free consultation today to discuss your needs and get started or learn more about our services here.

🐐You can support me for free by using this Amazon affiliate link in your next tech purchase :Computers & Stuff! 🐐

What’s going on?

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

https://yagisanatode.com/2019/03/07/google-apps-script-how-to-connect-a-button-to-a-function-in-google-sheets/

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:

All sheets that you want to be displayed are unhidden.
The setup sheet tab is deleted.
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.

Create and Publish Google Workspace Add-ons with Apps Script Course 300px

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

https://youtu.be/D_D32cWeBus

Run Authorisation and Setup Script Before Revealing Google Sheet Tabs Using Google Apps Script (https://youtu.be/D_D32cWeBus)

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.

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

Yagi says:

at 8:15 am

Hi folks, Yagi here. Just wanted to give a shout out to Ben Collins. I just discovered that this article was featured in his recent newsletter! If you want to learn about all the amazing work Ben does with Google Sheets and Google Workspace check out his site: https://www.benlcollins.com/

Loading...

Ben says:

at 7:06 pm

Hi Scott, I’m a newbie dev working on my very first script for Google Sheets and am super grateful to you for all your shared knowledge and inspiration! This is a great idea and I am trying to implement this. Is it also possible to authorize a linked library this way? Scenario: the user has in his copy of the Google Sheets file a bound script that points to my published library (view-only). Greetings to Australia, Ben

Loading...

1. Yagi says:
  
  at 8:23 pm
  
  Hi Ben,
  
  If the library is already shared in the template sheet, then the library(At the preassigned version) and Advance Services will carry across. This means that when authorization is requested, it will include those scopes requires for any library or advance service.
  
  I hope this answers your question.
  
  ~Scott
  
  Loading...

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