Find nearby amateur running, swimming, cycling and other sports competitions

In recent years I have been interested in amateur competitions and there is a problem with finding nearby competitions – there are several different sites that publish announcements from organizers and on these sites all disciplines are usually mixed up and there is never even a general map of which cities these competitions are held in.

For example, I live in Perm and want to take part in some activity next weekend, such as running, cycling or swimming. I enter “Perm” in the search, but there is nothing in the search results. Although there is a competition in the village of Yug, which is less than an hour’s drive from Perm. But since I’m looking for “Perm”, then “South”, of course, is not shown in the list…

Together with Alexander Ivanov, one of the authors telegram channel “Google Tables”we decided to solve this problem in a technical way – to write an Apps Script that will go through the general list of competitions on each of the sites where announcements are published and collect information about the name of the competition, the date and the city where the competition is held. And then it will compile a summary table of upcoming competitions and, ideally, display these points on the map – so the fan can immediately understand where the next competition is held, what date it is and where he can go.

As a result, it didn’t turn out quite as planned, but this could be a starting point for your own search.

Besides, amateur competitions are not primarily about the sport itself, but about improving health and taking care of yourself – at least for me. I don't focus on athletic achievements, but participate for fun, physical activity and the opportunity to visit new places.

Problems finding amateur competitions

The problem with searching actually exists – I've been in the subject for several years now and have been working on it for the last year instead of a live trainer under the guidance of ChatGPTBut when I want to find some kind of running, cycling or swimming competition, the information about it is scattered across several sites, which is very inconvenient.

For example, I have a free weekend and have to spend a lot of time studying different sites, for example:

And all because there is no centralized source for all disciplines. Not only are there a lot of sites, but the search functions on these sites are also ineffective.

For example, I might want to go to another city, but I won't drive more than 6 hours. Or I might even fly somewhere to visit St. Petersburg. That is, the interest affects all Russian cities in general.

But it's especially annoying when you realize that there was time and you missed some nearby location because of the exact city match requirements in the search on all sites. I type “Perm”, and the competition was in the village of Yug.

Proposed Solution

Guys from the telegram channel “Google Tables” created a simple Google Apps Script that automates the process of collecting event data from specific sites and updates a Google Sheet with this information.

The solution is available at the link: amateur_sports_competitions.

How it works?

This Google Apps script automates the scraping process and saves competition data from three specific top websites into the corresponding Google Sheets tabs.

Function run launches data collection functions for iron-star.com, myrace.info And russiarunning.com and ensures that the Google Sheets document is populated with up-to-date competition information from these data sources.

This code consists of three main functions, each dedicated to a specific site, and a central function to coordinate these automatic data extraction tasks.

Here's a detailed description of what each piece of code does:

1. Manifest (appscript.json file):

This file sets the following settings in the project:

– The Moscow time zone is established.

– Adds Cheerio library for HTML parsing.

– Enables exception logging via Stackdriver.

– The execution script uses the V8 environment.

