How do I program role-playing engines in Google Sheets

The idea of ​​using spreadsheets as an environment for developing toys is not new . In this article, I will share my experience in creating engines for live-action role-playing games and teambuildings that can greatly facilitate the life of game practitioners.

Why is it necessary

Suppose you are playing a game in which 50-60 people are participating at the same time. Each player has a set of personal parameters (for example, experience, money, health, reputation), which vary depending on the actions he performed. There are also a number of general game indicators (for example, ecology, the country's budget or government rating), which also fluctuate in accordance with the actions and decisions of the players. And there are game events that occur with a certain combination of external factors.

In general, this is a fairly dense stream of information that needs to be promptly and accurately calculated. In a classic role-playing game, this function is usually performed by the game master. But the master is not safe from mistakes, and he still will not process a lot of information - neither in his head, nor even with a piece of paper.

With an electronic engine, one person is able to process an information stream of any volume. At the same time, game statistics can be displayed on the projector so that participants can observe in real time how their actions affect the game layouts. Or, for example, you can show secret data to each player targeted on a smartphone. And most importantly - you can use this engine for free!

Carried away by game practices about a year ago, I managed to write 7 engines for different team-building games. Most of these games have commercial success, and one has even been sold as a franchise abroad.

Cash flow

It all started with the Cash Flow game . Friends invited me to play Robert Kiyosaki's famous table, and she really hooked me. The only thing that annoyed the game was the need to constantly make calculations in a column, erase the outdated data with the eraser and add new ones. Due to the fact that all participants, fearing to make a mistake, were constantly concentrated on their own calculations, the game lost much of its dynamics and was delayed for many hours.

I decided to optimize the gameplay by translating all the costing into a Google spreadsheet.



To test this "calculator", I spent several games for friends. The effect was amazing! Instead of poring over the calculations, the players were actively involved in the gameplay, negotiated, and came up with complex financial schemes and frauds. The speed of the game itself has almost doubled. We first managed to bring it to the end when all the players left the rat race track and reached their goals. For comparison, in the “manual” version, the participants barely had the patience to finish playing until the moment when at least someone alone managed to reach a big circle.

“To go nuts! - exclaimed my friend Ruben during one of the games. - Eight adult men gathered to stare at the screen with a Google tablet all evening. But it’s really more interesting than any movie! ”

My plate for calculations in the game "Cash Flow" can be downloaded from here . If it is not completely clear how to use it, write to me and I will send detailed instructions.

Zombies, Presidents and Assassins

A little later, I came across the script for the cabinet role-playing game " All The President's Zombies " by American game designer Mike Young. According to the plot, a zombie apocalypse takes place in the country, and a group of senior officials gather in the president’s office to work out a plan to solve this problem.



I liked the concept, and I decided to create my own game, localizing the creation of Young. The game " Emergency meeting", Which is far enough away from the original. It is based on a leafy tree of events that occur in real time depending on certain decisions made by the players at the meeting. Instead of painstakingly teaching the masters of the game, I created a Google table, which automatically calculated the consequences of each of the actions taken by the players. The presenter only had to tick the screen and draw cards from the deck, the numbers of which were displayed in the table.



A certain difficulty was caused by the need to track the dynamics of game parameters. After all, when a cell value changes, the table instantly recounts all the formulas associated with it, without saving the old value anywhere. I figured out how to get around this complexity, but I will talk about this below.

Having spent a couple of dozen “Emergency Meetings” for friends and acquaintances, I realized that this is a promising format for all kinds of team buildings and corporate stories. Only the restriction on the maximum number of participants in 12 people interfered. And I began to come up with new games that could be played by a large team. At the same time, one leader was still supposed to manage the gaming ecosystem. So there were the games “ Freaky Venice ” based on the Assassin's Creed franchise, “World Domination” on the confrontation of superpowers on the world stage, “Election Day” about honest and not very political technologies, and a few more games that solve specific tasks of specific customers.

When developing each of these games, I ran into certain difficulties, and now I will analyze some of the most interesting examples.

Problem - Solution

The basis of any game engine is the following sequence of actions: 1) collecting information about decisions made by players, 2) entering this information into the system, 3) checking conditions and calculating new data by the system, 4) issuing new data to players. Depending on the plot of the game, this cycle can be repeated from one to a conditionally infinite number of times. At the same time, all the communication of the players preceding the adoption of final decisions remains “behind the scenes”, and the game system is not taken into account in any way. So in general, the process does not produce the sensation of a computer game, leaving room for lively emotional interaction.

Cycle emulation

The main difficulty in developing the engine in the Google table is the lack of such familiar to the programmer entities as a variable, cycle, interrupt, procedure.

Suppose you can replace the missing buttons with a cell with the value “checkbox” - in a separate cell then there will be a formula that checks the state of the checkbox and calculates the value of the target cell depending on this. But what about the fact that the entire table is static in time, and when you change the value of one variable cell, the values ​​of all other cells that refer to it in the formula are instantly and “without demand”? I haven’t come up with anything better than compensating for the lack of routines and loops using tabs. You simply create as many tabs in the table as there are cycles in the game and how many times per game, theoretically, one variable can change its value. Then, for example, a cell in tab-2 will inherit the value of the same cell in tab-1, undergo some changes and transfer your value to the same cell from tab-3. For example, look at the tabs for cycles in the game World Domination.



