Logging Marathon Runner Checkpoints in Google Sheets

Registering runners:

The scrRegister Screen has

  • a text entry box for a runner name
  • a text entry box for a unique runner ID number, which can be optionally filled by
  • a Scan button
  • a Register button, to add the runner to the Runners sheet.

There is no Screen Initialization code for this screen.

A Scan button is provided for people who have pre-printed bar codes they want to register with runner names.
when  btnScan .Click do

When the Register button is Clicked,

  • Check for blank runner names and ID
  • Ask Sheets for any Runners rows that already have this runner ID, to avoid duplicates, using a Filter Query for just that ID.

We want the filter result to be empty. If it is not empty, complain. Otherwise start building the row that will hold this runner:

  • runner ID

  • runner name

  • lookup formulae for each of the checkpoint names (Staten Island, Brooklyn, etc.)

  • Pop up a Progress Dialog to warn that a sheets Add is in progress

  • issue the Add Row request

Here is how each lookup formula is built:

To verify I got the right ranges:
MilePosts Sheet

I had to add a function to bypass a bug in the Spreadsheet component that makes it choke on a text JOIN result:

This concludes the tutorial.

This is only one way to handle the problem of keeping milestones in a row.
It could also have been handled by looking up the row number of a runner when he passes a checkpoint, and updating that cell in the runners sheet.

The detail logging done here would facilitate adding more data to the checkpoint rows, like the identity of the person who logged that checkpoint for that runner.

Feel free to comment on this tutorial here.

P.S. @TimAI2 pointed out silently that the Sheets Pivot facility can be used to generate those rows of Checkpoint times by runner ID.

I have no data yet on whether or not this operation needs to be done after all data collection is complete, or if it can be done before data starts to arrive and will automatically update from the Checkpoint details sheet during the race.

1 Like

Its possible to give me this project demo .aia file?

Unfortunately, the required JSON authentication files in the .aia file are sensitive.

Fortunately, all the blocks in this thread are draggable to your own projects.

1 Like

how i connect my apps to google spreadsheet ? when i cant entry sheetname

See FAQ Section: Google Sheets
for more information on how to work with Google Sheets.

You have to set up your own Sheet and authentication for it according to the spreadsheet component help.

ah thanks for helping, this is my first time

I already following this post Google Sheets API Setup and Spreadsheet component. Example. Google Sheets

but I still cant get my sheetname. How i can do it ?


Sheetnames are at the bottom of your sheet, if you look at it on the web.

The sheet name for this sample is Runners.
See the highlighted tab?

A new sheet typically has a sheet name of Sheet1.

thanks for reply, so we need to type it manually ? or automatically connected and select from the list ? because the sheet im using is my copy from your google spreadsheet file

Im using this

So which sheet did you want to read?

Type that name into a text block.

how ? I try to type on after get


sorry if im disturbing you? Maybe any private message to this ?

There is no sheetname for GLOBAL_RUNNERS_SHEET...

Im following this sir, I do exact copy before editing

Before you refer to a global variable, you must code an init global block for that variable.
For global variable RUNNERS_SHEET, you would want to init it as 'Runners' to match the sheet name in your picture.

You might be wondering why i didn't just use a text block containing 'Runners' in that ReadRange block.
I use constant global variables to avoid typoes in important text values.

I hope you are not color blind.

Ah I see my mistake now.. Thanks for explain it, I re read the guide and I find my mistake.

Now i find a problem at this


Where the initialize global for global row ? Did I miss it ?

Sorry, I did not supply it because it is used temporarily, for debugging.
It could have been used as a local variable.
Its initialization would be irrelevant, since it is assigned at the start of GotFilterResult.
You could init it as CREATE EMPTY LIST, to set reader expectations of its usage.

This version is cleaner, more self contained, at the expense of not leaving behind a debugging aid.

Thank you so much, this is what i need..

Hello, can i ask more ? I already copy exact ur code but got error. Is i must learn script or using google script too ? I want create apps like this please help

this my worksheet

I want create when user login or entry his ID, apps search for ID on google sheet to continue the process this my block, I want use this for patrol report

this what i create but I cant complete the filter process.. Please help.


Unfortunately, this board does not translate text in images.

What is does that error message say?

I don't know your language.

Is it French?

I see you do a read Range in your screen Initialize event, but you are missing the event to catch the range data when it arrives:

I recommend looking through the spreadsheet blocks that read and write to the sheet, and see if you can match them up with the events that catch the results.

Do not be discouraged, you are going through the same learning curve you would get in any implementation.