{
  "timeZone": "Europe/Moscow",
  "dependencies": {
    "libraries": [
      {
        "userSymbol": "Cheerio",
        "version": "14",
        "libraryId": "1ReeQ6WO8kKNxoaA_O0XEQ589cIrRvEBA9qcWpNqdOP17i47u6N9M5Xh0"
      }
    ]
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8"
}

2. Main function (file 0.index.gs):

Main function run serves as an entry point that sequentially calls three other functions to extract data from sites:

1. userActionScrapeMyraceInfo

2. userActionScrapeIronStarCom

3. userActionScrapeRussiarunningCom.

function run() {
  userActionScrapeMyraceInfo();
  userActionScrapeIronStarCom();
  userActionScrapeRussiarunningCom();
}

3. Parsing the site iron-star.com (file iron-star.com.gs):

IRONSTAR TRIATHLON website is the largest series of triathlon competitions in Russia.

This function parses information about competitions from the site iron-star.com. It fetches the content of a web page, parses it using Cheerio, extracts event URLs, dates, names, and cities, and then writes the data into a Google Sheets document in a tab named iron-star.com.

function userActionScrapeIronStarCom() {
  console.log('userActionScrapeIronStarCom', 'start');
  // Получаем новые ссылки
  const url="https://iron-star.com/event/";
  const contentList = UrlFetchApp.fetch(url).getContentText();
  const $ = Cheerio.load(contentList);
  const itemList = $('.event-item-wrap a.event-item');

  const values = [];

  itemList.map((_, item) => {
    const $item = Cheerio.load(item);
    const date = $item('.date').text().trim();
    const name = $item('.title').text().trim();
    const city = $item('.place').text().trim();
    const url = `https://iron-star.com${$(item).attr('href')}`;
    values.push([url, date, name, city]);
  });

  const book = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = book.getSheetByName('iron-star.com');

  sheet.getRange(3, 1, sheet.getMaxRows() - 1, sheet.getMaxColumns()).clearContent();

  if (values.length) {
    sheet.getRange(2, 1, values.length, values[0].length).setValues(values);
  } else {
    sheet.getRange('2:2').clearContent();
  }
  console.log('userActionScrapeIronStarCom', 'finish');
}

4. Parsing the site myrace.info (file myrace.info.gs):

The MyRace website is a platform for registration for sporting events in Russia.

Similar to the previous function, this function collects race information from myrace.info. It extracts the web page content, parses it with Cheerio, extracts event URLs, dates, names, and cities, and then writes the data to a Google Sheets document in a tab named myrace.info.

function userActionScrapeMyraceInfo() {
  console.log('userActionScrapeMyraceInfo', 'start');
  // Получаем новые ссылки
  const url="https://myrace.info/take/";
  const contentList = UrlFetchApp.fetch(url).getContentText();
  const $ = Cheerio.load(contentList);
  const itemList = $('.container-content.centered > a.events-list__item.row');

  const values = [];

  itemList.map((_, item) => {
    const $item = Cheerio.load(item);

    const date = $item('.date')
      .text()
      .replace(/(\d+)-(\d)+/, '$1');
    const name = $item('h2').clone().children().remove().end().text().trim();
    const city = $item('.flag').text().trim();
    const url = `https://myrace.info${$(item).attr('href')}`;
    values.push([url, date, name, city]);
  });

  const book = SpreadsheetApp.getActive();
  const sheet = book.getSheetByName('myrace.info');

  sheet.getRange(3, 1, sheet.getMaxRows() - 1, sheet.getMaxColumns()).clearContent();

  if (values.length) {
    sheet.getRange(2, 1, values.length, values[0].length).setValues(values);
  } else {
    sheet.getRange('2:2').clearContent();
  }
  console.log('userActionScrapeMyraceInfo', 'finish');
}

5. Parsing russiarunning.com (file russiarunning.com.gs):

The RussiaRunning website is the largest platform for registration for sports events in Russia. It unites organizers of sports events and participants of competitions. On the website, you can choose an event, register for it, pay for participation and receive confirmation.

This feature collects information about competitions from russiarunning.com and works differently, sending a POST request to the site's API. It receives the event information in JSON format, extracts the relevant details (URLs, dates, names, and locations), and writes the data to a Google Sheets document in a tab named russiarunning.com.

function userActionScrapeRussiarunningCom() {
  console.log('userActionScrapeRussiarunningCom', 'start');
  const url="https://russiarunning.com/api/events/list/ru";
  const payload = {
    Take: 500,
    DateFrom: new Date().toISOString().split('T')[0],
  };
  const options = {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify(payload),
    muteHttpExceptions: true,
  };
  const data = UrlFetchApp.fetch(url, options);

  const items = JSON.parse(data.getContentText()).Items;

  const values = items.map((item) => {
    const { c, d, t, p } = item;
    const link = `https://russiarunning.com/event/${c}/`;
    const date = d.split('T')[0];
    const row = [link, date, t, p];
    return row;
  });

  const book = SpreadsheetApp.getActive();
  const sheet = book.getSheetByName('russiarunning.com');

  sheet.getRange(3, 1, sheet.getMaxRows() - 1, sheet.getMaxColumns()).clearContent();

  if (values.length) {
    sheet.getRange(2, 1, values.length, values[0].length).setValues(values);
  } else {
    sheet.getRange('2:2').clearContent();
  }
  console.log('userActionScrapeRussiarunningCom', 'finish');
}

What's the result?

After parsing three sites through a QUERY request, a selection of all upcoming competitions is made from today forward to the future. The QUERY request is needed to cut off previous dates – after all, when parsing, some sites also give previous competitions.

This Google Sheets formula combines competition data from three different sheets, filters it to include only future events (or events happening today), and sorts those events by date in ascending order:

=QUERY(
        {
        myrace.info!A:D;
        'iron-star.com'!A:D;
        russiarunning.com!A:D
        }; 
"SELECT * 
WHERE Col2 >= date '"&text(today();"yyyy-MM-dd")&"'
Order by Col2 asc
")

What didn't work out

The idea to make such a table came back in mid-May, but the work was completed only now and this is the second version of the code – the first one was much more complex and carried out the parsing process differently.

We decided to move the important part of the idea – not only to collect, but also to display places on the map – to the second part of this article (work on which is currently underway), because we were faced with the fact that the venues are not always indicated and they are not always clearly indicated: for example a village is indicated, but in what region it is located is not clear.

How to use for those who are far from programming

The link below will open your copy of the table. If you are logged into different Google accounts in your browser, you should leave only one, otherwise an error may appear when you click the link. The link will prompt you to create your own copy of the table – create a copy in your account:

➡️amateur_sports_competitions⬅️

Next, open the Google Apps Script Editor:

– In Google Sheet, select Extensions > Apps Script.

– The Google Apps Script Editor will open in a new tab.

Now that you have the Application Script Editor open, run the function called run(). To start it, click the play button (triangle icon).

When you first launch the script, you need to log in to work. This will require authorization in your account. The full launch process can be studied in the article open the script editor in Google Sheet. Don’t be afraid that the script will somehow harm you – at any time you can see the list of issued you permissions on a special page and recall them in one click. In general, you should not worry if it is your Table and your code, as all administrative rights remain solely with you.

Google will warn you that the application is not verified. You need to select “Advanced settings”, and then follow the link below. After this, all you have to do is click on the “Allow” button, and the initial setup will be ready.

After completing these steps, you can successfully run the search script for competitions throughout Russia – it will update the existing data in your copy of the table.

Conclusion

A lot of work has been done to create a summary table of the most popular sites that publish information about amateur competitions.

You can always make your own copy of this table and use it – it will automatically download all the competitions for you and present them in a convenient table format.

Program repository: googlesheets-ru/amateur_sports_competitions

The code was created by the team Telegram channel “Google Tables”

Author of the text and idea: Mikhail Shardin

July 1, 2024