According to the plot, the game always ends after 6 years, so there are 6 tabs with game fields in the table.
And here, for example, is a formula that calculates the country's budget in the third year of the game, summing up the initial budget of the second year and the sum of the budget growth of the second year:



Here E41 is the amount in the budget at the beginning of the year, X44 is the amount of changes in the budget during the year.

Or here is a formula that displays data on the economic condition of the city in a cell on the player’s tablet, depending on what year it is in the yard. Depending on the value of the “year counter” cell $ F $ 1, the values ​​of the same E36 cell are displayed, but from different tabs corresponding to a particular year.



Such a solution, unfortunately, makes it possible to develop only turn-based games consisting of certain cycles (day, day-night, month, year, etc.). Moreover, the number of cycles should be visible, otherwise it will take forever to write the code. However, if there is no task to inform the players of the intermediate values ​​of the game parameters and track their dynamics in the process, with the tabs-cycles you can not bother at all.

Role distribution

Sometimes, before the start of the game, I want to wisely distribute the roles so that, for example, the most active characters go to the more sociable players, and the double-bottom roles go to those participants who can bluff and restrain emotions. In the game "Emergency Meeting" this task is also automated. Before the start of the game, participants receive a link to a questionnaire in the Google Forms format, where they answer several questions about their gaming preferences.



These data fall into the table where the algorithm based on them distributes the roles between the players.



Player data collection

How to organize data collection from players, if there are 80 of them? At first, it seemed like a good idea to use messengers for this purpose. Each player (or each team captain) has a direct chat room with a host, where he can send his teams and orders, notify about decisions made. However, test games showed that messengers are a bad idea. Players begin to flood, ask a bunch of questions, make mistakes and cancel previously sent orders. As a result, the processing speed decreases, the query stack grows, and players have to wait a few minutes for their order to take effect. In addition, participants spend most of the game sitting in their smartphones, and this does not contribute to lively communication and team building.

A good decision was to collect data from players through questionnaires and cards. For example, in “Plague of Venice”, players throughout the game hand over to the host the completed cards with wordings such as “Paolo Alighieri sent killers to Giacomo Sforza” or “Leonardo Bertolucci seduced Lucretia Medici”, and the host only needs to check the table at the intersection of the corresponding names and surnames.



And in the game “Election Day”, senior students' teams submit completed questionnaires of this format after each game cycle.



The data in such a questionnaire is streamlined as much as possible, and the host needs no more than 5 seconds to drive them into the table.



Balance adjustment

In role-playing games, the game balance is important: resources should always be in short supply, and various tools to achieve goals are approximately equally effective. Balance can be built only manually and only empirically, conducting a series of test games. In order not to go crazy, changing the parameters in each formula and in each cell back and forth, a very convenient solution is a separate reference tab, where all the custom parameters are displayed, and the formulas of the rest of the table refer to them. Using the directory, you can also, for example, change the language of the game with one switch of the checkbox. Such a function is implemented in the game "World Domination".



Entering data into the system

To prevent the game from sagging, the host should be able to enter data into the table as quickly and ergonomically as possible. In Google tables, you can configure the data input format (dada validation). The fastest and most convenient format is the checkbox. True, not all formulas can work with TRUE and FALSE values. Therefore, it is better to immediately translate the values ​​of the checkbox into zero or one in some neighboring technical cell, and then do whatever you want with these zero and one.

A drop-down list is also a very convenient input format if more than two values ​​are assumed in a cell. The presence of ready-made options in the list protects the presenter from typos, which in the game can play a critical role.

Here is an example of data entry fields in World Domination.



Event indication

It is important for the facilitator to notice game events in time and respond to them. So, in the "Emergency Meeting" you need to notice the changes in the cells with the numbers of cards that need to be issued to the players in time. And in the "World Domination" you need to inform the players in time that the city has undergone a nuclear strike. So that the presenter does not miss anything, you can use the property of the cell conditional formatting (conditional formatting) - with its help you can, for example, make it so that when the text "city is destroyed" appears, the cell itself is painted in red. In the field of view of the presenter there will be a bright red spot that is impossible not to notice.



Individual Information

There may be situations in the game when some of the data is known to one team and the other is not. For example, in the game World Domination, information about the presence of nuclear weapons in a particular country is classified. In this case, it is convenient to use separate output devices for each command, and to display only publicly available information on the main screen. Technically, this is solved as a group of separate tables (a file for each team) into which only the necessary information is pulled from the master table using the IMPORTRANGE function.

By the way, Google Sheets provides the ability to export data from a table to the web interface , so if you want, instead of a dry table, players can display data on tablets in the form of a beautifully laid out and animated infographic.

Neat graphics

In the game, sometimes, instead of numbers, you want to display a graph, but standard diagrams annoy you with their redundancy. And also because they don’t know how to stick to the cells and always look very messy. Fortunately, Google tables have a SPARKLINE function. This function displays a mini graph inside the cell. It has certain settings, and it can be used for laconic visualization of some data.



Gaming News

Sometimes it is required not only to display a number in a cell, but also to formulate a thought in the form of a competently constructed sentence (for example, in the "Latest News" section). In this case, the CONCATENATE function comes to the rescue, which can glue pieces of text with digital values ​​from different cells. Here's what the formula looks like using this function.



Summarizing

In “World Domination” and “Plague of Venice”, not only the main winner, but also the teams that distinguished themselves in various nominations are announced in the final of the game. To summarize, it is very convenient to use the MAX function, which calculates the maximum number in the range. The host does not need to do anything extra - just read the names of the winning teams from the screen.