Have you ever wanted to split a list of Google Sheet rows in two by odd or even? Or had a time when you wanted to display all items in a range by odd or even ids in two separate lists?
You don’t have to do this manually, we can work smart and create a formula for each of these scenarios to easily separate your data.
In this tutorial, we will cover three scenarios:
Separate a range of data by odd and even rows.
Separate a range by odd and even where a selected column has a list of numerical ids.
Separate a range by odd and even where a selected column contains a list of alphanumeric ids.
If you want to play along, you can get a copy of the starter sheet here:
In this episode of Google Workspace Add On Walkthroughs (GWAOw!), we take a look at ImportFromWeb by NoDataNoBusiness.
ImportFromWeb is a powerful web scraping tool for Google Sheets that allows you to grab data from any website. The creators call it IMPORTXML on steroids.
You can use ImportFromWeb as a Google Sheets function or use one of their incredible templates.
This tool is ideal for Google Workspace businesses that need to find and store live data from websites. Whether you are scouring the web for new client prospects or keeping an eye on your competition, this might be the tool for you.
In this walkthrough, we really just scratched the surface on ImportFromWeb’s capabilities. We walk through three practical examples covering retrieving a list of books from a web store, listing the first page of Google Workspace jobs on Indeed.com and then getting the latest Tweets for Google Workspace on Twitter. I’ve added the formulas below from the video for you to try out once you installed the add on.
I also managed to score us a 40% discount when you purchase ImportFromWeb after your trial, use the code YAGISANATODE40 at check out to get the discount or use this link:
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.
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.
Step-by-step instructions: that the user can follow to give them confidence that they are following the process correctly.
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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
/**
* Runs the startup script from the buttons 'Click me to run authorisation'
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? Udemyhas 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().
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.
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.
In this episode of Google Workspace Add On Walkthroughs (GWAOw!), we take a look at Workbook Statistics by Sourabh Choraria.
Workbook Statistics is a FREE add-on that allows you to get intimate with all of your Google Sheets stats from your menu bar. You can grab stats on the number of rows, sheet names, pivot tables and much more from an individual Google Sheet to the entire workbook.
Working with IMPORTRANGE data in Google Sheets can be a little tricky. It may feel at times that it does not play by the same rules as when you are building formulas with data in the same Google Sheet.
In this tutorial, we’ll go through two approaches to filtering and sorting your IMPORTRANGE data by using the FILTER and QUERY functions. We’ll run through some examples of each and look at some of their pros and cons.
Then, we’ll wrap things up with a walkthrough and example on how to build your very own dynamic data dropdown dashboard from IMPORTRANGE data that lets us look at a set of sales by any company from our imported data any sales rep that makes a sale to them.
I encourage you to play along with the examples. You can find a copy of the Google Sheet that we will be importing here:
Click on the ‘Make a copy’ button to create your very own copy of the sales sheet. There are heaps of bonus formulas in there too along with a few fun Easter Eggs for the curios.
