We have been organizing ski trips since the beginning. I remember we could fit in a car when we started, then in a pickup van, we later had to rent a couple of cars to be able to go, soon we had to share a bus with other people and now we need an entire bus for us.

Getting people to join

Once you have a date and a location, you can create a form with all the available options and share it via email, slack or whatever. We used to create it using Google Forms because it integrated with Google Spreadsheets and was easy to use, but now we use Typeform. It’s beautiful and now also integrates with Spreadsheets.

Typeform captured results in the Google Spreadsheet

Calculating each individual price

Our ski trip has a lot of variability. One individual might want to rent gear while another one already owns it. Some individuals might want to snowboard and others might want to rent the highest quality skis available. Some need classes and some want an insurance. Some people don’t even want to ski, but just spend the weekend in the snow.

This results in a situation where almost everyone has a unique price and although it was easy to calculate and handle when we were 5, it gets harder when you are 50.

You could perform a 200 character long nested IFS function to calculate the price for every row in the Spreadsheet, but it’s easier to just code your own more readable and maintainable Google Script function.

From the ‘Tool’ menu you can access the ‘Script Editor’ and there you have a lot of freedom to write your own functions.

I coded the ‘skiPrice’ function to calculate the final price for every possible combination, depending on the arguments the function received. Once coded, you can call the funcion as if it was a normal Spreadsheet function, sending cells as parameters.

The ‘skiPrice’ function calculates the final price

Sharing the info with the agency

Once you have all this information available you have to share it with the agency so they can check all prices are correct and to generate the insurance policies for the people who are paying for them. You could copy and paste or export the document but the best option is to share the live spreadsheet so they always see updated information. You could share with them the whole spreadsheet as “read-only” but you might have information or sheets you don’t want to share with them.

The best approach is to create an ‘Agency’ sheet in the same spreadsheet, with all the information you want to share with the agency. Then create a new Spreadsheet and in the first row write:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/[YOUR_SPREADSHEET_ID]/edit";"Agency!A1:J60")

This will create a new Spreadsheet with only the sheet you want to show the agency, so you can now share it. Whenever you update the original Spreadsheet this mirror sheet will conveniently keep up to date.

Send custom payment emails from Spreadsheets

Once again this is a task that I could afford to do manually when we were less people but now doesn’t scale. Using the power of the Google Scripts I generate the corpus of the email, containing the options each user selected.

In the actual Spreadsheet I calculate the total price and generate the email corpus in one function call and a single iteration, but it’s easier to explain it separately.

The function ‘mailText’ generates a custom email corpus showing the user the options he chose and showing the total amount to pay. The funcion takes all options as parameters.


Once the email corpus is generated, a final script run directly from the script editor iterates through every row in the Spreadsheet and sends an email to each user containing the custom corpus.

And every user gets the email in their inbox.

Conclusion

The powerful combination of Typeform, Google Spreadsheets and Google Scripts is great, but after organising ski trips for years I see a clear next step. A modern ski agency app that would let companies and individuals organize their custom ski trips adjusting every detail to their budgets and keeping all the information, communication and payment processes in one place. Considering there is more than 6M skiers only in Spain we are looking at an interesting market.

How does Skitnig sound for a new startup name?

Previous ArticleNext Article

Leave a Reply

Your email address will not be published. Required fields are marked *

C.

Composing the ‘n!’ sound

 

There is always a moment in my daily life at itnig when a startup asks for a video. Sometimes it’s an ad, sometimes an event, sometimes it’s just a tutorial, but no matter what it is, it comes with the need of creating a sound that represents the company and can be played at the start of the video along with the appearance of their corporate logo.

This short sound must represent their essence, it has to have their DNA reflected somehow: a representation of their values, their culture or maybe even something related to their name or logo. It is mainly an artistic process that requires inspiration but in some cases the startup name and culture is geeky enough to allow some rational thinking into the music composition process. Last week I crafted the sound for our startup Factorial, inspired by the mathematical operation their name represents.

Assigning values to notes and choosing the first note

Putting a mathematical operation into sound requires a bit of imagination as well as a set of rules to get started. The first step was assigning values to notes. I decided to assign C4 the value 1. It is the middle key in a piano and also the middle C according to the International Pitch Notation so it seamed and appropriate value for that key.

1_2csM4IkMT5OMJLT889kA8w.png
Piano keys, notes and assigned values

Now we had to decide which factorial operation to represent. That is, choosing an x to which we would perform x! and represent it. Being the startup name Factorial and given that it starts with an F, it made sense to perform the operation on F4, the first F we would find after C4.1_XXstZcEGfSkP4fOQiouJdA.png

Performing the n! operation

Now that we had chosen to start at F4 we just had to assign notes to the operands in the operation and put them in the score.

4! = 4*3*2*1 = F4 E4 D4 C4

1_PoPubz4QQ0XrKpFqGntTPg.png

Now we had a simple downwards scale without much musical interest but how could we enrich the melody and still make it part of the factorial operation?

Making the middle operations sound too

As we manually start to calculate the factorial operation of a number and before we get the result, we obtain partial operands that are part of the process. How would the melody sound if we added those partcial numbers to the score?

The size of the partial operands makes the need to place them on a staff above obvius. Also because of the sequentiality of the operation we put the partial operands once we have been able to obtain the result, that is after the first note and while the second note is playing.

1_T4nZHkQywtBFrcCeySZaCw.png

The first partial operand is obtained after multiplying F4 and E4 wich is the same as 4*3 which equals 12 that represents a G5 if we check the keyboard note to number assignation.

1_s_3_aZPsjj_MZ3rXcHlTqA.png

F4 * E4 * D4 = G5 * D4 = 12 * 2 = 24 = E7

If we keep calculating we obtain the note E7, which is the result of multiplying F4 * E4 * D4.

1_HwTs4jf2xk612x7jjg8bpw.png

Finally, we obtain the same E7 after multiplying the previous result by C4, which has the value 1.

Final result

If you are curious to listen to how this mathematical representation of the factorial operation sounds like, play the video below.