Post number 11 of “The Practical Google Sheets Guide”

These blog posts are part of research I’m doing for a potential new book, hopefully allowing me to further my love for spreadsheets. Theses posts are hoping to provide a practical cookbook of examples to the reader and allow me to learn what people are interested in and how to approach different subjects working with Google Sheets.

TLDR: In this post we will discuss how to use Google Sheets to prototype apps and services you might want to put together for users. The complete sheet can be found at the following link:
https://docs.google.com/spreadsheets/d/1OdyefCS9GwbvX21pFfv5n584qU0aX5vvNEyfPhpbsbE/copy

Introduction

If you have an idea but want to get it live without both spending money and too much time setting this up. There are really great ways to utilise Google Sheets to get a service live and running pretty quickly and allow you to see if you should be spending more time and effort on what seems like a great idea.

In this post we are going to work through a really simple project but then use Google Sheets to put it online to allow multiple users to access and use it. The project is pretty simple. I do a weekly run with a group on Friday mornings, which usually involves multiple repeats of a certain course where we run. We usually rely on one specific member of the group to go through their training history and post the run, that usually alternates over about 8 different sessions.

So the goal of this service will be to add these training runs into a sheet and have the training sessions rotate over the weeks. Then with this service online, it will then allow anyone who has access to the service will be able to see the training run that is happening this week.

What You Need To Know To Get This Done?
So far we have steered clear of using AppScript so far, but in this post we are going to use AppScript to do our major block of work for us. Don’t worry though, we are doing to take things pretty easily and explain all the code we are working through. The set up and creation of our sheet is actually going to be pretty easy and you won’t even need any functions or formulas to create it. All you will need is access to Google Sheets.

Setting Up Our Google Sheet

Our sheet is going to be pretty simple. The image below gives you everything you need to know to set this up.

• Start in cell B2 and add in our title of our page as “FRIDAY INTERVAL RUN” in bold and size 20.
• Move down to cell A4 and add in the text “This Week”, and A5, will have the text as “Next Week”.
• We can now start to add in our interval training sessions into cell B4. Remember, we are setting this up now, but in the proceeding weeks, the data will rotate through, so we should see a different training session displayed in the “This Week” row. For now add the following details into the B4 cell, as we have in the image below:

MT EDEN HILL REPEATS
Come see the new year in from the top of Mt Eden. 3 or 4 times.
Depart grandstand 6:30am to head to Mt Eden via Mountain & Clive Roads. 
4 x steady up and over in clockwise direction, starting at entrance on Mt Eden Rd and easy back down as your recovery.
Slower/injured/special guest runners either do 2 or 3 full ones or some just from and around the roundabout. Or a mix...

• Cell B5, will have the following training session added:

Run intervals suggestion for tomorrow:
15/20 minute warm up.
1k steady,
1k as 500 steady then 500 fast.
1k as 500 fast then 500 steady.
And repeat once more.
Slower runners/time poor could do them as 800’s or just do 4 of the 1k’s. 10/15 min warm down

• B6 will have the following:

15-20min w/up
Steady/Fast pairs :
2x(400m/200m easy)
2x(600m /200m Easy)
2x(800m /200m Easy)
2x(1km/200m easy)
10-15 min cool down

The rest of our training weeks are listed in our example, but we will only show you these cells for now, but feel free to add in the rest of the training sessions we have posted in our sheet template.

Rotate Our Interval Runs With AppScript

It’s time to add in out App Script that will now move our data around making sure we have a new training session each Friday morning.

• Start by opening your App Script editor from Google Sheets by clicking on “App Script” from the Extensions menu:
• Enter the following 11 lines of code into the App Script editor, which will do the work for us:

function rotateColumnB() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange("B4:B9");
  var values = range.getValues();
  var first = values[0][0];
  for (var i = 0; i < values.length - 1; i++) {
    values[i][0] = values[i + 1][0];
  }
  values[values.length - 1][0] = first;
  range.setValues(values);
}

• The details of the code are as follow:
  • The first line gives our function a name rotateColumnB
  • Line 2 sets the sheet value, if we had a few different sheets, we could also specify the name of the sheet with the getSheetByName("Sheet Name") option, instead of just getting the ActiveSheet
  • Line 3 and 5 gets the values in the range we want to use
  • Line 6, 7, 8 move all of the cells up by one, with line 9 then moving the top cell in B4 to the end of the list.
• We can now test our new function. We have a screen shot of the App Script code editor below. Click on the save button to make sure your code is saved. We can then test. There is a drop down list next to the Debug button, and make sure your new function is selected. Once it is selected, click on the Run button, which is next to the Save icon. In my image below, my code executed and completed without any errors. And make sure your training sessions have rotated through as we would have hoped too.

NOTE: If you are doing this for the first time, you might need to verify permissions to allow this function to run. Simply follow the pop up, and approve where needed to

Instead of running the function manually whenever we want the cells to be rotated through, we can instead set the function to be run after a certain event of time of day. On the left hand menu of the App Script code editor, there is a Triggers option. Click on that now.

• Click on the Add Triggers button
• You will be presented with options similar to the screen below. Choose the function you want to run, it should run the latest code(Head), and we have set ours to run every Thursday between 6am and 7am.

Hosting Our Sheet On The Internet

Now we want to be able to allow other users to access our new service. We could simply share the sheet, but this might not be convenient for everyone, and simply sharing a web page might be a little easier.

• Start by clicking on the “Share” option from the File menu, and select “Publish to web”
• You will be presented with a few different options, with a similar screen to the image below, but stick to publishing only the one sheet, in my instance, it is named WEBDISPLAYING, and click on the Publish button.
  

You should now be presented with a large URL, you can now share with other people. The image below shows the page displayed in my cell phone.

Once you are no longer using this service, make sure to always stop publishing, by going to the same “Publish to web” option and clicking on “Stop Publishing”. If you try using the original link again, you should get an error stating “We’re sorry. This document is not published”

Summing Up

We’ve done a lot of cool work here to get our small service up and running and live on the internet. We created a nice little sheet that displayed our weekly Friday run session for the coming month or two. We set up some basic App Script code that rotates the cells for us, and set the function to run automatically every week, so we don’t need to touch anything for it to work.

In the end, we then used Google Sheets share option to Publish our sheet to the web. This allowed us to now share this sheet with anyone who wants to see an use it. Although, the URL is long and not something you can remember, like I said this is just a prototype, and something we can fix up long term.

About The Author

The post is written by Vincent Sesto, a Aussie Software Engineer, living and working in Auckland, New Zealand. If you are interested in my authors page on Amazon, feel free to checked it out at the following link:
https://us.amazon.com/stores/author/B073R3VW2G